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

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),