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 typeUnload 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.