Catalog Tables in Db2 12

A “already verified”

D “userid and security sensitive data encryption”

E “userid, password and security sensitive data encryption”

R “RACF PassTicket”

p “password”

O An outbound ID is subject to translation. USERNAMES table are used to perform ID translation. No translation or ‘come from’ checking is performed an inbound IDs.

S USERNAMES table is used to obtain system

AUTHID used to establish a trusted connection Blank No translation occurs

Y Location is trusted. Access to remote location requires trusted context defined at remote location

N Location is not trusted

Y Secure connection using SSL required for outbound DRDA

N Secure connection is not required for outbound DRDA

NAME

A Alias

C Clone table

P Implicit table created for XML columns

T Table

blank All the above object types

A Audit all failures (authorization and authentication)

Blank Audit none

A Audit all

Blank Audit none

A Audit when specified table is altered or dropped

Blank Audit none

A Audit when specified table is accessed during the first operation of any kind performed by each unit of work a utility or application process

C Audit when specified table is accessed during first insert, update or delete operation performed by each unit of work

Blank Audit none

A Audit all utilities

Blank Audit none

A Audit all utilities

Blank Audit none

Blank Audit none

* Audit all the authorities

I Installation SYSADM

L SYSCTRL

O SYSOPR

R Installation SYSOPR

S SYSADM

Can be concantenated string of all supported values

Blank Audit none

* Audit all the authorities

B System DBADM

C DBCTRL

D DBADM

E SECADM

G ACCESSCTRL

K SQLADM

M DBMAINT

P PACKADM

T DATAACCESS

Can be a concantenated string of all supported values

Y Audit policy will be started automatically

N Audit policy will not be started automatically

USE PROFILE use options specified in profile

TABLE Options only apply for this table

COLUMNS Options only apply for these columns

SAMPLE Sampling is allowed

OPEN Alert is not yet resolved

IN PROGRESS Alert execution is in progress

COMPLETED Alert execution is complete

M Value of DAY column is interpreted as day of month

W Value of DAY column is interpreted as day of week

B: BUSINESS_TIME check constraint

S: SYSTEM_TIME check constraint

Blank: not applicable

Blank: An authorization ID

L: Role

P: An application plan or package. Grantee is a package if COLLID is not blank

R REFERENCES privilege

Blank UPDATE privilege

L Role

Blank Authorization ID that is not a role

C Cardinality

F Frequent value

H Histogram statistics

N Nonpadded frequent value

TYPE=’H’, number of distinct values for column group in a quantile indicated by QUANTILENO

C Cardinality

F Frequent value

H Histogram statistics

N Non-padded frequent value

TYPE=N or F, number of rows or keys in partition for which FREQUENCYF value applies. TYPE=H, number of distinct values of column group in a quantile in QUANTILENO

C Cardinality

F Frequent value

H Histogram statistics

N Non-padded frequent value

TYPE=’H’, number of distinct values for column group in quantile in QUANTILENO. -1 if statistics not gathered

Blank Statistics have not been collected or varchar column statistical values are padded

N VARCHAR column statistical values are not padded

An updateable column

N = no

Y = yes

Can be N for a view column derived from an expression or a function. Allows nulls when an outer select list refers to it

N = no

Y = yes

The values is N the column is:

* Derived from a functional or expression

* A column that is defined with AS IDENTITY and GENERATED ALWAYS attributes.

Value can be Y for columns of a read-only view

B BIT data

M MIXED data

S SBCS data

Blank

N No

Y Yes

Blank For a view defined prior to V7

I Definition is incomplete because a LOB tablespace, auxiliary table or index on an auxiliary table not created

Blank Definition of complete

0 Not a LOB or ROWID column

40 For a ROWID column, the length of the returned value

1 to 2 147 483 647 bytes

For a LOB column, the maximum length

Blank Statistics have not been collected or varchar column statistical columns are padded

N Varchar column statistical values are padded

An updatable column

ORDERING

A Ascending

D Descending

Blank Column is not used as part of a partitioning key

P Partially hidden

N Not hidden

TYPEMOD_ID

B Start of period BUSINESS_TIME

C End of period BUSINESS_TIME with exclusive period

I End of period BUSINESS_TIME with inclusive period

S Start of period SYSTEM_TIME

T End of period SYSTEM_TIME

Blank Not used as start or the end of a period

ATTR

A Defined as GENERATED_ALWAYS

B Defined as GENERATEDBY_DEFAULT

blank Not applicable or value of DEFAULT column is A, D, E, F, I or J or defined from a prior release of DB2

COLSEQ

SCHEME

A ASCII

E EBCDIC

U UNICODE

0 Not a LOB or ROWID column

40 For a ROWID column, length of the returned value

