Mainframe Utility: DBSETUP

Return to Mainframe Utilities Page

Module


/* REXX ***************************************************************/

Say " "
Say " "
Say " "
Say " "
Say " "
Say " "
Say " "
Say " "
Say " "
Say " "
Say " "
Say "This utility is no longer functional nor supported."
Say " "
Say "Please use the option from the =D (DBA/MQSeries Tools) Panel."
Say " "
Say "Please see one of the DBA's for additional help."
Say " "
Say " "
Say " "
Say " "
Say " "
Say " "
Say " "
Say " "
Say " "

exit

/* UTILITY: DBSETUP                                                   */
/* AUTHOR: DAVID LEIGH                                                */
/* FUNCTION: THIS UTILITY CONTROLS FILE TAILORING OF A JOB WHICH WILL */
/*           CREATE A PROGRAMMER'S TEST DB2 ENVIRONMENT IN THIER      */
/*           DATABASE BASED ON SOME CRITERIA PASSED TO THIS EXEC.  IF */
/*           THIS EXEC DOES NOT GET EVERYTHING IT NEEDS, IT WILL POP  */
/*           UP A PANEL TO PROMPT FOR IT.  CONSEQUENTLY IT CAN BE     */
/*           CALLED BY OTHER PROCESSES THAT PASS THE REQUISITE        */
/*           INFORMATION VIA THE ISPF SHARED PROFILE POOL.            */
/**********************************************************************/
/*
M = SYSVAR(SYSUID)¦¦' USED DBSETUP ON '¦¦DATE('U')¦¦' '¦¦TIME()
"SEND '" M "' US(S@USMT) LOGON"
"SEND '" M "' US(D@USPH) LOGON"
*/
ADDRESS ISPEXEC
"CONTROL ERRORS RETURN"

USERDB = SYSVAR(SYSUID) ¦¦ 'DB'
"SELECT PGM(USERINFO) PARM("SYSVAR(SYSUID)")"

"VGET MJOBPFX DATABASE CREATOR APPLNAME",
      "JCLREVEW NUMACCTS TBLSDROP DBSETUP DEBUG) SHARED"

IF DBSETUP = AUTO THEN
    DO
        IF JOBPFX   = ''  THEN JOBPFX   = 'UF'
        IF DATABASE = ''  THEN DATABASE = USERDB
        IF CREATOR  = ''  THEN CREATOR  = SYSVAR(SYSUID)
        IF JCLREVEW = ''  THEN JCLREVEW = 'Y'
        IF NUMACCTS = ''  THEN NUMACCTS = 1000
        IF TBLSDROP = ''  THEN TBLSDROP = 'N'
        "VPUT (JOBPFX DATABASE CREATOR APPLNAME",
              "JCLREVEW NUMACCTS TBLSDROP DBSETUP DEBUG) SHARED"
    END
