RC/Query : Additional predicates added to RC/Query Combination reports where a UNION statement is used
Article ID: 244172
Updated On:
Products
Issue/Introduction
Extended Query Facility does not currently support the addition of additional predicates to RC/Query Combination reports where a UNION statement has been generated to produce the report such as the R/PK report.
Environment
Release : R20
DB2 for Z/OS
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
Feedback