2 to 2 147 483 647 bytes. For a LOB column, maximum length

F=Function instance

C Check constraint

D User-defined default constraint

blank Authorization ID

R Role

L Role

Blank Authorization ID

L ROLE AS OBJECT OWNER is specified

Blank ROLE AS OBJECT OWNER not specified

Y Enabled

N Disabled

Y Connection reuse is allowed

N Connection reuse is not allowed

SECURITYLABEL

Y Authentication token is required for auth ID. For local requests, token is the password. For remote requests, token can be password, a RACF passticket or a KERBEROS token

N Authentication is not required

blank authorization ID

L Role

R Row permission

M Column mask

A All Access

N Row permission was explicitly created or is a column mask

Y Row permission was implicitly created

N Not enabled

Y Enabled

Blank Definition is complete

R Error occurred where an attempt was made to regenerate row permission or column mask

A ALTER

B REBUILD INDEX

C CREATE

D CHECK DATA LOG(NO) (no log records for the range are available for RECOVER utility)

E RECOVER (to current point)

F COPY FULL YES

I COPY FULL NO

J RECORD TABLESPACE or LOAD REPLACE compression dictionary write to log

L SQL (type of operation)

M MODIFY RECOVERY utility

P RECOVER TO COPY or RECOVER TORBA (partial recovery point)

Q QUIESCE

R LOAD REPLACE LOG(YES)

S LOAD REPLACE LOG(NO)

V REPAIR VERSIONS utility

W REORG LOG(NO)

X REORG LOG(YES)

Y LOAD LOG(NO)

Z LOAD LOG(YES)

T TERM UTILITY command (terminated utility)

C Change

R Reference

blank Does not describe an image copy or was from V 1.1

Blank LOCALSITE primary copy

FC FlashCopy copy

LB LOCALSITE backup copy

RP RECOVERYSITE primary copy

RB RECOVERYSITE backup copy

D DASD

T Tape

Blank

A Partition was added to a table

B MEMBER CLUSTER was changed

C Column was added to a table and an index in different commit scopes, or column was dropped from a table

D DSSIZE attribute of the table space was altered

E Data set numbers of a base table and its associated clone table are exchanged

F Page size attribute of table space or index was altered

G Index was regenerated

I Inline attribute of LOB column was altered by REORG

L Logging attribute was altered to NOT LOGGED

M MAXPARTITIONS attribute was altered

N An index was altered to not padded

O Logging attribute was altered to NOT LOGGED P Index was altered to padded

R Table was altered to rotate partitions

S SEGSIZE attribute of the table space was altered

V Column was altered for numeric data type change and the column is in an index

X REORG dropped one or more empty partitions from the related table space

Z Column in key of an index that was versioned prior to DB2 V8 was altered

When ICTYPE=C:

L Logging attribute was altered to LOGGED

O Logging attribute was altered to NOT LOGGED

When ICTYPE=E:

B RECOVER utility with BACKOUT

blank RECOVER utility without BACKOUT

When ICTYPE=F, the values are:

C DFSMS concurrent copy (‘I’ instance of table space)

J DFSMS concurrent copy (‘J’ instance of tablespace)

N A FlashCopy copy is not consistent

Q Sequential copy is consistent

S LOAD REPLACE(NO)

T FlashCopy is consistent

U Sequential copy is not consistent

V ALTER INDEX NOT PADDED

W REORG LOG(NO)

X REORG LOG(YES)

blank DB2 image copy

When ICTYPE=L

M Mass DELETE, TRUNCATE TABLE, or DROP TABLE. LOWDSNUM contains table OBID of affected table

B Recover to a point in time with BACKOUT YES option ran

C Recover to a point in time without logonly with consistency

L Recover to a point in time logonly without consistency

M Recover to a point in time using logonly with consistency

blank Recover to a point in time without logonly without consistency

When ICTYPE=R or S, the values are:

A Resetting REORG pending status

T First materialized default value for a row change timestamp column

F COPY FULL YES

I COPY FULL NO

When ICTYPE=W or X:

A Restting REORG pending status or REBALANCE

H Hash organization attributes of the table were altered

T First materializing the default value for a row change timestamp column

For other values ICTYPE, the value is blank

I Index space

T Tablespace

Y LOGGED

N NOT LOGGED

Blank Row was inserted prior to VP

Y Previous member cluster attribute is used

N Previous member cluster attribute is not being used

When ICTYPE=A and STYPE=C, indicates if a column is added or dropped from a table:

blank A column was added to a table

D A column was dropped from a table

CMP=N index compression activated

CMP=Y index compression deactivated

When ICTYPE=A and STYPE=I

D REORG decremented the inline length of LOB column

