The

 SET INTEGRITY

 statement has many options like turning integrity on only for 

new data, turning integrity off, or specifying exception tables with additional 

diagnostic information. To read more about the 

SET INTEGRITY 

command refer 

to: 

http://www 306.ibm.com/cgi bin/db2www/data/db2/udb/winos2unix/support/v

8document.d2w/report?fn=r0000998.htm

9.2.2  Data checking

Scripts performing logical data integrity checks automate the data verification 

process and save administrator effort.

For small tables (with less that 50,000 rows) you can write a program that 

compares data byte by byte. The program can extract sorted rows from MySQL 

and DB2 UDB to files in the same ASCII format. The files should be then binary 

compared (on Linux use the 

diff

 command) and checked to determine if they 

are the same. 

For larger tables, comparing all rows byte by byte can be very inefficient. The 

data migration should be evaluated by comparing aggregate values like the 

number of rows. To do this you can create a special table for storing the 

information about the number of rows in the source MySQL database. Table 

CK_ROW_COUNT

 presented in Example 9 4 can be used for that purpose.

Example 9 4   Table for storing number of rows (MySQL)

CREATE TABLE CK_ROW_COUNT (

   TAB_NAME VARCHAR2(30),    table name

   ROW_COUNT INT,    number of rows

   SYS_NAME CHAR(3),    code to distinguish the system: MYS or DB2

   TIME_INS DATE    time when the count was performed

For each table you should count the number of rows and store the information in 

the 

CK_ROW_COUNT 

table. The following 

INSERT

 statement can be used for that 

purpose:

insert into ck_row_count select  TAB_NAME , count(*),  MYS , 

sysdate() 

from TAB_NAME

The table 

CK_ROW_COUNTS

 and its data

 can be manually migrated to the target 

DB2 database. Example 9 5 presents the DB2 version of the table.

 Chapter 9. Testing and tuning 

273