PARSE_STATEMENT table function

The PARSE_STATEMENT table function returns a list of object and column names that are used in an SQL query, data change statement, or other statement where a query or expression is specified.

The schema is QSYS2.

SQL-statement

A character or graphic string expression that contains a valid SQL statement. The maximum string length is 2 megabytes.

naming

A character or graphic string expression that defines the naming rule for the statement.

*SYS: System naming rules apply. This is the default.
*SQL: SQL naming rules apply.

decimal-point

A character or graphic string expression that defines the decimal point for numeric constants in SQL-statement.

*PERIOD or .: The decimal point is the period. This is the default.
*COMMA or ,: The decimal point is the comma.

SQL-string-delimiter

A character or graphic string expression that defines the string delimiter for strings in SQL-statement. Delimited identifiers in the SQL statement will use the opposite character.

*APOSTSQL or ': The apostrophe character (') is used to delimit strings. This is the default.
*QUOTESQL or ": The quote character (") is used to delimit strings.

Authorization:

None required.

When the SQL statement is parsed, object names are identified and a result row is returned for every name. This is done at the SQL parser level where names are identified strictly by where they appear in the syntax. The following restrictions apply:

Names used in data change statements and in any query construct are returned.
For DDL statements, the following additional items are returned:
- For CREATE INDEX, the table on which the index is being created.
- For CREATE TABLE, any table referenced using the LIKE clause.
- For CREATE TRIGGER, the table or view on which the trigger is being defined.
DDL statements that do not contain these constructs, a query, or an expression return no rows.
Names in a routine-body, triggered-action, and trigger-body are not returned. To see these references, use QSYS2.SYSPROGRAMSTMTSTAT to find all the statements for the generated program or service program and pass each of them as an argument to this table function.
If the SQL statement is the null value, an empty string, a string of all blanks, or contains a syntax error, no row is returned.

The result of the function is a table containing a row for each name reference with the format shown in the following table. All the columns are null capable.

Table 1. PARSE_STATEMENT table function
Column Name	Data Type	Description
NAME_TYPE	VARCHAR(8)	Type of object name. COLUMN This is a column name or a global variable name. FUNCTION This is a function name. SEQUENCE This is a sequence name. TABLE This is a table, view, or alias name. TYPE This is a user-defined type name.
NAME	VARCHAR(128)	The object name. Contains null if NAME_TYPE is COLUMN without a table qualifier.
SCHEMA	VARCHAR(128)	The schema name. Contains null if NAME is not qualified with a schema name.
RDB	VARCHAR(128)	The relational database name. Contains null if NAME is not qualified with a relational database name.
COLUMN_NAME	VARCHAR(128)	The column name. Contains null if NAME_TYPE is not COLUMN.
USAGE_TYPE	VARCHAR(17)	How this name is used in the statement. DDL SOURCE OBJECT Name identifies the table an index or trigger is being created on, or the table referenced by CREATE TABLE LIKE. EXPRESSION Name is referenced in an index key expression. PARAMETER DEFAULT Name is referenced in a parameter default expression. QUERY Name is referenced as part of a query construct. TARGET TABLE This is the table that will be affected for an insert, update, delete, or merge statement. Also set for any explicitly specified columns from the target table for insert, update, and merge.
NAME_START_POSITION	INTEGER	Position within the SQL-statement string that this name begins. For qualified TABLE names, this is the position where the RDB or schema name begins. For all other name types, this is the position of the name.
SQL_STATEMENT_TYPE	VARCHAR(32)	Type of SQL statement. ALTER FUNCTION ALTER PROCEDURE ALTER TABLE CALL CREATE FUNCTION CREATE INDEX CREATE MASK CREATE PERMISSION CREATE PROCEDURE CREATE TABLE CREATE TRIGGER CREATE VARIABLE CREATE VIEW DECLARE CURSOR DECLARE GLOBAL TEMPORARY TABLE DELETE EXECUTE IMMEDIATE INSERT MERGE PREPARE QUERY SET SET CURRENT TEMPORAL SYSTEM_TIME UPDATE VALUES INTO

Example

For every program and service program in library APPLIB, find all the references to table names referenced in static SQL statements.

WITH program_statements(naming_mode, dec_point, string_delim, stmt_text,
    system_program_name, program_type)
    AS (SELECT a.naming, a.decimal_point, a.sql_string_delimiter, b.statement_text,
               a.system_program_name, a.program_type
          FROM qsys2.sysprogramstat a INNER JOIN
               qsys2.sysprogramstmtstat b ON a.program_schema = b.program_schema AND
                                             a.program_name = b.program_name AND
                                             a.module_name = b.module_name 
          WHERE a.program_schema = 'APPLIB' AND b.program_schema = 'APPLIB')
SELECT system_program_name, program_type, c.schema, c.name, stmt_text
    FROM program_statements, 
    TABLE(qsys2.parse_statement(stmt_text, naming_mode, dec_point, string_delim)) c
    WHERE c.name_type = 'TABLE'
    ORDER BY c.schema, c.name;