IBM Mainframe Forum

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
table1 column2
table2 column1
table3 column1
table3 column2
....
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
5. fetch
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?

Thanks

it probably will need to do a tablescan on each of the tables, so I don't think you can do anything about it.
if you runstats regularly, you might get lucky and find it in HIGHKEY of SYSIBM.SYSCOLSTATS :

Thank you GuyC ..
I did not choose to use sysibm.syscolstats initially as I need the count of rows in each table with '240000' value. But given the number of tables I have to scan, I thought of using the query you suggested to get the list of tables that have '240000' value in its TIME columns and then use my 'select count' query on these tables. However the query you gave returned only a few tables (Even after removing cs.highkey = x'240000').
As per IBM manual

SYSIBM.SYSCOLSTATS table
Contains partition statistics for selected columns. For each column, a row exists for each partition in the table. Rows are inserted when RUNSTATS collects either indexed column statistics or non-indexed column statistics for a partitioned table space. No row is inserted if the table space is nonpartitioned. Rows in this table can be inserted, updated, and deleted.

My understanding it is that, apart from RUNSTATS not being run recently it is because the respective tablespaces are not partitioned, most tables (columns) are not there in SYSCOLSTATS. Is that right?

Thanks,
LML