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:
-
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.
-
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
...