PARSE_STATEMENT table function

Edit online

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.

Authorization: None required.

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.

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 rules apply:

Names used in data change statements and in any query construct are returned.
For CALL, the procedure being called is returned.
For DDL statements, most items are returned. Notable exceptions are:
- RCDFMT name for CREATE TABLE, CREATE VIEW, and CREATE INDEX.
- FIELDPROC program name for CREATE TABLE and ALTER TABLE.
- Columns used as partitioning keys for CREATE TABLE and ALTER TABLE.
- Parameter names for CREATE FUNCTION and CREATE PROCEDURE.
- Return column names for CREATE FUNCTION (Table).
- External program name for functions and procedures.
Any statement that does not contain these constructs, a query, or an expression returns 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(30)	Type of object name. ALIAS This is an alias name. COLUMN This is a column name, or a global variable name not returned as a DDL TARGET OBJECT. CONSTRAINT This is a constraint name. FUNCTION This is a function name. INDEX This is an index name. MASK This is a mask name. PACKAGE This is a package name. PARAMETER This is a parameter name for a COMMENT statement. PERM This is a permission name. PROC This is a procedure name. PROGRAM This is the program name for a CREATE FUNCTION, CREATE PROCEDURE, or CREATE TRIGGER statement. RETURN COLUMN This is a return column for a COMMENT statement. ROUTINE This is a routine name for a DDL operation. SCHEMA This is a schema name. SEQUENCE This is a sequence name. SPECIFIC This is a routine specific name for a DDL operation. TABLE This is a table, view, or alias name. TRIGGER This is a trigger name. TYPE This is a user-defined type name. VARIABLE This is a variable name for a DDL operation. VIEW This is a view name for a DDL operation. XSROBJ This is an XSROBJECT 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.
ADDITIONAL_NAME	VARCHAR(128)	Contains an additional name for some name types. Name of a member for an alias Name of a parameter Name of a table function return column System object name when FOR SYSTEM NAME provided in DDL System column name when two column names provided in DDL Contains null if there is no additional name.
USAGE_TYPE	VARCHAR(17)	How this name is used in the statement. CHECK CONSTRAINT Column is used in a check constraint. DDL SOURCE OBJECT Name identifies the table an index or trigger is being created on, the table referenced by CREATE TABLE LIKE, the table referenced by an alias, or the object being renamed. DDL TARGET OBJECT Name is the primary object of a DDL statement or the new name for a renamed object. EXPRESSION Name is referenced in an index key expression. FOREIGN KEY Column is used in a foreign key constraint. HISTORY TABLE Table is the history table for an ALTER TABLE statement with ADD VERSIONING. PARAMETER DEFAULT Name is referenced in a parameter default expression. PRIMARY KEY Column is used in a primary key constraint. QUERY Name is referenced as part of a query construct. REFERENCES Name is part of a foreign key constraint referencing another table. TARGET PROCEDURE This is the procedure that is the target of a CALL statement. TARGET TABLE This is the table that will be affected for an insert, update, delete, merge, truncate, lock table, or refresh table statement. The value is also returned for any explicitly specified columns from the target table for insert, update, and merge. UNIQUE KEY Column is used in a unique key constraint.
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 MASK ALTER PERMISSION ALTER PROCEDURE ALTER SEQUENCE ALTER TABLE ALTER TRIGGER ASSOCIATE LOCATOR CALL COMMENT ALIAS COMMENT COLUMN COMMENT CONSTRAINT COMMENT FUNCTION COMMENT INDEX COMMENT MASK COMMENT PACKAGE COMMENT PARAMETER COMMENT PERMISSION COMMENT PROCEDURE COMMENT RETURN COLUMN COMMENT ROUTINE COMMENT SEQUENCE COMMENT TABLE COMMENT TRIGGER COMMENT TYPE COMMENT VARIABLE COMMENT XSROBJECT
SQL_STATEMENT_TYPE (continued)		CREATE ALIAS CREATE FUNCTION CREATE INDEX CREATE MASK CREATE PERMISSION CREATE PROCEDURE CREATE SCHEMA CREATE SEQUENCE CREATE TABLE CREATE TRIGGER CREATE TYPE CREATE VARIABLE CREATE VIEW DECLARE CURSOR DECLARE GLOBAL TEMPORARY TABLE DELETE DESCRIBE PROCEDURE DROP ALIAS DROP FUNCTION DROP INDEX DROP MASK DROP PACKAGE DROP PERMISSION DROP PROCEDURE DROP ROUTINE DROP SCHEMA DROP SEQUENCE DROP TABLE DROP TRIGGER DROP TYPE DROP VARIABLE DROP VIEW DROP XSROBJECT EXECUTE IMMEDIATE GRANT FUNCTION GRANT PACKAGE GRANT PROCEDURE GRANT ROUTINE GRANT SCHEMA GRANT SEQUENCE GRANT TABLE GRANT TYPE GRANT VARIABLE GRANT XSROBJECT INSERT
SQL_STATEMENT_TYPE (continued)		LABEL ALIAS LABEL COLUMN LABEL CONSTRAINT LABEL FUNCTION LABEL INDEX LABEL MASK LABEL PACKAGE LABEL PERMISSION LABEL PROCEDURE LABEL ROUTINE LABEL SCHEMA LABEL SEQUENCE LABEL TABLE LABEL TRIGGER LABEL TYPE LABEL VARIABLE LABEL XSROBJECT LOCK TABLE MERGE PREPARE QUERY REFRESH TABLE RENAME INDEX RENAME TABLE REVOKE FUNCTION REVOKE PACKAGE REVOKE PROCEDURE REVOKE ROUTINE REVOKE SCHEMA REVOKE SEQUENCE REVOKE TABLE REVOKE TYPE REVOKE VARIABLE REVOKE XSROBJECT SET SET CURRENT TEMPORAL SYSTEM_TIME TRANSFER OWNERSHIP TRUNCATE UPDATE VALUES INTO

Example

For every program and service program in library APPLIB, find all the references to table names 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.number_statements > 0 AND
                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;