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.