Skip to content

Native SQL SP Versions and Bind Deploy

The following are some screen shots designed to show various aspects of the Native SQL stored procedure support using Db2 versioning and the BIND DEPLOY distribution methodology.

Note that the 'standard' processes associated with the drop/create methodology are similar to the existing DDL processes. There are added facilities such as the HLL exit point and the PASSTHRU facility which are described elsewhere in this document.

This section is intended to help you get to grips with support for Db2 versioning and BIND DEPLOY.

In this example, we are using Data Studio to generate/change the stored procedure definitions/SQL. Data Studio then deploys to our target Db2 subsystem (in this case this is DSN on U001).

We then stage from the Db2 catalog directly into a ZMF package and proceed to promote to a local site (using Db2 versioning but not bind deploy) and to remote site (using both Db2 versioning and bind deploy). The install to production uses versioning/bind deploy and we install to U810DP (which is the DSN Db2 subsystem) and U810P (which is for the DSN1 subsystem).

The library type for our Native SQL SP components is NSQ.

Here are some admin definitions.

First, the local site promote library. Note that the 'Cleanup Level' flag is set to 'N'. It is assumed that users will not want their promoted SPs to be dropped automatically as there is no concept of a search hierarchy for SPs in the same way as their might be for load modules.

When we promote to this level we will copy the SP component (which is a PDS member) to the library shown.

CMNLRPM3           STEV/LOCALVER - Promotion Libraries          Row 1 to 1 of 1
Command ===> _________________________________________________ Scroll ===> CSR

Promotion name:  UNIT     Level: 10

          Syslib Cleanup
      Lib exclude Level Target libraries
_____ NSQ   Y       N   ZMFSD.VUNIT.NSQ                           + Shadow
                        ZMFSD.VUNIT.NSQ                           + Library 1               
                        _____________________________________     + Library 2
                        _____________________________________     + Library 3

Enter N in this field if cleanup is to be skipped for this library type at this level during a promotion or the installation of a package.

Similarly, for the remote site promotion:

CMNLRPM3           STEV/REMOTEVER - Promotion Libraries         Row 1 to 1 of 1 
Command ===> ________________________________________________ Scroll ===> CSR

Promotion name:  UNIT     Level: 10

          Syslib Cleanup
      Lib exclude Level Target libraries
_____ NSQ   Y       N   ZMFSD.VQA1.NSQ                            + Shadow
                        ZMFSD.VQA1.NSQ                            + Library 1               
                        _____________________________________     + Library 2
                        _____________________________________     + Library 3

And here are the production library definitions for sites U810DP (the local DP site) and U810P (the 'remote' P site - not really remote) - U810DP:

CMNCPRDL           STEV - U810DP Production Libraries         Row 5 to 10 of 10 
Command ===> _________________________________________________ Scroll ===> CSR

     Type  Production dataset name  +
           Temporary dataset name   +
           Backup dataset name      + 
_____ NSQ  ZMFSD.PROD.NSQ
           NULLFILE 
           ZMFSD.PROD.NSQ.BKUP 
...

and U810P:

CMNCPRDL           STEV - U810P Production Libraries            Row 2 to 3 of 3 
Command ===> _________________________________________________ Scroll ===> CSR

     Type  Production dataset name  +
           Temporary dataset name   +
           Backup dataset name      + 
_____ NSQ  ZMFSD.PROD1.NSQ
           NULLFILE 
           ZMFSD.PROD1.NSQ.BKUP 
...

None of the above is new, just standard ZMF admin.

Now, turning to the Db2 option admin. Here is the list of logical subsystems we are working with. We have one logical subsystem per target 'environment'

CMNLD2LN                 Db2 Logical Subsystems                 Row 1 to 4 of 4
Command ===> _________________________________________________ Scroll ===> CSR

Line commands:
    P Specify miscellaneous parameters
    T Specify BIND process variable templates
    Q Specify SQL process named variable templates
    G Specify SQL process general token variable templates

         Logical     Db2 
         name       subsys      Site       Description
_____    PRODV       DSN        U810DP     PROD USING VERSION + DEPLOY
_____    PRODV1      DSN1       U810P      PROD ON DSN1 USING VER/DEPLOY
_____    QAD1        DSN1       REMOTVER   QA ON DSN1 USING VER/DEPLOY
_____    UNITV       DSN        LOCALVER   UNIT TEST WITH VERSION

