May 1 ’07

Using DB2 Generic Attach for Flexible Database Interfaces

by Editor in z/Journal

Common modules are becoming, well, more common. These are generic modules that perform a certain function that can be called from a variety of applications. One example might be a module that retrieves name and address information for a customer. This module may be called from the customer support application, the accounts receivable application, or from a Web interface. This module may be executed from one of several online, batch, or distributed execution environments.

Typically, to use this module in these different execution environments requires a link to a different DB2 attach module. This requires additional maintenance when the module has to be modified and perhaps even the creation of separate copies of the module. In addition, what if you don’t know whether the caller of the module has already established a DB2 connection for the environment under which they’re operating? The generic attach technique liberates the developer from this burden by letting the module use DB2 services, in any execution environment, without linking to a specific attach module and without necessarily having to explicitly connect.

Generic Development

Generic development involves the creation of various modules to serve specific business functions. This could be retrieving data from a table, sending a message, or performing some sort of calculation. This type of development has become more prominent as more applications are being developed in DB2, and many applications are being migrated from legacy systems into DB2.

Once these generic modules are created, everyone wants to use them because they speed up the development process. They would like to use these modules wherever they’re executing. However, what if the site’s application isn’t using DB2? What if they don’t know if the business functionality module they want uses DB2? For these reasons, companies taking this approach find themselves challenged with providing the flexibility of the reusable modules while minimizing the maintenance involved with managing the modules in multiple environments (see Figure 1).

Execution Environments on z/OS

If we’re creating generic reusable modules, they must be flexible; the modules must be accessible from several execution environments, including:

The development team should seek to make these modules easy to use, and flexible enough to automatically adapt to each environment.

IBM provides an attachment program for DB2. The attachment program is actually a collection of various attachment load modules—each specific to a particular execution environment. Traditionally, programs would statically link the DB2 attachment load module specific to their execution environment into their program’s load module.

DB2 Attach and the Risks for Generic Modules

To interface with DB2, programs must be processed by the DB2 pre-compiler. The pre-compiler translates DB2 SQL calls into calls to particular subprograms. The subprogram being called, otherwise known as the attach module, is controlled by a pre-compiler option called ATTACH. IBM provides different attach modules for each execution environment:

For example, if ATTACH(TSO) is specified as a pre-compiler option, then all SQL calls will be translated by the pre-compiler into subprogram calls to program DSNELI. When the programmer link-edits the program, the DSNELI module is included in the load module, and all calls to it are static. Likewise, if ATTACH(CICS) is specified, then calls are made to module DSNCLI, and so on. This is why you need different load modules for different execution environments, and why developers have to maintain separate libraries for their programs that are common modules called in several execution environments. They need one version for TSO, one for CICS, or for stored procedures (RRS), and so on.

Usually, this presents problems in the area of change management. These problems revolve around the automation and approval process for programs moving from one environment to another. Sometimes, the tools and approvals can be attached only to one load module. Sometimes, the load module has to be attached to source code in the change management tool. In these cases, the developers are forced to manage multiple copies of the source programs and load modules. These multiple program copies can lead to incomplete or mismatched deployments and possibly unstable systems.

If no ATTACH option is specified for the pre-compiler, then something else happens.

Generic Attach

When no ATTACH pre-compiler option is specified, the pre-compiler will substitute the SQL calls in the program with calls to a generic module named DSNHLI. This is the generic attachment call. By default, the DSNHLI module is a replica of the DSNELI TSO attach module. However, all the attach modules have a DSNHLI entry point, so all the generic calls will work for any of the attach modules statically linked into the target load module.

It’s possible to not include the appropriate attach module at link-edit time; instead, you can call it dynamically. This technique is useful when a particular program that accesses DB2 tables needs to be called from several execution environments. If the appropriate attach module is available in the load library accessed in each execution environment, then a generic dynamic call to DSNHLI will pick it up. The trick is to name each specific attach module DSNHLI in a load library specific to each environment. This generic attach technique is described in the IBM Redbook DB2 for z/OS Stored Procedures: Through the Call and Beyond (SG24- 7083-00) and requires these steps:

  1. Don’t specify the ATTACH option for the DB2 pre-compiler.
  2. Allow for dynamic calls from the user program. This can be done by including the “PROCESS DYNAM” keywords at the beginning of a COBOL program, or via specifying the DYNAM COBOL compiler option.
  3. Don’t include a DB2 attach module during link-edit.
  4. Link-edit the attach module for the appropriate execution environment into its own special load library, and rename it DSNHLI.
  5. Concatenate the special load library ahead of the SDSNLOAD library at execution time.

