PLAN_TABLE

For rows produced by EXPLAIN statements

The number specified in the QUERYNO clause, which is an optional part of the SELECT, INSERT, UPDATE, MERGE, and DELETE statement syntax.

For rows not produced by EXPLAIN statements

Db2 assigns a number that is based on the line number of the SQL statement in the source program.

When the values of QUERYNO are based on the statement number in the source program, values that exceed 32767 are reported as 0. However, in certain rare cases, the value is not guaranteed to be unique.

When the SQL statement is embedded in a compiled SQL function, native SQL procedure, or advanced trigger, if the QUERYNO clause is specified, its value is used by Db2. Otherwise Db2 assigns a number based on the line number of the SQL statement in the compiled SQL function, native SQL procedure, or advanced trigger.

When the SQL statement is embedded in a compiled SQL function, native SQL procedure, or advanced trigger, this column is not used, and is blank.

When the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column indicates the specific name of the compiled SQL function or native SQL procedure. When the SQL statement is embedded in an advanced trigger, this column contains the name of the trigger.

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.

• Table
• Materialized query table
• Created or declared temporary table
• Materialized view
• Materialized table expression
• Any of the following object names that identify intermediate results:

  'DSNWFQB(qblockno)'

  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)'

  The work file of a star join dimension table.

  'DSN_SPIX_TBLX(qblockno)'

  A sparse index used for a sideways table reference.

'A'

The query is sent to an accelerator server.

'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 access. IF an overflow condition occurs, the hash overflow index that is identified by ACCESSCREATOR and ACCESSNAME is used.

'HN'

By hash access using an IN predicate, or an IN predicate that Db2 generates. If a hash overflow condition occurs, the hash overflow index that is identified in ACCESSCREATOR and ACCESSNAME is used.

'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. A row that contains this value might be followed by a row that contains one of the following values:

• 'DI'
• 'DU'
• 'MH'
• 'MI'
• 'MU'
• 'MX'

'MH'

By the hash overflow index named in ACCESSNAME. A row that contains this value always follows a row that contains M.

'MI'

By an intersection of multiple indexes. A row that contains this value always follows a row that contains M.

'MU'

By a union of multiple indexes. A row that contains this value always follows a row that contains M.

'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. A row that contains this value always follows a row that contains M.

'N'

One of the following types:

• 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

'O'

Use of a work file was avoided when processing the result of a subquery, table expression, view, or intermediate result table.

'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

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 MIXOPSEQ; otherwise, blank.

Y

Yes

N

No

Y

Yes

N

No

Y

Yes

N

No

Y

Yes

N

No

Y

Yes

N

No

Y

Yes

N

No

Y

Yes

N

No

Y

Yes

N

No

Y

Yes

N

No

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

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.

Continues in next row.

Continued from previous row.

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.

Db2 inserts a value into this column in certain situations. ^{4 , 5}

'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 or no prefetch

'R'

While the 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 is specified

blank

After data retrieval and after any sorts

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.

'DSNDYNAMICSQLCACHE'

The row originates from the dynamic statement cache.

'DSNEXPLAINMODEYES'

The row originates from an application that specifies YES for the value of the CURRENT EXPLAIN MODE special register.

'DSNEXPLAINMODEEXPLAIN'

The row originates from an application that specifies EXPLAIN for the value of the CURRENT EXPLAIN MODE special register.

When the SQL statement is embedded in a compiled SQL function, native SQL procedure, or advanced trigger, this column indicates the schema name of the compiled SQL function, native SQL procedure, or advanced trigger.

C

Query CP parallelism.

Y

Yes

blank

No

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.

blank

At bind time, using a default filter factor for any host variables, parameter markers, or special registers.

B

At bind time, using a default filter factor for any host variables, parameter markers, or special registers; however, the statement is re-optimized at run time using input variable values for input host variables, parameter markers, or special registers. The bind option REOPT(ALWAYS), REOPT(AUTO), or REOPT(ONCE) must be specified for reoptimization to occur.

R

At run time, using input variables for any host variables, parameter markers, or special registers. The bind option REOPT(ALWAYS), REOPT(AUTO), or REOPT(ONCE) must be specified for this to occur.

'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

'COTBLX'

A table expression with a sideways reference.

'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

'EXCPTA'

EXCEPT ALL

'PRUNED'

Db2 does not generate an access path for the query because the query is guaranteed to qualify zero rows, such as the case of an always-false WHERE clause. For example:WHERE 0=1

'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 the 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 the 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:

CURRENT

The current copy of the package.

PREVIOUS

The previous copy of the package.

ORIGINAL

The original copy of the package.

'EXPLAIN SDQ: copy-id-number'

The string copy-id-number can have one of the following values:

CURRENT

The current copy.

INVALID

The invalid copy.

'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.

'P'

Db2 used data partitioned secondary index and a part-level operation to access the data.

'S'

Db2 used sparse index access for a 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, 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.

'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.