All of these logical subsystems have the following 'Use Db2 versioning' set in the 'miscellaneous parameters'. Without this you will be using the standard Drop/Create paradigm.

CMNGD2PM                  Db2 Logical Subsystem UNITV Settings 
Command ===> __________________________________________________________________

Preferred Libtypes: 
DBRM . . . . . . . . . . . . . . DBR
Plan bind parameters . . . . . . DBB
Package bind parameters . . . . PKG 

General Parameters:
Enter "/" to select option 
  _ Bind Failure is significant 
  _ Recycle Stored Procedures where WLM Environment is . . 
  _ Maintain Trigger Sequence
  / Use Db2 versioning for Native-SQL Stored Procedures

Here are what the different logical subsystems have specified for the SQL templates.

Note that most of these values are used to generate the names used at the target subsystem. So, in the case of UNITV below, the stored procedures will have whatever schema they have specified in the code replaced with 'UNIT', i.e. UNIT.spname

The DEPLOY location (highlighted in red) is slightly different in that it used when the logical subsystem is defined as the 'source' subsystem for the deploy action. More about this below but, in effect, the bind deploy request is sent to the source subsystem location as specified here.

In our test subsystems we had the following locations defined:

    DSN     DB2V11
    DSN1    SOW1DSN1
CMNGD2L3         Db2 Logical Subsystem UNITV SQL Process Templates (Named) 
Command ===> __________________________________________________________________

  Templates              Target              Source                Deploy

  Schema . . . . . . . . UNIT¬¬¬¬¬¬¬¬¬¬¬¬   + _________________   +
  Collection . . . . . . UNIT¬¬¬¬¬¬¬¬¬¬¬¬   + _________________   +
  WLM . . . . . . . . .  ________________   + _________________   +

  Location . . . . . . . ________________   + _________________   + DB2V11    +
  Qualifier . . . . . .  UNIT¬¬¬¬¬¬¬¬¬¬¬¬   + _________________   + UNIT      +
  Owner . . . . . . . .  SERD¬¬¬¬¬¬¬¬¬¬¬¬   + _________________   + SERD      +

...

CMNGD2L3          Db2 Logical Subsystem QAD1 SQL Process Templates (Named) 
Command ===> __________________________________________________________________

  Templates              Target              Source                Deploy

  Schema . . . . . . . . QA¬¬¬¬¬¬¬¬¬¬¬¬¬¬   + _________________   +
  Collection . . . . . . QA¬¬¬¬¬¬¬¬¬¬¬¬¬¬   + _________________   +
  WLM . . . . . . . . .  ________________   + _________________   +

  Location . . . . . . . ________________   + _________________   + DB2V11    +
  Qualifier . . . . . .  QA¬¬¬¬¬¬¬¬¬¬¬¬¬¬   + _________________   + QA        +
  Owner . . . . . . . .  SERD¬¬¬¬¬¬¬¬¬¬¬¬   + _________________   + SERD      +

...

CMNGD2L3         Db2 Logical Subsystem PRODD SQL Process Templates (Named) 
Command ===> __________________________________________________________________

  Templates              Target              Source                Deploy

  Schema . . . . . . . . PROD¬¬¬¬¬¬¬¬¬¬¬¬   + _________________   +
  Collection . . . . . . PROD¬¬¬¬¬¬¬¬¬¬¬¬   + _________________   +
  WLM . . . . . . . . .  ________________   + _________________   +

  Location . . . . . . . ________________   + _________________   + DB2V11    +
  Qualifier . . . . . .  PROD¬¬¬¬¬¬¬¬¬¬¬¬   + _________________   + PROD      +
  Owner . . . . . . . .  SERD¬¬¬¬¬¬¬¬¬¬¬¬   + _________________   + SERD      +

...

CMNGD2L3        Db2 Logical Subsystem PRODD1 SQL Process Templates (Named) 
Command ===> __________________________________________________________________

  Templates              Target              Source                Deploy

  Schema . . . . . . . . PROD¬¬¬¬¬¬¬¬¬¬¬¬   + _________________   +
  Collection . . . . . . PROD¬¬¬¬¬¬¬¬¬¬¬¬   + _________________   +
  WLM . . . . . . . . .  ________________   + _________________   +

  Location . . . . . . . ________________   + _________________   + DB2V11    +
  Qualifier . . . . . .  PROD¬¬¬¬¬¬¬¬¬¬¬¬   + _________________   + PROD      +
  Owner . . . . . . . .  SERD¬¬¬¬¬¬¬¬¬¬¬¬   + _________________   + SERD      +

