I'm working on a migration project and it involves DB2 to SQL server migration. While migrating existing data we face problems particularly with columns of type 'TIME'. This is because in DB2 the max value for 'TIME' field is 24:00:00 where as in SQL server it is 23:59:59 and all the rows which has '24:00:00' value for columns of type 'TIME' are errored out. So I am analysing on the tables with such rows and the respective programs which updates or inserts TIME values into those tables.
I collected the list of tables having columns of data type 'TIME' and column names by querying against the catalog tables SYSTABLES and SYSCOLUMNS. Now for each table in the list, I am trying to find the number of rows with '24:00:00' value for any of the columns. I acheived this by a DB2 COBOL program that uses dynamic SQL.
Input file: table1 column1
My cobol-DB2 program does the following end of input file
1. Read all records for a table
2. Build query (eg:- qry : Select count(*) from table1 where column1 = '24:00:00' or column2 = '24:00:00')
3. prepare statement from qry1
4. open cursor
6. closing cursor
7. write into ouput
8. go to step 1
Though I got the required output, I am not satisfied with its performance. It is not just the DB2-COBOL program but the query also takes time. Can anyone please suggest other alternatives to acheive this?