RC/Query : Additional predicates added to RC/Query Combination reports where a UNION statement is used

Products

• RC/Query for DB2 for z/OS

Issue/Introduction

Extended Query Facility does not currently support the addition of additional predicates to RC/Query for Db2 for z/OS combination reports where a UNION statement has been generated to produce the report such as the R/PK report.

Cause

Extended Query Facility is not designed to append additional predicate by end users to generated reports that use a UNION statement to both sides of the UNION.

Resolution

An ACM(Alternate Catalog Map) can be used by creating a view on SYSROUTINES and applying the WHERE condition to the view's definition:

The relevant example here is the R/PK (Routines/Packages) report. The SQL generated contains a UNION statement. 

The SQL command when on this report reveals the SQL:

Current SQL Statement:

SELECT  A.NAME , A.CREATEDBY , A.SCHEMA , A.SPECIFICNAME , A.ROUTINETYPE ,
        A.VERSION , B.NAME , B.COLLID , B.OWNER , B.CREATOR , B.BINDTIME ,
        B.VALID , B.OPERATIVE , B.ISOLATION , B.VERSION , B.RELEASE
   FROM SYSIBM.SYSROUTINES A , SYSIBM.SYSPACKAGE B , SYSIBM.SYSPACKDEP C
  WHERE A.SCHEMA = 'authid1'
    AND C.BNAME = A.NAME
    AND C.BQUALIFIER = A.SCHEMA
    AND C.BTYPE IN ( 'F' , 'O' )
    AND B.LOCATION = C.DLOCATION
    AND B.COLLID = C.DCOLLID
    AND B.NAME = C.DNAME
    AND B.CONTOKEN = C.DCONTOKEN
  UNION
SELECT  A.NAME , A.CREATEDBY , A.SCHEMA , A.SPECIFICNAME , A.ROUTINETYPE ,
        A.VERSION , B.NAME , B.COLLID , B.OWNER , B.CREATOR , B.BINDTIME ,
        B.VALID , B.OPERATIVE , B.ISOLATION , B.VERSION , B.RELEASE
   FROM SYSIBM.SYSROUTINES A , SYSIBM.SYSPACKAGE B
  WHERE A.SCHEMA = 'authid1'
    AND A.COLLID <> ''
    AND A.COLLID = B.COLLID
    AND ( ( A.ORIGIN = 'E'
    AND A.EXTERNAL_NAME = B.NAME )
     OR ( A.ORIGIN <> 'E'
    AND A.SPECIFICNAME = B.NAME
    AND A.CONTOKEN = B.CONTOKEN
    AND B.TYPE IN ( 'F' , 'N' ) ) ) WITH UR

The task is to add an additional predicate to both sides of the UNION and in this example we wish to add "A.NAME NOT LIKE 'ADMIN%' "

Step one: Create a view on the SYSIBM.SYSROUTINES table containing the additional predicate required.

CREATE VIEW authid1.MYROUTINES AS    
 SELECT * FROM SYSIBM.SYSROUTINES A  
  WHERE A.NAME NOT LIKE 'ADMIN%'   ;

Step 2: Create your Alternate Catalog Map

Create the ACM from the main menu using the M  Alt. Catalog Mapping.

A new ACM called MYACM has been created and then linked the SYSIBM.SYSROUTINES  table  to the new view authid1.MYROUTINES

Note: NO Need to make any other changes to the target mapping tables. Any data upades made to  SYSIBM.SYSROUTINES    after the ACM is created will be reflected by the VIEW.

 ----------------- Alternate Catalog Mapping ----------------- yyyy/mm/dd hh:mm

Command ===>                                                  SCROLL ===> CSR

 

Define ID   ==> MYACM            Copy from ID ==>

Description ==> ROUTINES VIEW

 

FROM: All: Define ID. tablename        TO:  All -->          .

To copy Define ID or standard NAME, type an '=' in the respective area above.

---------------------------------------------------------------------- authid1

       SYSIBM   . SYSROUTINES                authid1  . MYROUTINES

 

Step 3: Back on the main menu set the ACM ON and specify the ACM name.

 

20.0.04  CA Database Management Solutions for DB2 for z/OS    yyyy/mm/dd hh:mm

OPTION ===>                                                   SCROLL ===> CSR

PT367 PT367I: PLEASE CHOOSE AN OPTION FROM THE LIST SHOWN.

DB2 SSID ==> ssid LOCATION ==> LOCAL                   DB2 VERSION: V12R1M500

ACM      ==> ON   ACMID    ==> MYACM  SQLID ==> authid1

 

Then go to the RCQ R/PK report , enter the SQL command and the SQL will look like this:

Current SQL Statement:

SELECT  A.NAME , A.CREATEDBY , A.SCHEMA , A.SPECIFICNAME , A.ROUTINETYPE ,
        A.VERSION , B.NAME , B.COLLID , B.OWNER , B.CREATOR , B.BINDTIME ,
        B.VALID , B.OPERATIVE , B.ISOLATION , B.VERSION , B.RELEASE
   FROM authid1.MYROUTINES A , SYSIBM.SYSPACKAGE B , SYSIBM.SYSPACKDEP C
  WHERE A.SCHEMA = authid1
    AND C.BNAME = A.NAME
    AND C.BQUALIFIER = A.SCHEMA
    AND C.BTYPE IN ( 'F' , 'O' )
    AND B.LOCATION = C.DLOCATION
    AND B.COLLID = C.DCOLLID
    AND B.NAME = C.DNAME
    AND B.CONTOKEN = C.DCONTOKEN
  UNION
SELECT  A.NAME , A.CREATEDBY , A.SCHEMA , A.SPECIFICNAME , A.ROUTINETYPE ,
        A.VERSION , B.NAME , B.COLLID , B.OWNER , B.CREATOR , B.BINDTIME ,
        B.VALID , B.OPERATIVE , B.ISOLATION , B.VERSION , B.RELEASE
   FROM authid1.MYROUTINES A , SYSIBM.SYSPACKAGE B
  WHERE A.SCHEMA = authid1
    AND A.COLLID <> ''
    AND A.COLLID = B.COLLID
    AND ( ( A.ORIGIN = 'E'
    AND A.EXTERNAL_NAME = B.NAME )
     OR ( A.ORIGIN <> 'E'
    AND A.SPECIFICNAME = B.NAME
    AND A.CONTOKEN = B.CONTOKEN
    AND B.TYPE IN ( 'F' , 'N' ) ) ) WITH UR

This will apply that additional predicate required to both sides of the UNION because the new VIEW is referenced above instead if SYSIBM.SYSROUTINES.

Other changes to the VIEW can be made depending on your needs.

As many different ACM’s as required can be created depending on your needs.

Remember to turn off the ACM on the main menu when not using that view of sysroutines on the main menu page ……. ACM      ==> OFF

 

Additional Information

Use Alternate Catalog Mapping (ACM)