...

Having defined our logical subsystems we now need to tell the various ZMF actions when to use them. This is done (as usual) using the active library definitions. The first one below says that when ZMF delivers to ZMFSD.VUNIT.NSQ then Db2 actions will be governed by the UNITV logical subsystem. In this case we will create/alter a version of the SP and then activate that new version, according to the templates/values associated with the UNITV logical subsystem.

The others all target something called a connector, rather than a traditional logical subsystem. If an active library targets a connector you are requesting the distribution to be performed via BIND DEPLOY (see below for more on connectors).

CMNLD2AL                    Db2 Active Library List             Row 1 to 4 of 4
Command ===> _________________________________________________ Scroll ===> CSR

      Logical     Bind 
      name        /SQL     Db2 active library name
_____ UNITV        S       ZMFSD.VUNIT.NSQ
_____ UNIT2QA1     S       ZMFSD.VQA1.NSQ
_____ UNIT2PRD     S       ZMFSD.PROD.NSQ
_____ UNIT2PR1     S       ZMFSD.PROD1.NSQ

...

There is a new Db2 subtype which is used to indicate that we are processing a Native SQL stored procedure (NSQ):

CMNDLLT0                STEV - Db2 Library Types              Row 1 to 11 of 11
Command ===> _________________________________________________ Scroll ===> CSR

Lib                                                        Sub    End SQL
type     Description                                       type   sentence
DBR      DB2 DBRM's                                         R        _
DBP      DB2 Bind Package Commands                          P        _
PKG      DB2 Package Bind Control                           P        _
DBB      DB2 Bind Plan Commands                             P        _
STL      DB2 External Stored Procedure Load                 S        _
XPQ      DB2 External SQL stored proc Source                D        #
SPQ      Native SQL Stored Procedure                        _        _
MPQ      Native SQL Stored Procedure metadata               _        _
SPD      DB2 Stored Procedure Definition                    D        #
NSQ      Native SQL Stored Procedures                       N        #
DDL      Data Definition Language                           D        @

...

To use BIND DEPLOY we have to define connectors. These connect two different logical subsystems as source and target for a BIND DEPLOY operation. The source values are sued to find the pre-existing stored procedure and the target values tell Db2 how to define the copy of the stored procedure at the target location. In this test we have 2 connectors, all deploying from the UNITV logical subsystem to 3 different targets.

CMNLD2CL      Logical Subsystem Connectors for Appl - STEV      Row 1 to 3 of 3 
Command ===> _________________________________________________ Scroll ===> CSR 

        Connector    Source    Target 
        name         name      name       Description 
_____   UNIT2PRD     UNITV     PRODD      UNIT TO PROD (DSN)
_____   UNIT2PR1     UNITV     PRODD1     UNIT TO PROD (DSN1)
_____   UNIT2QA1     UNITV     QAD1       UNIT TO QA (DSN1) 
******************************* Bottom of data *******************************

When you define a connector all you are doing is specifying the source and target logical subsystem names, along with a description for it. There is nothing more you can define. Once you have saved the connector away, if you select the definition from the list you will get a (output only) indication of the values which will be used by the BIND DEPLOY operation. For example, for UNIT2PRD we see these values:

CMNLD2CN          Application STEV Logical Subsystem Connector - UNIT2PRD  
Command ===> __________________________________________________________________    

               UNIT TO PROD (DSN)     

Source . . . . UNITV 
Subsystem id . DSN 
Location . . . DB2V11           +     

Templates      Target            Source   
Schema . . . . UNIT¬¬¬¬¬¬¬¬¬¬¬¬ +                          +     

Target . . . . PRODD 
Subsystem id . DSN 
Site . . . . . U810DP     

Templates      Target            Source                      Deploy   
Collection . . PROD¬¬¬¬¬¬¬¬¬¬¬¬ +                          +   
Qualifier . .  PROD¬¬¬¬¬¬¬¬¬¬¬¬ +                          + PROD               +   
Owner . . . .  SERD¬¬¬¬¬¬¬¬¬¬¬¬ +                          + SERD               + 

The source location for the bind deploy is DB2V11 (which is hosted by subsystem DSN) and the schema used to define the SP at that location is, in this case, UNIT.

