Running UNLOAD and LOAD processes on Db2 to create a backup database

Edit online

You can use Db2® for z/OS® to unload your existing version of each IBM® Engineering Lifecycle Management (ELM) database and use the unloaded data sets to create a backup database.

You can use the unloaded data sets to create a backup database using either an existing database or creating a new database with a new database schema prefix. You can use the backup database you create to restore your database if you have any problems during an upgrade from one version of Engineering Lifecycle Management to another.

To automate the process, the SMP/E package includes programs and JCL samples. Use these programs with the standard Db2 for z/OS utility programs to back up your Engineering Lifecycle Management repositories.

These programs are compatible with the current and previous versions of IBM Engineering Workflow Management (EWM) in Engineering Lifecycle Management. You can only unload and load database tables for the same version of the product. It is recommended you use the latest available version of these programs.

Overview

The process involves generating UNLOAD templates based on the database being unloaded, then running the UNLOAD. The UNLOAD process will unload data in sequential data sets. The process will generate the LOAD utility control statements to be used later in the LOAD process. Part of the process will merge the separate LOAD utility control statements into a single sequential data set and edit this data set to modify some of the load parameters. Then the LOAD runs, and after it runs, the REPAIR utility control statements are generated based on the database being loaded. Finally, a REPAIR step runs.
Important: These tasks should be performed by an experienced Db2 database administrator. Do not attempt to run these jobs if you are not familiar with Db2 utilities. Test this process carefully, in particular the LOAD, with test data or on a test LPAR before using the process on your production data. Back up to a separate Db2 for z/OS database before using any production data.

Shipped components to aid backup and recovery

The following components are included in the SMP/E package in FMID HRCC702.
BLZUNLD
A program in hlq.SBLZLOAD that uses parameters that are passed to it to run SQL statements that generate the Db2 for z/OS UNLOAD utility control statements. The UNLOAD utility control statements are required to perform the unload process.
BLZBIND
JCL member in hlq.SBLZSAMP that is used to run the Db2 for z/OS BIND for programs BLZUNLD and BLZREPR (sample JCL).
BLZDB2UN
Sample JCL member in hlq.SBLZSAMP that is used to unload the ELM Db2 for z/OS database (sample JCL).
BLZDB2ED
Sample REXX member in hlq.SBLZSAMP that edits the generated unload statements for LOB tablespaces to remove the DB2_GENERATED_ROWID_FOR_LOBS statement prior to running the unload of the LOB tablespaces.
BLZSYSIN
Sample REXX member in hlq.SBLZSAMP that merges the SYSIN files generated by the UNLOAD process and then completes the following edits on the merged SYSIN file:
  1. Change the schema prefix to a new schema prefix if you are loading to a new set of tables.
  2. In the LOAD utility control statements, change RESUME YES to RESUME NO REPLACE.
BLZREPR
A program in hlq.SBLZLOAD that uses the Db2 for z/OS database parameters passed to it to run SQL statements that generate the Db2 for z/OS REPAIR utility control statements. The REPAIR utility control statements are required to perform the REPAIRs following the LOAD.
BLZDB2LD
Sample JCL member in hlq.SBLZSAMP that is used to load the ELM database tables.
BLZDWHUN
Sample JCL member in hlq.SBLZSAMP that is used to unload the ELM data warehouse tables.
BLZDWHLD
Sample JCL member in hlq.SBLZSAMP that is used to load the ELM data warehouse tables.

Initial setup required before running the unload process

Before you run the unload process, you must modify and run the BLZBIND job in hlq.SBLZSAMP to create the Db2 for z/OS PLAN for the BLZUNLD and BLZREPR programs that will be used in subsequent steps. Follow the instructions included with the job to make the required modifications.

Considerations before running unload/load JCL

If your tables have large tablespaces, you might need to modify the JCL provided in sample members BLZDB2UN, BLZDB2LD, BLZDWHUN and BLZDWHLD to temporarily allocate more space for the tables. For example, in the Db2 Load procedures, you might need to modify BLZDB2LD and BLZDWHLD to change the space allocations of the SYSUT1, SYSMAP, SORTOUT DDs and the SORTWK* allocations.

Steps required to unload and reload the Db2 for z/OS tables

Complete the following steps to unload your existing Db2 for z/OS tables and reload a new or existing set of Db2 for z/OS tables. This step does not include the data warehouse tables, which are included in the next section.
Unload the ELM tables (except the data warehouse tables)
Sample jobs are provided that can be used to unload the tables of Jazz® Team Server (JTS), of Change and Configuration Management (CCM), and of the other applications.
Note: RM UNLOAD/LOAD is supported starting with V5.0.
Modify the sample jobs to point to the databases and schema prefixes you require.
The sample jobs provided to perform the UNLOAD task have several steps:
  1. Using the parameters provided in the jobs, call program BLZUNLD to generate the unload templates that Db2 for z/OS will use to unload the tables. The tables that contain LOB columns must be handled differently; therefore, these unloads split the UNLOAD utility control statements into two for use in later steps.
  2. Call program BLZDB2ED to edit the generated unload statements, for LOB tablespaces, to remove the DB2_GENERATED_ROWID_FOR_LOBS statements.
  3. Print the UNLOAD utility control statements for reference.
  4. Unload the tables into sequential data sets. These data sets are created using the parameters specified in the job and will have the format of <hlq>.<schema prefix>.<tablespace name>. The high-level qualifier (hlq) and schema prefix are passed as parameters to the job.
