Pages

Wednesday, May 8, 2019

Parsing SQL statements using a table function

parse_statement sql table function

Someone brought to my attention a Db2 for i table function that parses the names of the tables and columns used in string passed to it. I have to admit I had not heard of this, therefore, I decided to test out what kinds of information it could return.

The PARSE_STATEMENT appears to have been introduced as part of the IBM i release 7.2 . It takes a string that contains a SQL statement and returns the columns, objects, etc. contained within. The basic syntax is:

SELECT * FROM TABLE(<sql string>)) AS A

For example:

SELECT * FROM
TABLE(QSYS2.PARSE_STATEMENT('SELECT ORDNO,ORDSTS FROM PRODLIB.ORDHDR'))
AS A

This returns:

NAME_
TYPE
NAME SCHEMA RDB COLUMN
_NAME
USAGE
_TYPE
NAME_
START_
POSITION
SQL_
STATEMENT
_TYPE
COLUMN - - - ORDNO QUERY 8 QUERY
COLUMN - - - ORDSTS QUERY 14 QUERY
TABLE ORDHDR PRODLIB - - QUERY 26 QUERY

Note:  The dash ( - ) indicates that the value in the column is null.

Passing string like this to the table function is not exactly "real world". What I am more likely to do is take SQL statements from another view and use them with the PARSE_STATEMENT table function. Fortunately there is the view, SYSPROGRAMSTMTSTAT, that contains many of the SQL statements in the programs in this IBM i partition. There are just two columns I need from this view I would want to use with the PARSE_STATEMENT:

  1. PROGRAM_NAME  Name of the program containing the SQL statement(s)
  2. STATEMENT_TEST  The SQL statement

If there is more than one SQL statement in the program then there will be more than one column.

I will be using the same SQL statement for all of the example programs I have created which is:

01  SELECT B.NAME_TYPE,B.NAME,B.SCHEMA,B.COLUMN_NAME,B.USAGE_TYPE,
02         B.SQL_STATEMENT_TYPE,A.STATEMENT_TEXT
03    FROM QSYS2.SYSPROGRAMSTMTSTAT AS A,
04         TABLE(QSYS2.PARSE_STATEMENT(A.STATEMENT_TEXT)) AS B
05   WHERE A.PROGRAM_SCHEMA = 'MYLIB'
06     AND A.PROGRAM_NAME = '<program name>'

Lines 1 and 2: I am selecting some of the available columns:

Column name Description
B.NAME_TYPE Type of object name: COLUMN,FUNCTION,SEQUENCE,TABLE,TYPE
B.NAME Object name
B.SCHEMA Library/schema name
B.COLUMN_NAME Column name. If null then NAME_TYPE is not COLUMN
B.USAGE_TYPE How the name is used in this statement
B.SQL_STATEMENT_TYPE Type of SQL statement
A.STATEMENT_TEXT SQL statement contained in the program (this column is from SYSPROGRAMSTMTSTAT)

Line 3: Defining the SYSPROGRAMSTMTSTAT view.

Line 4: I am passing the SQL statement from the column in SYSPROGRAMSTMTSTAT to the PARSE_STATEMENT table function.

Lines 5 and 6: I am selecting only those rows from tt>SYSPROGRAMSTMTSTAT view when the schema (library) and program names match.

For the examples I am not going to show complete programs, just the SQL statements they contain. The first example contains a multiple row insert:

exec sql INSERT INTO QTEMP.PERSON VALUES(DEFAULT,'ROBERT','ALLEN'),
                               (DEFAULT,'John','Austin'),
                               (DEFAULT,'don','bennett'),
                               (DEFAULT,'DIETMAR','BRUCK'),
                               (DEFAULT,'Brian','Caine'),
                               (DEFAULT,'george','curtis'),
                               (DEFAULT,'ALAN','DALEY'),
                               (DEFAULT,'Ron','Framer'),
                               (DEFAULT,'lol','harvey'),
                               (DEFAULT,'RON','HEWITT'),
                               (DEFAULT,'Brian','Hill'),
                               (DEFAULT,'peter','hill'),
                               (DEFAULT,'COLIN','HOLDER'),
                               (DEFAULT,'Stuart','Imlach'),
                               (DEFAULT,'eric','jones'),
                               (DEFAULT,'MICK','KEARNS'),
                               (DEFAULT,'Frank','Kletzenbauer'),
                               (DEFAULT,'arthur','lightening'),
                               (DEFAULT,'BILLY','MYERSCOUGH'),
                               (DEFAULT,'Brian','Nicholas'),
                               (DEFAULT,'reg','ryan'),
                               (DEFAULT,'KEN','SATCHWELL'),
                               (DEFAULT,'Nelson','Stiffle'),
                               (DEFAULT,'ray','straw'),
                               (DEFAULT,'BOB','WESSON') ;

This returns the following results, I am not going to include the STATEMENT_TEXT below as it contains the same as the above SQL statement:

NAME_
TYPE
NAME SCHEMA COLUMN
_NAME
USAGE
_TYPE
SQL_
STATEMENT
_TYPE
TABLE PERSON QTEMP - TARGET TYPE INSERT

TARGET TABLE in the USAGE_TYPE indicates that the table will be affected by an insert, delete, update or merge statement.

The next program just contains a delete statement, where any row equal where the last name is equal to "Hill" will be deleted.

exec sql DELETE FROM QTEMP.PERSON WHERE LASTNAME = 'Hill' ;

This time two rows are returned, one for the table and the other for the column name used.

NAME_
TYPE
NAME SCHEMA COLUMN
_NAME
USAGE
_TYPE
SQL_
STATEMENT
_TYPE
TABLE PERSON QTEMP - TARGET TYPE DELETE
COLUMN - - LASTNAME QUERY DELETE

The next program is a bit more complicated as it contains a multiple row Fetch.

01  exec sql SET OPTION COMMIT = *NONE ;

02  exec sql DECLARE C0 CURSOR FOR
             SELECT SYSTEM_VALUE_NAME,
                    CURRENT_NUMERIC_VALUE,
                    CAST(CURCHARVAL AS CHAR(1280) CCSID 37)
               FROM QSYS2/SYSTEM_VALUE_INFO
              ORDER BY SYSTEM_VALUE_NAME ;

03  exec sql OPEN C0 ;

04  exec sql FETCH C0 FOR :Rows ROWS INTO :InDs :InNulls ;

05  exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ;

06  exec sql CLOSE C0 ;

The only rows returned are for the DECLARE CURSOR:

NAME_
TYPE
NAME SCHEMA COLUMN
_NAME
USAGE
_TYPE
SQL_
STATEMENT
_TYPE
COLUMN - - SYSTEM_
VALUE_NAME
QUERY DECLARE CURSOR
COLUMN - - CURRENT_
NUMERIC
_VALUE
QUERY DECLARE CURSOR
COLUMN - - CURCHARVAL QUERY DECLARE CURSOR
TABLE SYSTEM
_VALUE
_INFO
QSYS2 - QUERY DECLARE CURSOR
COLUMN - - SYSTEM_
VALUE_NAME
QUERY DECLARE CURSOR

The column SYSTEM_VALUE_NAME is there a second type as that is the column that is used for the ORDER BY.

The next program is yet more complex. This program does:

01  exec sql DROP TABLE QTEMP.TESTFILE ;

02  exec sql CREATE TABLE QTEMP.TESTFILE
             (KEY_COLUMN FOR "KEYFLD",
              FIRST,SECOND,THIRD,FOURTH,FIFTH,SIXTH)
             AS (SELECT A.F1KEY1,A.F1F1,A.F1F2,
                        B.F2F1,B.F2F2,
                        C.F3F1,C.F3F2
                   FROM FILE1 A CROSS JOIN
                        FILE2 B CROSS JOIN
                        FILE3 C
                  WHERE A.F1KEY1 = B.F2KEY1
                    AND A.F1KEY1 = C.F3KEY1)
              DEFINITION ONLY
              INCLUDING COLUMN DEFAULTS ;

03  exec sql INSERT INTO QTEMP.TESTFILE
              (KEY_COLUMN,FIRST,SECOND)
              SELECT * FROM FILE1 ;