I REOG incremented the inline length of LOB column

n Previous value of MAXPARTITIONS attribute for table space

S Table space was converted from single-table segmented table space to a partition-by-growth universal table space

When ICTYPE=A and STYPE=P

ABSOLUTE – table space converted from absolute to relative page numbering.

When ICTYPE=A and STYPE=S

n Previous value of SEGSIZE attribute for table space

P Table space was converted from a partitioned table space to a range-partitioned universal table space

When ICTYPE=E

blank Full recovery reset the object

N Full recovery did not reset the object

When ICTYPE=F and STYPE=N, Q, T, or U,

A LOAD RESUME LOG NO

B REBUILD

C COPY

D LOAD RESUME LOG YES

E LOAD SHRLEVEL CHANGE

L LOAD

P REPAIR

R LOAD RPELACE LOG YES

S LOAD REPLACE LOG NO

T COPYTOCOPY

W REORG TABLESPACE LOG NO

X REORG TABLESPACE LOG YES

When ICTYPE=P, R, S, W, X

B RBA or LRSN format changed to 6-byte format

BRF Basic row format

BRF I Basic row format, and FORMAT INTERNAL used

E RBA or LRSN format changed to extended 10-byte

F REORG utility was run with

RRF Reordered row format

RRF I Reordered row format and FORMAT INTERNAL used

S REORG utility was run with FASTSWITCH NO option

When ICTYPE=M and STYPE=R

blank MODIFY RECOVERY deleted rows from SYSLGRRNX

N MODIFY RECOVERY did not delete rows from SYSLGRNX

ICTYPE=T, TTYPE of B

ICTYPE=W or X and STYPE=H

ICTYPE=Y or Z

Blank indicates that the FORMAT INTERNAL option was not specified during LOAD

I indicates that the FORMAT INTERNAL option was specified during LOAD

ICTYPE=A-A, A-R, B, C, P, R, S, W, or X

B Page format was converted to basic page format with 6 byte RBA or LRSN values

E Page format was converted to extended page format with 10-byte RBA or LRSN values

When ICTYPE=A and STYPE=A or R:

B Page format was converted to basic page format with 6 byte RBA or LRSN values

E Page format was converted to extended page format with 10-byte RBA or LRSN values

C Conversion mode

E Enabling-new-function mode

N New-function mode

blank Not a work file database or a TEMP database

W A work file database. Database is DSNDB07, or it was created with WORKFILE clause and used as a work file database by a data-sharing member

SCHEME

E EBCDIC

A ASCII

U UNICODE

blank For DSNDB04, a work file database and a TEMP database

Y Implicitly created

N Explicitly created

Blank Authorization ID

L Role

A User-defined ordinary array type

L User-defined associative array type

T Distinct type

B FOR BIG DATA

S FOR SBCS DATA

M FOR MIXED DATA

Blank Source type is not a character type

A ASCII

E EBCDIC

U UNICODE

blank Authorization ID

L Role

-1 Type does not specify INLINE LENGTH

greater than or equal to 0

Inline length attribute (in byte) of the type if it is based on LOB source type

B FOR BIT DATA

S FOR SBCS DATA

M FOR MIXED DATA

blank Array index is not a character type

blank Authorization ID

L Role

Blank Not applicable

C DBCTL

D DBADM

E SECADM

G ACCESSCTRL

L SYSCTRL

M DBMAINT

S SYSADM

blank Privilege is not held

G Privilege held with the GRANT option

Y Privilege is held without the GRANT option

blank Privilege is not held

G Privilege held with the GRANT option

Y Privilege is held without the GRANT option

blank Privilege is not held

G Privilege held with the GRANT option

Y Privilege is held without the GRANT option

blank Privilege is not held

G Privilege held with the GRANT option

Y Privilege is held without the GRANT option

blank Privilege is not held

G Privilege held with the GRANT option

Y Privilege is held without the GRANT option

blank Privilege is not held

G Privilege held with the GRANT option

Y Privilege is held without the GRANT option

blank Privilege is not held

G Privilege held with the GRANT option

Y Privilege is held without the GRANT option

blank Privilege is not held

G Privilege held with the GRANT option

Y Privileges held without the GRANT option

blank Privilege is not held

G Privilege held with the GRANT option

Y Privileges held without the GRANT option

blank Privilege is not held

G Privilege held with the GRANT option

Y Privileges held without the GRANT option

blank Privilege is not held

G Privilege held with the GRANT option

Y Privileges held without the GRANT option

blank Privilege is not held

G Privilege held with the GRANT option

Y Privileges held without the GRANT option

blank Privilege is not held

G Privilege held with the GRANT option

Y Privileges held without the GRANT option

blank Privilege is not held

