(This article is part of our Db2 Guide. Use the right-hand menu to navigate.)
PLAN_TABLE
Contains information about access paths for queries that were explained or hints.
| Column Name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statement being explained |
| QBLOCKNO | SMALLINT | A number that identifies each query block within a query. Numbers are not in any particular order, nor are they consecutive |
| APPLNAME | VARCHAR(24) | Name of application plan for row |
| PROGNAME | VARCHAR(128) | Name of program or package containing statement being explained |
| PLANNO | SMALLINT | Number of steps in which query indicated in QBLOCKNO was processed. Indicates order in which the steps were executed. |
| METHOD | SMALLINT | Join method used for the step: 0 = First table accessed, continuation of previous table accessed, or not used 1 = Nested loop join. For each row of the present composite table, matching rows of a new table are found and joined 2 = Merge scan join. The present composite table and the new tables are scanned in the order of the join columns and matching rows are joined. 3 = Sorts neededby ORDER BY, GROUP BY, SELECT DISTINCT, UNION, a quantified predicate or an IN predicate. Does not access a new table |
| CREATOR | VARCHAR(128) | Creator of the new table accessed in this step; blank if METHOD is 3 |
| TNAME | VARCHAR(128) | Name of a table, materialized query table, created or declared temporary table, materialized view, or materialized table expression. Blank if METHOD is 3. Can also contain name of a table in the form DSNWFQB(qblockno). DSNWFQB(qblockno) is used to represent the immediate result of a UNION ALL, an INTERSECT ALL, an EXCEPT ALL, or an outer join that is materialized. If a view is merged, the name of the view does not appear. DSN_DIM_TBLX(qblockno) is used to represent the work file of a star join dimension table. DSB_SPIX_TBLX(qblockno) is used for a sparse index for a sideways table reference. |
| TABNO | SMALLINT | IBM use only. |
| ACCESSTYPE | CHAR(2) | Method of accessing the new table: DI = An intersection of multiple DOCID lists to return final DOCID list. DU = Union of multiple DOCID lists to return the final DOCID list DX = An XML index scan of the index named in ACCESSNAME to return a DOCID list E = Direct row using a row change timestamp column H = Hash access. IF an overflow condition occurs, hash overflow index identified by ACCESSCREATOR and ACCESSNAME is used. HN = Hash access using an IN predicate, or an IN predicate that DB2 generates. If a hash overflow condition occurs, hash overflow index identified in ACCESSCREATOR AND ACCESSNAME is used. IN = Index scan when matching predicate contains an IN predicate and the IN-list is accessed through an in-memory table I = An index (identified in ACCESSCREATOR and ACCESSNAME). I1 = One-fetch index scan M = Multiple index scan (followed by MX, MI, MH, or MU). MH = Hash overflow index named in ACCESSNAME MX = Index scan on index named in ACCESSNAME. When the access method MX follows the access method DX, DI, or DU, the table is accessed by the DOCID index using the DOCID list returned by DX, DI, or DU MI = Intersection of multiple indexes MU = Union of multiple indexes N = Index scan when the matching predicate contains the IN keyword or by an index scan when DB2 rewrites a query using the IN keyword. NR = Range list access. O = Work file scan, as a result of a subquery. P = Dynamic pair-wise index scan R = Table space scan. RW = work file scan of materialized user-defined table function V = Buffers for an INSERT statement within a SELECT. Blank = Not applicable to the current row. |
| MATCHCOLS | SMALLINT | For ACCESSTYPE I, I1, N, NR, MX, or DX, number of index keys used in an index scan; otherwise 0. |
| ACCESSCREATOR | VARCHAR(128) | For ACCESSTYPE I, I1, N, NR, MX or DX, creator of index |
| ACCESSNAME | VARCHAR(128) | For ACCESSTYPE I, I1, H, MH, N, NR, MX, or DX, name of index. |
| INDEXONLY | CHAR(1) | If access to an index alone is enough to carry out the step, or if the data, too, must be accessed. Y = Yes; N = No |
| SORTN_UNIQ | CHAR(1) | New table is sorted to remove duplicate rows. Y=Yes; N=No |
| SORTN_JOIN | CHAR(1) | New table is sorted for join method 2 or 4. Y = Yes; N = No |
| SORTN_ORDERBY | CHAR(1) | New table is sorted for ORDER BY. Y = Yes; N = No |
| SORTN_GROUPBY | CHAR(1) | New table is sorted for GROUP BY. Y = Yes; N = No |
| SORTC_UNIQ | CHAR(1) | Composite table is sorted to remove duplicate rows. Y = Yes; N = No. |
| SORTC_JOIN | CHAR(1) | Composite table is sorted for join method 1, 2, or 4. Y = Yes; N = No. |
| SORTC_ORDERBY | CHAR(1) | Composite table is sorted for an ORDER BY clause or a quantified predicate. Y = Yes; N = No |
| SORTC_GROUPBY | CHAR(1) | Composite table is sorted for a GROUP BY clause. Y = Yes; N = No. |
| TSLOCKMODE | CHAR(3) | An indication of the mode of lock to be acquired on the new table or its table space or table space partitions. If the isolation can be determined at bind time, the values are: IS = Intent share lock IX = Intent exclusive lock S = Share lock U = Update lock X = Exclusive lock SIX = Share with intent exclusive lock N = UR isolation; no lock If the isolation cannot be determined at bind time, the lock mode determined by the isolation at runtime is shown by the following values: NS = for UR isolation, no lock; for CS, RS, or RR, an S lock NIS = For UR isolation, no lock; for CS, RS, or RR, an IS lock NSS = For UR isolation, no lock; for CS or RS, an IS lock; for RR, an S lock SS = For UR, CS or RS isolation, an IS lock; for RR, an S lock RR, an S lock The data in this column is right-justified. For example, IX appears as a blank followed by ǀ followed by X. If the column contains a blank, no lock is acquired. If the access method in the ACCESSTYPE column is DX, DI, or DU, no latches are acquired on the XML index page, and no lock is acquired on the new base table data page or row, nor on the XML table and the corresponding table spaces. The value of TSLOCKMODE is blank in this case. |
| TIMESTAMP | CHAR(16) | Deprecated, use EXPLAIN_TIME instead |
| REMARKS | (VARCHAR762) | Can insert any character string of 762 or fewer characters |
| PREFETCH | CHAR(1) | Whether data pages are to be read in advance by prefetch: D = optimizer expects dynamic prefetch S = Pure sequential prefetch L = Prefetch through a page list U = List prefetch with an unsorted RID list Blank = unknown at bind time or no prefetch |
| COLUMN_FN_EVAL | CHAR(1) | When a SQL aggregate function is evaluated: R = while data is being read from the table or index S = While performing a sort to satisfy a GROUP BY clause X = While data is read from a table or index, for aggregate functions when an OFFSET clause is specified Y = While performing a sort, for aggregate functions when an OFFSET clause if specified. Blank = After data retrieval after any sorts |
| MIXOPSEQ | SMALLINT | The sequence number of a step in a multiple index operation: 1, 2,…n = For the steps of the multiple index procedure (ACCESSTYPE is MX, MI, MU, DX, DI, or DU), the sequence number of the OR predicate in the SQL statement (ACCESSTYPE is NR) 0 = for any other rows |
| VERSION | VARCHAR(122) | Version idenfitier for the package |
| COLLID | VARCHAR(128) | Collection ID: DSNDYNAMICSQLCACHE: row originates from dynamic statement cache DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger |
| ACCESS_DEGREE | SMALLINT | Number of parallel tasks or operations activated by a query. Determined at bind time; actual number at execution time could differ |
| ACCESS_PGROUP_ID | SMALLINT | Identifier of parallel group for accessing new table |
| JOIN_DEGREE | SMALLINT | Numer of parallel operations or tasks used in joining composite table with new table |
| JOIN_PGROUP_ID | SMALLINT | Identifier of parallel group for joining composite table with new table |
| SORTC_PGROUP_ID | SMALLINT | Parallel group identifier for parallel sort of composite table |
| SORTN_PGROUP_ID | SMALLINT | Parallel group idenitifer for the parallel sort of the new table |
| PARALLELISM_MODE | CHAR(1) | Mode of parallelism. C = CP parallelism |
| MERGE_JOIN_COLS | SMALLINT | Number of columns joined during a merge scan join |
| CORRELATION_NAME | VARCHAR(128) | Correlation name of a table or view that is specified in the statement |
| PAGE_RANGE | CHAR(1) | If page range screening is used. Y = Yes; blank = No |
| JOIN_TYPE | CHAR(1) | Type of an outer join: F = Full outer join L = Left outer join P = Pair wise join S = Star join Blank = Inner join or no join RIGHT OUTER JOIN converts to a LEFT OUTER JOIN so that JOIN_TYPE contains L |
| GROUP_MEMBER | VARCHAR(24) | Member name of the DB2 that executed EXPLAIN |
| IBM_SERVICE_DATA | VARCHAR(254) | IBM use only |
| WHEN_OPTIMIZE | CHAR(1) | When access path was determined: Blank = At bind time, using a default filter factor for any host variables, parameter markers, or special registers; however, the statement is reoptimized at runtime using input variable values for input host variables, parameter markers, or special registers. The bind option REOPT(ALWAYS), REOPT(ONCE), or REOPT(AUTO), must be specified for reoptimization to occur R = At runtime, using input variables for any host variables, parameter markers, or special registers. The bind option REOPT(ALWAYS), REOPT(ONCE), or REOPT(AUTO) must be specified for this to occur. |
| QBLOCK_TYPE | CHAR(6) | For each query block, the type of SQL operation performed. For outermost query, identifies statement type. SELECT = SELECT INSERT = INSERT UPDATE = UPDATE MERGE = MERGE DELETE = DELETE SELUPD = SELECT with FOR UPDATE OF DELCUR = DELETE WHERE CURRENT OF CURSOR UPDCUR = UPDATE WHERE CURRENT OF CURSOR CORSUB = Correlated subquery TRUNCA = TRUNCATE NCOSUB = Noncorrelated subquery TABLEX = Table expression TRIGGR = WHEN clause on CREATE TRIGGER UNION = UNION UNIONA = UNION ALL INTERS = INTERSECT INTERA = INTERSECT ALL EXCEPT = EXCEPT EXCEPTA = EXCEPT ALL |
| OPTHINT | VARCHAR(128) | A string used to identify row as an optimization hint for DB2 |
| HINT_USED | VARCHAR(128) | APREUSE – When an access path was successfully reused because the APREUSE option was specified at bind or rebind ‘opthint-value’ – When PLAN_TABLE access path hints are used opthint-value is the value of OPTHINT column for the hint that was used SYSQUERYPLAN query-id When statement level access path hints are used. query-id is the value of QUERYID column in the SYSQUERYPLAN catalog table for the hint SYSQUERYSEL query-id When a predicate selectivity override is used. query-id is the value of the QUERYID column of the SYSQUERYSEL catalog table row for the hint. EXPLAIN PACKAGE: COPY copy-id – When the row is the result of an EXPLAIN PACKAGE statement. copy-id is one of the following values: 0 Current copy 1 Previous copy 2 Original copy |
| PRIMARY_ACCESSTYPE | CHAR(1) | Indicates whether direct row access will be attempted first: D = DB2 will try to use direct row access. If it cannot use direct row access at runtime, it uses the access path described in the ACCESSTYPE column of PLAN_TABLE P = DB2 used data partitioned secondary index and a part-level operation to access the data S = DB2 used sparse dinex access for sideways table reference T = The base table or result file is materialized into a work file, and the work file is accessed via sparse index access. If a base table is involved, ACCESSTYPE indicates how the base table is accessed Blank = DB2 will not try to use direct row access. The value of the ACCESSTYPE column provides information on the method of accessing the table |
| PARENT_QBLOCK | SMALLINT | Number that indicates the QBLOCKNO of the parent query |
| TABLE_TYPE | CHAR(1) | The type of new table: B = Buffers for SELECT from INSERT, SELECT from UPDATE, SELECT from MERGE, or SELECT from DELETE statement. C = Common table expression F = Table function I = The new table is generated from an IN-LIST predicate. If the IN-LIST predicate is selected as the matching predicate, it will be accessed as an in-memory table M = Materialized query table Q = Temporary intermediate result table (not materialized). For the name of the view or nested table expression, a value of Q indicates that the materialization was virtual and not actual. Materialization can be virtual when the view or nested table expression definition contains a UNION ALL that is not distributed. R = Recursive common table expression S = Subquery (correlated or non-correlated) T = Table W = Work file The value of the column is null if the query uses GROUP BY, ORDER BY, or DISTINCT, which requires an implicit sort. |
| TABLE_ENCODE | CHAR(1) | Encoding scheme of the table. If the table has a single CCSID set, possible values are: A = ASCII E = EBCDIC U = UNICODE M = The table contains multiple CCSID sets. |
| TABLE_SCCSID | SMALLINT | SBCS CCSID value of the table. If TABLE_ENCODE is M, value is 0. |
| TABLE_MCCSID | SMALLINT | Mixed CCSID value of the table. If TABLE_ENCODE is M, the value is 0. IF MIXED=NO in the application defaults module, the value is -2 |
| ROUTINE_ID | INTEGER | IBM use only |
| CTREF | SMALLINT | If referenced table is a common table expression, the value is the top-level query block number |
| STMTTOKEN | VARCHAR(240) | A user-specified statement token |
| PARENT_PLANNO | SMALLINT | Corresponds to the plan number in the parent query block where a correlated subquery is involved. Or, for non-correlated subqueries, corresponds to the plan number in the parent query block that represents the work file for the subquery. |
| BIND_EXPLAIN_ONLY | CHAR(1) | Identifies whether the row was inserted by the BIND command with the EXPLAIN(ONLY)option |
| SECTNOI | INTEGER | Section number of the statement |
| EXPLAIN_TIME | TIMESTAMP | Time when EXPLAIN information was captured: All cached statements: when statement entered the cache Non-cached static statements: When statement was bound Non-cached dynamic statements: When EXPLAIN was executed |
| MERGC | CHAR(1) | Indicates whether composite table is consolidated before join. Y = Yes N = No |
| MERGN | CHAR(1) | Indicates whether a new table is consolidated before the join, or whether access that used a data partitioned secondary index (DPSI) involved a merge operation. Y = Yes N = No D = Access through a DPSI involved a merge operation U = Access through a DPSI that did not involve a merge operation |
| SCAN_DIRECTION | CHAR(1) | For index access, direction of index scan: F Forward R Reverse Blank index scan is not used |
| EXPANSION_REASON | CHAR(1) | Applies to only statements that reference archive tables or temporal tables. Else, coloumn is blank. |
| PER_STMT_ID | BIGINT | Persistent statement identifier for SQL statements in DB2 catalog tables |
DSN_COLDIST_TABLE
Column distribution table contains non-uniform column group statistics that are obtained dynamically by the DB2 optimizer
| Column Name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statementbeing explained |
| APPLNAME | VARCHAR(128) | Name of application plan for row |
| PROGNAME | VARCHAR(128) | Name of program or package containing statement being explained |
| COLLID | VARCHAR(128) | Collection ID: DSNDYNAMICSQLCACHE: row originates from dynamic statement cache DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register When the SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, this column is the schema of the compiled SQL function, native SQL procedure or advanced trigger. |
| GROUP_MEMBER | VARCHAR(128) | Member name of the DB2 subsystem that executed EXPLAIN |
| SECTNOI | INTEGER | Section number of the statement |
| VERSION | VARCHAR(122) | Version identifier for the package |
| EXPLAIN_TIME | TIMESTAMP | EXPLAIN timestamp |
| SCHEMA | VARCHAR(128) | Schema of table that contains column |
| TBNAME | VARCHAR(128) | Name of the table that contains the column |
| NAME | VARCHAR(128) | Name of column |
| COLVALUE | VARCHAR(2000) | Contains data of a frequently occurring value in the column |
| TYPE | CHAR(1) | The type of statistics: C = Cardinality F = Frequent value H = Histogram T = Real-time table cardinality L = Real-time column cardinality(unique index only) P = Real-time partition cardinality |
| CARDF | FLOAT | For TYPE=’C’, the number of distinct values for the column group. For TYPE=’H’, the number of distinct values for the column group in a quantileindicated by the value of the QUANTILENO column. TYPE=’T’ value related to real-time statistics determined by COLVALUE. TYPE =’L’ value related to real-time statistics column. QUANTILENO contains column number. NAME contains partition number. |
| COLGROUPCOLNO | VARCHAR(254) | Identity of set of columns associated with the statistics |
| NUMCOLUMNS | SMALLINT | Identifies the number of columns associated with the statistics |
| FREQUENCYF | FLOAT | Percentage of rows in table with value that is specified in COLVALUE column when the number is multiplied by 100 |
| QUANTILENO | SMALLINT | Ordinary sequence number of a quantile in the whole consecutive value range, from low to high |
| LOWVALUE | VARCHAR(2000) | For TYPE=’H’, lower bound for quantile indicated by the value of the QUANTILENO column |
| HIGHVALUE | VARCHAR(2000) | For TYPE=’H’, higher bound for the quantile indicated by the value of the QUANTILENO column |
| EXPANSION_REASON | CHAR(2) | Applies to only statements that reference archive or temporal tables |
| PER_STMT_ID | BIGINT | Persistent statement identifier for SQL statements in the catalog tables |
DSN_DETCOST_TABLE
Contains information about detailed cost estimation of the mini-plans in query
| Column Name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statement being explained |
| QBLOCKNO | SMALLIN | A number used to identify each query block within a query |
| PLANNO | SMALLINT | A number used to identify each mini-plan within a queryblock |
| APPLNAME | VARCHAR(24) | Name of application plan for row |
| PROGNAME | VARCHAR(128) | Name of program or package containing statementbeing explained |
| OPENIO | FLOAT(4) | Do-at-open I/O cost for the non-correlated subquery |
| OPENCPU | FLOAT(4) | Do-at-open CPU cost for the non-correlated subquery |
| OPENCOST | FLOAT(4) | Do-at-open total cost for the non-correlated subquery |
| ONECOMPROWS | FLOAT(4) | Number of rows qualified after applying local predicates |
| IMFF | FLOAT(4) | Filter factor of matching predicates only |
| IMFFADJ | FLOAT(4) | Filter factor of matching and screening predicates |
| DMCOLS | FLOAT(4) | Number of data manager columns |
| DMROWS | FLOAT(4) | Number of data manager rows returned (after all stage 1 predicates are applied) |
| RDSROW | FLOAT(4) | Number of RDS rows returned (after all stage 1 predicates are applied) |
| SNCOLS | SMALLINT | Number of columns as sort input for a new table |
| SNROWS | FLOAT(4) | Number of rows as sort input for a new table |
| SNRECSZ | INTEGER | Record size for new table |
| SNPAGES | FLOAT(4) | Page size for new table |
| SNRUNS | FLOAT(4) | Number of runs generated for a sort of a new table |
| SNMERGES | FLOAT(4) | Number of merges needed during a sort |
| SNCCOLS | FLOAT(4) | Number of columns as sort input for a composite table |
| SCROWS | FLOAT(4) | Number of rows as sort input for a composite table |
| SCRECSZ | INTEGER | Record size for a composite table |
| SCPAGES | FLOAT(4) | Page size for a composite table |
| SCRUNS | FLOAT(4) | Number of runs generated during the sort of a composite table |
| SCMERGES | FLOAT(4) | Number of merges needed during a sort of a composite table |
| COMPCARD | FLOAT(4) | Total composite cardinality |
| COMPCOST | FLOAT(4) | Total cost |
| EXPLAIN_TIME | TIMESTAMP | EXPLAIN timestamp |
| GROUP_MEMBER | VARCHAR(24) | Member name of the DB2 subsystem that executed EXPLAIN |
| UNCERTAINTY | FLOAT(4) | Describes the uncertainty factor on inner table index access |
| UNCERTAINTY_1T | FLOAT(4) | Describes the uncertainty factor of ONECOMPROWS column of the table |
| SECTNOI | INTEGER | Section number of the statement |
| COLLID | VARCHAR(128) | Collection ID |
| VERSION | VARCHAR(128) | Version identifier for the package |
| IXSCAN_SKIP_DUPS | CHAR(1) | Whether duplicate index key values are skipped during index scan ‘Y’ Duplicate key values are skipped ‘N’ Duplicate key values are not skipped |
| IXSCAN_SKIP_SCREEN | CHAR(1) | Whether key ranges that are disqualified by index screening predicates are skipped during an index scan ‘Y’ Disqualified key ranges are skipped ‘N’ Key ranges are not skipped |
| EARLY_OUT | CHAR(1) | Whether fetching from the table stops after the first qualified row ‘Y’ Internal fetching stops after the first qualified row ‘N’ Internal fetching continues after the first qualified row Blank EXPLAIN information was captured in a previous release |
| EXPANSION_REASON | CHAR(2) | Applies to only statements that reference archive tables or temporal tables. Else, column is blank |
| BLOCK_FETCH | CHAR(1) | Whether block fetch was used for query: (Y or N) |
| PER_STMT_ID | BIGINT | Persistent statement identifier for SQL statements in DB2 catalog |
DSN_FILTER_TABLE
Contains information about how predicates are used during query processing.
| Column name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statement being explained. |
| QBLOCKNO | SMALLINT | A number used to identify each query block within a query |
| PLANNO | SMALLINT | A number used to identify each mini-plan within a query block |
| APPLNAME | VARCHAR(24) | Name of application plan for row |
| PROGNAME | VARCHAR(128) | Name of program or package containing statement being explained |
| COLLID | VARCHAR(128) | Collection ID: DSNDYNAMICSQLCACHE: row originates from dynamic statement cache DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register. When the SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, this column is the schema of the compiled SQL function, native SQL procedure or advanced trigger. |
| ORDERNO | INTEGER | Sequence number of evaluation. Order a predicate is applied within each stage |
| PREDNO | INTEGER | A number used to identify a predicate within a query |
| STAGE | CHAR(9) | Indicates at which stage the predicate is evaluated · Matching · Screening · Pagerange · Stage 1 · Stage 2 |
| EXPLAIN_TIME | TIMESTAMP | EXPLAIN timestamp |
| GROUP_MEMBER | VARCHAR(24) | Member name of the DB2 subsystem that executed EXPLAIN |
| SECTNOI | INTEGER | Section number of the statement |
| VERSION | VARCHAR(122) | Version identifier for the package |
| PUSHDOWN | CHAR(1) | Whether predicate is pushed down into the Index Manager or Data Manager subcomponents for evaluation: I = Index Manager subcomponent evaluates the predicate D = Data Manager subcomponent evaluates the predicate Blank = predicate is not pushed down for evaluation |
| EXPANSION_REASON | CHAR(2) | Column applies to only statements that reference archive tables or temporal tables. For other statements, this column is blank. |
| PER_STMT_ID | BIGINT | Persistent statement identifier for SQL statements in the DB2 catalog |
DSN_FUNCTION_TABLE
Contains information about the cost of user-defined functions used in a SQL statement
| Column name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statement being explained |
| QBLOCKNO | INTEGER | Number of query block within a query |
| APPLNAME | VARCHAR(24) | Name of application plan for row |
| PROGNAME | VARCHAR(128) | Name of program or package containing statement being explained |
| COLLID | VARCHAR(128) | Collection ID: DSNDYNAMICSQLCACHE: row originates from dynamic statement cache DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register When the SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, this column is the schema of the compiled SQL function, native SQL procedure or advanced trigger. |
| GROUP_MEMBER | VARCHAR(24) | Member name of DB2 that executed EXPLAIN, or blank |
| EXPLAIN_TIME | TIMESTAMP | Time at which the statement is processed |
| SCHEMA_NAME | VARCHAR(128) | Schema name of the function invoked in the explained statement |
| FUNCTION_NAME | VARCHAR(128) | Name of the function invoked in the explained statement |
| SPEC_FUNC_ID | VARCHAR(128) | Specific name of the function invoked in the explained statement |
| FUNCTION_TYPE | CHAR(2) | Type of function invoked in the explained statement: CU = Column function SU = Scalar function TU = Table function |
| VIEW_CREATOR | VARCHAR(128) | Uf the function specified in the FUNCTION_NAME column is referenced in a view definition, the creator of the view. Otherwise, blank. |
| VIEW_NAME | VARCHAR(128) | If the function specified in the FUNCTION_NAME column is referenced in a view definition, the name of the view. Otherwise, blank. |
| PATH | VARCHAR(2048) | Value of the SQL path used to resolve schema name of the function |
| FUNCTION_TEXT | VARCHAR(1500) | The text of the function reference (the function name and parameters) |
| FUNC_VERSION | VARCHAR(122) | For version of non-inline SQL scalar function, contains version identifier. |
| SECURE | CHAR(1) | Indicates whether user-defined function is secure. |
| SECTNOI | INTEGER | Section number of statement |
| VERSION | VARCHAR(122) | Version identifier for package |
| EXPANSION_REASON | CHAR(2) | Column applies to only statements that reference archive tables or temporal tables. Else, blank. |
| PER_STMT_ID | BIGINT | Persistent statement identifier for SQL statements in DB2 catalog |
DSN_KEYTGTDIST_TABLE
Contains non-uniform index expression statistics obtained dynamically by the optimizer
| Column name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify statement being explained |
| APPLNAME | VARCHAR(128) | Name of application plan for row |
| PROGNAME | VARCHAR(128) | Name of program or package containing statement being explained |
| COLLID | VARCHAR(128) | Collection ID: DSNDYNAMICSQLCACHE: row originates from dynamic statement cache DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register When the SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, this column is the schema of the compiled SQL function, native SQL procedure or advanced trigger |
| GROUP_MEMBER | VARCHAR(128) | Member name of DB2 subsystem that executed EXPLAIN |
| SECTNOI | INTEGER | Section number of statement |
| VERSION | VARCHAR(122) | Version identifier for package |
| EXPLAIN_TIME | TIMESTAMP | EXPLAIN timestamp |
| IXSCHEMA | VARCHAR(128) | Qualifier of the index |
| IXNAME | VARCHAR(128) | Name of the index |
| KEYSEQ | VARCHAR(128) | Numeric position of the key-target in the index |
| KEYVALUE | VARCHAR(2000) | Contains the data of a frequently occurring value |
| TYPE | CHAR(1) | Type of statistics: C = Cardinality F = Frequent value H – Histogram L = Real-time index statistics |
| CARDF | FLOAT | For TYPE=’C’, the number of distinct values for the column group. For TYPE=’H’, the number of distinct values for the column group in a quantile indicated by the value of the QUANTILENO column. For TYPE=’T’, value related to real-time index statistics determinedby KEYVALUE |
| KEYGROUPKEYNO | VARCHAR(254) | Contains a value that identifies the set of keys that are associated with the statistics. If the statistics are associated with more than a single key, if contains an array of SMALLINT key numbers with a dimension that is equal to the value in NUMKEYS. If the statistics are only associated with a single key, it contains 0. |
| NUMKEYS | SMALLINT | Number of keys that are associated with the statistics |
| FREQUENCYF | FLOAT | Percentage of rows in table with value that is specified in KEYVALUE column when the number is multiplied by 100 |
| QUANTILENO | SMALLINT | Ordinary sequence number of a quantile in the whole consecutive value range, from low to high. Not updateable |
| LOWVALUE | VARCHAR(2000) | For TYPE=’H’, this is the lower bound for the quantile indicated by the value of the QUANTILENO column. Not used if the value of the TYPE column is not ‘H’. Not updateable |
| HIGHVALUE | VARCHAR(2000) | For TYPE=’H’, this is the higher bound for the quantile indicated by the value of the QUANTILENO column. This column is not used if the value of the TYPE column is not ‘H’. This column is not updateable. |
| EXPANSION_REASON | CHAR(2) | This column applies only to statements that reference archive tables or temporal tables. Else, column is blank. |
| PER_STMT_ID | BIGINT | Persistent statement identifier for SQL statements in DB2 catalog. |
DSN_PGRANGE_TABLE
Contains information about qualified partitions for all page range scans in a query
| Column name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statement being explained. |
| QBLOCKNO | SMALLINT | A number used to identify each query block within a query |
| TABNO | SMALLINT | Table number |
| RANGE | SMALLINT | Sequence number of the current page range |
| FIRSTPART | SMALLINT | Starting partition in the current page range |
| LASTPART | SMALLINT | Ending partition in the current page range |
| NUMPARTS | SMALLINT | Number of partitions in the current page range |
| EXPLAIN_TIME | TIMESTAMP | EXPLAIN timestamp |
| GROUP_MEMBER | VARCHAR(24) | Member name of DB2 subsystem that executed EXPLAIN |
| SECTNOI | INTEGER | Section number of the statement |
| APPLNAME | VARCHAR(24) | Name of application plan for row |
| PROGNAME | VARCHAR(24) | Name of program or package containing statement being explained |
| COLLID | VARCHAR(128) | Collection ID: DSNDYNAMICSQLCACHE: row originates from dynamic statement cache DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register When the SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, this column is the schema of the compiled SQL function, native SQL procedure or advanced trigger |
| VERSION | VARCHAR(122) | Version identifier for the package |
| EXPANSION_REASON | CHAR(2) | Column applies to only statements that reference archive tables or temporal tables. Else, column is blank |
| PER_STMT_ID | BIGINT | Persistent statement identifier for SQL statements in DB2 catalog |
DSN_PGROUP_TABLE
Contains information about the parallel groups in a query
| Column name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statement being explained |
| QBLOCKNO | SMALLINT | A number used to identify each query block within a query |
| PLANNAME | VARCHAR(24) | Application plan name |
| COLLID | VARCHAR(24) | Collection ID: DSNDYNAMICSQLCACHE: row originates from dynamic statement cache DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is a schema of compiled SQL function, native SQL procedure or advanced trigger |
| PROGNAME | VARCHAR(128) | Program name (binding an application) or package name (binding a package) |
| EXPLAIN_TIME | TIMESTAMP | Explain timestamp |
| VERSION | VARCHAR(122) | Version identifier for the package |
| GROUPID | SMALLINT | Parallel group identifier within the current query block |
| FIRSTPLAN | SMALLINT | Plan number of first contributing mini-plan associated within this parallel group |
| LASTPLAN | SMALLINT | Plan number of the last mini-plan associated with this parallel group |
| CPUCOST | REAL | Estimated CPU cost of this parallel group in milliseconds |
| IOCOST | REAL | Estimated total I/O cost of this parallel group in milliseconds |
| BESTTIME | REAL | Estimated elapsed time for each parallel task for this parallel group |
| DEGREE | SMALLINT | Degree of parallelism for this parallel group determined at bind time |
| MODE | CHAR(1) | The parallel mode: I = I/O parallelism C = CPU parallelism N = No parallelism |
| REASON | SMALLINT | Reason for downgrading parallelism mode |
| LOCALCPU | SMALLINT | Number of CPUs currently online when preparing the query |
| TOTALCPU | SMALLINT | Total number of CPUs in Sysplex |
| FIRSTBASE | SMALLINT | Table number of the table that on which partitioning is performed |
| LARGETS | CHAR(1) | Value is Y if the table space is large in this group |
| PARTKIND | CHAR(1) | Partitioning type: L = Logical partitioning P – Physical partitioning |
| GROUPTYPE | CHAR(1) | Indicates operations in parallel group: table access, join, or sort (A, AJ or AJS) |
| ORDER | CHAR(1) | Ordering requirement of this parallel group: N = No order T = Natural order K = Key order |
| STYLE | CHAR(4) | Input/output format style of parallel group RIRO = Records IN, Records OUT WIRO = Work file IN, Records OUT WIWO = Work file IN, Work file OUT |
| RANGEKIND | CHAR(1) | Range type: K = Key range L = IN=list elements partitioning P = Page range R = Record range partitioning |
| NKEYCOLS | SMALLINT | Number of interesting key columns – number of columns that will participate in the key operation for this parallel group |
| LOWBOUND | VARCHAR(40) | Low bound of the parallel group |
| HIGHBOUND | VARCHAR(40) | High bound of the parallel group |
| LOWKEY | VARCHAR(40) | Low key of range if partitioned by key range |
| HIGHKEY | VARCHAR(40) | High key of range if partitioned by key range |
| FIRSTPAGE | CHAR(4) | First page in range if partitioned by page range |
| LASTPAGE | CHAR(4) | Last page in range if partitioned by page range |
| GROUP_MEMBER | VARCHAR(24) | Member name of DB2 where EXPLAIN was executed |
| APPLNAME | VARCHAR(24) | Application plan name |
| SECTNOI | INTEGER | Selection number of the statement |
| EXPANSION_REASON | CHAR(2) | Column applies to only statements that reference archive tables or temporal tables. Else, column is blank |
| PER_STMT_ID | BIGINT | Persistent statement identifier for SQL statements in DB2 catalog |
DSN_PREDICAT_TABLE
Contains information about all the predicates in a query
| Column name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statement being explained |
| QBLOCKNO | SMALLINT | A number used to identify each query block within a query |
| APPLNAME | VARCHAR(24) | Name of application plan for row |
| PROGNAME | VARCHAR(24) | Name of program or package containing statement being explained |
| PREDNO | INTEGER | A number used to identify a predicate within a query |
| TYPE | CHAR(8) | A string used to indicate type or operation of predicate: AND, OR, EQUAL, RANGE, BETWEEN, IN, LIKE, NOT LIKE, EXISTS, COMPOUND, NOT EXIST, SUBQUERY, HAVING, OTHERS |
| LEFT_HAND_SIDE | VARCHAR(128) | If left-hand side (LHS) of predicate is a table column (LHS_TABNO>0, indicates column name: VALUE, COLEXP, NONCOLEXP, CORSUB, NONCORSUB, SUBQUERY, EXPRESSION, blank |
| LEFT_HAND_PNO | INTEGER | If LHS of the predicate is a table column (LHS_TABNO>0), indicates column name: VALUE, COLEXP, NONCOLEXP, CORSUB, NOCORSUB, SUBQUERY, EXPRESSION, blank |
| LHS_TABNO | SMALLINT | If LHS of predicate is a table column, indicates a number that uniquely identifies corresponding table reference within a query |
| LHS_QBNO | SMALLINT | If LHS of predicate is a table column, indicates a number that uniquely identifies corresponding table reference within a query |
| RIGHT_HAND_SIDE | VARCHAR(128) | If right-hand side (RHS) of predicate is a table column (RHS_TABNO>0), indicates column name: VALUE, COLEXP, NONCOLEXP, CORSUB, NONCORSUB, SUBQUERY, EXPRESSION, blank |
| RIGHT_HAND_PNO | INTEGER | If predicate is a compound (AND/OR), indicates second child predicate |
| RHS_TABNO | CHAR(1) | If RHS of predicate is a table column, indicates a number that uniquely identifies corresponding table reference within a query |
| RHS_QBNO | CHAR(1) | If RHS of predicate is a subquery, indicates a number that uniquely identifies corresponding query block within a query |
| FILTER_FACTOR | FLOAT | Estimated filter factor |
| BOOLEAN_TERM | CHAR(1) | If predicate can be used to determine truth value of whole WHERE clause |
| SEARCHARG | CHAR(1) | Whether predicate can be processed by data manager (DM) stage 1 |
| AFTER_JOIN | CHAR(1) | Indicates predicate evaluation phase: A = After join D = During join Blank = Not applicable |
| ADDED_PRED | CHAR(1) | Whether predicate is generated by DB2, and reason predicate is added: Blank DB2 did not add the predicate ‘B’ For bubble up ‘C’ For correlation ‘J’ For join ‘K’ For LIKE for expression-based index ‘L’ For localization ‘P’ For push down ‘R’ For page range ‘S’ For simplification ‘T’ For transitive closure |
| REDUNDANT_PRED | CHAR(1) | Whether predicate is a redundant predicate |
| DIRECT_ACCESS | CHAR(1) | If predicate is direct access, navigated directly to the row through ROWID |
| KEYFIELD | CHAR(1) | Whether predicate includes the index key column of the involved table |
| EXPLAIN_TIME | TIMESTAMP | EXPLAIN timestamp |
| TEXT | VARCHAR(2000) | Text of transformed predicate |
| MARKER | CHAR(1) | Predicate includes host variables, parameter markers, or special registers |
| PARENT_PNO | INTEGER | Parent predicate number |
| NEGATION | CHAR(1) | Whether the predicate is negated via NOT |
| LITERALS | VARCHAR(128) | Literal value or literal values separate by colon symbols |
| CLAUSE | CHAR(8) | Clause where the predicate exists: HAVING = HAVING clause ON = ON clause WHERE = WHERE clause SELECT = The SELECT clause |
| GROUP_MEMBER | VARCHAR(24) | Member name of the DB2 that executed EXPLAIN |
| ORIGIN | CHAR(1) | Indicates the origin of the predicate. Blank = generated by DB2 C = column mask R = row permission U = specified by the user |
| UNCERTAINTY | FLOAT(4) | Describes the uncertainty factor of a predicate’s estimated filter factor. A bigger value indicates a higher degree of uncertainty. Zero indicates no uncertainty or uncertainty not considered |
| SECTNOI | INTEGER | Section number of the statement |
| COLLID | VARCHAR(128) | Collection ID: DSNDYNAMICSQLCACHE: row originates from dynamic statement cache DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger |
| VERSION | VARCHAR(122) | Version identifier for the package |
| EXPANSION_REASON | CHAR(2) | Column applies to only statements that reference archive tables or temporal tables. Else, column is blank. |
| PER_STMT_ID | BIGINT | Persistent statement identifier for SQL statements in DB2 catalog |
DSN_PREDICATE_SELECTIVITY
Contains information about the selectivity of predicates that are used for access path selection. It is used as an input table for the BIND QUERY command when selectivity overrides are specified.
| Column name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statement being explained |
| QBLOCKNO | SMALLINT | A number that identifies each query block within a query |
| APPLNAME | VARCHAR(24) | Name of application plan for row |
| PROGNAME | VARCHAR(128) | Name of program or package containing statement being explained |
| SECTNOI | INTEGER | Section number of the statement |
| COLLID | VARCHAR(128) | Collection ID: DSNDYNAMICSQLCACHE: row originates from dynamic statement cache DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger |
| VERSION | VARCHAR(122) | Version identifier for the package |
| PREDNO | INTEGER | Predicate number. Identifies a specific predicate within a query |
| INSTANCE | SMALLINT | Selectivity instance. Used to group related selectivities |
| SELECTIVITY | FLOAT | Selectivity estimate |
| WEIGHT | FLOAT(4) | Percentage of executions that have the specified selectivity |
| ASSUMPTION | VARCHAR(128) | NULL indicates how selectivity was estimated, or is used ‘NORMAL’ – Selectivity is estimated using normal selectivity assumptions ‘OVERRIDE’ – Selectivity is based on an override |
| INSERT_TIME | TIMESTAMP | Time when row was inserted or updated |
| EXPLAIN_TIME | TIMESTAMP | Time when EXPLAIN information was captured: All cached statements – When statement entered cache Non-cached static statements– When statement was bound, Non-cached dynamic statements – When EXPLAIN was executed |
| EXPANSION_REASON | CHAR(2) | Applies to only statements that reference archive tables or temporal tables. For other statements, this column is blank. |
| PER_STMT_ID | BIGINT | Persistent statement identifier for SQL statements in DB2 catalog |
DSN_QUERYINFO_TABLE
Contains information about the eligibility of query blocks for automatic query rewrite…
| Column name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statement being explained |
| QBLOCKNO | SMALLINT | A number that identifies each query block within a query |
| QINAME1 | VARCHAR(128) | When TYPE=’A’: · When REASON_CODE=0, this value is the name of the accelerator server to which the query is sent · When REASON_CODE<>0, the query was not sent to a accelerator server. The REASON_CODE value indicates why the query was not sent to the accelerator server |
| QINAME2 | VARCHAR(128) | When TYPE=’A’ and REASON_CODE=0, value is name of location |
| APPLNAME | VARCHAR(24) | Name of application plan for row |
| PROGNAME | VARCHAR(128) | Name of program or package containing statement being explained |
| VERSION | VARCHAR(122) | Version identifier for the package |
| GROUP_MEMBER | VARCHAR(24) | Member name of the DB2 that executed EXPLAIN |
| COLLID | VARCHAR(128) | Collection ID: DSNDYNAMICSQLCACHE – row originates from dynamic statement cache DSNEXPLAINMODEYES – row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN – row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger |
| SECTNOI | INTEGER | Section number of statement |
| SEQNO | INTEGER | Sequence number for row if QI_DATA exceeds size of its column |
| EXPLAIN_TIME | TIMESTAMP | Time when EXPLAIN information was captured: All cached statements – When statement entered the cahce Non-cached static statements – When statement was bound Non-cached dynamic statements – When EXPLAIN was executed |
| TYPE | CHAR(8) | Type of output for this row: A – row is for a query that DB2 attemptsto run on an accelerator server. The value in column REASON_CODE indicates the outcome |
| QI_DATA | CLOB(2M) | When TYPE=’A’: · For REASON_CODE values other than 0, value is description of the REASON_CODE value · For a REASON_CODE value of 0, value is query text, after it is converted for processing by accelerator |
| EXPANSION_REASON | CHAR(2) | Column applies to only statements that reference archive tables or temporal tables. For other statements, this column is blank. |
| PER_STMT_ID | BIGINT | Persistent statement identifier SQL statements in DB2 catalog |
DSN_PTASK_TABLE
Contains information about the parallel task in a query
| Column name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statement being explained |
| QBLOCKNO | SMALLINT | A number used to identify each query block within a query |
| PGDNO | SMALLINT | Parallel group identifier within current query block |
| APPLNAME | VARCHAR(24) | Name of application plan for row |
| PROGNAME | VARCHAR(128) | Name of program or package containing statement being explained |
| LPTNO | SMALLINT | Parallel task number |
| KEYCOLID | SMALLINT | Key indicates ID(KEY range only) |
| DPSI | CHAR(1) | Indicates whether a data partition secondary index (DPSI) is used |
| LPTLOKEY | VARCHAR(40) | Low key value for this key column for this parallel task (KEY range only) |
| LPTHIKEY | VARCHAR(40) | High key value for this key column for this parallel task (KEY range only) |
| LPTLOPAG | CHAR(4) | Low page information if partitioned by page range |
| LPTHIKEY | CHAR(4) | High page information if partitioned by page range |
| LPTLOPG | CHAR(4) | Lower bound page number for parallel task (page range or DPSI enabled) |
| LPTHIPG | CHAR(4) | Upper bound page number for parallel task (page range or DPSI enabled) |
| LPTLOPT | SMALLINT | Lower bound partition number for parallel task (page range or DPSI enabled) |
| KEYCOLDT | SMALLINT | Data type for this key column (KEY range only) |
| KEYCOLPREC | SMALLINT | Precision/length for this key column (KEY range only) |
| KEYCOLSCAL | SMALLINT | Scale for this key column (KEY range with decimaldata type only) |
| EXPLAIN_TIME | TIMESTAMP | EXPLAIN timestamp |
| GROUP_MEMBER | VARCHAR(24) | Member name of the DB2 that executed EXPLAIN |
| SECTNOI | INTEGER | Section number of the statement |
| COLLID | VARCHAR(128) | Collection ID: DSNDYNAMICSQLCACHE: row originates from dynamic statement cache DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger |
| VERSION | VARCHAR(122) | Version identifier for the package |
| EXPANSION_REASON | CHAR(2) | Column applies to only statements that reference archive tables or temporal tables. For other statements, this column is blank |
| PER_STMT_ID | BIGINT | Persistent statement identifier for SQL statements in DB2 catalog |
DSN_QUERY_TABLE
Contains information about an SQL statement and displays the statement before and after query transformation in XML
| Column name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statement being explained |
| TYPE | CHAR(8) | Type of the data in the NODE_DATA column |
| QUERY_STAGE | CHAR(8) | Stage during query transformation when this row is populated |
| SEQNO | INTEGER | Sequence number for this row if NODE_DATA exceeds size of its column |
| NODE_DATA | CLOB(2M) | XML data containing the SQL statement and its query block, table, and column information |
| EXPLAIN_TIME | TIMESTAMP | EXPLAIN timestamp |
| QUERY_ROWID | ROWID | ROWID of the statement |
| GROUP_MEMBER | VARCHAR(24) | Member name of the DB2 subsystem that executed EXPLAIN |
| HASHKEY | INTEGER | Hash value of the contents in NODE_DATA |
| HASH_PRED | CHAR(1) | When NODE_DATA contains an SQL statement, indicates whether statement contains a parameter market literal a non-parameter market literal or no predicates |
| SECTNOI | INTEGER | Section number of the statement |
| APPLNAME | VARCHAR(24) | Name of application plan for row |
| PROGNAME | VARCHAR(128) | Name of program or package containinig statement being explained |
| COLLID | VARCHAR(128) | Collection ID: DSNDYNAMICSQLCACHE: row originates from dynamic statement cache DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger |
| VERSION | VARCHAR(122) | Version identifier for the package |
| EXPANSION_REASON | CHAR(2) | Column applies to only statements that reference archive tables or temporal tables. Else, column is blank |
DSN_SORTKEY_TABLE
Contains information about sort keys for all the sorts requiredby a query
| Column name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statement being explained |
| QBLOCKNO | SMALLINT | A number used to identify each query block within a query |
| PLANNO | SMALLINT | A number used to identify each mini-plan within a query block |
| APPLNAME | VARCHAR(24) | Name of application plan for row |
| PROGNAME | VARCHAR(128) | Name of program or package containing statement being explained |
| COLLID | VARCHAR(128) | Collection ID: DSNDYNAMICSQLCACHE: row originates from dynamic statement cache DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger |
| SORTNO | SMALLINT | Sequence number of the sort |
| ORDERNO | SMALLINT | Sequence of the sort key |
| EXPTYPE | CHAR(3) | Type of the sort key. The possible values are COL, EXP, QRY |
| TEXT | VARCHAR(128) | Sort key text; can be a column name, a scalar subquery, or ‘Record ID’ |
| TABNO | SMALLINT | Number that uniquely identifies corresponding table reference within query |
| COLNO | SMALLINT | A number that uniquely idenfifies the corresponding column within a query. Applicable only when the sort key is a column |
| DATATYPE | CHAR(18) | Data type of the sort key: HEXADECIMAL, CHARACTER, PACKED FIELD, FIXED(31), FIXED(15), DATE, TIME, VARCHAR, PACKED FLD, FLOAT TIMESTAMP, UNKNOWN DATA TYPE |
| LENGTH | INTEGER | Length of the sort key |
| EXPLAIN_TIME | TIMESTAMP | EXPLAIN timestamp |
| GROUP_MEMBER | VARCHAR(24) | Member name of the DB2 subsystem that executed EXPLAIN |
| SECTNOI | INTEGER | Section number of statement |
| VERSION | VARCHAR(122) | Version identifier for package |
| EXPANSION_REASON | CHAR(2) | Applies to only statements that reference archive tables or temporal tables. Else, blank |
| PER_STMT_ID | BIGINT | Persistent statement identifier for SQL statements in DB2 catalog |
DSN_SORT_TABLE
Contains information about sort operations required for a query
| Column name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statement being explained |
| QBLOCKNO | SMALLINT | A number used to identify each query block within a query |
| PLANNO | SMALLINT | A number used to identify each mini-plan within a query block |
| APPLNAME | VARCHAR(24) | Name of application plan for row |
| PROGNAME | VARCHAR(128) | Name of program or package containing statement being explained |
| COLLID | VARCHAR(128) | Collection ID: DSNDYNAMICSQLCACHE: row originates from dynamic statement cache DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger |
| SORTC | CHAR(5) | Reasons for sort of composite table. Using a bitmap of following values: G = Group by O = Order by J = Join U = Uniqueness |
| SORTN | CHAR(5) | Reasons for sort of composite table. Using a bitmap of following values: G = Group by O = Order by J = Join U = Uniqueness |
| SORTNO | SMALLINT | Sequence of the sort |
| KEYSIZE | SMALLINT | Sum of the lengths of the sort keys |
| EXPLAIN_TIME | TIMESTAMP | EXPLAIN timestamp |
| GROUP_MEMBER | VARCHAR(24) | Member name of the DB2 subsystem that executed EXPLAIN |
| SECTNOI | INTEGER | Section number of the statement |
| VERSION | VARCHAR(122) | Version identifier for package |
| EXPANSION_REASON | CHAR(2) | Applies to only statements that reference archive tables or temporal tables. Else, blank |
| PER_STMT_ID | BIGINT | Persistent statement identifier for SQL statements in DB2 catalog |
DSN_STATEMENT_CACHE_TABLE
Contains information about the SQL statements in the statement cache
| Column name | Data type | Description |
| STMT_ID | INTEGER | An EDM unique token |
| STMT_TOKEN | VARCHAR(240) | A user-provided identification string |
| COLLID | VARCHAR(128) | Collection ID: DSNDYNAMICSQLCACHE: row originates from dynamic statement cache DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger |
| PROGRAM_NAME | VARCHAR(128) | Name of package or DBRM that performed the initial PREPARE |
| INV_DROPALT | CHAR(1) | Invalidated by DROP/ALTER |
| INV_REVOKE | CHAR(1) | Invalidated by REVOKE |
| INV_LRU | CHAR(1) | Removed from cache by LRU |
| INV_RUNSTATS | CHAR(1) | Invalidated by RUNSTATS |
| CACHED_TS | TIMESTAMP | Timestamp when statement was cached |
| USERS | INTEGER | Number of current users of statement. These are the users that have prepared or executed the statement during their current unit or work |
| COPIES | INTEGER | Number of copies of statement owned by all threads in the system |
| LINES | INTEGER | Precompiler line number from the initial PREPARE |
| PRIMAUTH | VARCHAR(128) | Primary authorization ID of the user that did the initial PREPARE |
| CURSQLID | VARCHAR(128) | CURRENT SQLID of the user that did the initial prepare |
| BIND_QUALIFIER | VARCHAR(128) | Bind object qualifier for unqualified table names |
| BIND_ISO | CHAR(2) | ISOLATION bind option: UR = Uncommitted read CS = Cursor stability RS = Read stability RR = Repeatable read |
| BIND_CDATA | CHAR(1) | DATA CURRENTDATA bind option: Y = CURRENTDATA(YES N = CURRENTDATA(NO) |
| BIND_DYNRL | CHAR(1) | DYNAMICRULES bind option: B = DYNAMICRULES(BIND) R = DYNAMICRULES(RUN) |
| BIND_DEGRE | CHAR(1) | CURRENT DEGREE value: A = ANY 1 = 1 |
| BIND_SQLRL | CHAR(1) | CURRENT RULES value: D = DB2 S = SQL |
| BIND_CHOLD | CHAR(1) | Cursor WITH HOLD bind option: Y = Initial PREPARE was done for a cursor WITH HOLD N = Initial PREPARE was not done for a cursor WITH HOLD |
| STAT_TS | TIMESTAMP | Timestamp of stats when IFCID 318 is started |
| STAT_EXEC | INTEGER | Column is deprecated. Use STAT_EXECB instead |
| STAT_GPAG | INTEGER | Column in deprecated. Use STAT_GPAGB instead |
| STAT_SYNR | INTEGER | Column is deprecated. Use STAT_SYNRB instead |
| STAT_WRIT | INTEGER | Column is deprecated. Use STAT_WRITB instead |
| STAT_EROW | INTEGER | Column is deprecated. Use STAT_EROWB instead |
| STAT_PROW | INTEGER | Column is deprecated. Use STAT_PROWB instead |
| STAT_SORT | INTEGER | Column is deprecated. Use STAT_SORTB instead |
| STAT_INDX | INTEGER | Column is deprecated. Use STAT_INDXB instead |
| STAT_RSCN | INTEGER | Column is deprecated. Use STAT_RSCNB instead |
| STAT_PGRP | INTEGER | Column is deprecated. Use STAT_PGRPB instead |
| STAT_ELAP | FLOAT | Accumulated elapsed time for statement |
| STAT_CPU | FLOAT | Accumulated CPU time used for statement |
| STAT_SUS_SYNIO | FLOAT | Accumulated wait time for synchronous I/O |
| STAT_SUS_LOCK | FLOAT | Accumulated wait time for lock and latch requests |
| STAT_SUS_SWIT | FLOAT | Accumulated wait time for synchronous execution unit switch |
| STAT_SUS_GLCK | FLOAT | Accumulated wait time for global locks |
| STAT_SUS_OTHER | FLOAT | Accumulated wait time for read activity done by another thread |
| STAT_SUS_OTHW | FLOAT | Accumulated wait time for write activity done by another thread |
| STAT_RIDLIMT | INTEGER | Column is deprecated. Use STAT_RIDLIMTB instead |
| STAT_RIDSTOR | INTEGER | Column is deprecated. Use STAT_RIDSTORB instead |
| EXPLAIN_TS | TIMESTAMP | When the statement cache table is populated |
| SCHEMA | VARCHAR(128) | CURRENT SCHEMA value |
| STMT_TEXT | CLOB(2M) | Statement text |
| STMT_ROWID | ROWID | Statement ROWID |
| BIND_RO_TYPE | CHAR(1) | Current specification of REOPT option for statement: N = REOPT(NONE) 1 = REOPT(ONCE) or its equivalent A = REOPT(AUTO) or its equivalent 0 = No need or REOPT(AUTO) |
| BIND_RA_TOT | INTEGER` | Total number of REBIND commands issued for dynamic statement because of REOPT(AUTO) option |
| GROUP_MEMBER | VARCHAR(24) | Name of DB2 data-sharing member that inserted row |
| STAT_GPAGB | BIGINT | Number of getpage operations performed |
| STAT_SYNRB | BIGINT | Number of synchronous buffer reads performed |
| STAT_WRITB | BIGINT | Number of buffer write operations performed |
| STAT_EPROWB | BIGINT | Number of rows that are examined |
| STAT_PROWB | BIGINT | Number of rows that are processed |
| STAT_SORTB | BIGINT | Number of sorts that are performed |
| STAT_EXECB | BIGINT | Number of times this statement has been run. For a statement with a cursor, this is the number of OPENs |
| STAT_INDXB | BIGINT | Number of index scans that are performed |
| STAT_RSCNB | BIGINT | Number of table space scans that are performed |
| STAT_PGRPB | BIGINT | Number of parallel groups that are created |
| STAT_RIDLIMTB | BIGINT | Number of times a RID list was not used because the number of RIDs would have exceeded DB2 limits |
| STAT_RIDSTORB | BIGINT | Number of times RID list was not used because there is not enough storage available to hold the list of RIDs |
| LITERAL_REPL | CHAR(1) | Identifies cached statements where literal values are replaced by ‘&’I: R = statement is prepared with CONCENTRATE STATEMENTS WITH LITERALS behaviour and literal constants in statement have been replaced with ‘&’ D = Statement is a duplicate statement instance with different literal reusability criteria Blank = literal values are not replaced |
| STAT_SUS_LATCH | FLOAT | Accumulated wait time for latch requests |
| STAT_SUS_PLATCH | FLOAT | Accumulated wait time for page latch requests |
| STAT_SUS_DRAIN | FLOAT | Accumulated wait time for a drain lock requests |
| STAT_SUS_CLAIM | FLOAT | Accumulated wait time for claim coutn requests |
| STAT_SUS_LOG | FLOAT | Accumulated wait time for the log writer requests |
| EXPANSION_REASON | CHAR(2) | Applies only to statements that reference archive tables or temporal tables. For other statements, this column is blank |
| ACCELERATED | CHAR(10) | Whether cached statement was prepared for acceleration server. NO, YES, NEVER |
| STAT_ACC_ELAP | BIGINT | Accumulated elapsed time for accelerator |
| STAT_ACC_CPU | BIGINT | Accumulated CPU time for accelerator |
| STAT_ACC_ROW | BIGINT | Accumulated number of rows returned from accelerator |
| STAT_ACC_BYTE | BIGINT | Accumulated number of bytes returned from accelerator |
| STAT_ACC_1ROW | BIGINT | Time waited for first row to be returned from accelerator |
| STAT_ACC_DB2 | BIGINT | Total time accelerator waited for DB2 to request query results |
| STAT_ACC_EXEC | BIGINT | Accumulated execution time for accelerator |
| STAT_ACC_WAIT | BIGINT | Accumulated wait time for accelerator |
| ACCEL_OFFLOAD_ELIGIBLE | CHAR(1) | NO – statement not eligible for acceleration YES – statement is candidate for acceleration if available |
| ACCELERATOR_NAME | VARCHAR(128) | Concatenated name of accelerator seserver that processed query |
| STAT_SUS_CHILDLLOCKS | FLOAT | Accumulated wait time for child L-locks for statement |
| STAT_SUS_OTHERLLOCKS | FLOAT | Accumulated wait time for other L-locks for statement |
| STAT_SUS_PPPLOCKS | FLOAT | Accumulated wait time for P/PP-Locks for statement |
| STAT_SUS_PAGEPLOCKS | FLOAT | Accumulated wait time for PAGE P-locks for statement |
| STAT_SUS_OTHERPLOCKS | FLOAT | Accumulated wait time for other P-locks for statement |
| PER_STMT_ID | BIGINT | Statement identifier for stabilized dynamic SQL |
| STBLGRP | VARCHAR(128) | Stabilization group name specified in a START DYNQRY |
| QUERY HASH | CHAR(16) | Has key generated by the statement user |
| QUERY_HASH_VERSION | INTEGER | Version of QUERY_HASH |
| STABILIZED | CHAR(1) | Indicates whether the statement was stabilized |
| APPLCOMPAT | CHAR(10) | Application compatibility level of a dynamic SQL statement |
| CNO | BIGINT | Command number for the dynamic query capture monitor |
| STAT_SUS_PIPE | FLOAT | Accumulated wait time for latch requests |
DSN_STATEMNT_TABLE
Contains information about the estimated cost of specified SQL statements.
| Column name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statement being explained |
| APPLNAME | VARCHAR(24) | Name of application plan for row |
| PROGNAME | VARCHAR(128) | Name of program or package containing statement being explained |
| COLLID | VARCHAR(128) | Collection ID: DSNDYNAMICSQLCACHE: row originates from dynamic statement cache DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger |
| GROUP_MEMBER | VARCHAR(24) | Member name of the DB2 that executed EXPLAIN, or blank |
| EXPLAIN_TIME | TIMESTAMP | Time statement is processed, same as BIND_TIME in PLAN_TABLE |
| STMT_TYPE | CHAR(6) | Type of statement being explained: SELECT = SELECT INSERT = INSERT UPDATE = UPDATE DELETE = DELETE MERGE = MERGE TRUNCA = TRUNCATE SELUPD = SELECT with FOR UPDATE OF DELCUR = DELETE WHERE CURRENT OF CURSOR UPDCUR = UPDATE WHERE CURRENT OF CURSOR |
| COST_CATEGORY | CHAR(1) | Where DB2 was forced to use default values when making its estimates: A = Cost estimation made without using default values B = some condition exists for which DB2 was forced to use default values. Values in REASON to determine why DB2 was unable to put estimate in cost category A |
| PROCMS | INTEGER | Estimated processor cost in milliseconds for the SQL statement |
| PROCSU | INTEGER | Estimated processor cost in service units for the SQL statement |
| REASON | VARCHAR(254) | Reasons for putting an estimate into cost category B: ACCELMODEL ELIGIBLE: eligible for acceleration ACCELMODEL NOT ELIGIBLE: not eleigble for acceleration HAVING CLAUSE: A subselect in SQL statement contains a HAVING HOST VARIABLES: the statement uses host variables, parameter markers, or special registers OPTIMIZATION HINTS: statement level or access path hint applied PROFILEID: Profile id if using profile monitoring REFERENTIAL CONSTRAINTS: referential constraints of the type CASCADE or SET NULL exist on the target table of a DELETE statement TABLE CARDINALITY: cardinality statistics are missing for one or more of the tables used in the statement UDF: the statement uses user-defined functions TRIGGERS: triggers are defined on the target table of an INSERT, UPDATE, or DELETE statement |
| STMT_ENCODE | CHAR(1) | Encoding scheme of the statement. if the statement represents a single CCSID set, possible values are: A = ASCII E = EBCDIC U = UNICODE If the statement has multiple CCSID sets, the column value is M |
| TOTAL_COST | FLOAT | Overall estimated cost of the statement. Should be used only for reference |
| SECTNOI | INTEGER | Section number of the statement |
| VERSION | VARCHAR(122) | Version identifier for the package |
| EXPANSION_REASON | CHAR(2) | Applies to only statements that reference archive tables or temporal tables. For other statements, this column is blank. |
| APCOMPARE_STATUS | CHAR(1) | Status of access path comparison operation for APCOMPARE option on BIND or REBIND S = Access path comparison succeded F = New access path does not match previous, comparison failed N – No match |
| APREUSE_STATUS | CHAR(1) | Status of access path comparison operation for APREUSE option on BIND or REBIND S = access path reuse succeded F = access path reuse failed N = no match |
| APREUSE_VERSION | VARCHAR(122) | Version identifier of the package |
| APREUSE_COPYID | INTEGER | Copy number of identifier for package |
| EXPLAIN_TYPE | CHAR(1) | Type of action that created row: A = Automatic rebind B = BIND command C = EXPLAIN STATEMENT CACHE statement D = dynamic EXPLAIN statement R = REBIND command S = EXPLAIN STABILIZED DYNAMIC QUERY statement |
| PER_STMT_ID | BIGINT | Persistent statement identifier for SQL statements in DB2 catalog tables |
| QUERY_HASH | CHAR(16) | Hash key generated by statement text |
DSN_STAT_FEEDBACK
Recommendations for capturing missing or conflicting statistics defined during EXPLAIN
| Column Name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statement being explained |
| APPLNAME | VARCHAR(24) | Name of application plan for row |
| PROGNAME | VARCHAR(128) | Name of program or package containing statement being explained |
| COLLID | VARCHAR(128) | Collection ID: DSNDYNAMICSQLCACHE: row originates from dynamic statement cache DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger |
| EXPLAIN_TIME | TIMESTAMP | EXPLAIN timestamp |
| GROUP_MEMBER | VARCHAR(24) | Member name of the DB2 subsystem that executed EXPLAIN |
| SECTNOI | INTEGER | Section number of statement |
| VERSION | VARCHAR(122) | Version identifier for package |
| TBCREATOR | VARCHAR(128) | Creator of table |
| TBNAME | VARCHAR(128) | Name of table |
| IXCREATOR | VARCHAR(128) | Creator of index |
| IXNAME | VARCHAR(128) | Name of index |
| COLNAME | VARCHAR(128) | Name of column |
| NUMCOLUMNS | SMALLINT | Number of columns in the column group |
| COLGROUPCOLNO | VARCHAR(254) | A hex representation identifies set of columns associated with statistics |
| TYPE | CHAR(1) | Type of statistic to collect: ‘C’ Cardinality ‘F’ Frequency ‘H’ Histogram ‘I’ Index ‘T’ Table |
| DBNAME | VARCHAR(24) | Name of database |
| TSNAME | VARCHAR(24) | Name of table space |
| REASON | CHAR(8) | Reason that the statistic was recommend: ‘BASIC’ – A basic statistic value for a column table or index is missing. No statistics were collected for the identified object ‘KEYCARD’ – Cardinalities of index key columns are missing ‘LOWCARD’ – Cardinality of the column is a low value, which indicates that data may be skewed ‘DEFAULT’ – a predicate references a value that is probably a default value, which indicates that data might be skewed ‘RANGEPRD’ – Histogram statistics not available for a range predicate ‘PARALLEL’ – Parallelism could be improved by uniform partitioning of key ranges ‘CONFLICT’ – Another statistic contains a value that conflicts with the value of this statistic ‘COMPFFIX’ – Multi-column cardinality statistics are needed for an index compound filter factor ‘STALE’ – A statistic appears to be out of sync |
| REMARKS | VARCHAR(254) | Free form text for extensibility |
DSN_STRUCT_TABLE
Contains information about the query blocks in a query
| Column name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statement being explained |
| QBLOCKNO | SMALLINT | A number used to idenfity each query block in a query |
| APPLNAME | VARCHAR(24) | Name of application plan for row |
| PROGNAME | VARCHAR(128) | Name of program or package containing statement being explained |
| PARENT | SMALLINT | Parent query block number of current query block in structure of SQL text; same as PARENT_QBLOCKNO in PLAN_TABLE |
| TIMES | FLOAT | Estimated number of rows returned by Data Manager and number of executions of the query block |
| ROWCOUNT | INTEGER | Estimated number of rows returned by RDS (query cardinality) |
| ATOPEN | CHAR(1) | Whether query block is moved up for do-at-open processing. Y if done-at-open or N otherwise |
| CONTEXT | CHAR(10) | Context of current query block. Values are: TOP LEVEL, UNION, UNION ALL, PREDICATE, TABLE EXP, UNKNOWN |
| ORDERNO | SMALLINT | Not used |
| DOATOPEN_PARENT | SMALLINT | Parent query block number of current query block. Do-at-open parent if the query block is done-at-open, may differ from PARENT_QBLOCKNO in PLAN_TABLE |
| QBLOCK_TYPE | CHAR(6) | Type of current query block: SELECT, INSERT, UPDATE, DELETE, SELUPD, DELCUR, UPDCUR, CORSUB, NCOSUB, TABLEX, TRIGGR, UNION, UNIONA, CTE Equivalent to QBLOCK_TYPE column in PLAN_TABLE, except for CTE |
| EXPLAIN_TIME | TIMESTAMP | EXPLAIN timestamp |
| GROUP_MEMEBR | CHAR(8) | Member name of DB2 subsystem that executed EXPLAIN |
| ORIGIN | CHAR(1) | Indicates the origin of the query block Blank = generated by DB2 C = column mask R = row permission U = specified by the user |
| SECTNOI | INTEGER | Section number of the statement |
| COLLID | VARCHAR(128) | Collection ID: DSNDYNAMICSQLCACHE: row originates from dynamic statement cache DSNEXPLAINMODEYES: row originates from an application that specifies YES for CURRENT EXPLAIN MODE special register DSNEXPLAINMODEEXPLAIN: row originates from an application that specifies EXPLAIN for CURRENT EXPLAIN MODE special register When SQL statement is embedded in a compiled SQL function, native SQL procedure or advanced trigger, column is schema of compiled SQL function, native SQL procedure or advanced trigger |
| VERSION | VARCHAR(122) | Version identifier for the package |
| EXPANSION_REASON | CHAR(2) | Applies to only statements that reference archive tables or temporal tables. For other statements, this column is blank |
| PER_STMT_ID | BIGINT | Persistent statement identifier for SQL statements in the DB2 catalog |
DSN_VIEWREF_TABLE
Contains information about all views and materialized query tables to process a query
| Column name | Data type | Description |
| QUERYNO | INTEGER | A number intended to identify the statement being explained |
| APPLNAME | VARCHAR(24) | Name of application plan for row |
| PROGNAME | VARCHAR(128) | Name of program or package containing statement being explained |
| VERSION | VARCHAR(122) | Version identifier for the package |
| COLLID | VARCHAR(128) | Collection ID |
| CREATOR | VARCHAR(128) | Authorization ID of the owner of the object |
| NAME | VARCHAR(128) | Name of the object |
| TYPE | CHAR(1) | Type of object: V = View R = MQT used to replace the base table for rewrite M = MQT |
| MQTUSE | SMALLINT | IBM internal use only |
| EXPLAIN_TIME | TIMESTAMP | EXPLAIN timestamp |
| GROUP_MEMBER | VARCHAR(24) | Explain name of DB2 subsytem that executed EXPLAIN |
| SECTNOI | INTEGER | Section number of the statement |
| EXPANSION_REASON | CHAR(2) | Applies to only statements that reference archive tables or temporal tables. For other statements, this column is blank. |
Disclaimer: This Db2® 12 for z/OS Reference Guide was developed to help users in their daily activities in administrating and programming in Db2 for z/OS. There are no guarantees expressed or implied with the contents in this guide. We want to provide a quality and useful reference for users. Please notify us of any mistakes or errors in this reference guide at blogs@bmc.com. Db2 is a registered trademark of the IBM Corporation.