Complete the following steps to unload your ELM tables:
  1. Stop the server.
  2. If any of your ELM tables are in Db2 for z/OS, modify and submit the BLZDB2UN job in hlq.SBLZSAMP to unload the corresponding tables. Follow the instructions included with the job to make the required modifications.
  3. Run the BLZDB2UN job for each Db2 database you want to unload.

The following two tasks describe how to either load the database tables back to the same database or to a different database.

Load the ELM tables back to the same database
The sample jobs provided to perform the LOAD task have a several steps:
  1. Delete the merged SYSIN data set.
  2. Using the parameters provided in the job, call program BLZSYSIN to merge the SYSIN data sets that contain the Db2 for z/OS LOAD utility control statements into a single sequential data set. BLZSYSIN also modifies the Db2 for z/OS LOAD parameters of RESUME YES to RESUME NO REPLACE. In addition, the BLZSYSIN program removes the load of the PACKAGE_MAP and TABLE_MAP tables from each schema. These tables are created by the -createTables process. If the PACKAGE_MAP and TABLE_MAP tables are loaded from a different installation, they might cause problems.
  3. Load the Db2 for z/OS tables using the modified SYSIN Db2 for z/OS LOAD utility control statements.
  4. Call program BLZREPR to generate Db2 for z/OS REPAIR utility control statements for the next step, based on the parameter passed to the program.
  5. Run the Db2 for z/OS REPAIR utility using the utility cards created in previous steps.
Complete the following steps to load your ELM tables back to the same database:
  1. Stop the server.
  2. Drop the required set of tables. Make sure this process works with loading to a different database before you attempt this procedure. The recommended method for testing is to DROP the database and re-create it with DDL shown in the following example for the Jazz Team Server tables:
    Drop the following Database for the Jazz Team Server
DROP DATABASE JTS702;
COMMIT;

Create the following Database for the Jazz Team Server
CREATE DATABASE JTS702 STOGROUP ELMSTG BUFFERPOOL BP16K0  
 			    CCSID UNICODE;  
COMMIT;
    For additional information about creating the Db2 for z/OS databases, see Setting up Db2 for z/OS to use with Jazz Team Server.
  3. Create the new ELM tables by running a repotools –createTables command for each application. To do this, configure and run the BLZCREDB job in hlq.SBLZSAMP, which was run to initially create the tables. This job is used to create the tables of JTS, CCM, and the other applications by changing the @appl@ parameter as described in the instructions included with the jobs.

    For more information about running this job, see Creating database tables using repository tools.

  4. Modify and submit the BLZDB2LD job in hlq.SBLZSAMP to load the ELM tables. Follow the instructions included with the job to make the required modifications. Note the parameters passed to the BLZSYSIN module. Because you are using the same schema prefix you will not be required to provide a new schema prefix in variable @newPrefix@.
  5. Run the BLZDB2LD job for each set of tables you want to load.
Load the ELM tables to a new database
The sample jobs provided to perform the LOAD task have a several steps:
  1. Delete the merged SYSIN data set.
  2. Using the parameters provided in the job, call program BLZSYSIN to merge the SYSIN data sets that contain the Db2 for z/OS LOAD utility control statements into a single sequential data set. BLZSYSIN also modifies the Db2 for z/OS LOAD parameters of RESUME YES to RESUME NO REPLACE.
  3. Load the Db2 for z/OS tables using the modified SYSIN Db2 for z/OS LOAD utility control statements.
  4. Call program BLZREPR to generate Db2 for z/OS REPAIR utility control statements for the next step based on the parameter passed to the program.
  5. Run the Db2 for z/OS REPAIR utility using the utility cards created previously.
Complete the following steps to load your ELM tables:
  1. Stop the server.
  2. Create the necessary tables as documented in Setting up Db2 for z/OS to use with Jazz Team Server. Note that you are creating a new set of tables with a new schema prefix, so you must use a modified teamserver.properties file that uses the correct schema prefix. Save the old teamserver.properties file so you can use it with the existing set of tables. Ensure that you use the new, modified teamserver.properties file or the –createTables command will delete the existing data.
  3. Create the new ELM tables by running a repotools –createTables command for each application. To do this, configure and run the BLZCREDB job in hlq.SBLZSAMP, which was run to initially create the tables. This job is used to create the tables of JTS, CCM, and the other applications by changing the @appl@ parameter as described in the instructions included with the jobs.

    For more information about running this job, see Creating database tables using repository tools.

  4. Modify and submit the BLZDB2LD job in hlq.SBLZSAMP to load the ELM tables. Follow the instructions included with the job to make the required modifications. Note the parameters passed to the BLZSYSIN module. Because you are not using the same schema prefix you must provide a new schema prefix in variable @newPrefix@. The first step in this job will modify the LOAD utility control statements for you to set the schema prefix to the new value. The@dbname@ parameter represents the name of the new database into which you will LOAD the tables.
  5. Run the BLZDB2LD job for each set of tables you want to load.

