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 :

PROGRAM_NAME Name of the program containing the SQL statement(s) 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 .