Example 9 5   Table for storing number of rows (DB2 UDB)

CREATE TABLE CK_ROW_COUNT (

   TAB_NAME VARCHAR(30),

   ROW_COUNT INT,

   SYS_NAME CHAR(3),

   TIME_INS TIMESTAMP

)

On the DB2 system, you should repeat the counting process with the equivalent 

INSERT

 statement:

insert into ck_row_count select  TAB_NAME , count(*),  DB2 ,  CURRENT 

TIMESTAMP from  TAB_NAME

After performing the described steps, DB2 table

CK_ROW_COUNT 

should contain 

information about the number of rows counted on MySQL and DB2 databases. 

The records in the table should look like Example 9 6.

Example 9 6   Sample table CK_ROW_COUNTS contents

select TAB_NAME, ROW_COUNT, SYS_NAME, TIME_INS from CK_ROW_COUNT

[...]

TABLE_A 39001 MYS 2004 02 13 10.13.39

TABLE_A 39001 DB2 2004 02 13 10.32.13

TABLE_B 60003 MYS 2004 02 13 10.15.29

TABLE_B 60002 DB2 2004 02 13 10.33.49

[...]

Having the information about the number of rows in a SQL table is very 

convenient, because with a single query you can get the table names that 

contain a different number of rows in the source and target database:

select tab_name from (select distinct tab_name, num_rows from CK_ROW_COUNT) 

as t_temp group by t_temp.tab_name having(count(*) > 1) 

The presented approach for comparing the number of rows can be extended for 

additional checking like comparing the sum of numeric columns. Here are the 

steps that summarize the technique:

1. Define check sum tables on the source database and characterize scope of 

the computation.

2. Perform the computation and store the results in the appropriate check sum 

tables.

3. Migrate the check sum tables as other user tables.

4. Perform equivalent computations on the target system, and store the 

information in the migrated check sum tables.

274 

MySQL to DB2 UDB Conversion Guide