General token templates
General token templates are provided to give the ability to implement your own automated changes for SQL and BIND components as they are moved through the lifecycle, without having to wait for specific keyword support to be programmed into the relevant utilities.
SQL general token templating
Sysin supplied to CMNDB2DD to implement a general token template:
TOKENNAME=
TOKENSRCT=
TOKENTGTT=
The parameters allow one to define one's own SQL parameters to be templated via 'standard' CMNDB2DD templating. Freeform token processing will take place in addition to and after all the existing fixed name clause processing (e.g. after the likes of owner, qualifier etc. templating). Lists of subparameters are supported by applying the relevant template to each of the subparameters in turn. Subparameters in a list can be removed by having the source template match the individual list entry and using target template of >REMOVE\<.
TOKENNAME= specifies a string which will be looked for in the SQL. This may include imbedded blanks as long as the whole string is enclosed in single quotes. If there are no imbedded blanks then quotes are optional. Strings including imbedded blanks must not contain more than 5 subwords (and each subword must be 16 bytes or less – this should cover all sensible requirements).
In the SQL, to be recognized the token name may be preceded by either a blank or a comma, and followed by a blank or a left hand bracket.
The next word following the token will be templated according to the standard rules with the (optional) source template being supplied via TOKENSRCT and the (required) target template by TOKENTGTT.
As many of these groups as one needs may be specified. They are processed sequentially. The code applies the template to each found occurrence of TOKENNAME (it doesn’t stop looking after the first found, only stopping when the current SQL sentence is exhausted). The resulting SQL is then subject to the next set of TOKEN templates and so on.
The TOKENNAME value may be up to 64 bytes The TOKEN template fields may be up to 128 bytes and can be specified across lines as per the other fixed name templates.
An example is:
TOKENNAME='ORDER BY'
TOKENSRCT=NAME
TOKENTGTT=CREATOR
This will look for the clause ‘order by’ in the SQL it will then look beyond that clause for the first word following on from there and, in this case, if it finds NAME it will replace it with CREATOR.
BIND general token templating
As CMNDB2PL parses the BIND command as a TSO command, each command parameter, and its subparameters, are addressed as discrete variables in the program. As such, the general token templating feature implemented for the BIND process is different to that for the SQL process. Instead of parsing the command string as a whole looking for one or more parameter strings, we match the exact general token name against those known to the program.
Apart from this difference, BIND processing general templating is similar to that implemented for the SQL process.
Bind parameters currently supported by general token templating are given here:
ACQUIRE
CACHESIZE
COPY
CURRENTDATA
CURRENTSERVER
DBPROTOCOL
DEGREE
DISCONNECT
DYNAMICRULES
ENCODING
EXPLAIN
FLAG
IMMEDWRITE
ISOLATION
KEEPDYNAMIC
LIBRARY
MEMBER
OPTHINT
OWNER
PACKAGE name
PATH
PKLIST
PLAN name
QUALIFIER
RELEASE
REOPT
REPLVER
ROUNDING
SQLERROR
SQLRULES
VALIDATE
General token templates allow collection names in the PKLIST bind parameter to be individually templated and, as required, removed from the PKLIST.
This section presents 4 test examples, one plan and 3 package bind control components.
Plan member (DBB)
TEST1:
BIND PLAN(TEST1) -
PKLIST(CA_TNG_SUBROUTINES.*, -
AD_SHR_ROUTINES.*, -
CR_UTILITIES.*) -
OWNER (DBPPMGS) -
QUALIFIER (DB2PMGS) -
VALIDATE (RUN) -
ISOLATION (CS) -
RELEASE (COMMIT) -
EXPLAIN (YES) -
ACTION (REPLACE) -
PATH(DB2PRTB,MGS)
Package members (PKG)
TEST2:
BIND PACKAGE(TEST2CCA0P05) MEMBER(EI58VUSP) VALIDATE(BIND) -
ISOLATION(CS) EXPLAIN(YES) CURRENTDATA(YES) -
CURRENTSERVER(DB2DSNX) DEGREE(0) ACTION(REPLACE) -
PATH(EPICPM05,EPICPMCR,EPICPMCN,EPICPMCF,EPICPMCS,EPICPXXX,EPICPYYY, -
EPICPMCY,EPICPMCT,EPICPMCU,EPICPMCY,EPICPMCT,EPICPMCU)
TEST3:
BIND PACKAGE(TEST3) -
OWNER (ASCMG) -
MEMBER (EI58VUSP) -
QUALIFIER (EPICPO05) -
VALIDATE (BIND) -
ISOLATION (CS) -
EXPLAIN (NO) -
CURRENTDATA (YES) -
DEGREE (1) -
ACTION (REPLACE) -
PATH
(EPICPM05,EPICPMCR,EPICPMCN,EPICPMCF,EPICPMCS,EPICPXXX,EPICPYYY, -
EPICPMCY,EPICPMCT,EPICPMCU, -
EPICPMCY,EPICPMCT,EPICPMCU) |
TEST4:
BIND PACKAGE(TEST4) -
OWNER(ASCMG) -
MEMBER(EI58VUSP) -
QUALIFIER(EPICPO05) -
VALIDATE(BIND) -
ISOLATION(CS) -
EXPLAIN(YES) -
CURRENTDATA(YES) -
CURRENTSERVER(XXXPROD) -
DEGREE(1) -
ACTION(REPLACE) -
PATH(EPICPM05)
Here are the admin panels for a promotion logical subsystem for which there are active bind libraries defined. First the named templates:
CMNGD2L2 Db2 Logical Subsystem UNIT2 Bind Named Templates
Command ===> ___________________________________________________________________
Templates Target Source Insert
General:
Schema . . . . . __________________ + __________________ + __________________
Qualifier . . . DEV + __________________ + DEV +
Bind owner . . . DEV + __________________ + DEV +
Plan:
Name . . . . . ________ ________
Package:
Location . . . __________________ + __________________ + __________________
Collection . . __________________ + __________________ + __________________
And the general templates:
CMNLD2AL Db2 Logical Subsystem UNIT1 BIND General Template Row 1 to 6 of 6
Command ===> _________________________________________________ Scroll ===> CSR
Token name + Target template + Source template +
_____ CURRENTSERVER ???DSNT ______________________
_____ DEGREE 1 ______________________
_____ EXPLAIN YES ______________________
_____ PATH ????U??? ______________________
_____ PKLIST >REMOVE< CR_UTIL
_____ PKLIST UA_SHR AD_SHR
******************************** Bottom of data ********************************
This set of definitions created a CMNDB2PL step with CMNPLCTL input which looked like this:
TYPE=PROMOTE
AUTHORITY=OWNER,INSERT
INSERTQUAL
*EARLYCHECK
*IGNORENOSUBSYS
*TRACE
USEREXIT=(ASM,NOUNLOAD)
USERID=SDOWNES
PACKAGE=STEV000365
PROJECT=STEV
NOBASEDBBRC=12
WARNINGRC=4
USEDB2PACKAGE
*NODB2PLAN
*FREEPLAN
*CREATECC
*IGNORENODBRM
*PKLTEMPLATE
DB2ID=D20L
LOGICAL=UNIT1
PLANTGT=
PLANSRC=
PKGETGT=
PKGESRC=
LOCNTGT=
LOCNSRC=
QUALIFIER=DEV
QUALTGT=DEV
QUALSRC=
OWNER=DEV
OWNRTGT=DEV
OWNRSRC=
TOKENNAME=PKLIST
TOKENSRCT=AD_SHR
TOKENTGTT=UA_SHR
TOKENNAME=PKLIST
TOKENSRCT=CR_UTIL
TOKENTGTT=
>REMOVE<
TOKENNAME=PATH
TOKENSRCT=
TOKENTGTT=????U???
TOKENNAME=EXPLAIN
TOKENSRCT=
TOKENTGTT=YES
TOKENNAME=DEGREE
TOKENSRCT=
TOKENTGTT=1
TOKENNAME=CURRENTSERVER
TOKENSRCT=
TOKENTGTT=???DSNT
REMOTEID=STEVEPRM
When the job ran the bind control output generated looked like this:
DSN SYSTEM(D20L )
BIND PACKAGE(TEST2CCA0P05) +
OWNER(DEV) +
QUALIFIER(DEV) +
PATH(+
EPICUM05,+
EPICUMCR,+
EPICUMCN,+
EPICUMCF,+
EPICUMCS,+
EPICUXXX,+
EPICUYYY,+
EPICUMCY,+
EPICUMCT,+
EPICUMCU,+
EPICUMCY,+
EPICUMCT,+
EPICUMCU) +
ACTION(REPLACE) +
CURRENTDATA(YES) +
CURRENTSERVER(DB2DSNT) +
DEGREE(1) +
EXPLAIN(YES) +
ISOLATION(CS) +
VALIDATE(BIND) +
MEMBER(EI58VUSP)
BIND PACKAGE(TEST3) +
OWNER(DEVMG) +
QUALIFIER(DEVCPO05) +
PATH(+
EPICUM05,+
EPICUMCR,+
EPICUMCN,+
EPICUMCF,+
EPICUMCS,+
EPICUXXX,+
EPICUYYY) +
ACTION(REPLACE) +
CURRENTDATA(YES) +
DEGREE(1) +
EXPLAIN(YES) +
ISOLATION(CS) +
VALIDATE(BIND) +
MEMBER(EI58VUSP)
BIND PACKAGE(TEST4) +
OWNER(DEVMG) +
QUALIFIER(DEVCPO05) +
PATH(+
EPICUM05) +
ACTION(REPLACE) +
CURRENTDATA(YES) +
CURRENTSERVER(XXXDSNT) +
DEGREE(1) +
EXPLAIN(YES) +
ISOLATION(CS) +
VALIDATE(BIND) +
MEMBER(EI58VUSP)
BIND PLAN(TEST1) +
OWNER(DEVPMGS) +
QUALIFIER(DEVPMGS) +
PATH(+
DB2PUTB,+
MGSU) +
RELEASE(COMMIT) +
ACTION(REPLACE) +
EXPLAIN(YES) +
ISOLATION(CS) +
VALIDATE(RUN) +
PKLIST(CA_TNG_SUBROUTINES.*,+
UA_SHR_ROUTINES.\*)
END
Notes
In the TEST2 package the owner and qualifier parameters were missing but have been inserted (as directed) by the templating process. In all other cases the owner and qualifier have had their first three characters overlaid by DEV. This is not new functionality, just a test of existing processes.
In all cases the fifth character of the path name has been replaced by U and this has occurred for each of the subparameters in the list. In TEST1 one of the PATH subparameters is not long enough to have its fifth character replaced by U (i.e. MGS) so the U has been appended (i.e. MGSU).
Where CURRENTSERVER is present it has had positions 4-7 replaced with DSNT
DEGREE has been set to 1
EXPLAIN has been set to YES.
The PKLIST in TEST1 has had the CR_UTILITIES.* entry removed (as it matched with one of the source templates for PKLIST where the target template was >REMOVE<). And the AD_SHR_ROUTINES.* entry has been changed to UA_SHR_ROUTINES.*
A production install logical subsystem was set up with the following admin settings, first the named templates:
CMNLD2AL Db2 Logical Subsystem PRODN BIND Named Templates
Command ===> ________________________________________________________
Templates Target Source Insert
General:
Qualifier . . . PROD + __________________ + PROD +
Owner . . . . . PROD + __________________ + PROD +
Plan:
Name . . . . . ____________________________________________________________
Package:
Location . . . __________________ + __________________ + __________________
Collection . . __________________ + __________________ + __________________
And the general templates:
CMNLD2AL Db2 Logical Subsystem UNIT1 BIND General Template Row 1 to 6 of 6
Command ===> _________________________________________________ Scroll ===> CSR
Token name + Target template + Source template +
_____ CURRENTSERVER ???DSNP ______________________
_____ DEGREE 0 ______________________
_____ EXPLAIN NO¬ ______________________
_____ PATH >REMOVE< EPICPXXX
_____ PATH >REMOVE< EPICPYYY
_____ PATH ????P???
_____ PKLIST CA_PRD CA_TNG
******************************** Bottom of data ********************************
When the same 4 components were installed the following bind control was generated:
DSN SYSTEM(D20L )
BIND PACKAGE(TEST2CCA0P05) +
OWNER(PROD) +
QUALIFIER(PROD) +
PATH(+
EPICPM05,+
EPICPMCR,+
EPICPMCN,+
EPICPMCF,+
EPICPMCS,+
EPICPMCY,+
EPICPMCT,+
EPICPMCU,+
EPICPMCY,+
EPICPMCT,+
EPICPMCU) +
ACTION(REPLACE) +
CURRENTDATA(YES) +
CURRENTSERVER(DB2DSNP) +
DEGREE(0) +
EXPLAIN(NO) +
ISOLATION(CS) +
VALIDATE(BIND) +
MEMBER(EI58VUSP)
BIND PACKAGE(TEST3) +
OWNER(PRODG) +
QUALIFIER(PRODPO05) +
PATH(+
EPICPM05,+
EPICPMCR,+
EPICPMCN,+
EPICPMCF,+
EPICPMCS) +
ACTION(REPLACE) +
CURRENTDATA(YES) +
DEGREE(0) +
EXPLAIN(NO) +
ISOLATION(CS) +
VALIDATE(BIND) +
MEMBER(EI58VUSP)
BIND PACKAGE(TEST4) +
OWNER(PRODG) +
QUALIFIER(PRODPO05) +
PATH(+
EPICPM05) +
ACTION(REPLACE) +
CURRENTDATA(YES) +
CURRENTSERVER(XXXDSNP) +
DEGREE(0) +
EXPLAIN(NO) +
ISOLATION(CS) +
VALIDATE(BIND) +
MEMBER(EI58VUSP)
BIND PLAN(TEST1) +
OWNER(PRODMGS) +
QUALIFIER(PRODMGS) +
PATH(+
DB2PPTB,+
MGSP) +
RELEASE(COMMIT) +
ACTION(REPLACE) +
EXPLAIN(NO) +
ISOLATION(CS) +
VALIDATE(RUN) +
PKLIST(CA_PRD_SUBROUTINES.*,+
AD_SHR_ROUTINES.*,+
CR_UTILITIES.*)
END
Notes
In the TEST2 package the owner and qualifier parameters were missing but have been inserted (as directed) by the templating process. In all other cases the owner and qualifier have had their first four characters overlaid by PROD. This is not new functionality, just a test of existing processes.
In all cases the fifth character of the path name has been replaced by P and this has occurred for each of the subparameters in the list. In TEST1 one of the PATH subparameters is not long enough to have its fifth character replaced by P (i.e. MGS) so the U has been appended (i.e. MGSP). The original bind component already had P in the 5th position in most cases.
Where CURRENTSERVER is present it has had positions 4-7 replaced with DSNP.
DEGREE has been set to 0.
EXPLAIN has been set to NO.
Values EPICPXXX and EPICPYYY have been removed where they have been found in any PATH list.
The PKLIST in TEST1 has had the CA_TNG_ROUTINES. entry changed to CA_PRD_ROUTINES.
Simple bindcntl can also be used for testing etc e.g.:
CBLDB201.PKG:
BIND PACKAGE(PROD) MEMBER(CBLDB201) ACT(REP) ISO(CS) -
EXPLAIN(YES) VALIDATE(BIND) RELEASE(COMMIT) -
QUALIFIER(PROD)
CBLDB201.DBB:
BIND PLAN(CBLDB201) -
PKLIST(PROD.CBLDB201) -
ACT(REP) -
EXPLAIN(YES) -
ISOLATION(CS) -
QUALIFIER(PROD)
Named templates:
CMNLD2L Db2 Logical Subsystem UNIT2 BIND Named Templates
Command ===>
Templates Target Source Insert
General:
Qualifier . . . UNIT¬¬¬¬ + __________________ + UNIT +
Owner . . . . . SERD¬¬¬¬ + __________________ + SERD +
Plan:
Name . . . . . _________
Package:
Location . . . __________________ + __________________ +
Collection . . UNIT + PROD +
General templates:
CMNLD2L4 Db2 Logical Subsystem UNIT2 BIND General Template Row 1 to 4 of 4
Command ===> ___________________________________________________ Scroll ===> CSR
Token name + Target template + Source template +
_____ EXPLAIN NO¬ ___________________
_____ ISOLATION UR CS
_____ PKLIST UNIT PROD
_____ VALIDATE RUN¬ ___________________
******************************** Bottom of data ********************************
Bind output:
IKJ56644I NO VALID TSO USERID, DEFAULT USER ATTRIBUTES USED
READY
DSN SYSTEM(D20L )
DSN
BIND PACKAGE(UNIT) OWNER(SERD) QUALIFIER(UNIT) RELEASE(COMMIT) ACTION(REP)
EXPLAIN(NO) ISOLATION(UR) VALIDATE(RUN) MEMBER(CBLDB201)
DSNT254I -D20L DSNTBCM2 BIND OPTIONS FOR
PACKAGE = D20L.UNIT.CBLDB201.()
ACTION ADD
OWNER SERD
QUALIFIER UNIT
VALIDATE RUN
EXPLAIN NO
ISOLATION UR
RELEASE COMMIT
COPY
APREUSE
APCOMPARE
APRETAINDUP
BUSTIMESENSITIVE YES
SYSTIMESENSITIVE YES
ARCHIVESENSITIVE YES
APPLCOMPAT V12R1M500
DESCSTAT YES
APREUSESOURCE
DSNT255I -D20L DSNTBCM2 BIND OPTIONS FOR
PACKAGE = D20L.UNIT.CBLDB201.()
SQLERROR NOPACKAGE
CURRENTDATA NO
DEGREE 1
DYNAMICRULES
DEFER
NOREOPT VARS
KEEPDYNAMIC NO
IMMEDWRITE INHERITFROMPLAN
DBPROTOCOL DRDA
OPTHINT
ENCODING EBCDIC(00037)
PLANMGMT OFF
PLANMGMTSCOPE STATIC
CONCURRENTACCESSRESOLUTION
EXTENDEDINDICATOR
PATH
DSNT275I -D20L DSNTBCM2 BIND OPTIONS FOR
PACKAGE = D20L.UNIT.CBLDB201.()
QUERYACCELERATION
GETACCELARCHIVE
CONCENTRATESTMT
DSNT232I -D20L SUCCESSFUL BIND FOR
PACKAGE = D20L.UNIT.CBLDB201.()
DSN
BIND PLAN(CBLDB201) OWNER(SERD) QUALIFIER(UNIT) ACTION(REP) EXPLAIN(NO)
ISOLATION(UR) PKLIST(UNIT.CBLDB201)
DSNT252I -D20L DSNTBCM1 BIND OPTIONS FOR PLAN CBLDB201
ACTION REPLACE
OWNER SERD
VALIDATE RUN
ISOLATION UR
ACQUIRE USE
RELEASE COMMIT
EXPLAIN NO
DYNAMICRULES RUN
PROGAUTH DISABLE
DSNT253I -D20L DSNTBCM1 BIND OPTIONS FOR PLAN CBLDB201
NODEFER PREPARE
CACHESIZE 3072
QUALIFIER UNIT
CURRENTSERVER
CURRENTDATA NO
DEGREE 1
SQLRULES DB2
DISCONNECT EXPLICIT
NOREOPT VARS
KEEPDYNAMIC NO
IMMEDWRITE NO
DBPROTOCOL DRDA
OPTHINT
ENCODING EBCDIC(00037)
CONCURRENTACCESSRESOLUTION
PATH
DSNT200I -D20L BIND FOR PLAN CBLDB201 SUCCESSFUL
DSN
END