The target location for the bind deploy will be determined by ZMF when the process runs but the logical subsystem we are using is PRODD (which is defined for site U810DP and hosted by the DSN Db2 subsystem). We will use PROD as the schema for the target SP and use a qualifier of PROD and an owner of SERD.

All of these values are taken from either the source or target logical subsystem definition as specified for this connector.

Here are the values which will be used by the other connectors in this example:

CMNLD2CN          Application STEV Logical Subsystem Connector - UNIT2PR1  
Command ===> __________________________________________________________________    

               UNIT TO PROD (DSN1)     

Source . . . . UNITV 
Subsystem id . DSN 
Location . . . DB2V11           +     

Templates      Target            Source   
Schema . . . . UNIT¬¬¬¬¬¬¬¬¬¬¬¬ +                          +     

Target . . . . PRODD1 
Subsystem id . DSN1 
Site . . . . . U810P  

...

Templates      Target            Source                      Deploy   
Collection . . PROD¬¬¬¬¬¬¬¬¬¬¬¬ +                          +   
Qualifier . .  PROD¬¬¬¬¬¬¬¬¬¬¬¬ +                          + PROD               +   
Owner . . . .  SERD¬¬¬¬¬¬¬¬¬¬¬¬ +                          + SERD               + 

...

CMNLD2CN          Application STEV Logical Subsystem Connector - UNIT2QA1  
Command ===> __________________________________________________________________    

               UNIT TO QA (DSN1)     

Source . . . . UNITV 
Subsystem id . DSN 
Location . . . DB2V11           +     

Templates      Target            Source   
Schema . . . . UNIT¬¬¬¬¬¬¬¬¬¬¬¬ +                          +     

Target . . . . PRODD1 
Subsystem id . DSN1 
Site . . . . . REMOTVER     

Templates      Target            Source                      Deploy   
Collection . . QA¬¬¬¬¬¬¬¬¬¬¬¬¬¬ +                          +   
Qualifier . .  QA¬¬¬¬¬¬¬¬¬¬¬¬¬¬ +                          + QA                 +   
Owner . . . .  SERD¬¬¬¬¬¬¬¬¬¬¬¬ +                          + SERD               + 

That's it for admin. Most of it is no different to the kind of Db2 option admin which preexisted. The major differences being the 'Use Db2 versioning' setting in miscellaneous parameters and the whole area of connectors being used to define BIND DEPLOY usage.

Now to see some output from the lifecycle processes. We have used Data Studio to generate a change to a pre-existing stored procedure. Data Studio runs its own proprietary deployment method (consisting of OCO stored procedures) so we let it complete deployment and pick up the SP from the target Db2 catalog.

In this example we are generating a stored procedure which has a name longer than 8 bytes (LONG_NAME_SP_NUMBER_3), the max length for the name is 128 bytes. Note that, the ZMF package component for this SP will be a member name which, of course, is restricted to 8 bytes. To make things consistent it is best to keep the SP name the same as the component name. However, the only downside to this is that there is no formal tieup between the component name and the SP name.

The SP presented here is a simple example (we are interested in the process not the SP itself). Here we are about to deploy version V2 to our target development Db2 subsystem.

Two things of note here:

  1. The version identifier is a freeform 64 byte field (122 bytes if you use DBCS). There is no ordinal sense to this field. While the default first version of an SP is assigned the version V1 by Db2 and we are adding here a new version of the same stored procedure called V2, there is no ordinal meaning to V1 and V2.

  2. The SP has been given a schema (ZMFSD, my userid, in this case). This is important to allow the lifecycle templating to work

The deployment mechanism gives you various options. The one of interest here is the 'Alter duplicates' flag. As far as Db2 is concerned there is a single entity (i.e. stored procedure) called ZMFSD.LONG_NAME_SP_NUMBER_3 at the target Db2 subsystem. This deployment is attempting to create another version of the same item, this is a duplicate item. Setting the 'Alter duplicates' flag tells the deployment to change the DDL (which, at the moment, say CREATE PROCEDURE) to ALTER PROCEDURE … ADD VERSION …

This is what we want for this example which makes use of Db2 versioning of Native SQL SPs

At this point the SP is in the target Db2 catalog. Because this is a change to an existing ZMF managed component we need to check it out into our target package before we can stage the changed version. This is where having the same name for the component as the SP comes in useful. Because we have an SP name longer than 8 bytes then we have to know the name of the component used to 'shadow' the SP in the Db2 catalog. In this case it is LONGSP03.NSQ.

