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:
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.
export to newsalary.ixf of ixf
messages newsalary.out
select empno, firstnme, lastname, salary * 1.3 as new_salary
from employee
where workdept='A00'
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.
db2move proddb export sn acct
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:
import from newsalary.ixf of ixf
messages newsalary.out
create into newsalary in datats index in indexts
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:
CREATE TABLE salaryexp
( empno CHAR(6), firstnme VARCHAR(12), lastname VARCHAR(15)
, new_salary DECIMAL(9,2), load_ts TIMESTAMP, load_msg CLOB(2K))
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:
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
The target tables should be ready and accessible for testing.
set integrity for newsalary immediate checked