ELSE
  DO WHILE GENJCL ¬= 'Y'
    "DISPLAY PANEL(DBSETUP)"
    IF RC = 8 THEN EXIT
    ERRFIELD = ''
    PARSE VAR DATABASE DFLD1 7 DFLD2 2 DFLD3 3 DREST
    PARSE VAR DATABASE 1 DFLD4 6 DFLD5
    PARSE VAR CREATOR CFLD1 7 CFLD2 1 CFLD3 6 CFLD4
    IF DFLD3 = '@' THEN
      DO
       IF DFLD2 = 'DB' THEN
         DO
          IF CFLD2 > '' THEN
             DO
               ZEDLMSG = 'CREATOR MUST EQUAL YOUR TSO ID -',
                   'CORRECT CREATOR ENTRY AND RETRY'
               "SETMSG MSG(UTLZ001W)"
               ERRFIELD = 'CREATOR'
               ITERATE
             END
          ELSE NOP
          IF DFLD1 ¬= CFLD1 THEN
             DO
               ZEDLMSG = 'FIRST SIX BYTES OF DATABASE/CREATOR MUST =',
                    ' TSO ID. CORRECT DATABASE/CREATOR ENTRY AND RETRY'
               "SETMSG MSG(UTLZ001W)"
               ERRFIELD = 'DATABASE'
               ITERATE
             END
          ELSE NOP
         END
       ELSE
          IF DFLD2 ¬= 'DB' THEN
             DO
               ZEDLMSG = 'DATABASE MUST EQUAL TSO ID FOLLOWED BY 'DB'',
                  '. CORRECT DATABASE ENTRY AND RETRY'
               "SETMSG MSG(UTLZ001W)"
               ERRFIELD = 'DATABASE'

               ITERATE
             END
          ELSE NOP
      END
    ELSE
       IF DFLD4 = 'USMST' THEN
         DO
          IF CFLD3 ¬= 'USSTR' THEN
             DO
               ZEDLMSG = 'CREATOR NAMING CONVENTION USSTR*** .',
                   'CORRECT CREATOR ENTRY AND RETRY.'
               "SETMSG MSG(UTLZ001W)"
               ERRFIELD = 'CREATOR'
               ITERATE
             END
          ELSE NOP
          IF DFLD5 ¬= CFLD4 THEN
             DO
               ZEDLMSG = 'DATABASE/CREATOR MISMATCH. MUST END WITH',
                   'SAME 3 CHARACTERS. CORRECT AND RETRY.'
               "SETMSG MSG(UTLZ001W)"
               ERRFIELD = 'DATABASE'
               ITERATE
             END
          ELSE NOP
         END
       ELSE
          DO
            ZEDLMSG = 'DATABASE NAMING CONVENTION - USMST***.',
                'CORRECT DATABASE ENTRY AND RETRY.'
               "SETMSG MSG(UTLZ001W)"
               ERRFIELD = 'DATABASE'
               ITERATE
          END
    IF GRPID > '' THEN
      DO
        "TBCREATE TEMPDBST NOWRITE REPLACE KEYS(GRPID) NAMES(PRTROWS)"
        DB2SSID = "DSNP"
        SQLQUERY = "SELECT A.GRP_ID,",
                   "       B.CARD",
                   "  FROM USSTRP00.ADM40T_GRPPROF A,",
                   "       SYSIBM.SYSTABLES B",
                   " WHERE A.GRP_TYPE = 'SVCR'",
                   "   AND A.DB2_QUALIFIER = B.CREATOR",
                   "   AND B.NAME = 'PRT01T_PARTICIPANT'",
                   " ORDER BY B.CARD,",
                   "          A.GRP_ID"
        ADDRESS LINK "REXXSQL"
        SQLRC = RC
        IF SQLRC <> 0 THEN
          DO
            SAY "BAD SQLCODE VERIFYING GROUP ID:" SQLRC
            EXIT SQLRC
          END
        DO I = 1 TO _NROWS
          GRPID = STRIP(GRP_ID.I)
          PRTROWS = STRIP(CARD.I)
          "TBADD TEMPDBST"
        END
        "VGET GRPID SHARED"
        "TBGET TEMPDBST"
        IF RC > 0 THEN
          DO
            ZTDSELS = ''
            "ADDPOP ROW(1) COLUMN(44)"
            DO WHILE ZTDSELS ¬= 1 & RC < 8
              ZEDLMSG = 'PLEASE SELECT 1 VALID GROUP ID'
              "TBDISPL TEMPDBST PANEL(DBSETPOP)",
                               "AUTOSEL(YES) MSG(UTLZ000)"
            END
            IF ZTDSELS = 0 THEN
              DO
                GRPID = ''
                GENJCL = 'N'
              END
            "REMPOP ALL"
          END
      END
  END