Once LONGSP03.NSQ has been checked out we can then stage the new version which Data Studio has deployed to a local Db2 subsystem (DSN in our case).

Using S1 against the package gives us the familiar stage-from-development panel, with a new option.

CMNSTG02                        Stage from Development 
Command ===> __________________________________________________________________

         Package: STEV000288           Status: DEV      Install date: 20161120
     Change rqst: 00000001                                  Location: HERE

ISPF Library: 
   Project . . . . ZMFSD 
   Group . . . . . DB2
   Type . . . . .  JCL
   Member . . . .  __________          (Blank/pattern for list; * for all members) 

Other partitioned, sequential or HFS dataset: 
   DSN . . . . . . _________________________________________________________  + 
   Org . . . . . . _____          (PDS, Seq, PAN, LIB, Oth, HFS) 

Library type . . . . . _____      (Blank for list) 
Stage name . . . . . . _____________________________________________________  + 
Stage mode . . . . . . 1          (1-Online, 2-Batch)

Enter "/" to select option
/ Confirm request                  _ Expand HFS subdirectories
_ Lock component                   _ Display component user options 
/ Extract Stored Procedure from Db2 catalog

Having chosen that option we are presented with a new panel from which we can stage the SP into the package (see the help panel for a description of what all the fields mean):

CMNSTG25                   Stage Native-SQL SP from Db2
Command ===> __________________________________________________________________

         Package: STEV000288           Status: DEV      Install date: 20161120

Stored Procedure: 
   Db2 id . . . .  DSN 
   Location . . .  __________________
   Schema . . . .  ZMFSD                                                      +
   Name . . . . .  LONG_NAME_SP_NUMBER_3                                      +
   Version  . . .  __________________________________________________________ + 
   Version Ind. .  __________________

Component:
   Name . . . . . LONGSP03 
   Library type . NSQ

Enter "/" to select option
  / Add package information to component
  _ Lock component in package

The software goes to the Db2 catalog, extracts the SP code, and attempts to stage it into the package. We get an overlay warning.

CMNSTG25                   Stage Native-SQL SP from Db2
Command ===> __________________________________________________________________

         Package: STEV000288           Status: DEV      Install date: 20161120
       +------------------------ Stage Warning ----------------------+ 
Stored | CMNSTGWP                                                    | 
   Db2 | Command ===> ______________________________________________ |
   LocSch |                                                           |       +
   SchNam | Staging Member:                                           |       +
   NamVer | LONGSP03                                                  |       +
   Ver | Will overlay ZMFSD      version.                            |
       |                                                             |
Compon |                                                             |
   Nam |                                                             |
   Lib +-------------------------------------------------------------+

Enter "/" to select option 
  / Add package information to component
  _ Lock component in package

On confirmation of the overlay the checkin service runs and stages the component to the package, we get the following message from checkin:

...

CMN408I - STEV000288 Component LONGSP03.NSQ ACTIVATED 20160222 112807. CN(INTERNAL) 
***

If we look at the component in the package we can see the SP definition as it was delivered to Db2 by Data Studio (note that we are now ALTERing the proc).

The first three (optional) comment lines have been added by ZMF.

-- +ZMF+--------------------------------------------------------------+ 
-- | Pkg: STEV000288 Ltp: NSQ Uid: ZMFSD Time: 2016/02/22-11.28.07 |
-- +ZMF+--------------------------------------------------------------+
ALTER PROCEDURE ZMFSD.LONG_NAME_SP_NUMBER_3
 ADD VERSION V2 ( )
  ISOLATION LEVEL CS
 RESULT SETS 1
 LANGUAGE SQL
P1: BEGIN
-- ####################################################################### 
-- # Returns all tables created by ZMFV2
-- #######################################################################
 -- Declare cursor 
 DECLARE cursor1 CURSOR WITH RETURN FOR

  SELECT NAME FROM SYSIBM.SYSTABLES WHERE CREATOR = 'ZMFV2'
   ORDER BY NAME;

 -- Cursor left open for client application
    OPEN cursor1;
END P1

...

The component is now active in the package.

We first promote it to the LOCALVER site, first promotion level. This delivers the component to ZMFSD.VUNIT.NSQ and, as a result of the active library definition and the fact that this libtype is Db2 indicated with a Db2 subtype of N, we take a number of options in the promotion skeleton.

