Skip to content

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