Skip to content

Process Overview

Bind service components come in two flavors:

  • There is the bind service component itself, which consists of the bind service command and any related service SQL. The bind command is placed first in the component and terminated with a semicolon. The SQL follows on from the semicolon (see below for examples).
  • There is the service package grant component, which specifies the userids/groups who are allowed to execute the service package.

Both sets of components have templates applied to them so that the production version of the components can be modified to be applied to the various test levels through the lifecycle.

These components can be hosted by PDS/E library types or by zFS library types. The latter are more useful as the component (both flavors) must have the same name as the service itself. Db2 allows the service name to be up to 128 bytes in length (a PDS/E libtype would restrict this to 8 bytes).

An example of a bind service component in a package staging directory is:

VIEW      /cmndev/cmnj/STEV/#000445/BSZ/Get_Area_Regression_Info
Command ===> __________________________________________________________________
*********************************** Top of Data *******************************
000001 BIND SERVICE(PRD) +
000002 NAME("Get_Area_Regression_Info") +
000003 SQLENCODING(EBCDIC) +
000004 DESCRIPTION('CREATED USING BIND SERVICE') +
000005 PATH( PRD , SCHEMA1 , +
000006 SCHEMA2, +
000007 SCHEMA3 ) +
000008 QUALIFIER(CMNJ) +
000009 ISOLATION(UR) +
000010 OWNER(WSER58) ;
000011 CALL GETAREA_REGINFO (:AREANAME)
*********************************** Bottom of Data ****************************

The component name is Get_Area_Regression_Info, which is the same as the service name.

The bind service command is terminated by the semicolon on line 10. The service SQL is on line 11.

The bind service command will result in a Db2 package being created which will have a name the same as the NAME clause and a collection id the same as the SERVICE clause.

Templates are provided to allow the ZMF processes (promote, and so on) to change the collection id, the qualifier, and the owner.

General token templates will also be provided for anything else. Only the bind service command itself is templated; the SQL is not touched.

Clauses such as the qualifier will be applied by Db2 to the SQL it executes on behalf of the service call.

For now, each bind parameter and SQL line must be completed within the first 72 columns of the component (just like a standard bind command).

This is an example of the bind service grant component associated with this bind service component (that is, it has the same name, different libtype):

...

VIEW      /cmndev/cmnj/STEV/#000445/BSG/Get_Area_Regression_Info
Command ===> ____________________________________________________________________
************************************ Top of Data *********************************
000001 GRANT EXECUTE ON PACKAGE PRD."Get_Area_Regression_Info" TO DBCORPR, GENID1,
000002 GENID2, GENID12;
*********************************** Bottom of Data *******************************

You can have as many GRANTs as you like in this component, separated by semicolons. The authorization id’s can be listed all on one GRANT or separated onto individual GRANTs or a mixture.

Once again, templating will be applied to the collection id (PRD in the above example). There is a special general token template (>GRANTEE<) which can be used against the authorization id list (see below).

The GRANT SQL must be contained within the first 80 bytes of each record.

Batch Utility Overviews

CMNDB2SV – Process Bind Service Parameter/SQL and Grant Request Components

This program takes the bind service component and splits it up into the bind command and the associated SQL for the service. It also processes any related grant request components.

Bind parameters and grant requests are templated as described above. There are (potentially) three resultant parameter sets output for each named service:

  1. The bind command for the service

  2. The SQL associated with the service

  3. The grant and/or revoke requests associated with the service.

The bind parameters and the SQL are passed to a subsequent execution of IKJEFT01, which will run the binds. The grant requests are passed to a subsequent execution of CMNDB2GR (details below).

