Section 12.10. Case Study

12.10. Case Study

Assume your company wants to deploy a new accounting application very soon, but the finance department director has demanded a more thorough testing. The only test machine that is available for testing has DB2 for Windows installed. However, you need to obtain data from a DB2 for AIX database server. Since the source and target platforms are different and not every table and views are required for testing, you choose to use data movement utilities to move data to the Windows server.

First, you connect to the source server and then export the required tables with this command:

 export to newsalary.ixf of ixf   messages newsalary.out   select empno, firstnme, lastname, salary * 1.3 as new_salary     from employee    where workdept='A00' 

You find out that the accounting application needs all of the 100 tables under the schema acct. To save the time and effort of typing the export command for each of the 100 tables, you choose to use the db2move command.

 db2move proddb export sn acct 

Because the output files are in IXF format, you can create the tables and import data directly to the target database using the import utility.

 import from newsalary.ixf of ixf   messages newsalary.out   create into newsalary in datats index in indexts 

Not that a new table called newsalary is created in the datats table space and that its indexes are stored in the indexts table space.

After the first few successful completions of the import operation, you realize that you cannot finish all the imports within the estimated time. The import utility performs insert statements behind the scenes, and thus activates constraint checking, logging, and triggers. The load utility, on the other hand, goes behind the DB2 engine and loads the data directly to the pages. You can choose to perform logging as well as performing only primary and unique key checks. Thus, for the sake of performance, you decide to change the plan and use the load utility instead.

To capture all rows that violated unique constraints of the target table, you create an exception table with this statement:

 CREATE TABLE salaryexp ( empno CHAR(6), firstnme VARCHAR(12), lastname VARCHAR(15) , new_salary DECIMAL(9,2), load_ts TIMESTAMP, load_msg CLOB(2K)) 

Since you are not that familiar with the syntax of the load command, you decide to use the Control Center to invoke the load utility. Each graphical tool has a Show Command button. You click on this button because you want to store the load command generated in a script so you can use it in the future. You obtain the following command, which you can issue later:

 load from newsalary.ixf of ixf   modified by dumpfile=salarydump.dmp   rowcount 5000   messages salary.out   tempfiles path c:\loadtemp   create into salary   for exception salaryexp 

After the load is completed successfully, the table is not accessible (by default) due to table space backup pending. Therefore, you need to perform a table space or database backup (see section 13.4, Performing Database and Table Space Backups).

If the table has any constraints defined such as referential integrity and check constraint, you need to validate the data integrity with the following command:

 set integrity for newsalary immediate checked 

The target tables should be ready and accessible for testing.