EDITDONE = 'N'
  DO WHILE EDITDONE ¬= 'Y'
    "DISPLAY PANEL(DBSETUP)"
    IF RC = 8 THEN EXIT
    ERRFIELD = ''
    PARSE VAR DATABASE DFLD1 7 DFLD2 2 DFLD3 3 DREST
    PARSE VAR DATABASE 1 DFLD4 6 DFLD5
    PARSE VAR CREATOR CFLD1 7 CFLD2 1 CFLD3 6 CFLD4
    IF DFLD3 = '@' THEN
      DO
       IF DFLD2 = 'DB' THEN
         DO
          IF CFLD2 > '' THEN
             DO
               ZEDLMSG = 'CREATOR MUST EQUAL YOUR TSO ID -',
                   'CORRECT CREATOR ENTRY AND RETRY'
               "SETMSG MSG(UTLZ001W)"
               ERRFIELD = 'CREATOR'
               ITERATE
             END
          ELSE NOP
          IF DFLD1 ¬= CFLD1 THEN
             DO
               ZEDLMSG = 'FIRST SIX BYTES OF DATABASE/CREATOR MUST =',
                    ' TSO ID. CORRECT DATABASE/CREATOR ENTRY AND RETRY'
               "SETMSG MSG(UTLZ001W)"
               ERRFIELD = 'DATABASE'
               ITERATE
             END
          ELSE NOP
         END
       ELSE
          IF DFLD2 ¬= 'DB' THEN
             DO
               ZEDLMSG = 'DATABASE MUST EQUAL TSO ID FOLLOWED BY 'DB'',
                  '. CORRECT DATABASE ENTRY AND RETRY'
               "SETMSG MSG(UTLZ001W)"
               ERRFIELD = 'DATABASE'
               ITERATE
             END
          ELSE NOP
      END
    ELSE
       IF DFLD4 = 'USMST' THEN
         DO
          IF CFLD3 ¬= 'USSTR' THEN
             DO
               ZEDLMSG = 'CREATOR NAMING CONVENTION USSTR*** .',
                   'CORRECT CREATOR ENTRY AND RETRY.'
               "SETMSG MSG(UTLZ001W)"
               ERRFIELD = 'CREATOR'
               ITERATE
             END
          ELSE NOP
          IF DFLD5 ¬= CFLD4 THEN
             DO
               ZEDLMSG = 'DATABASE/CREATOR MISMATCH. MUST END WITH',
                   'SAME 3 CHARACTERS. CORRECT AND RETRY.'
               "SETMSG MSG(UTLZ001W)"
               ERRFIELD = 'DATABASE'
               ITERATE
             END
          ELSE NOP
         END
       ELSE
          DO
            ZEDLMSG = 'DATABASE NAMING CONVENTION - USMST***.',
                'CORRECT DATABASE ENTRY AND RETRY.'
               "SETMSG MSG(UTLZ001W)"
               ERRFIELD = 'DATABASE'
               ITERATE
          END
  EDITDONE = 'Y'
  END

TEMPFILE = SYSVAR(SYSUID) ¦¦ '.TEMP.DBSETUP.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 DBSETUP"
SAVERC = RC
"FTCLOSE"
ADDRESS TSO "FREE DD(ISPFILE)"

IF SAVERC > 0 THEN
    DO
        ZEDLMSG = 'FILE TAILORING OF THE "DBSETUP" 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 = 'DBSETUP JOB SUBMITTED'
        "SETMSG MSG(UTLZ000W)"
    END

EXIT SAVERC
            


