z/OS subsystem cannot be configured for retrieval of SQL statements from a DB2 for z/OS dynamic cache by pureQuery Runtime

Technote (troubleshooting)


Problem(Abstract)

You cannot configure a z/OS subsystem so that pureQuery Runtime can retrieve SQL statements from a DB2 for z/OS dynamic cache.

Cause

The topic Configuring a z/OS subsystem to retrieve SQL statements from a dynamic cache does not contain information about the required characteristics for z/OS partitioned data sets and the use of the use of the z/OS system utility AMATERSE.

Resolving the problem

The following task contains information about the characteristics for z/OS partitioned data set and the use of the z/OS system utility AMATERSE.

Configuring a z/OS subsystem to retrieve SQL statements from a DB2 for z/OS 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 an 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 for Linux, UNIX, or Windows installation directory pdq/samples/dscache contains three files that configure the z/OS subsystem:

  • PQRDSCJ a customizable JCL job
  • load/PQRCACHE a load module that has been packed with the z/OS system utility AMATERSE.
  • dbrm/PQRCACHE the database request module (DBRM) associated with the load module


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. Copy the load module/PQRCACHE to the target system:
    1. Pre-allocate a sequential dataset on the target z/OS system with these characteristics:
      RECFM FB, LRECL 1024.
    2. FTP the load/PQRCACHE file in binary format to that sequential file.
    3. Use z/OS system utility AMATERSE to UNPCACK the file from the sequential dataset.
      Unpack the file to a load the module library (PDS) that is specified in the JCL PROC associated with the stored procedure WLM address space.
  3. FTP the DBRM dbrm/PQRCACHE in binary format to a member of a PDS on the target z/OS system. The member must have these characteristics:
    RECFM FB LRECL 80.
    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.
    There is a sample AMATERSE step in the JCL file that can be uncommented and customized with your dataset names. 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. When you submit the JCL job, a new member PQRCACHE load module is added to the load module dataset. 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.

Cross reference information
Segment Product Component Platform Version Edition
Information Management InfoSphere Optim pureQuery Runtime for Linux- UNIX and Windows AIX, HP-UX, Linux, Linux pSeries, Linux zSeries, Solaris, Windows 3.2

Rate this page:

(0 users)Average rating

Document information


More support for:

InfoSphere Optim pureQuery Runtime for z/OS

Software version:

3.2

Operating system(s):

z/OS

Reference #:

1616772

Modified date:

2012-12-07

Translate my page

Machine Translation

Content navigation