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.