Data preservation

Data preservation allows you to control how data appears in your target database when there are changes to the data structures or metadata. Whenever change commands are generated that involve CREATE and DROP table statements, that data will likely need to be preserved. You can create the necessary export and import commands by using the data preservation options in DB2® Change Management Expert.

DB2 Change Management Expert allows you to preserve your data from the Change Commands page in the Deployment Script Editor.

Data preservation is useful in the following situations:
When you drop a table
When you drop a table, you might want to store the data from that table in a file for future use. DB2 Change Management Expert automatically generates this file, but this functionality can be disabled.
When you create a new table
When you create a new table, you might want to populate the table with data from an existing data file. You can map the data that you want to export from one table and import into another table by using the Generate Change Commands wizard.

After you generate the change commands, you can manually edit the change commands to import the data from the data file. If you edit the change commands manually, verify that the import commands are added to the change command list after the other change commands.

When you perform a destructive change
If you rename a table by using a DROP and CREATE statement, you will need to export the data in that table and then import it. DB2 Change Management Expert automatically creates data preservation entries, one with the export commands to drop the table (old name), and another with the import commands to create the table (new table name). You can use the pair function to combine these two entries into one. This way, the export and import commands have the same data file.

You can toggle the Customize field to make data preservation entries read-only or customizable. When an entry is customizable, additional wizard pages that assist with the customization of export and import commands are available. Customization of the data preservation change commands is necessary to correctly deploy certain changes.

Restriction: When you generate change commands, the default data preservation entries are always regenerated. Previously generated data preservation commands are not brought in as part of rerunning the Generate Change Commands wizard.

To skip the data preservation process, uncheck the Enable data preservation option in the Generate Change Commands wizard.

Advanced data preservation techniques

Advanced data preservation techniques include:

Dropping columns
Dropping columns can be easily managed by creating a new data preservation entry. One approach is to accept the default during export customization. For example, if you enable undo by selecting that check box, an export command will automatically be generated. However, when you customize your import commands, you must map the exported columns to the imported columns.
Adding NOT NULL columns
Adding NOT NULL columns is simple provided that the column has a default value. If there is no default value, customize the export command by using the Customize Export Command page of the Generate Change Commands wizard or use the Generate Data Migration wizard.
Normalizing tables
There are many strategies for normalizing a table. These techniques range from creating new normalized tables and transforming them in the database to transforming during the export and import of data.
Start of changeMoving data between databasesEnd of change
Start of changeMoving data between databases requires exporting data from one database and importing data into another. DB2 Change Management Expert supports data migration with the Generate Data Migration wizard.End of change
Start of changeLOAD ProvidersEnd of change
Start of changeDB2 Change Management Expert now supports LOAD commands as well as IMPORT commands. You can specify the default load provider for the data load operations by using the preferences page (Windows > Preferences). You can change the load provider for a particular load operation by selecting Data Load Provider from the drop-down menu under Mapping Details on the Mapping Tables page. DB2 Change Management Expert also supports the IMPORT provider. You can specify the command options for all providers on the Specify File Information page of the Generate Change Commands wizard.

For more information about the DB2 LOAD and the DB2 IMPORT commands, see the IBM® DB2 Command Reference.

End of change
Start of changeUNLOAD providersEnd of change
Start of changeData Unload Providers generate different types of unload commands. The following table shows the three different providers that DB2 Change Management Expert supports and the corresponding file formats for the EXPORT commands:
Table 1. Supported unload providers and their corresponding file formats
Unload provider Corresponding file format for EXPORT commands
EXPORT_DEL DEL data file format
EXPORT_IXF IXF data file format
HPU HPU unload commmands
In order to deploy the generated HPU unload commands, the DB2 High Performance Unload (HPU) for Multiplatforms (or Workgroups) product must be installed.
Attention: DB2 Change Management Expert supports the DB2 High Performance Unload (HPU) for Multiplatforms (or Workgroups) product to unload your data. However, note that HPU is a separately priced tool and must be installed before setting the DB2 Change Management Expert option for Data Unload Provider to HPU. If you set the Data Unload Provider to HPU and HPU is not installed, the generated unload commands will fail.
You can unload data using either the HPU command or a DB2 EXPORT command. You can specify the default unload provider on the preferences page by selecting Windows > Preferences > Data > Change Management Options. If you select HPU as the default provider, then DB2 Change Management Expert generates HPU commands for all of the unload operations. You can override this behavior for any particular unload operation by selecting a different Data Unload Provider on the Mapping Tables page of the Generate Change Commands wizard.

For more information about High Performance Unload, see the IBM DB2 High Performance Unload for Multiplatforms and Workgroups User's Guide V3.1.

For more information about EXPORT commands, see the IBM DB2 Command Reference.

End of change
Start of changeUsing REORG commandsEnd of change
Start of changeDB2 Change Management Expert now supports the REORG maintenance command. DB2 Change Management Expert can generate REORG commands for you through the Generate Change Commands wizard. When a table space is altered, DB2 Change Management Expert generates a REORG command for all of the indexes and tables contained within the table space. When a table is altered, DB2 Change Management Expert generates a REORG command for the indexes defined on the table. REORG commands are optional. End of change
Start of changeUsing the Auto CAST functionEnd of change
Start of changeIf the data types of the import and export columns are mismatched, DB2 Change Management Expert resolves them automatically by adding a CAST column function in the SELECT clause of the EXPORT statement. This can be done by selecting Auto Cast on the Customize Export Commands page of the Generate Change Commands wizard. There is another option, Default Query that allows you to revert to the default SELECT clause.
Restriction: If you make any additional changes to the SELECT clause, they will be lost when you select the Auto Cast or Default Query options.
End of change
Related tasks
Generating change commands from the Deployment Script Editor
Generating data unload and load commands for data preservation
Migrating data


Feedback

Copyright IBM Corporation 2006, 2007. All Rights Reserved.