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 221

"EXECIO 0 DISKR DDLIXIN (FINIS" /* Close the files */

"EXECIO 0 DISKW DDLIXOUT (FINIS"

TAILORTB

In Example C-10, we provide the REXX procedure used to append the OBID

clause to the CREATE TABLE statements generated by DB2 Admin. The source

of this procedure (the TAILORTB.RXX file) is also part of the additional material

that be downloaded from the Internet (see Appendix E, “Additional material” on

page 265).

Example: C-10 REXX procedure to tailor CREATE TABLE statements

/****************************************************************/

/* REXXSQL

REXX routine name: TAILORTB

This routine will read through the untailored DDL file which

contains CREATE TABLE statements.

For the CREATE TABLE statements it will add the OBID we want to

use when defining the table in the target system.

The tailored DDL is then written to the output file.

Parameters to call this procedure:

METADATA_SSID Target DB2 subsystem containing metadata tables

METADATA_OWNER Owner of the metadata tables in target system

Note that this 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

SAY ' '

SAY 'TAILORTB executing in DB2 subsystem' METADATA_SSID

SAY ' using metadata from table ' METADATA_OWNER'.ZMCOD_TABLES'

SAY ' '

/*******************************************************************/

/* Add DSNREXX to the host command environment table if not there. */

/*******************************************************************/

'SUBCOM DSNREXX'

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

IF RC THEN ,

S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')

ADDRESS DSNREXX

/*******************************************************************/

/* Connect to TARGET DB2 subsystem containing metadata tables */

/*******************************************************************/

'CONNECT' METADATA_SSID

/***************************************************************/

/* Setup the cursor for reading from the ZMCOD_TABLES table. */

/* The ZMCOD_TABLES table contains one row for every database */

/* being merged. */

/***************************************************************/

SQLSTMT1 = "SELECT SRCTABOBID FROM " METADATA_OWNER".ZMCOD_TABLES "

SQLSTMT1 = SQLSTMT1 " WHERE TRGSCHEMA = " ? " AND SRCTABLE = " ?

"EXECSQL DECLARE C1 CURSOR FOR S1"

IF SQLCODE <> 0 THEN

DO

SAY "TAILORTB from DECLARE is " SQLCODE SQLERRMC SQLERRD.5

EXIT(12)

END

ELSE NOP

/* Return to the TSO environment so EXECIO will work */

ADDRESS TSO

/* Open input file which contains untailored DDL and read the

first record */

"EXECIO 0 DISKRU DDLTBIN (OPEN"

"EXECIO 1 DISKRU DDLTBIN"

IF rc <> 0 THEN

DO

SAY 'TAILORTB Error opening DDLTBIN input file. rc=' rc

EXIT(12)

END

ELSE NOP

/* Open output file which will contain tailored DDL */

"EXECIO 0 DISKW DDLTBOUT (OPEN"

IF rc <> 0 THEN

DO

SAY 'TAILORTB Error opening DDLTBOUT file. rc=' rc

EXIT(12)

END

ELSE NOP

/* Read through each line of the input file */

DO WHILE RC = 0

PULL currline

Appendix C. Merge in place: Defining source objects in target system 223

newline = currline

/* If this line contains the CREATE TABLE statements then

extract table name and creator. These will be used to determine the

OBID to be used to create the table */

IF (WORD(currline,1) = 'CREATE') & (WORD(currline,2) = 'TABLE') THEN

DO

/* SAY 'Found the CREATE TABLE LINE ' */

currcreatortable = WORD(currline,3) /*extract CREATOR.TABLE */

PARSE VAR currcreatortable currcreator '.' currtable

/* SAY 'Extracted ' currcreator 'and' currtable */

END

ELSE NOP

/* If this line starts with 'IN' and contains a ';' then it is

the final line of the CREATE TABLE statements. Call subroutine to

lookup the OBID and append the OBID clause to the line */

IF (WORD(currline,1) = 'IN') & (POS(';',currline) <> 0) THEN

DO

currobid = 'AA'

CALL OBIDLookup currcreator, currtable, currobid

/* Separate the line at the semicolon */

PARSE VAR currline startofline ';' endofline

newline = startofline ' OBID ' currobid ' ;'

END

ELSE NOP

/* Write the record, which may or may not have been changed

to the output file */

PUSH newline

"EXECIO 1 DISKW DDLTBOUT"

IF rc <> 0 THEN

DO

SAY 'TAILORTB Error writing DDLTBOUT file. rc=' rc

EXIT(12)

END

ELSE NOP

"EXECIO 1 DISKRU DDLTBIN" /* Read next input record */

END

"EXECIO 0 DISKRU DDLTBIN (FINIS" /* Close the files */

"EXECIO 0 DISKW DDLTBOUT (FINIS"

EXIT

/***************************************************************/

/* SUBROUTINES */

/***************************************************************/