DB2 Version 9.7 for Linux, UNIX, and Windows

Backing up DB2 server configuration and diagnostic information

Backing up your settings for database and database manager configuration parameters before DB2® server upgrade, allows you to verify DB2 server behavior after upgrade, and to re-create 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 that can help in problem determination if you encounter any post-upgrade 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 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. 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.