SAP on DB2 Universal Database for OS/390 and z/OS: Multiple Components in One Database (MCOD)

Chapter 4. Merging SAP components without moving data 55

The procedure DUPLICDB finds any clashes and tries to resolve them by

regenerating the last three characters of the database name to new random

values. It tries this up to 20 times for each clash, and if it cannot resolve the

clash, it reports this. If this error occurs, then you will need to change the value of

the target database name in the migration tables to a unique value. This action is

very unlikely, and all clashes should be resolved.

The JCL to move the metadata from the source to the target, the procedure

DUPLICDB, as well as the JCL to submit it, are included in “Working with

metadata tables” on page 182.

4.4.6 Source and Target: Define source objects in target system

In order to merge the tables from the source to the target system, the definitions

of the source objects need to be added into the target DB2 catalog.

We use a mix of methods for generating the DDL to create the source objects in

the target system:

򐂰 For generating the DDL for creating stogroups and views, we extract the data

out of the DB2 catalog tables directly using SQL statements and REXX

procedures.

򐂰 For generating the DDL for databases, tablespaces, tables, and indexes, we

use the reverse engineering functionality of DB2 Admin. Output from DB2

Admin is tailored using REXX procedures.

We assume that the only objects in the system are those in a typical SAP

installation. Therefore, we only generate DDL for stogroups, databases,

tablespaces, tables, indexes, and views. Objects, such as synonyms, aliases,

comments, relationships, and triggers, are not expected to exist in the system, so

these are not included in our generation procedures.

We chose to write SQL queries and REXX procedures for creating stogroups and

views rather than using DB2 Admin. DB2 Admin generates objects for a given

database, and, as views and stogroups may be associated with more than one

database, duplicate stogroups and views would have been created.

Important: A fundamental requirement of the merge in place procedure is to

retain the OBIDs of the tables when they are merged from the source to target

systems. When the OBID is retained, only the header pages of the tablespace

must be REPAIRed. If the OBID of the table is changed, then every single

page of the table must be REPAIRed, and this is not feasible.

56 SAP on DB2 UDB for OS/390 and z/OS: Multiple Components in One Database (MCOD)

Before executing DB2 Admin, we add extra indexes to the DB2 catalog to

improve the performance of the DDL generation program ADB2GEN. Details of

these indexes are in Chapter 2, “Customizing DB2 Admin,” in

IBM DB2

Administration Tool for z/OS User’s Guide Version 4 Release 1,

SC27-1601.

Source: Generate DDL for storage groups

The DDL for creating storage groups can easily be generated by running simple

SQL (see Example 4-2) against the source DB2 catalog. Make sure that the

naming convention follows the requirements of your MCOD landscape and add

the correct SET CURRENT SQLID when you run the generated DDL on the

target system (for example, in our case, SAPBLU).

Example 4-2 Generating DDL for storage groups

SELECT 'CREATE STOGROUP BLU'!!

SUBSTR(NAME,4,3)!!

' VOLUMES (''*'') VCAT MCDBLU ;'

FROM SYSIBM.SYSSTOGROUP

WHERE CREATOR='SAPR3' ;

Source and Target: Generate DDL for DB, TS, TB, and IX

DB2 Admin is used to generate the DDL for databases, tablespaces, tables and

indexes. We use a two-step process for this generation:

1. Target: Generate JCL to invoke DB2 Admin

The REXX procedure JCLGEN and its subroutines JCLGENDB, JCLGENTS,

JCLGENTB, and JCLGENIX produce JCL for invoking DB2 Admin. These

REXX procedures use the metadata tables in the target system as

parameters to generate the JCL.

2. Source: Execute JCL to invoke DB2 Admin

The size of the generated JCL prevents us from using TSO SUBMIT in our

system. So, once the JCL is generated, it is copied directly to the JES2

internal reader for submission. The JCL job cards contain TYPRUN=HOLD,

so release the jobs (using the “a” action character against each job in SDSF)

when you are ready to run them. The order in which these jobs are run is not

important. These jobs run on the source system to extract information from

the source DB2 catalog.

The REXX procedure JCLGEN (and its subroutines) and the JCL to execute it

and submit the generated JCL are included in “Generating DDL using DB2

Admin” on page 198.