G Privilege held with the GRANT option

Y Privileges held without the GRANT option

blank Privilege is not held

G Privilege held with the GRANT option

Y Privileges held without the GRANT option

Blank Authorization ID

L Role

N Apostrophe

Y Quotation mark

N Period

Y Comma

B Assembler language

C OS/VS COBOL

D C

F Fortran

P PL/I

2 VS COBOL II or IBM COBOL R1

3 IBM COBOL (Release 2 or subsequent releases)

4 C++

A No

K Yes

N No

Y Yes

Blank No

Y Yes

TYPE

Blank Authorization ID

L Role

C Column

E INSTEAD OF trigger

F Function

G Global temporary table

I Index

M Materialized query table

O Procedure

P Partitioned table space

Q Sequence

R Table space

S Synonym

T Table

U Distinct type

V View

W SYSTEM_TIME period

Z BUSINESS_TIME period

0 zero

L Role

Blank Authorization ID that is not a role

B Trigger package for basic trigger

C Generated column

F Function

I Index

M Materialized query table

O Procedure

V View

X Row permission

Y Column mask

1 Trigger package for advanced trigger

L Role

Blank Authorization ID

0 Current copy

4 Invalid copy

VERSION

A – ALTER changed table or base table of view

H – ALTER of object created prior to DB2 V5

N – Stabilized access path is not valid

Y – Stabilized access path for dynamic query is valid

0 Current copy

1 Previous copy

2 Original copy

BTYPE=R – value is database

BTYPE=B or C – value is table

BTYPE=F, O or Q – value is schema name

Blank if BNAME is role

Else value is schema of BNAME

B BUSINESS_TIME

C SYSTEM_TIME

E INSTEAD OF trigger

F UDF or cast function

G Global temporary table

I Index

M materialized query table

O Stored procedure

P Partitioned tablespace with LARGE or DSSIZE

Q Sequence object

R Table space

S Synonym

T Table

U Distinct type

V View

W SYSTEM_TIME period

Z BUSINESS_TIME period

0 Alias

D DDL dependency

50 SELECTAUTH

51 INSERTAUTH

52 DELETEAUTH

53 UPDATEAUTH

64 EXECUTE AUTH

263 USAGEAUTH

291 READAUTH

292 WRITEAUTH

0 not used – CLASS = D

Blank Value of CLASS = D or CLASS = A (authid)

L AUTHID contains name of role

B SDBAMAUTH

D DBADMAUTH

G ACCESSCTRLAUTH

K SQL ADMCUTH

L SYSCTRLAUTH

S SYSADMAUTH

T DATAACCESSAUTH

Blank authority not held

Blank privilege not held by public or CLASS = D

Blank privilege not held on all objects or CLASS = D

ENCODING_CCSID

ENCODING_CCSID

C Comma

P Period

SCALE

N The usual rules apply for decimal division in SQL

Y Retain at lease three digits to the right of the decimal point after any decimal division

DELIMITER

A Apostrophe (‘)

Q Quote (“)

DELIMITER

A Apostrophe (‘)

Q Quote (“)

N No mixed data

Y Mixed data

ARITHMETIC

1 DEC15 specifies that the rules do not allow a precision greater than 15 digits

2 DEC31 specifies that the rules allow a precision of up to 31 digits

I ISO-yyyy-mm-dd

J JIS-yyyy-mm-dd

U USA-mm/dd/yyyy

E EUR-dd.mm.yyyy

L Locally defined by an installation exit routine

I ISO-hh.mm.ss

J JIS-hh.mm.ss

U USA-hh:mm AM or hh:mm PM

E EUR-hh.mm.ss

L Locally defined by an installation exit routine

I IEEE floating point format

S System/390 floating point format

A Alphanumeric

N Lower case letters in SBCS ordinary identifiers are not folded to uppercase

Y Lower case letters in SBCS ordinary identifiers are folded to uppercase

Blank Not applicable

C ROUND_CEILING

D ROUND_DOWN

F ROUND_FLOOR

G ROUND_HALF_DOWN

E ROUND_HALF_EVEN

H ROUND_HALF_UP

U ROUND_UP

F Fast Index Traversal(FTB)

F Force FTB creation

D Disable FTB creation

A Automatic FTB creation

M Day of month

W Day of week

‘E’ Enabled

‘D’ Disabled

‘M’ Day of the month

‘W’ Day of the week

G Yes, and it is an index used to enforce the uniqueness of values in a column defined as ROWID GENERATED BY DEFAULT

X Yes, and it is an index used to enforce the uniqueness of values in a column that contains XML data

C Yes, and it is used to enforce uniquenessof a UNIQUE constraint or hash key columns

D No (duplicates are allowed)

