Wednesday, January 24, 2018

SQL Views to list SQL programs and the statements within them

use sql views to find out about programs containing sql

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:

 

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

4 comments:

  1. Reynaldo Dandreb MedillaApril 21, 2021 at 7:58 AM

    Awesome article Simon

    ReplyDelete
  2. Extremely useful for surveying a schema for target keywords ... might have had reason to do that recently.

    ReplyDelete
  3. Hi Simon, great post! I didn't know that, thanks.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.