If you do these things properly and specifically to the various execution environments, then the generic attach calls will execute the appropriate environment attach module.

This is beneficial because only one user program load module is then required for multiple execution environments. Moreover, only one user program load library is required for any execution environment that needs its services.

Initial Tests Conducted

A simple test program was written to test the generic attach approach. This program simply reads a few DB2 tables. Multiple copies of the program were created: one for TSO, one for Call Attach Facility (CAF), and one for RRS Attach Facility (RRSAF). Each program was written, linked, and tested using the traditional method of static calls to the DB2 attach module. For each program, the ATTACH pre-compiler option wasn’t specified, so the generic calls to DSNHLI were always used. For each load module created, the appropriate specific attach module was statically linked. Each program was tested before recoding for generic attach. After the initial testing, each program was altered for generic attach and tested. After numerous attempts, all tests were successful.

Specific Program and Binder (Link-Edit) Changes

Few changes are required to make the generic attach process work.

COBOL Program Changes

The following line was placed as the initial line of each COBOL program:

PROCESS DYNAM

This allowed for dynamic calls from COBOL without having to change the COBOL compiler options. Optionally, the DYNAM COBOL compiler option can be specified.

COBOL Program Link-edit Changes

Consider an example of the link-edit control cards for one of the COBOL programs. The COBOL program name in this example is SRPOCCUB, and all link-edit control cards for this program are exactly the same for any normal COBOL program, but the attach module (DSNALI, DSNELI, DSNRLI) is intentionally left out. Figure 2 shows the link-edit control cards for the program.

Attach Module Changes

Each attach module needs to be linked into its own library. Figure 3 shows an example of a link-edit for the CAF attach module.

Run-Time Changes

For any run-time execution, the appropriate special attach library must be concatenated to the STEPLIB before SDSNLOAD. Figure 4 shows an example of the run-time STEPLIB for the CAF attach.

For CICS, the special load library for DSNCLI must be placed in the RPL DD allocation before SDSNLOAD. In addition, an entry must be made into the CICS Processing Program Table (PPT) for the programs named DSNADDR, DSNADDR2, and DSNHLI (which is the renamed DSNCLI). Also, keep in mind these modules will now be dynamically called, so you could take a performance hit (the price generally made for generic design).

IBM Documentation

Two IBM manuals refer to generic attach: the Application Programming manual (SC18-7415) and the Redbook, DB2 for z/OS Stored Procedures: Through the CALL and Beyond (SG24-7083-00). The application programming guide indicates that you need to write a stub program for CAF and RRSAF to properly pass the generic calls. It also indicates that the CICS attach module must be statically linked. These instructions are no longer valid. In the Redbook, the link-edit examples for the various attach modules are incorrect. Those examples are missing the entry point card.

Optional Database Connection

The concept of a generic module can be taken a step further. Suppose you need to provide a service module that uses DB2, but you’re not sure whether the systems requiring that service are connected to DB2. The module can perform an RRS or CAF attachment. If the caller has already attached to DB2 via CICS, TSO, or any other attach, then the attempt to open another connection will result in failure—with a reason code of X’00F30049’ (duplicate connection attempted). The program can tolerate this code and continue. No matter which environment is being used, the appropriate attach module is already in use and the generic attachment will simply use that module. Cool!

Conclusion

Managing common reusable modules can be a challenge, especially when they can be called from various execution environments. Typically, this means multiple copies of load modules and special considerations for change management. DB2 generic attach solves this problem by letting the program execute the DB2 calls in any environment from a process that may or may not have already connected to DB2. Once you get the compiler option changed, and the various attach modules linked into their special libraries, you’re good to go! Z