U Yes

P Yes, and it is a primary index

C Yes, it is an index used to enforce UNIQUE constraint

N Yes, and it is defined with UNIQUE WHERE

R Yes, it is an index used to enforce uniqueness of a non-primary parent key

G Yes, and it is as an index used to enforce the uniqueness of values in a column defined as ROWID GENERATED BY DEFAULT

X Yes, and it is an index used to enforce the uniqueness of values in a column that contains XML data

N No

Y Yes

N A significant number of rows are not in clustering order, or statistics have not been gathered

Y Most of the rows are in clustering order

blank Not applicable

An updateable column that can be changed by RUNSTATS.

For sparse index, is based on actual contents of index

blank Type 1 index

D Data partition secondary index

P Both partitioned and is a partitioning index

N No

Y Yes

Y Index is padded

N Index is not padded

Blank Index does not contain varying length or graphic data

VERSION

COUNT

TYPE

blank Simple index

S Index on a scalar expression

N Node ID index

T Spatial index

V XML index

N Index compression is not active

Y Index compression is active

blank Authorization ID

L Role

FACTOROF

N No. (default)

Y Yes

N No. (default) Every data row has an index entry

Y Yes. Index might not have an entry for each data row in the table

X Excluded, Index will not have an index entry when every data row for a key column contains the NULL value

A Absolute

R Relative

E Explicit(storage group not used)

I Implicit(storage group used)

blank Only changed pages

A Changed and unchanged pages

N No data is cached

Blank Does not enforce a non-unique constraint

F Enforces a foreign key for a temple referential constraint

Blank n/a

N Explicitly created

Y Implicitly created

N No

Y Yes

FACTORF

E Explicit, and STORNAME, names an ICF catalog

I Implicit, and STORNAME, names in storage group

blank only changed pages are cached

A Changed and unchanged pages are cached

N No data is cached

ENTRIES

B Basic, 6-byte RBA/LRSN format

E Extended, 10-byte RBA/LRSN format

U Undefined

blank for migrated objects

A Absolute

R Relative

EXTERNAL

ENTRIES

PSEUDODELETES

HDD Hard Disk Drive

SDD Solid State Drive

FACTORF

FACTORF

blank Authorization ID

L Role

PATTERN

B Start of the period BUSINESS_TIME

C End of the period BUSINESS_TIME

I End of period BUSINESS_TIME w/inclusive end

blank Not used as either start or end of BUSINESS_TIME

blank index is based on an expression or column is specified for the index using the INCLUDE clause

A Ascending

D Descending

R Random

B Start of the period BUSINESS_TIME

C End of the period BUSINESS_TIME

I End of period BUSINESS_TIME w/inclusive end

blank Not used as start or end of a BUSINESS_TIME period

0 Not a ROWID column

40 For a ROWID

N No

Y Yes. Y also indicates that the index is an XML index

B BIT data

M MIXED data

S SBCS data

blank non-character data

N VARCHAR column statistical values are not padded blank Statistics have not been collects or VARCHAR column statistical values are padded

An updateable column

N VARCHAR column statistical values are not padded

blank Statistics have not been collected or VARCHAR column statistical values are padded

0 Not a ROWID column

40 For a ROWID

N No

Y Yes

N VARCHAR column statistical values are not padded blank Statistics have not been collects or VARCHAR column statistical values are padded

C Cardinality

F Frequent value

N Non-padded frequent value

H Histogram statistics

TYPE=’H’= number of distinct values for the key group in a quantile indicated by QUANTILENO

TYPE=’H’- percentage of entries in index that have a value that is in the range of the quantile in QUANTILENO column

Not used if TYPE does not equal ‘H’

C Cardinality

F Frequent value

N Non-padded frequent value

H Histogram statistics

TYPE=’H’- percetage of entries in the index that have a value that is in the range of the quantile in QUANTILENO

C Cardinality

F Frequent value

N Not a frequent value

H Histogram statistics

LVL

_LVL

C Catalog level change

F Function level change

M Code level change

L Role

blank Authorization ID

A Alias

B Trigger

D Database

E Distinct type

F User-defined function

I Index

J Jar

L Role

M Materialized query table

N Trusted context

O Stored procedure

Q Sequence

R Table space

S Storage group

T Table

V View

X Row permission

Y Column mask

0 Alias

A Alter statement changed the description of the table of base table of a view referred to by the package. For a CREATE INDEX involving data sharing, VALID is also marked as ‘A’. Changes do not invalidate the package.

H ALTER TABLE statement changed description of the table or base table of a view referred to by the package

N No

Y Yes

N No

Y Yes

B All checking must be performed at bind time

R Validation is done at run time for tables, views and privileges that do not exist at bind time

