Configuring a z/OS subsystem to retrieve SQL statements from a dynamic cache

The pureQuery® GeneratePureQueryXml utility can import SQL statements from a DB2® for z/OS® dynamic cache. To retrieve SQL statements, you configure the target DB2 for z/OS subsystem by creating a utility stored procedure, an associated DB2 table, and static package.

Before you begin

This task requires a user on the z/OS system that has the DB2 permissions to create a table, and to a create a stored procedure and bind the accompanying static package. A user with SYSADM privilege can perform this task.

About this task

You submit a JCL job to configure the z/OS subsystem. The pureQuery Runtime installation directory contains three files that configure the z/OS subsystem:
  • PQRDSCJ a customizable JCL job
  • load/PQRCACHE a load module
  • dbrm/PQRCACHE the database request module (DBRM) associated with the load module
The files are in the pdq/samples/dscache/ directory of the pureQuery Runtime installation directory on a Linux, UNIX, or Windows system.

Procedure

To configure a z/OS subsystem to retrieve SQL statements from a DB2 for z/OS dynamic cache:

  1. FTP the JCL file PQRDSCJ in ASC format to a member of pre-defined partitioned data set (PDS) on the target DB2 for z/OS subsystem.

    The PDS is defined with a fixed 80-byte record format. You can use an existing data set that is used for JCL jobs.

  2. FTP the load module load/PQRCACHE in binary format to a member of a PDS on the target z/OS system

    FTP the file to a member in the program runtime library as specified in the JCL PROC associated with the stored procedure WLM address space. The PDS is defined with a fixed 80-byte record length.

  3. FTP the DBRM dbrm/PQRCACHE in binary format to a member of a PDS on the target z/OS system.

    FTP the file to a member in a pre-defined PDS with a fixed 80-byte record length format. You can FTP the file to an existing library that holds DBRM files.

  4. Customize the JCL file PQRDSCJ that is on target DB2 for z/OS subsystem.
    You customize these items in the JCL file:
    • Add a valid JOB card.
    • Change data set qualifier, plan, DB2 subsystem, and WLM names to match the information in your target DB2 and z/OS environment.
    • Make any additions to the CREATE statements for the work table and the underlying objects necessary for your site.
    • Make any wanted modifications to the database permissions for the procedure
    For more information about the customizing the JCL file, see the prologue section of the file PQRDSCJ.
  5. Submit the JCL job PQRDSCJ. The job creates a table, and creates a stored procedure and binds the accompanying static package.

Results

You can run the pureQuery GeneratePureQueryXml utility to import SQL statements from the DB2 for z/OS dynamic cache.

Feedback