CMNDB2DD is informed that the target logical subsystem is using Db2 versioning for Native SQL SPs, it issues (amongst others) the following messages:

CMNDD041I          Not a CREATE, autodrop will do nothing at this time.
CMNDD037I          Stored Procedure version information has been written to the 
     VERSION DDname.
CMNDD001I          Templated SQL sentence extracted from member LONGSP03 :
-- +ZMF+--------------------------------------------------------------+
-- | Pkg: STEV000288 Ltp: NSQ Uid: ZMFSD Time: 2016/02/22-11.28.07 |
-- +ZMF+--------------------------------------------------------------+
ALTER PROCEDURE UNIT .LONG_NAME_SP_NUMBER_3
 ADD VERSION V2 ( )
  ISOLATION LEVEL CS
 RESULT SETS 1
 LANGUAGE SQL
P1: BEGIN
-- #######################################################################
-- # Returns all tables created by ZMFV2
-- #######################################################################
 -- Declare cursor
 DECLARE cursor1 CURSOR WITH RETURN FOR

  SELECT NAME FROM SYSIBM.SYSTABLES WHERE CREATOR = 'ZMFV2'
   ORDER BY NAME;
 -- Cursor left open for client application 
 OPEN cursor1;
END P1
CMNDD002I          Sentence processed successfully.
CMNDD003I          Work committed

We can see that the schema for the SP has been templated using the target logical subsystem settings (i.e. it has been replaced by UNIT), and the resulting definition has been presented to Db2 and processed successfully. Also, message CMNDD037I indicates that version information has been written to the VERSION DDname. This information is passed on to a subsequent job (submitted via internal reader from the promotion job) which is (optionally) held.

When it runs that job uses the version information passed to it to activate the new SP version delivered by the promote action:

CMNAV003I       Statement generated for SP activation:
ALTER PROCEDURE UNIT.LONG_NAME_SP_NUMBER_3 ACTIVATE VERSION V2
CMNAV004I       Version activation completed successfully

As part of this activation process information about the currently active version is written to the local CMNZMF.CMNDB2_ATTRIBS table (for use by any future demotion). So, at this point in time, the active version is V2 and the prior active version has been recorded as V1.

Moving on to the promotion to remote site REMOTVER and the first level defined there. This delivers the component to ZMFSD.VQA1.NSQ and, as a result of the active library definition (which targets a connector, see above) and the fact that this libtype is Db2 indicated with a Db2 subtype of N, the promotion skeleton is driven to use the BIND DEPLOY mechanism for distributing the SP.

CMNDB2DD is supplied a series of settings from the source and target logical subsystems as identified by the target connector from the active library definition. As a result the following BIND DEPLOY command is built. It is routed back to the source Db2 subsystem via a remote call to the IBM supplied stored procedure ADMIN_COMMAND_DSN. The location identified for the source logical subsystem is used to do this, in this case the remote call is to DB2V11.ADMIN_COMMAND_DSN and that call is presented with the following command text. Note that the location for the deployment (S0W1DSN1 in this case) is determined by CMNDB2DD at run time. CMNDB2DD is actually running at the target, it sends the deploy command back to the source using the remote call.

Note

To be clear, there is no presentation of SQL/DDL to Db2 at the target location. The distribution of the Native SQL SP is performed by the BIND DEPLOY command.

CMNDD045I          BIND DEPLOY processing requested, command(s) will be sent to 
     location: DB2V11
BIND PACKAGE(S0W1DSN1.QA) + 
    DEPLOY(UNIT.LONG_NAME_SP_NUMBER_3) +
     COPYVER(V2) +
     OWNER(SERD) +
     QUALIFIER(QA) +
     ACTION(REPLACE)

DSNT232I -DSN0 SUCCESSFUL BIND FOR 
           PACKAGE = S0W1DSN1.QA.LONG_NAME_SP_NUMBER_3.(V2)

As well as issuing the BIND DEPLOY we also generate version information and submit the 'activation' job as before. When it runs it activates this newly deployed version of the SP:

CMNAV003I       Statement generated for SP activation:
ALTER PROCEDURE QA.LONG_NAME_SP_NUMBER_3 ACTIVATE VERSION V2
CMNAV004I       Version activation completed successfully

