CMNDB2DD
Program CMNDB2DD executes DDL/SQL at promote, demote, install and backout to register stored procedures, triggers, and user defined functions in the Db2 catalog.
The program reads a specified member from a concatenation of SQL libraries, parses the records in the member into SQL sentences using a specified terminator, applies all relevant templates and offers the SQL sentences to the target Db2 subsystem. Further manipulation of the DDL/SQL may be achieved using general token templates and/or the HLLX exit facility. The results of the execution may also, optionally, be passed in an output dataset to be processed by some other Db2 utility of your choosing.
Program CMNDB2DD writes to ddname TRIGGER the tablename/event combinations for which a trigger has been added or updated. This file is passed to utility program
CMNDB2TR.
This table shows the skeletons that include program CMNDB2DD and where those skeletons are used.
| Skeleton | ...embedded in skeletons | Skeleton Description |
|---|---|---|
CMN$$PSQ |
CMN$$PRM |
Perform promotion or demotion to local sites. |
CMNIMPRM |
IMS Option: Perform promotion or demotion to local sites. | |
CMN$$RSQ |
CMNRPICR |
IMS Option: Perform remote promotion or demotion. |
CMNRPMCR |
Perform promotion or demotion to remote sites. | |
CMN$$SQL |
CMN21 |
Used to perform db2 binds and/or ddl processing for installation of packages into production libraries and db2 catalogs. |
CMN49 |
Used to perform db2 binds and/or ddl processing for backout of packages from production libraries and db2 catalogs. |
Keyword Options
This list describes the keyword options that control the behavior or program CMNDB2DD. Default values are in italics:
ACTION=PROMOTE/DEMOTE/INSTALL/BACKOUT This setting is used by the Native SQL SP version process. See description of keyword SPVERPKGRC. The value is also passed to the HLL exit if it is active
AUTODROP=YES/NO Autodrop facility issues a DROP command for a procedure, trigger, or user defined function before processing CREATE SQL for the object.
BINDDEPLOY= Specify any extra clauses to be included in the generated BIND DEPLOY command for a native SQL stored procedure. The value for this keyword is freeform text and whatever you put here will be appended to the command asis. You may specify as many BINDDEPLOY= keywords as you wish. An example might be BINDDEPLOY=QUERYACCELERATION(ENABLE).
CMP= Synonym for MBR=. See MBR=.
DB2ID= Db2 subsystem ID to which SQL should be presented. The DB2ID keyword control statement must precede one or more MBR keyword control statements.
DEPLOYFROMLOCATION= This is the Db2 location to which CMNDB2DD will route the call to ADMIN_COMMAND_DSN in order to execute the BIND DEPLOY request. The value is populated from the 'Deploy' value for location on the source logical subsystem definition.
DEPLOYQUAL=
DEPLOYOWNER=
If standard templating processes result in a blank value for qualifier and/or owner when a bind deploy command has been requested then any values entered for these parameters will be used on the command (cf. bind insert values). Values for both the templates and these 'deploy' fields will be provided by the target logical subsystem definition.
DROPRC=
Return code set if the requested SQL action is DROP and the component is not found in the Db2 catalog.
Valid values: 0 to 99
Default: 0
ERRSTOPAFT=
Number of SQL errors allowed before the program is terminated.
Valid values: 0 to 99999999
Default: 0
HLLX=(name,type)
This parameter indicates that a HLL exit be taken by CMNDB2DD. This does not use the standard HLLX scheduling system as CMNDB2DD needs to be able to run on remote z/OS images. However, the call mechanism is the same, i.e. the exit can be coded in REXX or any LEsupported language.
The purpose is to allow you to manipulate the DDL being processed by CMNDB2DD directly using your own business logic. It will also allow them to stop CMNDB2DD from continuing should it decide to do so.
The 'name' sub-parameter specifies the external name of the HLL exit (i.e. the REXX exec name of LE program name). The 'type' sub-parameter must be either REXX or LE as appropriate.
LINEFEED=YES/NO If this is set to YES then CMNDB2DD will insert a linefeed character (EBCDIC x'25') to the end of each physical line of SQL code. This is useful during formatting by various debug tools. Default is NO. Note that Data Studio inserts its own linefeed characters
MBR= Name of SQL component to be processed in the Db2 subsystem specified in the preceding DB2ID keyword control statement.
NOTFOUNDRC=
Return code set if the requested SQL component (MBR=) is not found in the library concatenation at ddname SQLIN.
Valid values: 0 to 99
Default: 8
Comment: Set to 0 for demotion and backout functions.
PASSTHRU=YES/NO
The usual method that CMNDB2DD employs is to present DROP/CREATE DDL directly to the target Db2 subsystem. Customers may wish to do this using other utilities but may also wish to avail themselves of the facilities offered by CMNDB2DD. This new parameter allows them to do this. If PASSTHRU=YES is specified then the resulting DDL, as manipulated by this program, is then written to an output ddname rather than being presented to Db2. The ddname used is:
//ssssOUT
Where ssss is the Db2 subsystem id currently being processed (i.e. as directed by the DB2ID= sysin parameter). The output DCB is checked for compatibility with the SQLIN ddname. The member read from SQLIN is directed by the MBR= sysin parameter and this same member name is used to write to ssssOUT.
The following message will be seen in sysprint:
CMNDD033I Sentence passed to ssssOUT, no action taken at target Db2 subsystem. The ssss is resolved to the actual subsystem id in the message.
SPVERPKGRC=n 8 is the default. This is the return code set should the ZMF id and package name check, described in the keyword ACTION for demote/backout, fail.
SPVERSION= NO YES ONLY COMMAND BOTH UNDO
Lets CMNDB2DD know whether Native SQL SP versioning is supported.
YES: As well as the standard templating/presentation of the SP SQL to Db2 we also take actions designed to activate the correct SP version at the target Db2 subsystem. This is done by writing transactions (intended for CMNDB2AV) to the VERSION DDname. In the supplied skeletons this ddname is passed to a subsequent job submission step which submits an execution of CMNDB2AV to act on these transactions
ONLY: Do not present SQL to Db2, extract version information and write to the VERSION ddname only.
COMMAND: Generate the relevant BIND DEPLOY command and call location.SYSPROC.ADMIN_COMMAND_DSN to execute it.
BOTH: Equivalent to ONLY and COMMAND - take both actions.
UNDO: This takes the same action as ONLY but uses the COLLID templates to generate the schema of the SP whose version information we need (ONLY uses the SCHEMA templates). This action is required during demote and backout.
SQLTERM=
Alternate SQL statement terminator. If the input includes SQL that uses the semicolon (;) as a statement terminator, specify an alternate terminator for the input so that the semicolon is passed through to the server.
You can specify any character except the following:
- blank
- comma
- underscore
- single quote
- double quote
- left parenthesis
- right parenthesis
If you omit this keyword parameter, the default SQL statement terminator is semicolon (;).
SQUEEZE=YES/NO If this is set to YES then superfluous blanks will be stripped out from the SQL sentence prior to it being presented to Db2. YES is the default for legacy reasons. If you wish to be able to view the Db2 object back directly from the Db2 catalog using a debugger or some other tool then you will want to use SQUEEZE=NO.
SRCCOLLIDTEMPLATE= Source template for COLLID parameter in SQL definitions for stored procedures and user defined functions.
SRCQUALTEMPLATE= Source template for explicit table qualifier in SQL definitions.
SRCSCHEMATEMPLATE= Source template for schema in SQL definitions.
SRCWLMTEMPLATE= Source template for WLM application environment.
TEST=
Program trace facility.
Valid values:
YES/NO
Default: NO
TGTCOLLIDTEMPLATE= Target template for COLLID parameter in SQL definitions for stored procedures and user defined functions.
TGTQUALTEMPLATE= Target template for explicit table qualifier in SQL definitions.
TGTSCHEMATEMPLATE= Target template for schema in SQL definitions.
TGTWLMTEMPLATE= Target template for WLM application environment.
TOKENNAME=
TOKENSRCT=
TOKENTGTT=
The parameters allow you to define your own DDL parameters to be template 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).
TOKENNAME= specifies a string which will be looked for in the DDL. 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).
In the DDL, 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 you need 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 DDL 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.
TOLSTDNUM=YES/NO If this is set to YES then the last 8 bytes of each 'card image' is ignored. YES is the default for legacy reasons, 80 byte card images being the standard format for DDL/SQL. Variable length records have been supported for some time (e.g. Data Studio generated SQL needs VB,255), in anything other than standard 80 byte card images you should use TOLSTDNUM=NO.
TRACKTRIGGER=YES/NO Write to ddname TRIGGER the tablename/event combinations for which a trigger has been added or updated.
ZMFID=CMNx This is the ZMF subsystem id which owns the package associated with this action. It is used in the SP version validation process for demote/backout. It is also passed on in the VERSION ddname transactions.
ZMFPACKAGE=aaaannnn nn This is the ZMF package name associated with this action. It is used in the SP version validation process for demote/ backout as. It is also passed on in the VERSION ddname transactions.
See the description of the Db2 Logical Subsystem nickname Templates panel in Define Global Logical Subsystems for an explanation of Source and Target templates. See Templating Examples to see how the Source and Target fields interact to modify templated fields in SQL.
A new DB2ID control statement makes program CMNDB2DD disconnect from the current subsystem and connect to the new one. The number of MBR control statements that follow a DB2ID control statement is not limited.
Return Codes and Messages
| Return Code | Description |
|---|---|
| 00 | Success |
| 04 | Warnings Issued |
| 08 | SQL errors |
| 12 | Parameter errors |
| 16 | Other unrecoverable errors |