Steps required to unload and reload the Db2 for z/OS data warehouse tables

If you have the data warehouse tables installed there are sample jobs provided that you can use to unload and load the data warehouse tables. Modify the sample jobs to point to the databases and schema prefixes you require.
Unload the ELM data warehouse tables
The sample jobs provided to perform the UNLOAD task have a single step:
  1. Unload the data warehouse tables into sequential data sets. These data sets are created using the parameters specified in the job and will have the format of: <hlq>.DWH.<tablespace_name>. The high-level qualifier (hlq) is passed as parameters to the job.
    Note: Do not change the middle-level qualifier, DWH, because it is used by both the UNLOAD and LOAD jobs.
Complete the following steps to unload your ELM tables:
  1. Stop the server.
  2. If any of your data warehouse tables are in Db2 for z/OS, modify and submit the BLZDWHUN job in hlq.SBLZSAMP to unload the Jazz data warehouse tables. Follow the instructions included with the job to make the required modifications.
Load the ELM tables back to the same database
The sample jobs provided to perform the LOAD task have a several steps:
  1. Delete the merged SYSIN data set.
  2. Using the parameters provided in the job, call program BLZSYSIN to merge the SYSIN data sets that contain the Db2 for z/OS LOAD utility control statements into a single sequential data set. BLZSYSIN also modifies the Db2 for z/OS LOAD parameters of RESUME YES to RESUME NO REPLACE ENFORCE NO.
  3. Load the Db2 for z/OS tables using the modified SYSIN Db2 for z/OS LOAD utility control statements.
  4. Call program BLZREPR to generate Db2 for z/OS REPAIR utility control statements and Db2 for z/OS CHECK utility control statements for the next steps based on the parameter passed to the program.
  5. Run the Db2 for z/OS CHECK utility using the utility cards created in the previous step to check referential integrity.
  6. Run the Db2 for z/OS REPAIR utility using the utility cards created in previous steps.
Complete the following steps to load your data warehouse tables:
  1. Stop the server.
  2. Drop the required set of tables. The recommended method is to DROP the database and re-create it with DDL shown in the following example for the data warehouse tables:
    Drop the following Database for the Jazz Team Server
DROP DATABASE DW702;
COMMIT;

Create the following Database for the Jazz Team Server
CREATE DATABASE DW702 STOGROUP ELMSTG BUFFERPOOL BP16K0  
 			    CCSID UNICODE;  
COMMIT;
    For additional information about creating the Db2 for z/OS databases, see Setting up Db2 for z/OS to use with Jazz Team Server.
  3. Create the new ELM tables by running a repotools –createWarehouse command for the JTS application. To do this, configure and run the BLZCREDB job in hlq.SBLZSAMP, which was run to initially create the tables. This job is used to create the JTS tables by changing the @appl@ parameter as described in the instructions included with the jobs. For more information about running this job, see Creating database tables using repository tools.
  4. Modify and submit the BLZDWHLD job in hlq.SBLZSAMP to load the data warehouse tables. Follow the instructions included with the job to make the required modifications. Note the parameters passed to the BLZSYSIN module.
Load the ELM tables to a new database
The sample jobs provided to perform the LOAD task have a several steps:
  1. Delete the merged SYSIN data set.
  2. Using the parameters provided in the job, call program BLZSYSIN to merge the SYSIN data sets that contain the Db2 for z/OS LOAD utility control statements into a single sequential data set. BLZSYSIN also modifies the Db2 for z/OS LOAD parameters of RESUME YES to RESUME NO REPLACE ENFORCE NO.
  3. Load the Db2 for z/OS tables using the modified SYSIN Db2 for z/OS LOAD utility control statements.
  4. Call program BLZREPR to generate Db2 for z/OS REPAIR utility control statements and Db2 for z/OS CHECK utility control statements for the next steps, based on the parameter passed to the program.
  5. Run the Db2 for z/OS CHECK utility using the utility cards created in previous step to check referential integrity.
  6. Run the Db2 for z/OS REPAIR utility using the utility cards created previously.
Complete the following steps to load your data warehouse tables
  1. Stop the server.
  2. Create the data warehouse tables according to the instructions in Setting up Db2 for z/OS to use with Jazz Team Server.
    Note: You are creating a new set of tables, so you must use a modified teamserver.properties file that uses the data warehouse database.
  3. Create the new ELM tables by running a repotools –createWarehouse command for the JTS application. To do this, configure and run the BLZCREDB job in hlq.SBLZSAMP, which was run to initially create the tables. This job is used to create the JTS tables by changing the @appl@ parameter as described in the instructions included with the jobs. For more information about running this job, see Creating database tables using repository tools.
  4. Modify and submit the BLZDWHLD job in hlq.SBLZSAMP to load the data warehouse tables. Follow the instructions included with the job to make the required modifications. Note the parameters passed to the BLZSYSIN module.