Mainframe Utility: COPYDB

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
            


Leave a Reply

Your email address will not be published. Required fields are marked *