I Local packages inheriting the value from the plan

R RR (repeatable read)

S CS (cursor stability)

T RS (read stability)

U UR (uncommitted read)

blank Not specified, and therefore at the level specified for the plan executing the package

C Value used was COMMIT

D Value used was DEALLOCATE

I Local package is inheriting value from plan

blank Not specified, and therefore the value specified for the plan executing the package

N No

Y Yes

N Apostrophe

Y Quotation mark

N Period

Y Comma

B Assembler language

C OS/VS COBOL

D C

F Fortran

P PL/I

2 VS COBOL II or IBM COBOL Release 1

3 IBM COBOL (Release 2 or subsequent releases)

4 C++

Blank For remotely bound packages, trigger packages (TYPE=’T’), SQL procedure packages (TYPE=’N’), or non-inline SQL scalar function packages (TYPE=’F’)

K Yes

A No

N No

Y Yes

N No

Y Yes

A Data currency is required for all cursors. Inhibit blocking for all cursors

B Data currency is not required for ambiguous cursors

C Data currency is required for ambiguous cursors blank Package was created before CURRENTDATA option was available

C Continue

N No package

C Package was created by BIND COPY

D Package was created by BIND COPY with the OPTIONS(COMMAND) option

K Package was copied from a package that was originally bound on behalf of a remote requester

L Package was copied with the OPTIONS(COMMAND) option from a package that was originally bound on behalf of a remote requester

N Package was locally bound from a DBRM

Y Package was bound on behalf of a remote requester

Any DEGREE(ANY)

1 or blank DEGREE(1)

blank if the package was migrated

B BIND. Dynamic SQL statements are executed with DYNAMICRULES bind behaviour

D DEFINEBIND

E DEFINERUN

H INVOKERUN

R RUN

Blank DYNAMICRULES not specified for the package

A REOPT(AUTO)

N REOPT(NONE)

Y REOPT(ALWAYS)

1 REOPT(ONCE)

N NODEFER(PREPARE)

Y DEFER(PREPARE)

I Local package is inheriting value from the plan blank Bind option not specified. Inherited from plan

N KEEPDYNAMIC(NO)

Y KEEPDYNAMIC(YES)

F CREATE FUNCTION or ALTER FUNCTION statement, or a BIND PACKAGE DEPLOY command created the package, and this package is a non-inline SQL scalar function package

N CREATE PROCEDURE or ALTER PROCEDURE statement, or BIND PACKAGE DEPLOY command created the package, and this package is a native SQL routine package

R Reserved for IBM use

T CREATE TRIGGER statement created the package, and the package is a trigger package. blank BIND PACKAGE command created the package

D DRDA

C DRDA access with package-based continuous block feature enabled

CCSID Specified or derived CCSID

0 EBCDIC default CCSID as specified on panel DSNTIPF at installation time

I Local package is inheriting the value from the plan

N IMMEDWRITE(NO)- normal write activity is done

Y IMMEDWRITES(YES)- immediate writes are done for updated group buffer pool dependent pages

1 IMMEDWRITES(PH1)- updated group bufferpool dependent pages are written at or before phase 1 commit

Blank if the package was migrated

blank Bound prior to V7

blank Authorization ID

L Role

C ROUND_CEILING

D ROUND_DOWN

F ROUND_FLOOR

G ROUND_HALF_DOWN

E ROUND_HALF_EVEN

H ROUND_HALF_UP

U ROUND_UP blank

Blank The package created in a DB2 release prior to V9

A DB2 will collect remote location names from SQL statements during local bind, and automatically create remote packages at those sites

L DB2 will automatically create remote packages at sites specified in the list of location-names

RES

blank Not specified

N WAITFOROUTCOME

Y USECURRENTLYCOMMITTED

INDICATOR

N EXTENDEDINDICATOR NO

Y EXTENDEDINDICATOR YES

B PLANMGMT BASIC

E PLANMGMT EXTENDED

Blank PLANMGMT OFF

S PLANMGMTSCOPE STATIC

N No or none: Access paths are not reused

W WARN: DB2 tries to reuse access paths. Processing continues when an access path cannot be reused

E ERROR: DB2 tries to reuse access paths. Processing ends when an access path cannot be reused

Y Yes specified. All copies were retained

0 No specified; however, the previous or original package copy is still retained due to access path differences

1 No specified, and the previous package copy is not retained as access paths are identical to current copy

2 No specified, and previous and original package copies are retained as access paths are identical to current

Y References to system-period temporal tables are affected by CURRENT TEMPORAL BUSINESS_TIME

N References to application-period temporal tables are not affected by CURRENT TEMPORAL BUSINESS_TIME

