SYSIBM. SYSPACKSTMT DB2 Storage Statement table

SYSIBM. SYSPACKSTMT table

The SYSIBM. SYSPACKSTMT table contains one or more rows for each statement in a package.

Use

Column name	Data Type	Description
Location	VARCHAR (+) not NULL	Always contains blanks	S
Collid	VARCHAR (+) not NULL	Name of the Package collection.	G
name	varchar (+) not NULL	name of the package".	g
contoken	char (8) Not nullfor BIT DATA	consistency token for the package. This is either: the the timestamp indicating when the package's program is precompiled, in an internal format< /li>	s
seqno	integernot NULL	not used.	g
stmtno	smallintnot NULL	the statement number of the statement in the SOURC E program. A statement number greater than 32767 is stored as Zero1 or as a negative number2. If the value is zero, see Stmtnoi for the statement number.	g
sectno	smallintnot NULL	the section number of the Statement.2	g
binderror	char (1) Not NULL	whether an SQL error is detected at bind time: N No Y Yes	g
ibmreqd	char (1) Not NULL	a value of Y indicates that the row came from the basic Machin E-readable material (MRM) tape. For all other values, see Release dependency indicators. The value of this field was not a reliable indicator of release dependencies.	g
VERSION	VARCHAR (122) Not NULL	Version identifier for the package.	G
	VARCHAR (3500) not NULL withdefaultfor BIT DATA	Internal use only.	I
Isolation	CHAR (1) Not NULL Withdefault	Isolation level for the SQL statement: R RR (Repeatable Read) T RS (read stability) S CS (cursor stability) U UR (Uncommitted Read) L RS isolation, with a lock-clause X RR isolation, with a lock-clause Blank The WITH clause is not specified on this statement. The isolation level was recorded in Syspackage. Isolation and in Sysplan. Isolation.	G
STATUS	CHAR (1) Not NULL Withdefault	Status of the binding the statement: A Distributed-statement uses Db2®private protocol access. The statement is parsed and executed at the server using defaults for input variables during access path selection. B Distributed-statement uses DB2 private protocol access. The statement is parsed and executed at the server using the values for input variables during access path selection. C Compiled-statement is bound successfully using defaults for input variables during access path selection. D distributed-statement references a remote object using a Three-part name. DB2 would implicitly use drda®access either because the dbprotocol bind option is not specified (defaults to DRDA), or th E bind option Dbprotocol (DRDA) was explicitly specified. This option allows the use of Three-part names with DRDA access but it requires that the package is bound at the target re Mote site. E Explain-statement is an SQL Explain statement. The explain is do at bind time using defaults for input variables during access path selection. F Parsed-statement didn't bind successfully and VALIDATE (RUN) was used. The statement is rebound at execution time using the values for input variables during access path selection. G compiled-statement bound successfully, but REOPT is specified. The statement is rebound at execution time using the values for input variables during access path selection. H Parsed-statement is either a data definition statement or a statement that does not bind successfully and VALIDATE ( RUN) was used. The statement is rebound at execution time using defaults for input variables during access path selection. Data manipulation statements use defaults for input variables during access path selection. I indefinite-statement is dynamic. The statement is bound at execution time using defaults for input variables during access path selection.	S
STATUS (cont.)		J indefinite-statement is dynamic. The statement is bound at execution time using the values for input variables during access path selection. K control-call statement. L bad-the Statement has some allowable error. The BIND continues but the statement cannot is executed. M parsed-statement references a table that's qualified with SESSION and were not bound because the table reference C Ould be-for-a declared temporary table that won't be defined until the package or plan is run. The SQL statement is rebound at execution time using the values for input variables during access path selection. O Compiled for acceleration. The static query was bound successfully for acceleration and would be routed to a accelerator when executed. Blank The statement is non-executable, or were bound in a DB2 release prior to Version 5.
Accesspath	CHAR (1) Not NULL Withdefault	For static statements, indicates if the access path for the statement are based on user-specified optimization hints: H Optimization hints were used. A The access path was reused because of the apreuse bind option. Blank One of the following situations: The access path is determined without the use of hints, and a previous access path is not reused. No access path is associated with the statement. The statement is a dynamic SQL statement	G
stmtnoi	integernot NULL withdefault	if the value of Stmtno is zero, the column contain s the statement number of the statement in the source program. If both Stmtno and Stmtnoi are zero, the statement number is greater than 32767.	g
sectnoi	integernot NULL withdefault	the section number of the statement.	g
explainable	char (1) Not NULL Withdefault	contains one of the following values: Y indicates that SQL statement can is used with the EXPLAIN function And might have rows describing their access path in the owner. Plan_table. N indicates that the SQL statement does not has any rows describing Its access path in the owner. Plan_table. Blank indicates that the SQL statement is bound prior to Version 7.	g
queryno	integernot NULL withdefault–1	the query number of the SQL statement in the sour CE program. SQL statements bound prior to Version 7 has a default value of of–1. Statements bound in Version 7 or later with the value specified on the Queryno clause on SELECT, UPDATE, INSERT, DELETE, EX PLAIN, DECLARE CURSOR, or refresh TABLE statements. If The QUERYNO clause is isn't specified, the query number is set to the statement number.	g
ROWID	Rowidnull generatedalways	ROWID column, created for the LOB columns in this table.	G
stmt_id	Bigintnot NULL	A Unique statement identifier.	G
STATEMENT	CLOB (2M) not Nullwith DEFAULT	The complete text is the SQL statement that the row represents.	G
	BLOB (2M) not Nullwith DEFAULT	Internal use only.	I

1 Rows in which the value of Seqno, Stmtno, and Sectno is zero is for internal use.2 to convert a negative stmtno to a M Eaningful statement number, corresponds to your precompile output, add 65536 to it. For example, -26472 are equivalent to +39064 (-26472 + 65536).

SYSIBM. SYSPACKSTMT DB2 Storage Statement table