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