A tabela SYSQUERYPLAN contém as informações de dica de plano para as consultas na tabela SYSIBM.SYSQUERY.
Ele se correlaciona com a tabela SYSQUERY pela coluna QUERYID.
Para uma consulta, pode haver até 3 cópias de dicas de plano armazenadas na tabela SYSQUERYPLAN, diferenciadas pelo valor da coluna COPYID.
O esquema é SYSIBM.
The SYSQUERYPLAN table contains the plan hint information for the queries in the SYSIBM.SYSQUERY table.
It correlates to the SYSQUERY table by the QUERYID column.
For a query, there can be up to 3 copies of plan hints stored in the SYSQUERYPLAN table, distinguished by the value of the COPYID column.
The schema is SYSIBM.
| Column name |
Data type |
Description |
Use |
| QUERYID |
BIGINT
NOT NULL
ON DELETE
CASCADE |
Unique identifier for the query.
The value of QUERYID corresponds to the value of the QUERYID column in the SYSIBM.SYSQUERY column. |
G |
| COPYID |
SMALLINT
NOT NULL |
The version of the plan hints for the query in this row.
- 0
- Current version of the plan hints.
- 1
- Previous version of the plan hints used by PLAN STABILITY
- 2
- Original version of the plan hints used by PLAN STABILITY
|
G |
| PLAN_VALID |
CHAR(1)
NOT NULL |
Whether the plan hints are valid:
- N
- The plan hints are invalid
- Y
- The plan hints are valid
|
G |
| IBMREQD |
CHAR(1)
NOT NULL |
A value of Y indicates that the row came from the basic machine-readable material (MRM) tape. For all other values,
see Release dependency indicators.
The value in this field is not a reliable indicator of release dependencies. |
G |
| QBLOCKNO |
SMALLINT
NOT NULL |
A number that identifies each query block within a query.
The value of the numbers are not in any particular order, nor are they necessarily consecutive. |
G |
| PLANNO |
SMALLINT
NOT NULL |
The number of the step in which the query that is indicated in QBLOCKNO was processed.
This column indicates the order in which the steps were executed. |
G |
| METHOD |
SMALLINT
NOT NULL |
A number that indicates the join method that is used for the step:
- 0
- The table in this step is the first table that is accessed, a continuation of a previous table that was accessed, or a table that is not used.
- 1
- A nested loop join is used. For each row of the current composite table, matching rows of a new table are found and joined.
- 2
- A merge scan join is used. The current composite table and the new table are scanned in the order of the join columns, and matching rows are joined.
- 3
- Sorts are needed by ORDER BY, GROUP BY, SELECT DISTINCT, UNION, INTERSECT, EXCEPT, a quantified predicate, or an IN predicate.
This step does not access a new table.
- 4
- A hybrid join was used. The current composite table is scanned in the order of the join-column rows of the new table.
The new table is accessed using list prefetch.
|
G |
| CREATOR |
VARCHAR(128)
NOT NULL |
The creator of the new table that is accessed in this step, blank if METHOD is 3. |
G |
| TNAME |
VARCHAR(128)
NOT NULL |
The name of one of the following objects:
- Materialized query table
- Created or declared temporary table
- Materialized view
- materialized table expression
The value is blank if METHOD is 3.
The column can also contain the name of a table in the form DSNWFQB(qblockno).
DSNWFQB(qblockno) is used to represent the intermediate result of a UNION ALL, INTERSECT ALL, 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 the represent the work file of a star join dimension table. |
G |
| — |
SMALLINT
NOT NULL |
Internal use only. |
I |
| ACCESSTYPE |
CHAR(2)
NOT NULL |
The method of accessing the new table:
- A
- Accelerated query table access.
- DI
- By an intersection of multiple DOCID lists to return the final DOCID list
- DU
- By a union of multiple DOCID lists to return the final DOCID list
- DX
- By an XML index scan on the index that is named in ACCESSNAME to return a DOCID list
- E
- By direct row access using a row change timestamp column.
- H
- By hash overflow index (identified in ACCESSCREATOR and ACCESSNAME)
- I
- By an index (identified in ACCESSCREATOR and ACCESSNAME)
- IN
- By an index scan when the matching predicate contains an IN predicate and the IN-list is accessed through an in-memory table.
- I1
- By a one-fetch index scan
- M
- By a multiple index scan (followed by MX, MI, MU, or MH)
- MH
- By the hash overflow index named in ACCESSNAME
- MI
- By an intersection of multiple indexes
- MU
- By a union of multiple indexes
- MX
- By an index scan on the 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 by using the DOCID list that is returned by DX,
DI, or DU.
- N
-
- By an index scan when the matching predicate contains the IN keyword
- By an index scan when Db2 rewrites a query using the IN keyword
- By hash access with the IN keyword
- By hash access when Db2 rewrites a query using the IN keyword
- NR
- Range list access.
- P
- By a dynamic pair-wise index scan
- R
- By a table space scan
- RW
- By a work file scan of the result of a materialized user-defined table function
- V
- By buffers for an INSERT statement within a SELECT
- blank
- Not applicable to the current row
|
G |
| MATCHCOLS |
SMALLINT
NOT NULL |
For ACCESSTYPE I, I1, N, NR, MX, or DX, the number of index keys that are used in an index scan; otherwise, 0. |
G |
| ACCESSCREATOR |
VARCHAR(128)
NOT NULL |
For ACCESSTYPE I, I1, N, NR, MX, or DX, the creator of the index; otherwise, blank. |
G |
| ACCESSNAME |
VARCHAR(128)
NOT NULL |
For ACCESSTYPE I, I1, H, MH, N, NR, MX, or DX, the name of the index; for ACCESSTYPE P, DSNPJW(mixopseqno) is the starting pair-wise join leg in
MIXOPSEQNO; otherwise, blank. |
G |
| INDEXONLY |
CHAR(1)
NOT NULL |
Indication of whether access to an index alone is enough to perform the step, or Indication of whether data too must be accessed.
- Y
- Yes
- N
- No
|
G |
| SORTN_UNIQ |
CHAR(1)
NOT NULL |
Indication of whether the new table is sorted to remove duplicate rows.
- Y
- Yes
- N
- No
|
G |
| SORTN_JOIN |
CHAR(1)
NOT NULL |
Indication of whether the new table is sorted for join method 2 or 4.
- Y
- Yes
- N
- No
|
G |
| SORTN_ORDERBY |
CHAR(1)
NOT NULL |
Indication of whether the new table is sorted for ORDER BY.
- Y
- Yes
- N
- No
|
G |
| SORTN_GROUPBY |
CHAR(1)
NOT NULL |
Indication of whether the new table is sorted for GROUP BY.
- Y
- Yes
- N
- No
|
G |
| SORTC_UNIQ |
CHAR(1)
NOT NULL |
Indication of whether the compositetable is sorted to remove duplicate rows.
- Y
- Yes
- N
- No
|
G |
| SORTC_JOIN |
CHAR(1)
NOT NULL |
Indication of whether the composite table is sorted for join method 1, 2 or 4.
- Y
- Yes
- N
- No
|
G |
| SORTC_ORDERBY |
CHAR(1)
NOT NULL |
Indication of whether the composite table is sorted for an ORDER BY clause or a quantified predicate.
- Y
- Yes
- N
- No
|
G |
| SORTC_GROUPBY |
CHAR(1)
NOT NULL |
Indication of whether the composite table is sorted for a GROUP BY clause.
- Y
- Yes
- N
- No
|
G |
| TSLOCKMOD |
CHAR(3)
NOT NULL |
An indication of the mode of lock that is acquired on either 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 level cannot be determined at bind time, the lock mode is determined by the isolation level at run time 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.
The data in this column is right justified.
For example, IX appears as a blank, followed by I, followed by X.
If the column contains a blank, then 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 a blank in this case. |
G |
| PREFETCH |
CHAR(1)
NOT NULL |
Indication of 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
- blank
- Unknown or no prefetch
|
G |
| COLUMN_FN_EVAL |
CHAR(1)
NOT NULL |
When an SQL aggregate function is evaluated:
- R
- While the data is being read from the table or index
- S
- While performing a sort to satisfy a GROUP BY clause
- blank
- After data retrieval and after any sorts
|
G |
| MIXOPSEQ |
SMALLINT
NOT NULL |
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), or the sequence number of range list access (ACCESSTYPE is 'NR').
- 0
- For any other rows.
|
G |
| ACCESS_DEGREE |
SMALLINT |
The number of parallel tasks or operations that are activated by a query. This value is determined at bind time; the actual number of parallel operations that
are used at execution time could be different.
This column contains 0 if a host variable is used.
This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns.
Otherwise, it can contain null if the method that it refers to does not apply. |
G |
| ACCESS_PGROUP_ID |
SMALLINT |
The identifier of the parallel group for accessing the new table.
A parallel group is a set of consecutive operations, executed in parallel, that have the same number of parallel tasks.
This value is determined at bind time; it could change at execution time.
This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns. Otherwise, it can contain null if the
method that it refers to does not apply. |
G |
| JOIN_DEGREE |
SMALLINT |
The number of parallel operations or tasks that are used in joining the composite table with the new table.
This value is determined at bind time and can be 0 if a host variable is used.
The actual number of parallel operations or tasks used at execution time could be different.
This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns.
Otherwise, it can contain null if the method that it refers to does not apply. |
G |
| JOIN_PGROUP_ID |
SMALLINT |
The identifier of the parallel group for joining the composite table with the new table.
This value is determined at bind time; it could change at execution time.
This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns.
Otherwise, it can contain null if the method that it refers to does not apply. |
G |
| SORTC_PGROUP_ID |
SMALLINT |
The parallel group identifier for the parallel sort of the composite table.
This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns.
Otherwise, it can contain null if the method that it refers to does not apply. |
G |
| SORTN_PGROUP_ID |
SMALLINT |
The parallel group identifier for the parallel sort of the new table.
This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns.
Otherwise, it can contain null if the method that it refers to does not apply. |
G |
PARALLELISM_
MODE |
CHAR(1) |
The kind of parallelism, if any, that is used at bind time:
- I
- Query I/O parallelism
- C
- Query CP parallelism
This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns.
Otherwise, it can contain null if the method that it refers to does not apply. |
G |
MERGE_
JOIN_
COLS |
SMALLINT |
The number of columns that are joined during a merge scan join (Method=2).
This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns.
Otherwise, it can contain null if the method that it refers to does not apply. |
G |
CORRELATION_
NAME |
VARCHAR(128) |
The correlation name of a table or view that is specified in the statement.
If no correlation name exists, then the column is null.
This column contains the null value if the plan or package was bound using a plan table with fewer than 43 columns.
Otherwise, it can contain null if the method that it refers to does not apply. |
G |
| PAGE_RANGE |
CHAR(1)
NOT NULL
WITH DEFAULT |
Indication of whether the table qualifies for page range screening, so that plans scan only the partitions that are needed.
- Y
- Yes
- blank
- No
|
G |
| JOIN_TYPE |
CHAR(1)
NOT NULL
WITH DEFAULT |
The type of 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 when you use it, so that JOIN_TYPE contains L. |
G |
| QBLOCK_TYPE |
CHAR(6)
NOT NULL
WITH DEFAULT |
For each query block, an indication of the type of SQL operation that is performed.
For the outermost query, this column identifies the statement type. Possible values include:
- 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 subselect or fullselect
- TRUNCA
- TRUNCATE
- NCOSUB
- Noncorrelated subselect or fullselect
- TABLEX
- Table expression
- TRIGGR
- WHEN clause on CREATE TRIGGER
- UNION
- UNION
- UNIONA
- UNION ALL
- INTERS
- INTERSECT
- INTERA
- INTERSECT ALL
- EXCEPT
- EXCEPT
- EXCEPTA
- EXCEPT ALL
|
G |
PRIMARY_
ACCESSTYPE |
CHAR(1)
NOT NULL
WITH DEFAULT |
Indicates Indication of whether direct row access is attempted first:
- D
- Db2 tries to use direct row access with a rowid column.
If Db2 cannot use direct row access with a rowid column at run time, it uses the access path that is described in the ACCESSTYPE column of PLAN_TABLE.
- 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, then ACCESSTYPE indicates how the base table is accessed.
- blank
- Db2 does not try to use direct row access by using a rowid column or sparse index access for a work file.
The value of the ACCESSTYPE column of PLAN_TABLE provides information on the method of accessing the table.
|
G |
| PARENT_QBLOCKNO |
SMALLINT
NOT NULL |
A number that indicates the QBLOCKNO of the parent query block. |
G |
| 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 a 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. |
G |
| TABLE_ENCODE |
CHAR(1) |
The encoding scheme of the table.
The possible values are:
- A
- ASCII
- E
- EBCDIC
- U
- Unicode
- M
- The table contains multiple CCSID sets
|
G |
| TABLE_SCCSID |
SMALLINT
NOT NULL
WITH DEFAULT |
The SBCS CCSID value of the table.
If column TABLE_ENCODE is M, the value is 0. |
G |
| TABLE_MCCSID |
SMALLINT
NOT NULL
WITH DEFAULT |
The mixed CCSID value of the table.
If the value of the TABLE_ENCODE column is M, the value is 0.
If MIXED=NO in the DSNHDECP module, the value is -2. |
G |
| TABLE_DCCSID |
SMALLINT
NOT NULL
WITH DEFAULT |
The DBCS CCSID value of the table.
If the value of the TABLE_ENCODE column is M, the value is 0.
If MIXED=NO in the DSNHDECP module, the value is -2. |
G |
| |
INTEGER
NOT NULL
WITH DEFAULT |
The values in this column are for IBM® use only. |
I |
| CTEREF |
SMALLINT
NOT NULL
WITH DEFAULT |
If the referenced table is a common table expression, the value is the top-level query block number. |
G |
| PARENT_PLANNO |
SMALLINT
NOT NULL |
Corresponds to the plan number in the parent query block where a correlated subquery is invoked.
Or, for non-correlated subqueries, corresponds to the plan number in the parent query block that represents the work file for the subquery. |
G |
| EXPANSION_REASON |
CHAR(2)
NOT NULL |
This column applies to only static statements that reference archive tables or temporal tables.
For other statements, this column is blank.
Indicates the effect of the CURRENT TEMPORAL BUSINESS_TIME special register, the CURRENT TEMPORAL SYSTEM_TIME special register, and the
SYSIBMADM.GET_ARCHIVE built-in global variable.
These items are controlled by the BUSTIMESENSITIVE, SYSTIMESENSITIVE, and ARCHIVESENSITIVE bind options.
If one of these special registers or the global variable is set to Y and the corresponding bind option is set to YES, Db2 implicitly adds certain syntax to the
statement.
This column indicates whether the query contains this implicit query transformation and why.
This column can have one of the following values:
- A
- The query contains implicit query transformation as a result of the SYSIBMADM.GET_ARCHIVE built-in global variable.
- B
- The query contains implicit query transformation as a result of the CURRENT TEMPORAL BUSINESS_TIME special register.
- S
- The query contains implicit query transformation as a result of the CURRENT TEMPORAL SYSTEM_TIME special register.
- SB
- The query contains implicit query transformation as a result of the CURRENT TEMPORAL SYSTEM_TIME special register and the CURRENT TEMPORAL BUSINESS_TIME
special register.
- blank
- The query does not contain implicit query transformation.
|
G |
