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 */
/***************************************************************/

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.