Y References to application-period temporal tables are affected by CURRENT TEMPORAL BUSINESS_TIME

N References to application-period temporal tables are not affected by CURRENT TEMPORAL BUSINESS_TIME

V10R1 SQL statements in the package have V10R1 compatibility behavior

V11R1 SQL statements in the package V11R1 compatibility behaviour

Function-level – SQL statements in package of compatibility behaviour with the specified function level

Y (default) references to archive-enabled tables are affected SYSIBMADM.GET_ARCHIVE built-in global variable

N References to archive-enabled tables are not affected by SYSIBMADM.GET_ARCHIVE built-in global variable

Y DB2 database manager generates a DESCRIBLE SQLDA at bind time so DESCRIBE requests for static SQL can be satisfied during execution

N DB2 database managers does not generate a DESCRIBE SQLDA at bind time for static SQL statements

A Automatic bind

B Bind command

G Explicit ALTER REGENERATE

I Implicit automatic regeneration

R REBIND command

Blank existed before 12

N No

Y Yes

A ALTER statement changed description of table or base table of a view referred to by the package

H ALTER TABLE statement changed description of table or base table of a view referred to by package

N No

Y Yes

N An explicit BIND or REBIND is required before package can be allocated

Y Yes

B All checking must be performed at bind time

R Validtion is done at run time for tables, views, and privileges that do not exist at bind time

R RR(repeatable read)

S CS(cursor stability)

T RS(read stability)

U UR(uncommitted read)

Blank Not specified, level specified for plan

C Value used was COMMIT

D Value used was DEALLOCATE

I Local package is inheriting the value from the plan

Blank Not specified, value for plan executing the package

N No

Y Yes

N Apostrophe

Y Quotation mark

N Period

Y Comma

B Assembler language

C OS/VS COBOL

D C

F Fortran

P PL/I

2 VS COBOL II or IBM COBOL Release 1

3 IBM COBOL (Release 2 or subsequent release)

4 C++

Blank for remotely bound packages, trigger packages (TYPE=’T’), SQL procedure packages (TYPE=’N’), or non-inline SQL scalar function packages (TYPE=’F’)

K Yes

A No

N No

Y Yes

N No

Y Yes

A Data currency is required for all cursors. Inhibit blocking for all cursors

B Data currency is not required for ambiguous cursors

C Data currency is required for ambiguous cursors

Blank Package was created before CURRENTDATA option was available

C CONTINUE

N NOPACKAGE

C Package was created by BIND COPY

D Package was created by BIND COPY with the OPTIONS(COMMAND) option

K Package was copied from a package that was originally bound on behalf of a remote requester

L Package was copied with the OPTIONS(COMMAND) option from a package that was originally bound on behalf of a remote requester.

N Package was locally bound from a DBRM

Y Package was bound on behalf of a remote requester

ANY DEGREE(ANY)

1 or blank DEGREE(1)

Blank if the package was migrated

B BIND. Dynamic SQL statements are executed with DYNAMICRULES bind behaviour

D DEFINEBIND

E DEFINERUN

H INVOKEBIND

I INVOKERUN

R RUN

Blank DYNAMICRULES not specified for package

A REOPT(AUTO)

N REOPT(NONE)

Y REOPT(ALWAYS)

1 REOPT (ONCE)

N NODEFER(PREPARE)

Y DEFER(PREPARE)

I Local package is inheriting the value from the plan

blank Bind option not specified. Inherited from plan

N KEEPDYNAMIC(NO)

Y KEEPDYNAMIC(YES)

F CREATE FUNCTION or ALTER FUNCTION statement, or a BIND PACKAGE DEPLOY command created the package, and this package is a non-inline SQL scalar function package

N CREATE PROCEDURE or ALTER PROCEDURE statement, or BIND PACKAGE DEPLOY command created the package, and this package is a native SQL routine package

R Reserved for IBM use

T CREATE TRIGGER statement created the package, and the package is a trigger package. Blank BIND PACKAGE command created the package

D DRDA

C DRDA access with package-based continuous block fetch enabled

CCSID

CCSID Specified or derived CCSID

0 EBCDIC default CCSID as specified on panel DSNTIPF at installation time

I Local package is inheriting the value from the plan

N IMMEDWRITE(NO) – normal write activity is done

Y IMMEDWRITE(YES) – immediate writes are done for updated grup buffer pool dependent pages

1 IMMEDWRITE(PH1) – updated group bufferpool dependent pages are written at or before phase 1 commit

Blank if the package was migrated

blank Bound prior to V7

blank Authorization ID

L Role

C ROUND_CEILING

D ROUND_DOWN

F ROUND_FLOOR

G ROUND_HALF_DOWN

E ROUND_HALF_EVEN