Documentation


                                                 Last updated: 04/28/97

  *********************************************************************
  *                          DBSETUP                                  *
  *********************************************************************

  OVERVIEW: DBSETUP is an ISPF dialog which creates JCL to facilitate
  the creation of all the necessary DB2 objects in a target testing
  database necessary to test a specific program, group of programs,
  specific job, group of jobs, or entire client UNISTAR database.

  EXECUTION: As an ISPF dialog, DBSETUP is invoked by typing "tso
  dbsetup" at any ISPF command line and pressing the ENTER key.  The
  resulting screen looks like:

  ----------------- UNISTAR DB2 Personal Testing Database Setup ----------------
  COMMAND ==>

  Target DB2 Database ==> D@UDALDB            Target DB2 CREATOR Id ==> D@UDAL__

  Resize Percentage ==> __1 (1-100 Percent Of Production Database Size)

  Drop Conflicting Objects In Target Database? N (Y/N)

  Review JCL Prior To Submitting? N (Y/N)

  Create JCL Now? N (Y/N)

  ------------------------------ Selection Criteria ----------------------------

  Batch Job Name ==> USC1021_ (You may enter a prefix as well.  e.g. UFBL)

                                     *** OR ***

  USSTRP00.ADM40T_GRPPROF GRP_ID ==> ______
                              *** AND (Optionally) ***
  Program Name ==> ________ (You may enter a prefix as well.  e.g. BLB)

                                   END To Cancel

  PANEL OPTIONS

  TARGET DB2 DATABASE: This field contains the name of database where
  the new objects will be created.  This will, usually, be the
  programmer's own personal test database.  This is the programmer's
  user id with a "DB" concatenated onto the end.  This is the value that
  the utility defaults to.  Another option could be one of the "team
  databases" which begin "T1A".  Special caution should be exercised
  when specifying one of the team databases as the target database since
  this usually will affect multiple programmers.  All affected by the
  change should be notified if such a change is going to take place.

  TARGET DB2 CREATOR ID: This field contains the character string to be
  used as the DB2 creator id of the new objects.  If this is the
  programmer's personal database, this will be their user id.  If this
  is one of the team databases this will be "T1A*" where "*" is 1-9.

  RESIZE PERCENTAGE: This is a number from 1 to 100.  It will be applied
  as a percentage to the current primary and secondary allocation
  quantities of the production tablespaces and indexes to size the new
  target objects.  The specific client production objects that have the
  percentage applied to them are the ones associated with the job name
  or group id specified in the lower portion of the panel.  For
  instance, if the primary quantity of the PRT01T_PARTICIPANT table's
  tablespace for the full service database ("DENVER" group id) is
  "2000", when the RESIZE PERCENTAGE value of 1 is applied to it, the
  new target object would have a primary quantity of "20" .  While the
  primary and secondary quantity values are 1K increments and do not
  specifically relate to number of rows, programmers may use percentage
  of rows as a good percentage figure for sizing.  For instance, if the
  client database they are extracting production data from for testing
  has 100,000 PRT01T_PARTICIPANT rows and they will test with 1,000
  rows, they could use the RESIZE PERCENTAGE value of "1" safely.

  DROP CONFLICTING OBJECTS IN TARGET DATABASE: This field is a "Y/N"
  flag field.  If "Y" is specified (usually recommended), then DBSETUP
  will execute "DROP" statements against any objects in the target
  database which would conflict with the new TABLESPACES, TABLES,
  INDEXES, and VIEWS that it intends to build.  This allows the
  programmer to "start clean" for the testing situation that they are
  trying to set up for.  If there is a situation that requires keeping a
  given object or set of objects (with their associated data), then this
  value can be left as "N".  When DBSETUP tries to create an object that
  already exists, it will get an error, but it will go on and try to
  create the next object.  Results of the process should be reviewed in
  this situation to see just what the errors were that occurred.  There
  may have been unexpected failures because of interrelationships
  between various DB2 objects.

  REVIEW JCL PRIOR TO SUBMITTING: This field is a "Y/N" flag field.  A
  value of "Y" in this field will bring you into an edit session of the
  JCL that is created by DBSETUP.  You may make modifications to the JCL
  at this point (or not).  You must, however, submit the JCL yourself if
  you specify "Y".  If "N" is the value in this field, when the JCL is
  created by DBSETUP, DBSETUP will automatically submit it for you.  In
  either case, the JCL will saved in a dataset named
  userid.TEMP.DBSETUP.JCL.  This will be stored on a work pack and will,
  consequently, be scratched the evening of the day it's created.

  CREATE JCL NOW: This field is "Y/N" flag field.  When you put "Y" in
  this field and press ENTER, you are signifying that you are ready to
  have DBSETUP create the JCL.

  OBJECT SELECTION CRITERIA

  This bottom portion of the screen will determine, in part, what
  objects are created in the target environment.  In UNISTAR, there are
  a number of tables/views which are shared among all production clients
  and those which are physically separate between the client databases.
  In a testing environment, many of those shared objects can continue to
  be shared and a few should be specific to each project.  The creation
  of tables/views on these shared objects is a "boilerplate" activity in
  DBSETUP, i.e. it will create these objects regardless of what is
  entered on this panel.  The "Selection Criteria" refer only to the
  "client-specific" database objects.  Please refer to the section of
  this document which details the processing steps of DBSETUP for more
  information on how the shared objects are created in the target
  environment.

  The Selection Criteria give you three ways of specifying how you want
  to build your target environment.

  1. You can select an entire client's set of database objects by just
  specifying a value in the USSTRP00.ADM40T_GRPPROF GRP_ID field.  Valid
  values for this field are the values found in the GRP_ID column of the
  USSTRP00.ADM40T_GRPPROF table in the DSNP (Production) DB2 subsystem,
  for which the GRP_TYPE column is SVCR.  Another way of stating this is
  with the following SQL statement:

  SELECT GRP_ID FROM USSTRP00.ADM40T_GRPPROF WHERE
  GRP_TYPE = ‘SVCR';

  This will take a snapshot of all the object definitions of all the
  dependant objects for all the TABLESPACEs in that client's UNISTAR
  "Master" DB2 database.  If you're running "full cycles" and testing
  some on-line transactions, this will probably be the option you
  select.

  2. You can enter a value in both the USSTRP00.ADM40T_GRPPROF GRP_ID
  and Program Name fields.  This will capture all the object definitions
  for a specific client database which that program (or set of programs
  beginning with that prefix) uses. For instance, you may only want to
  test all the TLAD transaction programs (independant of their batch
  associations).  You'd specify the Group Id and then put SLSTLA in the
  Program Name field.

  3. You can enter a value in the Batch Job Name field.  This value may
  be a complete job name or a job name prefix.  Since job names in
  UNISTAR are prefixed (in both the old and new naming conventions) with
  the client that they belong to, the job name implies not only the
  content of that job, but also the client for whom it runs.  With the
  new naming convention, when you specify a job name prefix, you can
  approximate the concept of establishing the object definitions that
  are pertinant to a single subsystem (Note that data definitions
  typically cross sub-system boundries).  For instance, if you wanted to
  test the batch run for EPD, you could specify a Batch Job name of UFED
  and DBSETUP would find all the object definitions used by all the
  programs in all the jobs which begin "UFED".....the full service, EPD
  jobs.

  DBSETUP PROCESSING STEPS

  The DBSETUP batch job goes through the following "high-level" steps
  (in some cases multiple actual job steps are required to accomplish
  one of the high-level steps.

  1. Based on the "Selection Criteria", query the DB2 catalog (joining
  to UNISTAR ADM40T and/or UTL61T tables where necessary) to return a
  list of DB2 TABLESPACEs that are used by the programs/jobs/group
  specified in the Selection Criteria.

  2. Use Platinum's RC/Migrator Recovery Snapshot facility to create a
  DDL file of all the object definitions and dependant object
  definitions (TABLEs, VIEWs, INDEXs) for the TABLESPACEs listed in step
  #1.

  3. Edit the DDL created in step #2 and apply the name changes
  appropriate for the target database and target creator id.  Also apply
  the space percentage calculations against the primary and secondary
  space allocations to size down for the testing environment.

  4. Use Platinum's RC/Migrator Recovery Snapshot facility to create a
  DDL file of all the object definitions and dependant object
  definitions (other dependant VIEWs) for a list of "stage-level",
  "base" views.  These are views that point to a set of shared table in
  the testing environment that should not, generally, be recreated for
  each project.  The list of views at this time is:

  ADM01V_USERPROF
  UTL80V_BASERATE
  UTL70V_OUTPTDEFN
  UTL61V_JOBSTEPPARM
  UTL30V_TIMEZONE
  UTL21V_DTEEXPAND
  UTL20V_CALENDAR
  UTL16V_APPLRUNHST
  UTL15V_CHKPNT
  UTL03V_FLDCDEXREF
  UTL02V_CODE
  UTL01V_CODEDEFN
  SVC03V_SVCRCNTCT
  SVC02V_SVCRADDR
  SVC01V_SERVICER
  MSG01V_MESSAGE
  LTR01V_LETTERPROF
  EPD15V_ODFICAL
  EPD14V_ODFI
  EPD13V_SVCRODFI
  EPD12V_SVCREPD
  DOC50V_DOCPRNTQUE
  DOC04V_DOCUTOC
  DOC02V_DOCUTXT
  DOC01V_DOCULINK
  CST14V_DISCPGMNAME
  CST13V_DISCPGMQUAL
  CST12V_DISCPGMGNTR
  CST11V_DISCPGLNTYP
  CST10V_DISCPGMPROF
  CST01V_CUSTOMER
  BAT01V_EXCPMESSAGE
  ADM63V_BULLTN
  ADM60V_DBCOLLDEFN
  ADM50V_ACCVIOLTN
  ADM47V_AREADEFN
  ADM46V_GRPSUBSYS
  ADM45V_JOBDEFN
  ADM44V_DEPTDEFN
  ADM40V_GRPPROF
  ADM24V_MENUITEM
  ADM23V_MNESUBSYS
  ADM22V_MNEMONIC
  ADM21V_SECURITY
  ADM05V_TERMID
  ADM04V_USERJOBGRP
  ADM03V_USERJOBCRD
  ADM02V_USERLANG

  5. Edit the DDL created in step #4 and apply the name changes
  appropriate for the target creator id.

  6. Use Platinum's RC/Migrator Recovery Snapshot facility to create a
  DDL file of all the object definitions and dependant object
  definitions (TABLEs, INDEXs, VIEWs) for a specific list of
  "stage-level" TABLESPACEs.  These are tablespaces for tables that are
  shared in production but which cause problems when shared by
  development projects.  Consequently, they are created as separate
  physical objects in the target database instead of as base views
  pointing to a shared set of physical objects.  The list of tablespaces
  at this time is:



  USUTLS07
  USUTLS75
  USWRKS50

  7. Edit the DDL created in step #6 and apply the name changes
  appropriate for the target database and target creator id.  Also apply
  the space percentage calculations against the primary and secondary
  space allocations to size down for the testing environment.

  8. If Drop Conflicting Objects In Target Database is "Y", then create
  DROP DDL statements for all the new objects to be created in the
  target environment.

  9. If Drop Conflicting Objects In Target Database is "Y", then run the
  DDL created in step #8.

  10. Run the DDL created in step #6.

  11. Run the DDL created in step #4.

  12. Run the DDL created in step #2.
            


Leave a Reply

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