Skip to content

Db2 Option and Component Management

The Db2 Option of ChangeMan ZMF manages Db2 components through a rules-based life cycle for development, test, and install that ensures component and application integrity. The Db2 Option automates two classes of functions for Db2 components:

  • Db2 binds

  • Db2 object management for stored procedures, triggers, and user defined functions.

Important

ChangeMan ZMF programs in the Db2 Option assume that BIND commands and DDL for stored procedures, triggers, and user-defined functions are syntactically correct. BIND commands that are input to program CMNDB2PL are parsed with IBM service routine IKJPARS to ensure that CMNDB2PL processing is synchronized with IBM changes to BIND keyword operands.

Bind Processing

The ChangeMan ZMF Db2 Option binds the DBRM for programs in change packages when the component is promoted or demoted, and when the package is installed or backed out.

Features of automated bind processing:

  • The Db2 catalog is searched for plans and packages that reference staged DBRM, and those plans and packages are bound.

  • Parameters in BIND PLAN and BIND PACKAGE commands in staging libraries or baseline libraries can be modified according to fixed rules to adapt the BIND commands to the Db2 subsystems used for test and production.

Stored Procedure Processing

Stored procedures are user-written application programs that can be called by SQL programs that run either locally or remotely on any platform supported by the IBM® Db2® UDB network. The Db2 Option of ChangeMan ZMF supports external stored procedures, external SQL stored procedures, and native SQL stored procedures.

External Stored Procedures

These are programs coded in a traditional host language like assembler, COBOL, PL/I, C or C++, or REXX.

Db2 Option and Component Management

These component types are involved in managing external stored procedures:

  • Stored procedure source

  • Stored procedure Load

  • DBRM

  • Link edit control statements

  • DSN BIND command

  • Non-SQL stored procedure DDL (CREATE PROCEDURE)

    External stored procedures are staged as like-source components. The source is processed by the Db2 precompiler to create a DBRM, then compiled, prelinked (for some languages), and link edited to create an executable load.

    For a new external stored procedure, a link control member is staged to include required Db2 subroutines in the stored procedure load module. A BIND command member is staged to bind the DBRM at promotion and install. A CREATE PROCEDURE DDL is staged to define the stored procedure in the Db2 subsystem at promotion and install.

    At promotion and install, the BIND command and CREATE PROCEDURE DDL are templated to adapt them to the target Db2 subsystem. The DBRM for the stored procedure is bound in the target Db2 subsystem. A DROP PROCEDURE is automatically issued, then the CREATE PROCEDURE DDL is executed to register the stored procedure in the Db2 catalog.

    The stored procedure load module is copied to the target execution library. The VARY WLM,APPLENV=envname,REFRESH command is automatically issued to refresh the stored procedure executable in the WLM-managed address space.

    The external stored procedure source, the BIND command, and the CREATE PROCEDURE DDL can be staged separately to make changes to the external stored procedure after the initial installation.

External SQL Stored Procedures

External SQL stored procedures combine procedural code written in SQL with the CREATE PROCEDURE DDL that define the procedure in the Db2 subsystem. External SQL stored procedures are either hand coded or are generated by programs like the Db2 Data Studio which executes on a client platform such as Windows® and forms part of the Db2 Connect.

These component types are involved in managing SQL stored procedures:

  • Stored procedure source

  • Stored procedure Load

  • DBRM

  • Link edit control statements

  • DSN BIND command

    An SQL language stored procedure is staged as a like-source component. The entire component, including the CREATE PROCEDURE DDL and SQL procedural code, is translated by the Db2 precompiler into C code. The C code is then processed like a traditional external stored procedure module through the Db2 precompiler to create a DBRM, then compiled, prelinked, and link edited to create an executable load.

    For a new SQL stored procedure, a BIND command member is staged to bind the DBRM at promotion and install.

    At promotion and install, the BIND command is templated to adapt it to the target Db2 subsystem. The DBRM for the stored procedure is bound in the target Db2 subsystem. The SQL language stored procedure source is processed to extract the CREATE

    PROCEDURE DDL. A DROP PROCEDURE is automatically issued, then the CREATE

    PROCEDURE DDL is executed to register the stored procedure in the Db2 catalog. The VARY WLM,APPLENV=envname,REFRESH command is automatically issued to refresh the stored procedure executable in the WLM-managed address space.

    The SQL language stored procedure source and the BIND command can be staged separately to make changes to the stored procedure after the initial installation.