04  exec sql MERGE INTO QTEMP.TESTFILE A USING FILE2 B
                   ON A.KEY_COLUMN = B.F2KEY1
                 WHEN MATCHED THEN
                      UPDATE SET A.THIRD = B.F2F1,
                                 A.FOURTH = B.F2F2
                 WHEN NOT MATCHED THEN
                      INSERT (KEY_COLUMN,THIRD,FOURTH)
                      VALUES (B.F2KEY1,B.F2F1,B.F2F2) ;

This program returns 24 rows, therefore, I am going to separate each set of rows by the SQL statement that generated them.

I find it interesting that the DROP TABLE did not generate any rows.

The CREATE TABLE did:

NAME_
TYPE
NAME SCHEMA COLUMN
_NAME
USAGE
_TYPE
SQL_
STATEMENT
_TYPE
COLUMN FILE1 - F1KEY QUERY CREATE TABLE
COLUMN FILE1 - F1F1 QUERY CREATE TABLE
COLUMN FILE1 - F1F1 QUERY CREATE TABLE
COLUMN FILE1 - F1F2 QUERY CREATE TABLE
COLUMN FILE2 - F2F1 QUERY CREATE TABLE
COLUMN FILE3 - F3F1 QUERY CREATE TABLE
COLUMN FILE3 - F3F1 QUERY CREATE TABLE
TABLE FILE1 - - QUERY CREATE TABLE
TABLE FILE2 - - QUERY CREATE TABLE
TABLE FILE3 - - QUERY CREATE TABLE
COLUMN FILE1 - F1KEY QUERY CREATE TABLE
COLUMN FILE2 - F2KEY QUERY CREATE TABLE
COLUMN FILE1 - F1KEY QUERY CREATE TABLE
COLUMN FILE3 - F3KEY QUERY CREATE TABLE

The last four rows are to do with the join criteria.

The next SQL statement in this program is the INSERT. Which generates the following rows:

NAME_
TYPE
NAME SCHEMA COLUMN
_NAME
USAGE
_TYPE
SQL_
STATEMENT
_TYPE
TABLE TESTFILE QTEMP - TARGET TABLE INSERT
COLUMN TESTFILE QTEMP KEY_COLUMN TARGET TABLE INSERT
COLUMN TESTFILE QTEMP FIRST TARGET TABLE INSERT
COLUMN TESTFILE QTEMP KEY_COLUMN SECOND INSERT
TABLE FILE1 - - QUERY INSERT

The first table row is for the table that the data is being inserted into. The second table, and last row, is for the table that the data is taken from.

The final statement in this program was a MERGE.

NAME_
TYPE
NAME SCHEMA COLUMN
_NAME
USAGE
_TYPE
SQL_
STATEMENT
_TYPE
TABLE TESTFILE QTEMP - TARGET TABLE MERGE
COLUMN TESTFILE QTEMP KEY_COLUMN QUERY MERGE
COLUMN FILE2 - F2KEY1 QUERY MERGE
COLUMN TESTFILE QTEMP THIRD TARGET TABLE MERGE
COLUMN FILE2 - F2F1 QUERY MERGE

 

The examples I have shown above are only part of what I experimented with the PARSE_STATEMENT table function. I have to admit I cannot think of a scenario I would use it. I am left asking myself: "What purpose do these results serve?"

I remain disappointed with the SYSPROGRAMSTMTSTAT view as it does not contain all of the SQL statements from my example programs. I might want to retrieve the SQL statement for a program from this view, but without statements, like the FETCH, I cannot say I feel confident I could recreate all of the SQL statements used by a particular program.

 

You can learn more about the PARSE_STATEMENT table function from the IBM website here.

 

This article was written for IBM i 7.3 and 7.2 .

1 comment:

  1. The usefulness comes in hanging this off an ODBC exit or similar where you can quickly judge what verb the user is using and the tables acted upon. However it really is hampered by not being remote verb complete and if it does not like a statement for any reason; be it an unsupported verb or bad statement; you have to check the SQL state to see that it faiedl

    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.