SQL Views to list SQL programs and the statements within them

I found these two Views when going through the list of Db2 for i Views on Scott Forstie's Db2 for i poster (if you have not printed this out and stuck it to your wall do so now!). Both contain information about programs containing SQL statements:

1. SYSPROGRAMSTAT:  One row for each program that contains a SQL statement
2. SYSPROGRAMSTMTSTAT:  One for each SQL statement in a program

There are a couple for gotchas I found working with these Views, which I was disappointed by, I will mention these shortcomings when I discuss the View in detail below.

 

SYSPROGRAMSTAT

This View contains one row for each program, module, and service program that contains a SQL statement. I was disappointed to find this does not include CL programs that have the RUNSQL command in them. I know that Query Management Queries (*QMQRY) are not programs, but I would have liked them to be included in this view too.

For examples I created three objects:

1. MYPGM:  RPG program with six SQL statements
2. MYMODULE:  RPG module containing two SQL statements
3. MYSRVPGM:  I bound the module I created into a service program

Source code for MYPGM and MYMODULE is at the bottom of this post.

I am not going to use all of the columns in this View, just the following and then only use the short (system) names:

• SYS_DNAME:  Library
• SYS_NAME:  Program, module, or service program name
• MODNAME:  Module name
• PGMTYPE:  Program type
• NBRSTMTS:  Number of SQL statements in the program
• NAMING:  Whether used system or SQL naming conventions
• TGTRLS:  The target release the program was created for
• MINRLS:  The earliest release this statement can be run
• RDB:  Relational database name
• CLOSQLCSR:  When the cursor is closed
• DATFMT:  Date format
• TIMFMT:  Time format
• DBGVIEW:  Debug view

Let me put that all together in a SQL Select statement:

SELECT SYS_DNAME AS LIBRARY,SYS_NAME AS PROGRAM, CAST(MODNAME AS CHAR(10)) AS MODULE, CAST(PGMTYPE AS CHAR(10)) AS PGMTYPE, NBRSTMTS,NAMING,TGTRLS,MINRLS,RDB,CLOSQLCSR, DATFMT,TIMFMT,DBGVIEW FROM QSYS2.SYSPROGRAMSTAT

I am sure you noticed that I CAST the module and program types columns. In the View these are defined as 128 long VARCHAR, and as I only care about the first ten positions I have CAST them to be ten long character columns. I have used the AS to rename some of the columns, I have done this to give them what I think are more meaningful names. When I run that statement I am returned the following:

LIBRARY PROGRAM MODULE PGMTYPE NBRSTMTS NAMING MYLIB MYPGM MYPGM *PGM 5 *SYS MYLIB MYSRVPGM MYMODULE *SRVPGM 2 *SYS MYLIB MYMODULE - *MODULE 2 *SYS TGTRLS MINRLS RDB CLOSQLCSR DATFMT TIMFMT DBGVIEW V7R3M0 - *LOCAL *ENDMOD *ISO *ISO *SOURCE V7R3M0 V7R1M0 *LOCAL *ENDMOD *MDY *HMS *SOURCE V7R3M0 V7R1M0 *LOCAL *ENDMOD *MDY *HMS *SOURCE

The date and time formats for MYPGM is *ISO as I use the SET OPTION SQL statement to override the default date and time format.

I have to admit having played with this View for a while I do not see a scenario I would use it.

 

SYSPROGRAMSTMTSTAT

This View contains the SQL statements from the programs. I can use this View to search for examples of SQL statements that I could then adapt to use in my own programs.

As with the other View I am only interested in a few of the columns:

• SYS_DNAME:  Library
• SYS_NAME:  Program, module, or service program name
• MODNAME:  Module name
• PGMTYPE:  Program type
• STMTNBR:  SQL statement number. This is not the source statement number. For example, if this column contains 3 then this is the third SQL statement in this program
• STMTTEXT:  SQL statement

Without further ado, let me put that into a Select statement:

SELECT SYS_DNAME AS LIBRARY, SYS_NAME AS PROGRAM, MODNAME AS MODULE, CAST(PGMTYPE AS CHAR(10)) AS PGMTYPE, STMTNBR,STMTTEXT FROM QSYS2.SYSPROGRAMSTMTSTAT

As I did before I have used the AS to give the columns meaningful names:

LIBRARY PROGRAM MODULE PGMTYPE STMTNBR STMTTEXT MYLIB MYPGM MYPGM *PGM 1 *POINTER MYLIB MYPGM MYPGM *PGM 2 *POINTER MYLIB MYPGM MYPGM *PGM 3 *POINTER MYLIB MYPGM MYPGM *PGM 4 *POINTER MYLIB MYPGM MYPGM *PGM 5 *POINTER MYLIB MYSRVPGM MYMODULE *SRVPGM 1 *POINTER MYLIB MYSRVPGM MYMODULE *SRVPGM 2 *POINTER MYLIB MYMODULE MYMODULE *MODULE 1 *POINTER MYLIB MYMODULE MYMODULE *MODULE 2 *POINTER

