Parsing SQL statements using a 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 .