Commands for data preservation and DB2 maintenance

When you make database object changes, data might need to be unloaded and then loaded into data objects. Having certain DB2® maintenance commands in your change commands is also helpful for ensuring that packages are rebound, statistics are updated, and data is reorganized.

Data preservation is enabled by default. When IBM® Data Studio detects that a destructive change has occurred, which means that an object must be dropped and re-created, the data is unloaded and then loaded back into the newly created object. In addition, a subset of DB2 maintenance commands are also included in the generated change commands by default, if they are needed. You can change these default options.

Saving data

Data preservation is useful in these situations:
When you perform a destructive change
When a table must be dropped and then re-created to be changed, data must be unloaded and then loaded into the newly created table.
When you drop a table
You might want to store the data from a dropped table in a file for future use. For example, you might need to undo the changes and restore the data.
When you create a table
You might want to populate the table with data from another table. You can migrate data to the table by using the Migrate Data action under the Migrate main menu item.
When you migrate data from one table to another
Data must be unloaded from the source table and then loaded into the data to the target table. You can migrate the data by using the Migrate Data action under the Migrate main menu item.

Unload and reload command methods

IBM Data Studio uses the export utility to unload data and the import utility to load data. The export utility uses an IXF file format with a few exceptions, such as in a data partitioning environment, when a DEF file format is used instead.

When data is reloaded into a table that has a trigger, the trigger is not activated. The load utility cannot enforce the business rules that are associated with a trigger.

DB2 maintenance commands

As you make changes, specific database packages might become inoperative and statistics might become inaccurate. For example, DB2 marks packages as invalid or inoperative when you drop objects. You might need to issue rebind commands to re-create the packages based on the most current statistics.

The following commands are included in the generated commands by default, if they are needed, but you can choose not to have them included:
RUNSTATS commands
Regenerating statistics is important after a database is changed or after data is loaded into tables.
REORG commands
You should reorganize all indexes when a table is altered. You should also reorganize all tables and indexes when a table space is altered. You should also rebind all of your application packages to take advantage of the reorganized data.
REBIND commands
You must rebind packages if your change commands contain the following DROP statements:
  • TABLE
  • TRIGGER
  • MQT
  • UDF
  • VIEW
  • ALIAS
  • INDEX
  • STRUCTURE TYPE

If you are making several changes, you should rebind your packages to improve performance. IBM Data Studio generates rebind commands for the packages that are affected by the changes that are defined in the change plan.

FLUSH CACHE PACKAGE statements
After the statistics are updated, flush the package cache so that the dynamic SQL statements use updated statistics, which enhances database performance.

Feedback