Return to Mainframe Utilities Page
Module
/* REXX ***************************************************************/
/* UTILITY: COPYDB */
/* AUTHOR: DAVID LEIGH */
/* FUNCTION: THIS UTILITY ALLOWS THE USER TO SPECIFY SOURCE AND */
/* TARGET DATABASES (ACROSS SUBSYSTEMS) AND A TIMESTAMP */
/* RANGE. IT THEN USES THIS INFORMATION TO CONSTRUCT JCL */
/* WHICH WILL INVOKE DSN1COPY WITH SYSXLAT CARDS TO COPY */
/* IMAGE COPIES OF THE SOURCE DATABASES INTO THE TARGET */
/* DATABASE. */
/**********************************************************************/
ADDRESS ISPEXEC
"CONTROL ERRORS RETURN"
/**********************************************************************/
/* GET THE JOBCARD INFO */
/**********************************************************************/
"SELECT PGM(USERINFO) PARM("SYSVAR(SYSUID)")"
IF POS('D@',SYSVAR(SYSUID)) = 1 THEN
DO
JCLASS = 'S'
J2CLASS = 'S'
END
ELSE
DO
JCLASS = '1,TIME=(1,00)'
J2CLASS = 'C,TIME=(5,00)'
END
/**********************************************************************/
/* MAIN PROCESSING AND DISPLAY LOOP */
/**********************************************************************/
DO WHILE CREATJCL ¬= 'Y'
"DISPLAY PANEL(COPYDB)"
IF RC = 8 THEN EXIT
/********************************************************************/
/* VERIFY THE SOURCE DATABASE AND POP UP A SELECTION LIST IF */
/* NECESSARY. */
/********************************************************************/
"TBCREATE TEMPTABL NOWRITE REPLACE KEYS(POPFIELD)"
DB2SSID = SSSID
SQLQUERY = "SELECT A.NAME"
SQLQUERY = SQLQUERY " FROM SYSIBM.SYSDATABASE A"
IF POS('%',SDBNAME) > 0 THEN
SQLQUERY = SQLQUERY " WHERE A.NAME LIKE '"SDBNAME"'"
ELSE
SQLQUERY = SQLQUERY " WHERE A.NAME = '"SDBNAME"'"
SQLQUERY = SQLQUERY " ORDER BY A.NAME"
ADDRESS LINK "REXXSQL"
SQLRC = RC
IF _NROWS = 0 THEN
DO
ZEDLMSG = 'NO SOURCE DATABASE FOUND IN 'SSSID' BY THAT NAME'
"SETMSG MSG(UTLZ001W)"
ITERATE
END
IF SQLRC <> 0 THEN
DO
ZEDLMSG = 'BAD SQLCODE VERIFYING SOURCE DATABASE:' SQLRC
"SETMSG MSG(UTLZ001W)"
ITERATE
END
DO I = 1 TO _NROWS
POPFIELD = STRIP(NAME.I)
"TBADD TEMPTABL"
END
IF _NROWS > 1 THEN
DO
"TBTOP TEMPTABL"
ZTDSELS = ''
"ADDPOP ROW(1) COLUMN(44)"
DO WHILE ZTDSELS ¬= 1 & RC < 8
ZEDLMSG = 'SELECT 1 VALID SOURCE DATABASE'
"TBDISPL TEMPTABL PANEL(CPYDBPOP)",
"AUTOSEL(YES) MSG(UTLZ000)"
END
IF ZTDSELS = 0 THEN
DO
SDBNAME = ''
CREATJCL = 'N'
"REMPOP ALL"
ZEDLMSG = 'YOU STILL NEED A SOURCE DATABASE'
"SETMSG MSG(UTLZ001W)"
ITERATE
END
"REMPOP ALL"
END
SDBNAME = POPFIELD
/********************************************************************/
/* VERIFY THE TARGET DATABASE AND POP UP A SELECTION LIST IF */
/* NECESSARY. */
/********************************************************************/
"TBCREATE TEMPTABL NOWRITE REPLACE KEYS(POPFIELD)"
DB2SSID = TSSID
SQLQUERY = "SELECT A.NAME"
SQLQUERY = SQLQUERY " FROM SYSIBM.SYSDATABASE A"
IF POS('%',TDBNAME) > 0 THEN
SQLQUERY = SQLQUERY " WHERE A.NAME LIKE '"TDBNAME"'"
ELSE
SQLQUERY = SQLQUERY " WHERE A.NAME = '"TDBNAME"'"
SQLQUERY = SQLQUERY " ORDER BY A.NAME"
ADDRESS LINK "REXXSQL"
SQLRC = RC
IF _NROWS = 0 THEN
DO
ZEDLMSG = 'NO TARGET DATABASE FOUND IN 'TSSID' BY THAT NAME'
"SETMSG MSG(UTLZ001W)"
ITERATE
END
IF SQLRC < 0 THEN
DO
ZEDLMSG = 'BAD SQLCODE VERIFYING TARGET DATABASE:' SQLRC
"SETMSG MSG(UTLZ001W)"
ITERATE
END
DO I = 1 TO _NROWS
POPFIELD = STRIP(NAME.I)
"TBADD TEMPTABL"
END
IF _NROWS > 1 THEN
DO
"TBTOP TEMPTABL"
ZTDSELS = ''
"ADDPOP ROW(1) COLUMN(44)"
DO WHILE ZTDSELS ¬= 1 & RC < 8
ZEDLMSG = 'SELECT 1 VALID TARGET DATABASE'
"TBDISPL TEMPTABL PANEL(CPYDBPOP)",
"AUTOSEL(YES) MSG(UTLZ000)"
END
IF ZTDSELS = 0 THEN
DO
TDBNAME = ''
CREATJCL = 'N'
"REMPOP ALL"
ZEDLMSG = 'YOU STILL NEED A TARGET DATABASE'
"SETMSG MSG(UTLZ001W)"
ITERATE
END
"REMPOP ALL"
END
TDBNAME = POPFIELD
/********************************************************************/
/* POPUP A SELECTION LIST FOR "FROM" TIMESTAMP IF GIVEN A PARTIAL */
/********************************************************************/
SYSCRETR = STRIP(SYSCRETR)
IF FTSYYYY = '' ¦,
FTSMM = '' ¦,
FTSDD = '' ¦,
FTSHH = '' ¦,
FTSMN = '' ¦,
FTSSS = '' ¦,
FTSMLSS = '' THEN
DO
IF FTSYYYY = '' THEN FTSYYYY = '0001'
IF FTSMM = '' THEN FTSMM = '01'
IF FTSDD = '' THEN FTSDD = '01'
IF FTSHH = '' THEN FTSHH = '00'
IF FTSMN = '' THEN FTSMN = '00'
IF FTSSS = '' THEN FTSSS = '00'
IF FTSMLSS = '' THEN FTSMLSS = '000000'
FTMESTMP = FTSYYYY"-" ¦¦,
FTSMM"-" ¦¦,
FTSDD"-" ¦¦,
FTSHH"." ¦¦,
FTSMN"." ¦¦,
FTSSS"." ¦¦,
FTSMLSS
"TBCREATE TEMPTABL NOWRITE REPLACE KEYS(POPFIELD)"
DB2SSID = SSSID
SQLQUERY = "SELECT TSNAME, CHAR(TIMESTAMP) AS TSTAMP"
SQLQUERY = SQLQUERY " FROM "SYSCRETR".SYSCOPY"
SQLQUERY = SQLQUERY " WHERE DBNAME = '"SDBNAME"'"
SQLQUERY = SQLQUERY " AND TSNAME LIKE '"STSNAME"'"
SQLQUERY = SQLQUERY " AND ICTYPE = 'F'"
SQLQUERY = SQLQUERY " AND ICBACKUP = ' '"
SQLQUERY = SQLQUERY " AND TIMESTAMP >= '"FTMESTMP"'"
SQLQUERY = SQLQUERY " ORDER BY TSTAMP"
ADDRESS LINK "REXXSQL"
SQLRC = RC
IF SQLRC = 100 THEN
DO
ZEDLMSG = 'NO TIMESTAMP >=' FTMESTMP 'IN 'SSSID' SYSCOPY'
"SETMSG MSG(UTLZ001W)"
ITERATE
END
IF SQLRC < 0 THEN
DO
ZEDLMSG = 'BAD SQLCODE VERIFYING "FROM" TIMESTAMP"' SQLRC
"SETMSG MSG(UTLZ001W)"
ITERATE
END
DO I = 1 TO _NROWS
POPFIELD = SUBSTR(STRIP(TSNAME.I)" ",1,8),
STRIP(TSTAMP.I)
"TBADD TEMPTABL"
END
IF _NROWS > 1 THEN
DO
"TBTOP TEMPTABL"
ZTDSELS = ''
"ADDPOP ROW(1) COLUMN(34)"
DO WHILE ZTDSELS ¬= 1 & RC < 8
ZEDLMSG = 'SELECT 1 "FROM" TIMESTAMP ROW'
"TBDISPL TEMPTABL PANEL(CPYDBPO2)",
"AUTOSEL(YES) MSG(UTLZ000)"
END
IF ZTDSELS = 0 THEN
DO
FTSYYYY = ''
FTSMM = ''
FTSDD = ''
FTSHH = ''
FTSMN = ''
FTSSS = ''
FTSMLSS = ''
CREATJCL = 'N'
"REMPOP ALL"
ZEDLMSG = 'YOU STILL NEED A "FROM" TIMESTAMP'
"SETMSG MSG(UTLZ001W)"
ITERATE
END
"REMPOP ALL"
END
PARSE UPPER VAR POPFIELD TSNAME FTMESTMP
PARSE UPPER VAR FTMESTMP FTSYYYY '-',
FTSMM '-',
FTSDD '-',
FTSHH '.',
FTSMN '.',
FTSSS '.',
FTSMLSS .
END
FTMESTMP = FTSYYYY"-" ¦¦,
FTSMM"-" ¦¦,
FTSDD"-" ¦¦,
FTSHH"." ¦¦,
FTSMN"." ¦¦,
FTSSS"." ¦¦,
FTSMLSS
/********************************************************************/
/* POPUP A SELECTION LIST FOR "TO" TIMESTAMP IF GIVEN A PARTIAL ONE */
/********************************************************************/
IF TTSYYYY = '' ¦,
TTSMM = '' ¦,
TTSDD = '' ¦,
TTSHH = '' ¦,
TTSMN = '' ¦,
TTSSS = '' ¦,
TTSMLSS = '' THEN
DO
IF TTSYYYY = '' THEN TTSYYYY = '0001'
IF TTSMM = '' THEN TTSMM = '01'
IF TTSDD = '' THEN TTSDD = '01'
IF TTSHH = '' THEN TTSHH = '00'
IF TTSMN = '' THEN TTSMN = '00'
IF TTSSS = '' THEN TTSSS = '00'
IF TTSMLSS = '' THEN TTSMLSS = '000000'
TTMESTMP = TTSYYYY"-" ¦¦,
TTSMM"-" ¦¦,
TTSDD"-" ¦¦,
TTSHH"." ¦¦,
TTSMN"." ¦¦,
TTSSS"." ¦¦,
TTSMLSS
"TBCREATE TEMPTABL NOWRITE REPLACE KEYS(POPFIELD)"
DB2SSID = SSSID
SQLQUERY = "SELECT TSNAME, CHAR(TIMESTAMP) AS TSTAMP"
SQLQUERY = SQLQUERY " FROM "SYSCRETR".SYSCOPY"
SQLQUERY = SQLQUERY " WHERE DBNAME = '"SDBNAME"'"
SQLQUERY = SQLQUERY " AND TSNAME LIKE '"STSNAME"'"
SQLQUERY = SQLQUERY " AND ICTYPE = 'F'"
SQLQUERY = SQLQUERY " AND ICBACKUP = ' '"
SQLQUERY = SQLQUERY " AND TIMESTAMP >= '"TTMESTMP"'"
SQLQUERY = SQLQUERY " ORDER BY TSTAMP"
ADDRESS LINK "REXXSQL"
SQLRC = RC
IF SQLRC = 100 THEN
DO
ZEDLMSG = 'NO TIMESTAMP >=' TTMESTMP 'IN 'SSSID' SYSCOPY'
"SETMSG MSG(UTLZ001W)"
ITERATE
END
IF SQLRC < 0 THEN
DO
ZEDLMSG = 'BAD SQLCODE VERIFYING "TO" TIMESTAMP"' SQLRC
"SETMSG MSG(UTLZ001W)"
ITERATE
END
DO I = 1 TO _NROWS
POPFIELD = SUBSTR(STRIP(TSNAME.I)" ",1,8),
STRIP(TSTAMP.I)
"TBADD TEMPTABL"
END
IF _NROWS > 1 THEN
DO
"TBTOP TEMPTABL"
ZTDSELS = ''
"ADDPOP ROW(1) COLUMN(34)"
DO WHILE ZTDSELS ¬= 1 & RC < 8
ZEDLMSG = 'SELECT 1 "TO" TIMESTAMP ROW'
"TBDISPL TEMPTABL PANEL(CPYDBPO2)",
"AUTOSEL(YES) MSG(UTLZ000)"
END
IF ZTDSELS = 0 THEN
DO
TTSYYYY = ''
TTSMM = ''
TTSDD = ''
TTSHH = ''
TTSMN = ''
TTSSS = ''
TTSMLSS = ''
CREATJCL = 'N'
"REMPOP ALL"
ZEDLMSG = 'YOU STILL NEED A "TO" TIMESTAMP'
"SETMSG MSG(UTLZ001W)"
ITERATE
END
"REMPOP ALL"
END
PARSE UPPER VAR POPFIELD TSNAME TTMESTMP
PARSE UPPER VAR TTMESTMP TTSYYYY '-',
TTSMM '-',
TTSDD '-',
TTSHH '.',
TTSMN '.',
TTSSS '.',
TTSMLSS .
END
TTMESTMP = TTSYYYY"-" ¦¦,
TTSMM"-" ¦¦,
TTSDD"-" ¦¦,
TTSHH"." ¦¦,
TTSMN"." ¦¦,
TTSSS"." ¦¦,
TTSMLSS
END
/**********************************************************************/
/* OK, NOW WE'VE GOT THE APPROPRIATE PARAMETERS TO PASS TO THE NEXT */
/* STEP, WHICH IS RUN IN BATCH. THE FOLLOWING CODE CREATES A FILE TO */
/* HOLD THE JCL AND THEN CREATES THE JCL. IF THE USER REQUESTS TO */
/* "REVIEW" THE JCL, THEY ARE TAKEN INTO AN EDIT SESSION AND THE JOB */
/* IS *NOT* SUBMITTED. OTHERWISE THE JOB IS SUBMITTED AUTOMATICALLY. */
/**********************************************************************/
TEMPFILE = SYSVAR(SYSUID) ¦¦ '.TEMP.COPYDB.JCL'
ADDRESS TSO
DUMMY = OUTTRAP('NULL.')
"FREE DD(ISPFILE)"
"DELETE '"TEMPFILE"'"
"ALLOCATE DD(ISPFILE) DSN('"TEMPFILE"')" ,
"NEW CATALOG" ,
"UNIT(SYSDA) VOLUME(WRK$$$)" ,
"SPACE(1,1) TRACKS RELEASE" ,
"RECFM(F B) LRECL(80) DSORG(PS)"
DROP NULL.
ADDRESS ISPEXEC
"FTOPEN"
"FTINCL COPYDB"
SAVERC = RC
"FTCLOSE"
ADDRESS TSO "FREE DD(ISPFILE)"
IF SAVERC > 0 THEN
DO
ZEDLMSG = 'FILE TAILORING OF THE "COPYDB" SKELETON FAILED',
'WITH RC =' SAVERC
"SETMSG MSG(UTLZ001W)"
"EDIT DATASET('"TEMPFILE"')"
END
ELSE IF JCLREVEW = 'Y' THEN
DO
ZEDLMSG = 'YOU MUST SUBMIT THIS JCL YOURSELF'
"SETMSG MSG(UTLZ000W)"
"EDIT DATASET('"TEMPFILE"')"
END
ELSE
DO
ADDRESS TSO "SUBMIT '"TEMPFILE"'"
ZEDLMSG = 'COPYDB JOB SUBMITTED'
"SETMSG MSG(UTLZ000W)"
END
EXIT SAVERC
Documentation
COPYDB is a utility that will generate necessary JCL to copy ENTIRE DB2 databases from a source database to a target database. The use of Princeton's Move For DB2 in conjunction with BMC's Unload Plus can move referentially intact subsets of a given source database (live or from image copy) to a target database, but that solution presents problems when an entire database (especially a large one) needs to be moved from a source to a target. At that point the IBM-supplied program DSN1COPY works better (and much faster), but it is difficult to set up and requires that some follow-up "clean-up" steps be executed as well. This is where COPYDB comes in. COPYDB allows you to specify a source database (on a source subsystem) and a target database (on a target subsystem) that you want to work with. It then allows you to specify what set of image copies you want to deal with by allowing you to specify a timestamp range within which the source subsystem's SYSIBM.SYSCOPY table is searched for the source database local, full image copes. This becomes the set of image copies that COPYDB will use as a source. Then COPYDB attempts to match up appropriate target database objects with the source database objects. This indicates that COPYDB requires that the target database objects must exist. COPYDB also makes no attempt to determine if the target database is sized appropriately to receive the source data. If the database in question is a UNISTAR or ELM database, it is recommended that DBSETUP or ELMSETUP be used to create the target database environment to accomplish the appropriate sizing. COPYDB does it's matching based on DB2 Table name. This makes the usually true assumption that a tablespace contains one table. The DSN1COPY activity happens at a tablespace level but as long as there is a one-to-one relationship between tables and tablespaces, this will work well. If there are multiple tables in the tablespace, some manual JCL editing may be required. As an example (very real-world) of how COPYDB matches up source and target, consider the following: Source Subsystem: DSNP Source Database: UFMSTP00 (UNISTAR Denver Full-Service database) Source Tablespace: UFNTES01 Source Table: NTE01T_NOTE Target Subsystem: DSNT Target Database: P@UXXXDB Target Tablespace: XXNTES01 Target Table: NTE01T_NOTE In this case, COPYDB determines that NTE01T_NOTE in the source matches NTE01T_NOTE in the target. From this it creates JCL to copy the image copy from the UFMSTP00.UFNTES01 tablespace into the dataset associated with P [at] UXXXDB [dot] XXNTES01 target tablespace. The main COPYDB panel allows you to specify all the parameters necessary for it to create and submit JCL which actually creates another set of jobs (in a single output dataset) that does the actual copy of the data and the associated cleanup. The process of the actual data copy activity is as follows: 1 1. STOP all target tablespaces 2. Perform all the individual DSN1COPY steps 3. START all the target tablespaces 4. Image copy all the target tablespaces 5. Recover all the indexes for all the tables in the target tablespaces 6. Execute RUNSTATS on all the target tablespaces This process is broken in to several jobs since the number of steps will typically exceed JES limitations on steps per job. The first job is stored in: userid.TEMP.COPYDB.JCL The job to do the data copy is stored in: userid.TEMP.DSN1COPY.JCL