SYSPACKAGESTAT

The SYSPACKAGESTAT view contains one row for each SQL package in the SQL schema.

The following table describes the columns in the SYSPACKAGESTAT view:

Table 1. SYSPACKAGESTAT view
Column Name	System Column Name	Data Type	Description
PACKAGE_SCHEMA	COLLID	VARCHAR(128)	Name of the schema
PACKAGE_NAME	NAME	VARCHAR(128)	Name of the SQL package
PACKAGE_OWNER	OWNER	VARCHAR(128)	Owner of the SQL package
PACKAGE_CREATOR	CREATOR	VARCHAR(128)	Creator of the SQL package
CREATION_TIMESTAMP	TIMESTAMP	CHAR(26)	Timestamp of when the SQL package was created
DEFAULT_SCHEMA	QUALIFIER	VARCHAR(128)	Implicit name for unqualified tables, views, and indexes
ISOLATION	ISOLATION	CHAR(2)	Isolation option specification: RR Repeatable Read (RR) RS Read Stability (ALL) CS Cursor Stability (CS) UR Uncommitted Read (CHG) NC No Commit (*NONE)
CONCURRENTACCESSRESOLUTION	CONCURRENT	CHAR(1)	Specifies the concurrent access resolution: blank Not specified W Wait for outcome U Use currently committed
SECONDARY_SPACE_COUNT	PKG_SPACES	INTEGER	Number of spaces in the package.
PENDING_FULL	PENDFULL	VARCHAR(3) Nullable	Indicates whether the package is pending full. NO The package is not pending full. YES The package is pending full. Contains null for a DRDA package.
PACKAGE_TYPE	PKG_TYPE	VARCHAR(16)	Indicates the type of package. EXTENDED DYNAMIC The package is an extended dynamic package. DRDA The package is a DRDA package.
NUMBER_STATEMENTS	NBRSTMTS	INTEGER	Number of SQL statements in the package
PACKAGE_USED_SIZE	PKSIZE	INTEGER	Number of bytes that are used for SQL statements and access plans in the package.
NUMBER_DUMMIES	NBRDUMMIES	INTEGER Nullable	Number of dummy statements in the package. Contains null for a DRDA package.
NUMBER_COMPRESSIONS	PGM_CMP	INTEGER Nullable	Number of times the package has been compressed. Contains null for a DRDA package.
STATEMENT_CONTENTION_COUNT	CONTENTION	BIGINT	Number of times contention occurred when attempting to store a new access plan.
EXTENDED_INDICATOR	EXTIND	VARCHAR(9)	Indicates the EXTIND attribute: EXTIND Extended indicator support is enabled. NOEXTIND Extended indicator support is not enabled.
C_NUL_REQUIRED	CNULRQD	VARCHAR(10)	Indicates the CNULRQD attribute: CNULRQD C nuls are required. NOCNULRQD C nuls are not required.
NAMING	NAMING	VARCHAR(4)	Indicates the NAMING attribute: SYS This is system naming. SQL This is SQL naming.
TARGET_RELEASE	TGTRLS	VARCHAR(6)	Indicates the target release of the package (VxRxMx).
EARLIEST_POSSIBLE_RELEASE	MINRLS	VARCHAR(6) Nullable	Indicates the earliest IBM i release that supports all the SQL statements in the package (VxRxMx). ANY The statements are valid on any supported IBM i release. VxRxMx The statement is valid on IBM i VxRxMx release or later. Contains null if the earliest release has not yet been determined.
RDB	RDB	VARCHAR(18)	Indicates the RDB specified for the package. rdb-name The name of the relational database. *NONE A relational database was not specified.
CONSISTENCY_TOKEN	CONTOKEN	VARBINARY(8) Nullable	Indicates the consistency token of the package. Contains null if the package is not a DRDA package.
ALLOW_COPY_DATA	ALWCPYDTA	VARCHAR(9)	Indicates the ALWCPYDTA attribute: NO A copy of the data is not allowed. OPTIMIZE A copy of the data is allowed whenever it might result in better performance. *YES A copy of the data is allowed, but only when necessary.
LOB_FETCH_OPTIMIZATION	OPTLOB	VARCHAR(9)	Indicates the LOB optimization attribute: OPTLOB The first FETCH for a cursor determines how the cursor will be used for LOB and XML result columns on all subsequent FETCHes. NOOPTLOB Any FETCH may retrieve a LOB or XML result column into either a locator or variable.
DECIMAL_POINT	DECPNT	VARCHAR(7)	Indicates the decimal point for numeric constants used in SQL statements. PERIOD The decimal point is a period. COMMA The decimal point is a comma.
SQL_STRING_DELIMITER	STRDLM	VARCHAR(9)	Indicates the character used as the string delimiter in the SQL statements. APOSTSQL The string delimiter is an apostrophe ('). QUOTESQL The string delimiter is a quote (").
DATE_FORMAT	DATFMT	VARCHAR(4)	Indicates the DATFMT attribute: JOB The date format specified in the job at runtime is used. USA The date format is USA. ISO The date format is ISO. EUR The date format is EUR. JIS The date format is JIS. MDY The date format is MDY. DMY The date format is DMY. YMD The date format is YMD. JUL The date format is *JUL.
DATE_SEPARATOR	DATSEP	CHAR(1)	Indicates the date separator.
TIME_FORMAT	TIMFMT	VARCHAR(4)	Indicates the TIMFMT attribute: JOB The time format specified in the job at runtime is used. USA The time format is USA. ISO The time format is ISO. EUR The time format is EUR. JIS The time format is JIS. HMS The date format is *HMS.
TIME_SEPARATOR	TIMSEP	CHAR(1)	Indicates the time separator.
DYNAMIC_DEFAULT_SCHEMA	DYNDFTCOL	VARCHAR(4) Nullable	Indicates whether the value for DFTRDBCOL should be used for implicit qualification on dynamic SQL statements: NO The schema specified in DFTDRBCOL is not used for dynamic SQL statements. YES The schema specified in DFTDRBCOL is used for dynamic SQL statements. Contains null if a default schema was not specified (DFTRDBCOL).
CURRENT_RULES	SQLCURRULE	VARCHAR(4)	Indicates the SQLCURRULE attribute: DB2 The semantics of all SQL statements will default to the rules established for DB2®. STD The semantics of all SQL statements will default to the rules established by the ISO and ANSI SQL standards.
ALLOW_BLOCK	ALWBLK	VARCHAR(8)	Indicates the ALWBLK attribute: ALLREAD Rows are blocked for read-only cursors. NONE Rows are not blocked for retrieval of data for cursors. READ Records are blocked for read-only retrieval of data for cursors when: NONE is specified for the Commitment control (COMMIT) parameter. The cursor is declared with a FOR READ ONLY clause or there are no dynamic statements that could run a positioned UPDATE or DELETE statement for the cursor.
DELAY_PREPARE	DLYPRP	VARCHAR(4)	Indicates the DLYPRP attribute: NO Dynamic statement validation is performed when the dynamic statements are prepared. YES Dynamic statement validation is delayed until the dynamic statements are used.
USER_PROFILE	USRPRF	VARCHAR(7)	Specifies the user profile used for authority checking: USER The profile of the user running statements in the package is used. OWNER The profiles of both the owner of the package and the user running statements in the package is used. NAMING If the naming convention is SQL, OWNER is used. If the naming convention is SYS, *USER is used.
DYNAMIC_USER_PROFILE	DYNUSRPRF	VARCHAR(6)	Specifies the user profile used for dynamic SQL statements: USER Local dynamic SQL statements are run under the profile of the job or thread. Distributed dynamic SQL statements are run under the profile of the application server job. OWNER Local dynamic SQL statements are run under the profile of the package's owner. Distributed dynamic SQL statements are run under the profile of the SQL package's owner.
SORT_SEQUENCE	SRTSEQ	VARCHAR(12)	Indicates whether the package uses a collating sequence: BY HEX VALUE The package does not use a collating table. LANGIDSHR The package uses a shared weight sort sequence (SRTSEQ). LANGIDUNQ The package uses a unique weight sort sequence (SRTSEQ). ALTSEQ The package uses an alternate collating sequence (ALTSEQ).
LANGUAGE_IDENTIFIER	LANGID	CHAR(3) Nullable	The language ID sort sequence. Contains null if the sort sequence is not LANGIDSHR or LANGIDUNQ.
SORT_SEQUENCE_SCHEMA	SRTSEQSCH	CHAR(10) Nullable	The sort sequence table system schema. Contains null if the sort sequence is hex.
SORT_SEQUENCE_NAME	SRTSEQNAME	CHAR(10) Nullable	The sort sequence table name. Contains null if the sort sequence is hex.
RDB_CONNECTION_METHOD	RDBCNNMTH	VARCHAR(4)	Specifies the semantics used for CONNECT statements: RUW CONNECT (Type 1) semantics are used to support remote unit of work. DUW CONNECT (Type 2) semantics are used to support distributed unit of work.
DECRESULT_MAXIMUM_PRECISION	DECMAXPRC	SMALLINT	Specifies the maximum precision. 31 The maximum precision is 31. 63 The maximum precision is 63.
DECRESULT_MAXIMUM_SCALE	DECMAXSCL	SMALLINT	The maximum scale (number of decimal positions to the right of the decimal point) that should be returned for result data types.
DECRESULT_MINIMUM_DIVIDE_SCALE	DECMINDIV	SMALLINT	The minimum divide scale (number of decimal positions to the right of the decimal point) that should be returned for both intermediate and result data types.
DECFLOAT_ROUNDING_MODE	DECFLTRND	VARCHAR(8) Nullable	Indicates the DECFLOAT rounding mode: CEILING ROUND_CEILING DOWN ROUND_DOWN FLOOR ROUND_FLOOR HALFDOWN ROUND_HALF_DOWN HALFEVEN ROUND_HALF_EVEN HALFUP ROUND_HALF_UP UP ROUND_UP
DECFLOAT_WARNING	DECFLTWRN	VARCHAR(3)	Indicates whether DECFLOAT warnings are returned. NO DECFLOAT warnings are not returned. YES DECFLOAT warnings are returned.
SQLPATH	SQLPATH	VARCHAR(3483) Nullable	Identifies the SQL path. Contains the null value if an SQL path is not specified.
LAST_USED_TIMESTAMP	LASTUSED	TIMESTAMP Nullable	The timestamp of the last time the package was used. If the package has never been used, contains null.
DAYS_USED_COUNT	DAYSUSED	INTEGER	The number of days the package was used since the last time the usage statistics were reset. If the package has never been used since the last time the usage statistics were reset, contains 0.
LAST_RESET_TIMESTAMP	LASTRESET	TIMESTAMP Nullable	The timestamp of the last time the usage statistics were reset. If the statistics have never been reset, contains null.
SYSTEM_PACKAGE_NAME	SYS_NAME	CHAR(10)	System name of the package.
SYSTEM_PACKAGE_SCHEMA	SYS_DNAME	CHAR(10)	System name of the schema containing the package.
IASP_NUMBER	IASPNUMBER	SMALLINT	Specifies the independent auxiliary storage pool (IASP) number.