H ROUND_HALF_UP

U ROUND_UP blank

blank The package created in a DB2 release prior to V9

A DB2 will collect remote location names SQL statements during local bind, and automatically create remote packages at those sites

L DB2 will automatically create remote packages at sites specified in the list of location-names

RES

blank Not specified

N WAITFOROUTCOME

Y USECURRENTLYCOMMITTED

INDICATOR

N EXTENDEDINDICATOR NO

Y EXTENDEDINDICATOR YES

B PLANMGMT BASIC

E PLANMGMT EXTENDED

Blank PLANMGMT OFF

S PLANMGMTSCOPE STATIC

N No or none: Access paths are not reused.

W WARN: DB2 tries to reuse access paths. Processing continues when an access path cannot be reused

E ERROR: DB2 tried to reuse access paths. Processing ends when an access path cannot be reused

Y YES specified. All copies were retained.

0 NO specified; however, the previous or original package copy is still retained due to access path differences

1 NO specified, and the previous package copy is not retained as access paths are identical to current copy

2 NO specified, and previous and original package copies are not retained as access paths are identical to current

SENSITIVE

Y References to system-period temporal tables are affected by CURRENT TEMPORAL SYSTEM_TIME

N References to system-period temporal tables are not affected by CURRENT TEMPORAL SYSTEM_TIME

SENSITIVE

Y References to application-period temporal tables are affected by value of CURRENT TEMPORAL BUSINESS_TIME

N References to application-period temporal tables are not affected by CURRENT TEMPORAL BUSINESS_TIME

V10R1 SQL statements in the package have V10R1 compatibility behaviour

V11R1 SQL statements in the package have V11R1 compatibility behaviour

Function-level – SQL statements in package have compatibility behaviour with the specified function level

SENSITIVE

Y (default) references to archive-enabled tables are affected SYSIBMADM.GET_ARCHIVE built-in global variable

N References to archive-enabled tables are not affected by SYSIBMADM.GET_ARCHIVE built-in global variable

Y DB2 database manager generates a DESCRIBE SQLDA at bind time so DESCRIBE requests for static SQL can we satisfied during execution

N DB2 database manager does not generate a DESCRIBE SQLDA at bind time for static SQL statements

A Automatic bind

B BIND command

G Explicit ALTER REGENERATE

I Implicit automatic regeneration

R REBIND command

blank existed before 12

N No

Y Yes

blank An authorization ID

L Role

P An application plan

blank Not applicable

A PACKADM (on collection*)

C DBCTL

D DBADM

E SECADM

G ACCESSCTRL

L SYSCTRL

M DBMAINT

P PACKADM (on a specific collection)

S SYSADM

T DATAACCESS

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege iss held without the GRANT option

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege iss held without the GRANT option

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege iss held without the GRANT option

blank Authorization ID

L Role

A Alias

B BUSINESS_TIME

C SYSTEM_TIME

E INSTEAD OF trigger

F User-defined function or cast function

G Global Temporary Table

I Index

M Materialized query table

O Stored procedure

P Partitioned tablespace if it is defined as LARGE or with DSSIZE

Q Sequence object

R Tablespace

S Synonym

T Table

V View

0 Alias

F Non-inline SQL scalar function

N Native SQL routine package

O Original copy of a package

P Previous copy of a package

R Reserved for IBM use

T Trigger package for basic trigger

blank Not a trigger package or a native SQL routine package

1 Trigger package for an advanced trigger

blank Authorization ID

L Role

N No

Y Yes

R RR (repeatable read)

T RS (read stability)

S CS (cursor stability)

U UR (uncommitted read)

L KEEP UPDATE LOCKS or an RS isolation

X KEEP UPDATE LOCKS for and RR isolation

blank WITH clause was not specified on statement. Isolation level is recorded in SYSPACKAGE.ISOLATION and

SYSPLAN.ISOLATION

A Distributed – statement uses DB2 private protocol access

B Distributed – statement uses DB2 private protocol access

C Compiled – statement was bound successfully using defaults for input variables during access path selection

E Explain – statement is an SQL EXPLAIN statement

F Parsed – statement is either a data definition statement or a statement that did not bind successfully and VALIDATE(RUN) was used

G Compiled – statement bound successfully, but REOPT is specified

H Parsed – statement is either a data definition statement or a statement that did not bind successfully and VALIDATE(RUN) was used

I Indefinite – statement is dynamic

J Indefinite – statement is dynamic

K Control – CALL statement

L Bad – statement has some allowable error

M Parsed – statement references a table qualified with SESSION and was not bound because table reference could be for a declared temporary table that will not be defined until package or plan is run

blank Statement is non-executable, or was bound prior to V5