Accessing non-Db2 resources in your stored procedure

Applications that run in a stored procedures address space can access any resources that are available to z/OS® address spaces. For example, they can access VSAM files, flat files, APPC/MVS conversations, and IMS or CICS® transactions.

About this task

Accessing these resources from a stored procedure can be useful if you want to update older applications. Suppose that you have existing applications that access non-Db2 resources, but you want to use newer Db2 applications to access the same data. You do not need to rewrite the application or migrate the data to Db2. Instead, you can use stored procedures to execute the existing program or access the non-Db2 data directly.

When a stored procedure runs, the stored procedure uses the Recoverable Resource Manager Services (RRS) for commitment control. When Db2 commits or rolls back work, Db2 coordinates all updates that are made to recoverable resources by other RRS compliant resource managers in the z/OS system.

Procedure

To access non-Db2 resources in your stored procedure:

  1. Consider serializing access to non-Db2 resources within your application.
    Not all non-Db2 resources can tolerate concurrent access by multiple TCBs in the same address space.
  2. To access CICS, use one of the following methods:
    • Stored procedure DSNACICS
    • Message Queue Interface (MQI) for asynchronous execution of CICS transactions
    • External CICS interface (EXCI) for synchronous execution of CICS transactions
    • Advanced Program-to-Program Communication (APPC), using the Common Programming Interface Communications (CPI Communications) application programming interface

    If your system is running a release of CICS that uses z/OS RRS, z/OS RRS controls commitment of all resources.

  3. To access IMS DL/I data, use one of the following methods
    • Open Database Access interface (ODBA)
    • Stored procedures DSNAIMS and DSNAIMS2
    If your system is not running a release of IMS that uses z/OS RRS, take one of the following actions:
    • Use the CICS EXCI interface to run a CICS transaction synchronously. That CICS transaction can, in turn, access DL/I data.
    • Invoke IMS transactions asynchronously using the MQI.
    • Use APPC through the Common Programming Interface (CPI) Communications application programming interface.
  4. Determine which of the following authorization IDs you want to use to access the non-Db2 resources.
    Table 1. Authorization IDs for accessing non-Db2 resources from a stored procedure
    ID that you want to use to access the non-Db2 resources SECURITY value to specify in the CREATE PROCEDURE statement
    The authorization ID that is associated with the stored procedures address space SECURITY Db2
    The authorization ID under which the CALL statement is executed SECURITY USER
    The authorization ID under which the CREATE PROCEDURE statement is executed SECURITY DEFINER
  5. Issue the CREATE PROCEDURE statement with the appropriate SECURITY option that you determined in the previous step.

Results

When the stored procedure runs, Db2 establishes a RACF® environment for accessing non-Db2 resources and uses the specified authorization ID to access protected z/OS resources.