Commands for data and DB2 maintenance

Data preservation allows you to control how data appears in your target database when you change the data structures or metadata or when you migrate data from one table to another.

Optim™ Database Administrator supports extended alters. Extended alters are required when an ALTER statement cannot be used to easily implement the change. An extended alter saves and preserves the table data, drops and re-creates the table, and then reloads the data. In addition, data also must be saved and preserved when you migrate data from one table to another.

When you click the Preview Commands link in the Change Management Script Editor, Optim Database Administrator generates the commands for the changes. The product also automatically generates unload and reload commands when it detects that data must be preserved. DB2® maintenance commands are also automatically generated. You can then click Data Options to start the Customize Data Preservation wizard if you want to modify the commands. The wizard guides you through the process of modifying the unload and reload methods to be used, modifying the unload commands and the reload commands, and changing which DB2 maintenance commands are generated.

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, especially in case you need to undo the changes.
When you create a table
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 Change Management main menu item. You can then customize the data that you want to load by clicking Data Options in the Commands section of the Change Management Script Editor. The Customize Data Preservation wizard starts. The wizard guides you through the process of mapping the data that you want to unload from one table and load into the new table.
Tip: To populate a new table with data from a file, you can use the LOAD or IMPORT utility. In the Object List, right-click the new table, and select the appropriate action from the menu.
When you perform a destructive change
When you make a change that requires a table to be dropped and then re-created, you must unload the data in that table and then reload it.
When you migrate data from one table to another
When you migrate data, you must unload the data from the source table and then reload the data to the target table.

On the Unload and Reload Information page of the Customize Data Preservation wizard, you can perform several actions. You can select a table and select the Change query check box to customize the unload command for the table. Similarly, you can select the Change mapping check box to customize the reload command. Additional wizard pages that assist you in customizing the unload and reload commands are displayed. Customizing 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 Customize Data Preservation wizard.

Unload and reload command methods

Optim Database Administrator supports several different methods for unloading and reloading data. You can choose and customize the method that is used for unload and reload operations on the Specify Data Unload and Reload Information page of the Customize Data Preservation wizard.

The type of unload command method that you choose determines whether data preservation is external or internal, as shown in the following table:
Table 1. Supported unload methods and data preservation type
Unload provider Type of data preservation
EXPORT for DEL data format External
EXPORT IXF data format External
High Performance Unload (DB2 HPU unload commands) External
Internal data preservation provider Internal
Internal data preservation cursor provider Internal

With external data preservation, data is saved to external files. The data is unloaded to the external files with either Export commands or DB2 HPU. The data is reloaded from the external files with Import or Load commands, depending on the reload command method that you choose. When you are migrating data from one table to another, you must use external data preservation because the data must be saved to an external file.

With internal data preservation, data is saved internally within the database. When a table is changed, the table in the database is renamed to create a shadow table. After the table is re-created, the data is moved from the shadow table into the re-created, changed table. The data is moved either with INSERT statements or by using a load from cursor, depending on the unload command method that you selected.

By default, shadow tables are renamed with a prefix of 'SHAD_'. If a table with that name exists, that table is dropped before the shadow table is created. You can customize the options for the internal unload methods to specify that a different prefix be used, or to create the shadow table with a different prefix if a table with that name exists.

Internal data preservation can be faster than external preservation because I/O to files is not required. However, ensure that you have adequate space in your database when you use internal data preservation.

Requirement: To specify High Performance Unload as the unload method, DB2 High Performance Unload (HPU) for Multiplatforms or DB2 High Performance Unload (HPU) for Workgroups must be installed. Otherwise, the generated unload commands will fail. These products are separately priced and separately installed.

When data is reloaded into a table that has a trigger, the trigger is activated only if the data is reloaded with Import commands or from a data file with INSERT statements. The trigger is not activated when the data is reloaded with Load commands or from a data file using a load from cursor because the Load utility cannot enforce the business rules that are associated with a trigger. If you do not want triggers to be activated, use Load commands (or load from cursor), or modify the generated change commands so that any triggers are created after the data is loaded.

Important: If you choose a reload method that activates triggers, check the change commands file to verify the order that the data is reloaded. Optim Database Administrator does not automatically reload the data in a manner that activates the triggers in the way that you intend

Advanced data preservation techniques

Advanced data preservation techniques include:

Dropping columns
Dropping columns can be easily managed by customizing the generated unload and reload commands. You can change the commands to ensure that the data in the dropped columns is preserved. You can also customize the reload command to ensure that the unloaded columns are mapped to the reloaded columns appropriately.
Adding NOT NULL columns
Adding NOT NULL columns is simple for columns that have a default value. If a column does not have a default value, customize the SELECT clause in the unload command on the Customize Unload Commands page of the Customize Data Preservation wizard.
Using the Auto Cast function
If the data types of the unload and reload columns are mismatched, you can use the Auto Cast function to resolve the mismatch. When you select Auto Cast on the Unload and Reload Information page or the Customize Unload Commands page of the Customize Data Preservation wizard, Optim Database Administrator automatically adds a CAST column function in the SELECT clause of the export or unload statement. You can select Default Query to revert to the default SELECT clause.
Restriction: Any additional changes that you make to the SELECT clause are lost when you select the Auto Cast or Default Query options.

Supported DB2 maintenance commands

Throughout the change management process, 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 DB2 maintenance commands include:
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. Optim Database Administrator will issue REORG TABLE commands by default when forward engineering a model, unless you clear that option on the Maintenance Commands page of the Customize Data Preservation wizard. Optim Database Administrator will also automatically generate runstats commands to refresh the statistics after a table is reorganized. You should also rebind all of your application packages to take advantage of the reorganized data.

For more information about the impact of reorg commands, see the DB2 Command Reference.

Rebind commands
You need to 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. Optim Database Administrator generates rebind commands for the packages that are affected by the changes defined in the change management script.

Flush package cache commands
After the statistics are updated, flush the package cache so that the dynamic SQL statements will use the updated statistics, which will enhance the performance.

Feedback