Skip to content

Db2 Option Concepts

This section defines terms and concepts used by the ChangeMan ZMF Db2 Option.

Core Db2 Option

The original support provided by the ZMF Db2 option addresses the automation of plan and package binds throughout the lifecycle. In more recent years, support for additional objects and processes has been introduced. This additional support requires the creation of a number of infrastructure items (Db2 option tables etc.) and the binding of a wider range of ZMF supplied packages. If you do not want to use this additional support, you do not need to put in place the extra infrastructure items that it requires. You can choose to keep your overhead low and concentrate on the original ‘core’ Db2 option support. In this section, the term ‘Core ZMF Db2 option support’ or, simply, ‘core support’ refers to the automation of program package and plan binds (through the use of program CMNDB2PL and supporting admin).

Physical and Logical SubSystem

In the ChangeMan ZMF Db2 Option, the Db2 subsystems where test and production Db2 components run are identified as physical subsystems. Each Db2 Option physical subsystem is associated with a local or remote site.

If you have enough Db2 resources, you can have a separate Db2 subsystem for production and for every test environment. Every program can be bound under the same plan name and package collection ID in each Db2 subsystem without conflict.

Most ChangeMan ZMF user sites, however, use the same Db2 subsystem for several test environments. Some user sites use a single Db2 subsystem for production and test. Where the same program is run in multiple environments in the same Db2 subsystem, it must be bound under different package collection IDs and plan names to avoid conflict.

Qualifier and bind owner may also be different for the different uses of the program. The same stored procedures can be registered in the Db2 catalog under different schemas.

The ChangeMan ZMF Db2 Option partitions a physical Db2 subsystem with logical subsystems. A logical subsystem is a set of rules for changing plan names, package location, package collection ID, qualifier, bind owner, schema, and WLM environments to provide unique entries in the Db2 catalog. A logical subsystem also includes rules for managing stored procedures and triggers, as well as what to do in promotion when a bind fails.

Logical subsystems are assigned a name, which is sometimes called a nickname. Each logical subsystem is associated with a single physical subsystem.

Recent enhancements address the requirements of a modern Native SQL development lifecycle such as when initiated via IBM ® Data Studio (although IBM Data Studio is not a pre-requisite, the same Native SQL code can be hand written directly via an ISPF edit session).

Active Libraries

Automated Db2 Option functions are activated when libraries managed by ChangeMan ZMF are changed in promotion and production environments. These libraries are defined as active libraries in the Db2 Option. Each active library is associated with a logical subsystem. When the contents of an active library are changed, Db2 Option functions are invoked and executed according to the rules defined in the logical subsystem.

Bind Active Libraries

When a Db2 program is changed, or when the BIND command that references a DBRM is changed, the DBRM for that program must be bound in the Db2 subsystem where the program is executed. DBRM libraries and libraries containing BIND command members are defined as BIND active libraries to trigger Db2 binds at promote, demote, install, and backout.

SQL Active Libraries

When a stored procedure, user-defined function, or trigger is added or changed, SQL must be processed to create or change the definition in the Db2 catalog. Stored procedures may be stopped and started to activate changes. When a database trigger is changed, it may be necessary to recreate other triggers to maintain the original firing order. This special processing is invoked by defining certain libraries as SQL active libraries. Promotion and production libraries that contain stored procedures, triggers, and user-defined functions are defined as SQL active libraries.

Bind Service Active Libraries

Copying into a Bind service active library causes ChangeMan to drive the process required to define a Db2 RESTful service at the appropriate Db2 subsystem(s).

Db2 Library Subtypes

Db2 library subtypes invoke special processing for Db2 components. There is a discussion with more information in the Define Global Db2 Library Subtypes section under Configuring the Db2 Option.

Templates

In the ChangeMan ZMF Db2 Option, you create transformation rules to express the difference between BIND commands (etc.) executed in various environments such as production and test. When you promote, demote, install, or back out a Db2 program, the Db2 Option applies these transformation rules to model BIND commands in staging or baseline libraries to create BIND commands suitable for the target environment. The binds (etc.) are then performed automatically. Bind service definitions are also templated in a similar way, though using a different process to plans and packages.