Here is some typical JCL showing the promotion of the components associated with two Db2 REST services:

    //SRVBSZ EXEC PGM=CMNDB2SV,       *** BIND SERVICE PROCESS
    //            COND=(4,LT),       *** FOR LIBTYPE BSZ
    //            REGION=0M
    //SYSPRINT DD  SYSOUT=*
    //SERPRINT DD  SYSOUT=*
    //SYSUDUMP DD  SYSOUT=*
    //CMNZFSIN DD  PATH='/cmndev/CMNKP/STEV/#000436/BSZ',
    //            PATHOPTS=ORDONLY
    //CMNGRANT DD  PATH='/cmndev/CMNKP/STEV/#000436/BSG',
    //            PATHOPTS=ORDONLY
    //*
    //SYSIN    DD  *
    TYPE=PROMOTE
    DB2ID=D10L
    LOGICAL=UNIT1
    GENERATEREVOKES=YES
    COLLSRC=PRD
    COLLTGT=DEVU
    QUALSRC=
    QUALTGT=WSER58¬¬¬¬¬¬¬¬¬¬
    OWNRSRC=
    OWNRTGT=SERD¬¬¬¬¬¬¬¬¬¬¬¬
    TOKNAME=>GRANTEE<
    TOKNSRC='DBCORPR'
    TOKNTGT=DBCORP4
    TOKNAME=>GRANTEE<
    TOKNSRC='GENID1'
    TOKNTGT=TGENID1
    TOKNAME=>GRANTEE<
    TOKNSRC='GENID2'
    TOKNTGT=TGENID3
    FILE=SQL00001,zFScomponentName001
    FILE=SQL00002,zFScomponentName002
    /*
    //*
    //* OUTPUT TEMPORARY FILES FOR THE BIND SERVICE COMPONENT SQL
    //*
    //SQL00001 DD DISP=(,PASS),DSN=&&SQL00001,
    //            UNIT=SYSDA,SPACE=(TRK,(5,10),RLSE),
    //            DCB=(DSORG=PS,LRECL=80,RECFM=FB,BLKSIZE=0)
    //SQL00002 DD DISP=(,PASS),DSN=&&SQL00002,
    //            UNIT=SYSDA,SPACE=(TRK,(5,10),RLSE),
    //            DCB=(DSORG=PS,LRECL=80,RECFM=FB,BLKSIZE=0)
    //*
    //* OUTPUT TEMPORARY FILE FOR THE BIND SERVICE PARAMETERS
    //*
    //D10LBCTL DD DISP=(MOD,PASS),DSN=&&D10LBCTL,
    //            UNIT=SYSDA,SPACE=(TRK,(15,1),RLSE),
    //            DCB=(DSORG=PS,LRECL=80,RECFM=FB,BLKSIZE=0)
    //*
    //* OUTPUT TEMPORARY FILE FOR THE GRANT SQL
    //*
    //D10LGCTL DD DISP=(MOD,PASS),DSN=&&D10LGCTL,
    //            UNIT=SYSDA,SPACE=(TRK,(15,1),RLSE),
    //            DCB=(DSORG=PS,LRECL=80,RECFM=FB,BLKSIZE=0)

The skeletons are set up to cope with bind service and grant requests components at the same time, or individually.

The output DDnames for the service SQL are generated dynamically by skeleton logic – these same DDnames must be used by the follow-on IKJEFT01 step.

One of the bind service parameters is SQLDDNAME, which is used by the bind service command to find the SQL associated with the service. This DDname, if specified in the input component, is ignored (and, in fact, stripped out); we generate our own SQLDDNAME parameter value for each service being processed.

The input bind service components are read from the CMNZFSIN ddname (if the staging library is a zFS libtype; CMNPDSIN if not). The separator between the bind parms and the associated SQL is a semicolon. For example:

    BIND SERVICE(PRD) -
         NAME("zFScomponentName001") -
         SQLDDNAME(SQL) -
         SQLENCODING(EBCDIC) -
         DESCRIPTION('From an ERO package') -
         QUALIFIER(SCD) -
         ISOLATION(UR) -
         OWNER(WSER58) ;
    SELECT * FROM CMNADMIN_GENERAL
            WHERE PROCESS_IND = 'V'

Note that the SQLDDNAME parm is included above. CMNDB2SV will remove this before adding our own. (In this example we will replace it with SQLDDNAME(SQL000001.)

After separating the two parts of the component, CMNDB2SV will apply templates as specified in the sysin. (See below for full list of sysin parms for CMNDB2SV).

It will restructure the bind parameters to make applying templates more tractable. In this example, the bind parms written to D10LBCTL, after templates have been applied, look like this:

    BIND SERVICE(DEVU) +
    NAME("zFScomponentName001") +
    SQLENCODING(EBCDIC) +
    DESCRIPTION('From an ERO package') +
    QUALIFIER(WSER58) +
    ISOLATION(UR) +
    OWNER(SERD) +
    SQLDDNAME(SQL00001)

If Grant request components are present, these components are also restructured. They are broken up into one grant request per service and authorization id and templates applied (especially note the >GRANTEE< special template described above).

For example, the grant request component for this particular service looks like this:

    GRANT EXECUTE ON PACKAGE PRD."zFScomponentName001" TO DBCORPR,
        GENID1, EROID, GENID3, BILYBOB, GENID12;

Note that grant request parms must be contained within the first 80 bytes of the record.

After templating CMNDB2SV, put the following out to DDname D10LGCTL:

    GRANT EXECUTE ON PACKAGE DEVU."zFScomponentName001" TO DBCORP4;
    GRANT EXECUTE ON PACKAGE DEVU."zFScomponentName001" TO TGENID1;
    GRANT EXECUTE ON PACKAGE DEVU."zFScomponentName001" TO EROID;
    GRANT EXECUTE ON PACKAGE DEVU."zFScomponentName001" TO GENID3;
    GRANT EXECUTE ON PACKAGE DEVU."zFScomponentName001" TO BILYBOB;
    GRANT EXECUTE ON PACKAGE DEVU."zFScomponentName001" TO GENID12;

CMNDB2SV will query the Db2 catalog to see whether a grant request is actually needed. If the request is already satisfied, it will not be passed on (to avoid unnecessary SQL errors stating that the grantee already has the privilege, sqlcode +562). Requests to grant privileges to the current authorization id (that is, the RACF userid of the promotion (and so on) job being run) will not be propagated (to avoid -554 sql errors).

Similarly, if GENERATEREVOKES=YES is used, and if catalog grantee’s exist that are not reflected in the grant component for this service, then REVOKE requests will be generated to have them removed from the catalog.

Revoke requests for the current authorization id will not be generated (that is, they would lead to -555 sql errors).

The skeletons are set up to cope with just bind service components, just grant components, or both in any one run.

SYSIN parameters for CMNDB2SV (records that start with an asterisk are comments and are ignored):

SYSIN Parameter Description
TYPE=[PROMOTE/DEMOTE/ INSTALL/BACKOUT Processing differs slightly for each type of ZMF lifecycle action. There is no default.
TRACE=YES/NO Db2 CAF traffic will be traced to SERPRINT (default is NO).
AUTOFREE=YES/NO If the service to be bound is already present at the target Db2, do we issue a FREE SERVICE first? Note that there is no such thing as ACTION(REPLACE) for a service bind. If the service is already present, the bind will fail. The default for the AUTOFREE parm is YES.
GENERATEREVOKES=YES/ NO When processing grant requests, do we want to generate REVOKE requests for ids that currently have authority for the target bind service but that do not appear in the (templated) list presented in the grant component? For example, if userid WSER58 has execute authority on DEVU.”zFScomponentName001” will we generate a REVOKE for it based on the fact that it doesn’t appear in the list presented above? The default for the GENERATEREVOKES parm is NO. If this parm is set to YES then, in the example above, CMNDB2SV would generate this REVOKE request: REVOKE EXECUTE ON PACKAGE DEVU."zFScomponentName001" FROM WSER58 NOT INCLUDING DEPENDENT PRIVILEGES; DB2ID=ssss where ssss is the target Db2 subsystem id for this set of processes. There is no default.
LOGICAL=*xxxxxxxx The ZMF logical subsystem name that has generated the templates and so on for this run. This name is only used in messaging; CMNDB2SV makes no actual connection to ZMF.
COLLSRC= The source template for the collection id. The collection id is the value specified in the BIND SERVICE() parameter and is used by Db2 to qualify the package name for the service.
COLLTGT= The target template for the collection id.
QUALSRC= The source template for the qualifier.
QUALTGT= The target template for the qualifier.
OWNRSRC= The source template for the owner.
OWNRTGT= The target template for the owner.
TOKNAME= The name of a general token (>GRANTEE< is a special case, see above).
TOKNSRC= The source template for this token.
TOKNTGT= The target template for this token.

You can specify as many TOKNAME, TOKNSRC, TOKNTGT general token triplets as you need.

SYSIN Parameter Description
FILE=sqlddname, *ffffffffffffffffffffffffffffffffffffffffff zFS file name to be read from the CMNZFSIN DD statement to be parsed for DDL/SQL to be templated and passed through to the ddddBCTL ddname.
MBR=sqlddname, *mmmmmmmm MVS member name to be read from the CMNPDSIN DD statement to be parsed for DDL/SQL to be templated and passed through to the ddddBCTL ddname. For both FILE= and MBR=, the first parameter, sqlddname, is intended to be generated by the file tailoring process. This name will be inserted into the SQLDDNAME= bind service parameter and will be added to the follow-on bind step.

The case of the SYSIN keywords is not significant. Also, where the keyword value is unambiguous (for example, YES/NO, PROMOTE/INSTALL/and so on) the specified value is not case-sensitive.

CMNDB2SV will generate the SQL for this service into a temporary dataset which will be passed to the bind step.

Note

For isolation testing purposes you can replace the output ddnames in the CMNDB2SV step with SYSOUT to see what the program has generated without actually doing anything.

CMNDB2SV itself does not change anything in the Db2 catalog. For example, in the above step you could specify this:

    //*
    //* OUTPUT TEMPORARY FILES FOR THE BIND SERVICE COMPONENT SQL
    //*
    //SQL00001 DD SYSOUT=*
    //SQL00002 DD SYSOUT=*
    //*
    //* OUTPUT TEMPORARY FILE FOR THE BIND SERVICE PARAMETERS
    //*
    //D10LBCTL DD SYSOUT=*
    //*
    //* OUTPUT TEMPORARY FILE FOR THE GRANT SQL
    //*
    //D10LGCTL DD SYSOUT=*

CMNDB2GR - Process Grant Requests Passed by CMNDB2SV

Note that we could equally well process the grant requests using the IBM-supplied sample program DSNTEP2. However, some value is added with the CMNDB2GR utility.

The program reads the grant/revoke requests passed by CMNDB2SV (or anything else for that matter) and presents them to the target Db2 and reports back on the success of the operation.

It allows you to create a list of SQL codes which are of no significance as well as to set a return code of your choosing should any of the requests fail. Typical JCL, taken from a promote job, looks like this:

    //D10LGRN EXEC PGM=CMNDB2GR, *** PERFORM GRANTS
    // COND=(4,LT)
    //SYSPRINT DD SYSOUT=*
    //SERPRINT DD SYSOUT=*
    //SYSUDUMP DD SYSOUT=*
    //*
    //* GRANT SQL
    //*
    //CMNGRANT DD DISP=(OLD,DELETE),DSN=&&D10LGCTL
    //SYSIN DD * 
    db2Id=D10L 
    ignoreSQLcode=-556
    /*

The available SYSIN parameters are (records that start with an asterisk are comments and are ignored):

SYSIN Parameter Description
TRACE=YES/NO Db2 CAF traffic will be traced to SERPRINT. Default is NO.
ERRORRC=*nnn Set up to three-digit step completion code should any of the requests be deemed to have failed. Default is 8.
DB2ID=*ssss The target Db2 subsystem id for this set of processes. No default.
IGNORESQLCODE=-nnnnn or +*nnnnn An SQL code that is not to be considered as an error situation. That is, the step condition code will remain zero if this sqlcode is encountered for one or more requests. You can code up to 1,024 of these parameters.
PROMOTE The promote action will attempt to bind the Db2 REST service requests and process any grant components included in the promotion.
DEMOTE The demote action will free the Db2 REST services. No grant/revoke processing will take place. Freeing a service will result in authorities granted to that service being dropped from the catalog anyway.
INSTALL The install action will attempt to bind the Db2 REST service requests and process any grant components included in the install.
BACKOUT The backout action will attempt to bind the Db2 REST service requests and process any grant components included in the original install but based on the components as they exist in the backout library (if they exist).

The case of the SYSIN keywords is not significant. Also, where the keyword value is unambiguous (for example, YES/NO, PROMOTE/INSTALL/and so on) the specified value is not case-sensitive.