Appendix C. Merge in place: Defining source objects in target system 199
GRANTVW = 'N',
GRANTSG = 'N',
NEWDB = 'A000X02A',
NEWTSSG = '',
NEWIXSG = '',
NEWSQLID = 'SAPBLU',
SPCALLOC = 'DEFINED',
COMMITFR = 'N',
DEFAULTS = 'R',
TGTDB2 = '710';
DB='A000X00V', TS='';
/*
In this example:
򐂰 DB2 Admin is invoked for database A000X00Y (DB=A000X00Y) that is being
renamed to A000X02A (NEWDB = A000X02A).
򐂰 This JCL generates statements for CREATE DATABASE (GENDB=Y). Other
jobs for creating DDL for other objects would use parameters GENTS,
GENTABLE, and GENINDEX.
򐂰 The generated DDL is appended to the
SAPRES4.MRGBLU.REXXFILE.DDLDB.UNTAIL file.
򐂰 The storage group name is renamed from SAP* to BLU* using the MASK
parameter.
JCLGEN
In Example C-2, we provide the REXX procedure used to generate JCL to invoke
DB2 Admin. The source of this procedure (the JCLGEN.RXX file) is also part of
the additional material that can be downloaded from the Internet (see
Appendix E, Additional material on page 265).
Example: C-2 REXX procedure to generate JCL to invoke DB2 Admin
/****************************************************************/
/* REXXSQL
REXX routine name: JCLGEN
This routine is used to generate JCL for invoking
DB2 Administration Tool in batch. To simplify the REXX it
uses the following external subroutines:
JCLGENDB (for generating JCL for CREATE DATABASE)
JCLGENTS (for generating JCL for CREATE TABLESPACE)
JCLGENTB (for generating JCL for CREATE TABLE)
JCLGENIX (for generating JCL for CREATE INDEX)
200 SAP on DB2 UDB for OS/390 and z/OS: Multiple Components in One Database (MCOD)
The logic of the routine is:
Loop through the ZMCOD_DATABASE table. For each database:
- call subroutine JCLGENDB for database JCL
- call subroutine JCLGENTS for tablespace JCL
- call subroutine JCLGENTB for table JCL
- call subroutine JCLGENIX for index JCL
Parameters to call this procedure:
METADATA_SSID Target DB2 subsystem containing metadata tables
METADATA_OWNER Owner of the metadata tables in target system
DATASET_PREFIX Prefix for the dataset names to generate
DDL & JCL into
AUTHID AUTHID for Admin Tool to use
Note that this REXX routine needs to run against the metadata
tables located on the target DB2 system. Review the parameters
to ensure this.
*******************************************************************/
/* TRACE(R) */
PARSE ARG METADATA_SSID METADATA_OWNER DATASET_PREFIX AUTHID
SAY ' '
SAY 'JCLGEN executing in DB2 subsystem' METADATA_SSID
SAY ' using metadata from table ' METADATA_OWNER'.ZMCOD_DATABASE'
SAY ' and SQL authority of ' AUTHID
SAY ' JCL will be generated into datasets prefixed' DATASET_PREFIX
SAY ' '
/***************************************************************/
/* Initialize variables for use within this routine */
/***************************************************************/
SRC.DB2REL='999'
SRC.DB='DDDDDDDD'
SRC.SSID='SSSS'
SRC.STOG3='GGG'
SRC.SCHEMA='SSSSSSSS'
TARG.DB='DDDDDDDD'
TARG.DB2REL='999'
TARG.OWNER='OOOOOOOO'
TARG.SQLID='SSSSSSSS'
TARG.STOG3='GGG'
/*******************************************************************/
/* Add DSNREXX to the host command environment table if not there. */
/*******************************************************************/
'SUBCOM DSNREXX'
IF RC THEN ,
Appendix C. Merge in place: Defining source objects in target system 201
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX
/*******************************************************************/
/* Connect to TARGET DB2 subsystem */
/*******************************************************************/
'CONNECT' METADATA_SSID
/***************************************************************/
/* Setup the cursor for reading from the ZMCOD_DATABASE table */
/* The ZMCOD_DATABASE table contains one row for every database*/
/* being merged. */
/***************************************************************/
SQLSTMT = "SELECT SRCDBNAME, SRCRELEASE, SRCSSID, TRGDBNAME, "
SQLSTMT = SQLSTMT " TRGRELEASE, TRGSCHEMA, SRCSTOGROUP, "
SQLSTMT = SQLSTMT " TRGSTOGROUP, SRCSCHEMA "
SQLSTMT = SQLSTMT " FROM " METADATA_OWNER".ZMCOD_DATABASE "
SQLSTMT = SQLSTMT " ORDER BY SRCDBNAME "
/* SAY "JCLGEN SQLSTMT is " SQLSTMT */
"EXECSQL DECLARE C1 CURSOR FOR S1"
IF SQLCODE <> 0 THEN
DO
SAY "JCLGEN SQLCODE from DECLARE is "SQLCODE SQLERRMC SQLERRD.5
EXIT(12)
END
ELSE NOP
"EXECSQL PREPARE S1 FROM :SQLSTMT"
IF SQLCODE <> 0 THEN
DO
SAY "JCLGEN SQLCODE from PREPARE is "SQLCODE SQLERRMC SQLERRD.5
EXIT(12)
END
ELSE NOP
/* Open Cursor */
"EXECSQL OPEN C1"
IF SQLCODE <> 0 THEN
DO
SAY "JCLGEN SQLCODE from OPEN is "SQLCODE SQLERRMC SQLERRD.5
EXIT(12)
END
ELSE NOP
/***************************************************************/
/* Loop through each database record */
/***************************************************************/
"EXECSQL FETCH C1 INTO " || ,
202 SAP on DB2 UDB for OS/390 and z/OS: Multiple Components in One Database (MCOD)
" :SRC.DB, :SRC.DB2REL, :SRC.SSID, :TARG.DB," || ,
" :TARG.DB2REL, :TARG.OWNER, :SRC.STOG, :TARG.STOG, :SRC.SCHEMA"
IF SQLCODE <> 0 THEN
DO
SAY "JCLGEN SQLCODE from FIRST FETCH is "SQLCODE SQLERRMC SQLERRD.5
EXIT(12)
END
ELSE NOP
/* While the fetches are OK, continue to loop */
loopctr=0
StepsInJob=0
ADDJOBCARD='Y'
DO WHILE SQLCODE = 0
CALL GETSTOG3 /* Get first 3 chars of stogroup for JCL Mask */
SAY 'JCLGEN Processing database 'SRC.DB,
' (renamed to:' TARG.DB ')'
/* Call external subroutines to generate JCL
JCLGENDB (for generating JCL for CREATE DATABASE)
JCLGENTS (for generating JCL for CREATE TABLESPACE)
JCLGENTB (for generating JCL for CREATE TABLE)
JCLGENIX (for generating JCL for CREATE INDEX)
Strip off leading and trailing spaces off parameters */
ADDRESS TSO
CALL JCLGENDB STRIP(SRC.DB),
STRIP(SRC.DB2REL),
STRIP(SRC.SSID),
STRIP(TARG.DB),
STRIP(TARG.DB2REL),
STRIP(TARG.OWNER),
STRIP(TARG.SQLID),
STRIP(SRC.STOG3),
STRIP(TARG.STOG3),
STRIP(AUTHID),
ADDJOBCARD,
STRIP(SRC.SCHEMA),
DATASET_PREFIX
CALL JCLGENTS STRIP(SRC.DB),
STRIP(SRC.DB2REL),
STRIP(SRC.SSID),
STRIP(TARG.DB),
STRIP(TARG.DB2REL),
STRIP(TARG.OWNER),

Get SAP on DB2 Universal Database for OS/390 and z/OS: Multiple Components in One Database (MCOD) now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.