REPORT FACILITY V20: How to get an overview of various object types and corresponding SQL statements.

Products

• Report Facility for TSO

Issue/Introduction

How do you get a list of Report Facility(PRF) objects of a specific ID including their 'execute' statements(the statements placed in those QUERIES,FORMS,PROCS etc)?

Noted these 2 tables: PTI.PTPRF_LIB_2000 and PTI.PTPRF_STR_2000 contain this information and form this query for it:

SELECT L.PRF_ID,L.PRF_NAME,L.PRF_TYPE, ST.PRF_SEQUENCE ,CAST(ST.PRF_DATA as VARCHAR(4000) CCSID 500) 
FROM PTI.PTPRF_LIB_2000 L LEFT JOIN PTI.PTPRF_STR_2000 ST
  ON L.PRF_ID = ST.PRF_ID AND L.PRF_TYPE = ST.PRF_TYPE AND L.PRF_NAME = ST.PRF_NAME
WHERE L.PRF_ID = 'XXXX'
AND L.PRF_NAME ='XXXX_TWIN_CLOSE'
WITH UR;

 

Environment

Release : 20.0

Resolution

Using the following SQL statement:

SELECT L.PRF_ID,L.PRF_NAME,L.PRF_TYPE, ST.PRF_SEQUENCE,
  CAST(ST.PRF_DATA as VARCHAR(4000) CCSID 500)  
FROM PTI.PTPRF_LIB_2000 L LEFT JOIN PTI.PTPRF_STR_2000 ST
  ON L.PRF_ID = ST.PRF_ID AND L.PRF_TYPE = ST.PRF_TYPE AND
  L.PRF_NAME = ST.PRF_NAME AND 'SQLX----' = ST.PRF_STRUC_TYPE
WHERE L.PRF_ID = 'XXXX'
AND L.PRF_NAME ='XXXX_TWIN_CLOSE'
ORDER BY L.PRF_TYPE, L.PRF_NAME, ST.PRF_SEQUENCE
WITH UR;

The SQL will show all the objects of the given user with the specified name,and for the objects where SQL is available, it will show the SQL statement.
The SQL statements can span more rows, so in the resulting output,there may be more rows for one SQL statement if it's long (approx more than 4000 characters). 
Also, the first 4 bytes of the SQL data are the length of the SQL in hex/binary format.

For FORMs and PROCedures, there are no SQL statements, so the information for that will be missing.

Most of the data in PRF_DATA (in both the PTPRF_LIB_2000 and PTPRF_STR_2000 tables)
is in hex/binary format, the kind of data it contains is determined by the value in the PRF_STRUC_TYPE column.