What happens on a demote? Here we have requested a demote for the component we just promoted to site REMOTVER. Again, there is no presentation of SQL/DDL to Db2 but also, no BIND DEPLOY either (as BIND DEPLOY is all about delivering an SP to another location, not removing it). However, version information is written (using a different transaction code, one of which indicates a demote) and the activation job is submitted.

CMNDD041I          Not a CREATE, autodrop will do nothing at this time. 
CMNDD037I          Stored Procedure version information has been written to the  
     VERSION DDname.

When it runs it uses the information in the CMNZMF.CMNDB2_ATTRIBS table to decide which version of the SP to re-instate. It also drops the version of the SP that was just promoted:

CMNAV003I       Statement generated for SP activation:
 ALTER PROCEDURE QA.LONG_NAME_SP_NUMBER_3 ACTIVATE VERSION V1

CMNAV004I Version activation completed successfully


CMNAV016I       Statement generated to drop the demoted version of this SP:
 ALTER PROCEDURE QA.LONG_NAME_SP_NUMBER_3 DROP VERSION V2

CMNAV018I       Version dropped successfully.

Moving on to the install, we have an install to both a local site, U810DP, and a remote site, U810P. Both are set up to be delivered via BIND DEPLOY and, in the CMN21 job, we see for U810DP the following:

CMNDD045I BIND DEPLOY processing requested, command(s) will be sent to 
     location: DB2V11

 BIND PACKAGE(DB2V11.PROD) + 
    DEPLOY(UNIT.LONG_NAME_SP_NUMBER_3) +
    COPYVER(V2) +
    OWNER(SERD) +
    QUALIFIER(PROD) +
    ACTION(REPLACE)

DSNT254I -DSN0 DSNTBCM2 BIND OPTIONS FOR
       PACKAGE = DB2V11.PROD.LONG_NAME_SP_NUMBER_3.(V2)
         ACTION       ADD
         OWNER        SERD
         QUALIFIER    PROD
         VALIDATE     RUN
         EXPLAIN      NO
         ISOLATION    CS
         RELEASE      COMMIT
         COPY
         APREUSE
            APCOMPARE
            APRETAINDUP
            BUSTIMESENSITIVE YES
            SYSTIMESENSITIVE YES
            ARCHIVESENSITIVE YES
            APPLCOMPAT V11R1

...

DSNT255I   -DSN0 DSNTBCM2 BIND OPTIONS FOR
            PACKAGE = DB2V11.PROD.LONG_NAME_SP_NUMBER_3.(V2)
            SQLERROR      NOPACKAGE
            CURRENTDATA   NO
            DEGREE        1
            DYNAMICRULES  RUN
            DEFER
            REOPT         NONE
            KEEPDYNAMIC   NO
            IMMEDWRITE    NO
            DBPROTOCOL    DRDA
            OPTHINT
            ENCODING      EBCDIC(01047)
            PLANMGMT      OFF
            PLANMGMTSCOPE STATIC
            CONCURRENTACCESSRESOLUTION
            EXTENDEDINDICATOR
            PATH
DSNT232I   -DSN0 SUCCESSFUL BIND FOR
            PACKAGE = DB2V11.PROD.LONG_NAME_SP_NUMBER_3.(V2)

Notice that the messages fed back by the deployment (actually, from the deployment via the ADMIN_COMMAND_DSN SP) are much more verbose when the deployment is from/to the same Db2 subsystem (DSN in this case). We also see the activation job submitted:

CMNAV003I Statement generated for SP activation:
 ALTER PROCEDURE PROD.LONG_NAME_SP_NUMBER_3 ACTIVATE VERSION V2

CMNAV004I Version activation completed successfully

The install to the U810P site proceeds along similar lines:

CMNDD045I BIND DEPLOY processing requested, command(s) will be sent to 
     location: DB2V11

BIND PACKAGE(S0W1DSN1.PROD) + 
    DEPLOY(UNIT.LONG_NAME_SP_NUMBER_3) +
     COPYVER(V2) +
     OWNER(SERD) +
     QUALIFIER(PROD) +
     ACTION(REPLACE)

DSNT232I  -DSN0 SUCCESSFUL BIND FOR
       PACKAGE = S0W1DSN1.PROD.LONG_NAME_SP_NUMBER_3.(V2)


CMNAV003I        Statement generated for SP activation:
 ALTER PROCEDURE PROD.LONG_NAME_SP_NUMBER_3 ACTIVATE VERSION V2

CMNAV004I Version activation completed successfully

...