Backing up your settings for database
and database manager configuration parameters before DB2® server upgrade, or conversion to a DB2 pureScale® environment,
allows you to verify DB2 server
behavior after upgrade, or converting to DB2 pureScale environment,
and to recreate instances and databases.
In
addition, you can collect information from your DB2 servers about the database system catalogs, DB2 registry variables settings,
explain table data, and diagnostic information. This information can
help in problem determination if you encounter any post-upgrade or any post-conversion to a DB2 pureScale environment, differences
in the database manager behavior or performance.
Before you begin
You must have SYSADM authority in order to execute all
of the following tasks, although some tasks require lesser authority
privileges or none.
Procedure
To back up your DB2 server
configuration and diagnostic information:
- Collect information from your DB2 servers
by running the db2support command for all your
databases that you are going to upgrade, or convert to DB2 pureScale,
in all your instances. This command allows you to collect
information about the database system catalog, database and database
manager configuration parameters settings, DB2 registry variables settings, explain table
data, and diagnostic information required by DB2 support in case of problems.
db2support output-directory -d database-name -cl 0
The -cl
0 parameter collects the database system catalog, database
and database manager configuration parameters settings, DB2 registry variables settings. The information
collected is stored in the db2support.zip compressed
zip file under the output directory. A summary report in HTML format
is included. In the db2supp_opt.zip file that
is also included, you should check the optimizer.log file
to verify that the collection of information was performed successfully.
Keep
this zip file for several months after you complete the upgrade, or
conversion to DB2 pureScale.
The information in the zip file can help in quickly resolving any
performance issues with the new release.
- Back up the information about all the packages for your
applications associated with each database. Use the following command
to list packages associated with your databases and redirect the command
output to a file:
db2 LIST PACKAGES FOR SCHEMA schema-name
SHOW DETAIL > /upgrade/sample_pckg.txt
The
FOR SCHEMA clause allows you to list all packages for a specific schema,
if your application has several schemas you need to repeat this command
for each schema name or use the FOR ALL clause.
db2 LIST PACKAGES FOR ALL
SHOW DETAIL > /upgrade/sample_pckg.txt
- If you enabled the audit facility, back up the audit configuration
of your instances by issuing the following command:
db2audit describe > audit_instance-name.cfg
If
you have multiple instances, repeat this command for each instance.
- Back up all your external routines. The following example shows how to backup all external routines
created using the default path in UNIX operating
systems:
cp -R $INSTHOME/sqllib/function $INSTHOME/routine_backup
Where INSTHOME is
set to the home directory of the instance owner. If you have specified
a full path that is not under the default routines path when you created
your external routines in the database, you must ensure the existing
libraries remain on their original location.
- Optional: The db2support command
HTML report includes the database manager configuration parameter
settings for the instance that owns the specified database. You can
use the GET DATABASE MANAGER CONFIGURATION command
to back up your settings for database manager configuration parameters
and redirect the command output to a file to save these settings for
each instance:
db2 GET DBM CFG > dbm_instname.cfg
where instname is
the instance name.
- Optional: The db2support command
HTML report includes the database configuration parameter settings
for the specified database. You can use the GET DATABASE
CONFIGURATION command to back up your settings for database
configuration parameters and redirect the command output to a file
to save these settings for each database:
db2 CONNECT TO database_alias
db2 GET DB CFG FOR database_alias
SHOW DETAIL > db_database_alias.cfg
where database_alias is
the database alias. The SHOW DETAIL clause displays
the values calculated by the database manager when configuration parameters
are set to AUTOMATIC.
Database configuration
parameters can be the same on each database partition in a partitioned
database environment. If they are not the same, back up the database
configuration parameter settings for each database partition.
- Optional: The db2support command
generates a file with the output of the db2look command
for the specified database. However if you need additional information
not present in the generated DDL file, you can use this command to
save the DDL information for your databases and the statements to
re-create your database objects:
db2look -d sample -e -o sample_tbs.db2 -l -x
- Optional: The db2support command
HTML report includes the environment and registry variable settings
for the instance that owns the specified database. You can use the db2set command
to back up your DB2 profile
registry variables settings and redirect the command output to a file
to save these settings:
db2set -all > reg_instname.txt
If
you set DB2 environment variables,
use the appropriate system command to list environment variables and
their values. For example, on AIX® you
can issue the following command:
set |grep DB2 > env_instname.txt
When
possible, use the output from the set command and run the db2set command
to set these environment variables as registry variables in the DB2 profile registry.
What to do next
Take your server offline before converting to a DB2 pureScale environment.