Transformation rules can also be defined for parameters in DDL for stored procedures, triggers, and user-defined functions. When you promote, demote, install, or back out a Db2 stored procedure, trigger, or user-defined function, the Db2 Option applies the transformation rules to model DDL statements in staging or baseline libraries to generate SQL suitable for the target environment. The modified DDL are then actioned automatically.

There are two types of template definitions, named field templates and general token templates. The former are applied to a set of commonly templated bind/DDL parameters and are described in this section. The latter are more general and allow you to search for a parameter keyword before applying a template to the values on that general keyword. General token templating is described more fully in the Define Application Logical Subsystems section.

Transformation rules are defined in templates in the ChangeMan ZMF Db2 Option. You define a set of templates for each Db2 Option logical subsystem.

The model BIND commands and DDL to which these templates are applied are often the BIND commands and SQL you use in one of your production environments. If you use these production components for your models, the templates are empty for the logical subsystem corresponding to the production environment.

You can modify the following parameters in a BIND command by using templating:

  • PLAN Name

  • PACKAGE Location

  • PACKAGE Name/Collection ID

  • Owner

  • Qualifier

If the owner or qualifier parameters are missing from the BIND command, you can insert these parameters by coding Insert values in the logical subsystem definition. Insert values are applied during templating when owner and qualifier are missing from the BIND command and the following CMNDB2PL control cards are present: AUTHORITY=OWNER,INSERT and INSERTQUAL.

You can modify the following parameters in the DDL for stored procedures, triggers, and user defined functions by using templating:

  • Schema

  • Collection ID

  • Qualifier

  • WLM environment

  • Owner

The following parameters can be templated directly in bind service definitions:

  • Collection ID

  • Qualifier

  • Owner

The template algorithms are Insert, Deploy, Search and Replace, and Positional Character Replacement.

Insert

Insert applies only to BIND templates owner and qualifier. If these keywords are missing from a BIND command, and control statement parameters AUTHORITY=OWNER,INSERT Db2 Option Concepts

and INSERTQUAL are input to the Plan Lookup program CMNDB2PL, the values specified in the logical subsystem template are added to the BIND command.

Deploy

Deploy applies only to SQL templates LOCATION, owner and qualifier. They are used if the

BIND DEPLOY mechanism is chosen for Native SQL stored procedures. The 'DEPLOY Location' is used to route the execution of the bind deploy command from the target Db2 subsystem back to the source Db2 subsystem for the deployment. OWNER and QUALIFIER may be specified on the bind deploy command itself and the values for these will be templated as normal. However, if the template process does not result in a nonblank value then anything specified in the DEPLOY templates for these parameters will be used instead.

Search and Replace

BIND command parameters and DDL parameters are searched for a value specified in the logical subsystem template. If the string is located, it is replaced by another value specified in the template.

Positional Character Replacement

The character in a particular position of a BIND command parameter or DDL parameter is replaced with a character specified in the logical subsystem template. Specified characters can be also be added at the end of BIND command parameters or DDL parameter.

Templates And Change Management

Validated templates are essential to software change management for BIND commands or DDL because you cannot test these components in a production environment. Validated templates provide an automated transformation for these components that ensures that if they work for promotion (test), they will also be valid for production.

Plan/package Lookup

Plan/package Lookup program CMNDB2PL is included in batch jobs that promote, demote, install, or back out change packages that contain Db2 programs. The Plan/package Lookup program performs two functions:

  • Finds the Db2 plans and packages that contain the DBRM staged in the change package and locates the PDS members that contain BIND commands to bind the DBRM.

  • Applies templates to the BIND commands to transform them for use in the target Db2 environment.

The Plan/package Lookup program searches the Db2 SYSPACKAGE and SYSDBRM tables to find packages and plans where the DBRM in the change package was bound previously. The program then looks in staging libraries for the members that contain BIND commands for the list of plans and packages. If the BIND command members are not in staging libraries, then the Plan Lookup program looks in promotion and baseline libraries, in that order.

See the CMNDB2PL - BIND Utility section for a more detailed description of how the Plan/package Lookup program works and the control statements that can alter its behavior.

BIND Fail

When you install a Db2 component into your production environment, you want the install process to fail if the Db2 binds fail.

However, the same may not be true for your test environments. You can set a parameter in each logical subsystem to allow the promotion process to complete successfully even if the Db2 bind jobs fail.