Native SQL Stored Procedures

A Native SQL stored procedure is one in which the DDL, the procedural logic, and the SQL statements are contained in a single component. Db2 builds and schedules the executable internally and no other input is required to define this object. A Native SQL stored procedure is staged as a like-PDS component. There is no transformation at stage, and no other component types are required. At promotion and install, the DDL is templated to adapt it to the target Db2 subsystem. Propagation to the target Db2 subsystem is via one of DROP/CREATE, ALTER ADD VERSION, or BIND PACKAGE DEPLOY mechanisms. Facilities are in place within the ZMF Db2 option to automate all of these deployment mechanisms.

User-Defined Functions

A user-defined function (UDF) is defined to Db2 with the CREATE FUNCTION statement and can be referenced thereafter in SQL statements. User-defined functions can be used in place of or in addition to built-in functions. There are two major categories of UDFs: sourced and external.

Sourced User-Defined Functions

Sourced user-defined functions are composed of existing built-in functions and previously defined user-defined functions. The definition of a sourced UDF is made entirely within a CREATE FUNCTION statement.

A sourced user-defined function is staged as a like-PDS component. No other components are required.

At promotion and install, the CREATE FUNCTION statement is templated to adapt it to the target Db2 subsystem. A DROP FUNCTION is automatically issued, then the CREATE FUNCTION statement is executed to define the sourced UDF in the Db2 subsystem.

External User-Defined Functions

External user-defined functions are implemented by means of an externally written program and are managed in the Db2 Option like an external stored procedure.

These component types are involved in managing external user defined functions:

  • Stored procedure source

  • Stored procedure Load

    Db2 Option and Component Management

  • DBRM

  • Link edit control statements

  • DSN BIND command

  • Non-SQL stored procedure definition (CREATE PROCEDURE)

    External UDFs are staged as like-source components. If the source contains imbedded SQL, the source is processed by the Db2 precompiler to create a DBRM. The source is compiled, prelinked (for some languages), and link edited to create an executable load.

    For a new external UDF, a link control member is staged to include required Db2 subroutines in the stored procedure load module. If the source contains imbedded SQL, A BIND command member is staged to bind the DBRM at promotion and install. A CREATE FUNCTION statement is staged to define the UDF in the Db2 subsystem at promotion and install.

    At promotion and install, the BIND command and CREATE FUNCTION statement are templated to adapt them to the target Db2 subsystem. The DBRM for the UDF is bound in the target Db2 subsystem. A DROP FUNCTION is automatically issued, then the CREATE FUNCTION statement is executed to register the UDF in the Db2 catalog. The UDF load module is copied to the target execution library, and a VARY WLM,APPLENV=envname, REFRESH command is automatically issued to refresh the UDF executable in the WLMmanaged address space.

    The external user defined function source, the BIND command, and the CREATE

    FUNCTION statement can be staged separately to make changes to the external UDF after the initial installation.

Database Triggers

A trigger is a set of SQL statements that is stored in a Db2 database and executed when a certain event occurs in a Db2 table.

A trigger definition is staged as like-PDS. Trigger definitions are not transformed at stage, and no other component types are required.

At promotion and install, the CREATE TRIGGER statement is templated to adapt it to the target Db2 subsystem. A DROP TRIGGER is automatically issued. Then the CREATE TRIGGER statement is executed to define the trigger.

The Db2 Option looks in the Db2 catalog to see if there are multiple triggers for the same table/event/time combination. Multiple triggers can be recreated in an order defined by the contents of the COMMENT ON field in the CREATE TRIGGER SQL to maintain the desired trigger firing order.

Triggers are defined in CREATE TRIGGER SQL statements only. There is no external equivalent.

Db2 Object Dependency Report

The Db2 Object Dependency report is a batch report that analyzes stored procedures and user-defined functions for dependencies that will interfere with the automatic DROP that is issued before a CREATE is executed at promote, demote, install, or backout.