The Statement Text, STMTTEXT, shows a *POINTER as this column is defined as DBCLOB, double-byte character large object. I can a CAST to convert it something that I can read:

SELECT SYS_DNAME AS LIBRARY, SYS_NAME AS PROGRAM, MODNAME AS MODULE, CAST(PGMTYPE AS CHAR(10)) AS PGMTYPE, STMTNBR, CAST(STMTTEXT AS CHAR(200)) AS SQL_STMT FROM QSYS2.SYSPROGRAMSTMTSTAT

In this example I have CAST the column to be a 200 long character column. It could be as easily converted to 1,000 long or more. I just chose 200 as I could see the entire statements that were returned by this statement.

LIBRARY PROGRAM MODULE PGMTYPE STMTNBR MYLIB MYPGM MYPGM *PGM 1 MYLIB MYPGM MYPGM *PGM 2 MYLIB MYPGM MYPGM *PGM 3 MYLIB MYPGM MYPGM *PGM 4 MYLIB MYPGM MYPGM *PGM 5 MYLIB MYSRVPGM MYMODULE *SRVPGM 1 MYLIB MYSRVPGM MYMODULE *SRVPGM 2 MYLIB MYMODULE MYMODULE *MODULE 1 MYLIB MYMODULE MYMODULE *MODULE 2 SQL_STMT DECLARE C0 CURSOR FOR SELECT * FROM QTEMP . TEST1 ORDER BY COL OPEN C0 FETCH C0 FOR : H ROWS INTO : H GET DIAGNOSTICS : H = ROW_COUNT CLOSE C0 INSERT INTO QTEMP . TEST1 VALUES ( 'Ninety nine' ) UPDATE QTEMP . TEST1 SET COLUMN1 = UPPER ( COLUMN1 ) WHERE COL INSERT INTO QTEMP . TEST1 VALUES ( 'Ninety nine' ) UPDATE QTEMP . TEST1 SET COLUMN1 = UPPER ( COLUMN1 ) WHERE COL

The first thing I noticed was that the SET OPTION statement in MYPGM is absent.

In the FETCH and GET DIAGNOSTICS statements there is : H, I presume that is to show that this is a value from a host variable.

If I had a question about how to define a cursor I could use this View to find examples that I could modify to fit my need.

SELECT CAST(STMTTEXT AS CHAR(1000)) FROM QSYS2.SYSPROGRAMSTMTSTAT WHERE CAST(STMTTEXT AS CHAR(1000)) LIKE '%CURSOR%'

When using LIKE I need to use the wildcard characters, which is the percentage character ( % ). In this example I am looking for any reference of CURSOR anywhere in the column.

I did not bother to give the column a meaningful name as it is the only column that is returned in the results. I cast the column to be 1,000 characters to ensure that I can capture most statements.

CAST function DECLARE C0 CURSOR FOR SELECT * FROM QTEMP . TEST1 ORDER BY COLUMN1 FOR READ ONLY

You can learn more about this from the IBM website:

• SYSPROGRAMSTAT View
• SYSPROGRAMSTMTSTAT View

 

This article was written for IBM i 7.3, and should work for earlier releases too.

---

Source code for example programs

MYPROGRAM

**free ctl-opt option(*nodebugio:*srcstmt:*nounref) alwnull(*usrctl) ; dcl-ds Data extname('QTEMP/TEST1') qualified dim(200) end-ds ; dcl-s NbrOfElements packed(3) inz(%elem(Data)) ; dcl-s RowsFetched like(NbrOfElements) ; exec sql SET OPTION COMMIT = *NONE, CLOSQLCSR = *ENDMOD, TIMFMT = *ISO, DATFMT = *ISO ; exec sql DECLARE C0 CURSOR FOR SELECT * FROM QTEMP.TEST1 ORDER BY COLUMN1 FOR READ ONLY ; exec sql OPEN C0 ; exec sql FETCH C0 FOR :NbrOfElements ROWS INTO :Data ; if (SQLCOD <> 0) ; dsply ('SQLCOD = ' + %char(SQLCOD)) ; endif ; exec sql GET DIAGNOSTICS :RowsFetched = ROW_COUNT ; exec sql CLOSE C0 ; *inlr = *on ;

 

MYMODULE

**free ctl-opt option(*nodebugio:*srcstmt:*nounref) alwnull(*usrctl) nomain ; dcl-pr TestProcedure ; end-pr ; dcl-proc TestProcedure export ; dcl-pi *n char(1) ; end-pi ; exec sql INSERT INTO QTEMP.TEST1 VALUES('Ninety nine') ; exec sql UPDATE QTEMP.TEST1 SET COLUMN1 = UPPER(COLUMN1) WHERE COLUMN1 <> 'Ninety nine' ; return ; end-proc ;

Return