Table access information

A statement in db2expln output provides the name and type of table being accessed.

Information about regular tables includes one of the following table access statements:

   Access Table Name = schema.name  ID = ts,n
   Access Hierarchy Table Name = schema.name  ID = ts,n
   Access Materialized Query Table Name = schema.name  ID = ts,n

where:

schema.name is the fully qualified name of the table that is being accessed
ID is the corresponding TABLESPACEID and TABLEID from the SYSCAT.TABLES catalog view entry for the table

Information about temporary tables includes one of the following table access statements:

   Access Temp Table  ID = tn
   Access Global Temp Table  ID = ts,tn

where ID is the corresponding TABLESPACEID from the SYSCAT.TABLES catalog view entry for the table (ts) or the corresponding identifier that is assigned by db2expln (tn).

After the table access statement, the following more statements are provided to further describe the access.

Number of columns statement

The following statement indicates the number of columns that are being used from each row of the table:

   #Columns = n

Block access statement

The following statement indicates that the table has one or more dimension block indexes that are defined on it:

   Clustered by Dimension for Block Index Access

If this statement does not appear, the table was created without the ORGANIZE BY DIMENSIONS clause.

Parallel scan statement

The following statement indicates that the database manager uses several subagents to read the table in parallel:

   Parallel Scan

If this statement does not appear, the table is read by only one agent (or subagent).

Scan direction statement

The following statement indicates that the database manager reads rows in reverse order:

   Scan Direction = Reverse

If this statement does not appear, the scan direction is forward, which is the default.

Row access statements

One of the following statements indicates how qualifying rows in the table are being accessed.

The Relation Scan statement indicates that the table is being sequentially scanned for qualifying rows.
- The following statement indicates that no prefetching of data is done:
```
   Relation Scan
   |  Prefetch: None
```
- The following statement indicates that the optimizer determined the number of pages that are prefetched:
```
   Relation Scan
   |  Prefetch: n Pages
```
- The following statement indicates that data must be prefetched:
```
   Relation Scan
   |  Prefetch: Eligible
```
The following statement indicates that qualifying rows are being identified and accessed through an index:
```
   Index Scan:  Name = schema.name  ID = xx
   | Index type
   | Index Columns:
```
where:
- schema.name is the fully qualified name of the index that is being scanned
- ID is the corresponding IID column in the SYSCAT.INDEXES catalog view
- Index type is one of:
```
   Regular index (not clustered)
   Regular index (clustered)
   Dimension block index 
   Composite dimension block index
   Index over XML data
```
This is followed by one line of output for each column in the index. Valid formats for this information are as follows:
```
   n: column_name (Ascending)
   n: column_name (Descending)
   n: column_name (Include Column)
```
The following statements are provided to clarify the type of index scan.
- The range-delimiting predicates for the index are shown by the following statements:
```
   #Key Columns = n
   |  Start Key: xxxxx
   |  Stop Key: xxxxx
```
  where xxxxx is one of:
  - Start of Index
  - End of Index
  - Inclusive Value: or Exclusive Value:
    An inclusive key value is included in the index scan. An exclusive key value is not be included in the scan. The value of the key is determined by one of the following items for each part of the key:
    n: 'string' n: nnn n: yyyy-mm-dd n: hh:mm:ss n: yyyy-mm-dd hh:mm:ss.uuuuuu n: NULL n: ?
    Only the first 20 characters of a literal string are displayed. A string is longer than 20 characters is indicated by an ellipsis (...) at the end of the string. Some keys cannot be determined until the section is run and is indicated by a question mark (?) as the value.
- Index-Only Access
  If all of the needed columns can be obtained from the index key, this statement displays and no table data are accessed.
- The following statement indicates that no prefetching of index pages is done:
```
   Index Prefetch: None
```
- The following statement indicates that for index prefetching sequential detection prefetching is enabled and it shows the MAXPAGES value for this type of prefetching denoted by x:
```
   Index Prefetch: Sequential (x)
```
- The following statement indicates that for index prefetching readahead prefetching is enabled:
```
   Index Prefetch: Readahead
```
- The following statement indicates that for index prefetching sequential detection and readahead prefetching are enabled. It also shows the MAXPAGES value for sequential detection prefetching that is denoted by x:
```
   Index Prefetch: Sequential (x), Readahead
```
- The following statement indicates that no prefetching of data pages is done:
```
   Data Prefetch: None
```
- The following statement indicates that for data prefetching sequential detection prefetching is enabled and it shows the MAXPAGES value for this type of prefetching denoted by x:
```
   Data Prefetch: Sequential (x)
```
- The following statement indicates that for data prefetching readahead prefetching is enabled:
```
   Data Prefetch: Readahead
```
- The following statement indicates that for data prefetching sequential detection and readahead prefetching are enabled. It also shows the MAXPAGES value for sequential detection prefetching that is denoted by x:
```
   Data Prefetch: Sequential (x), Readahead
```
- If there are predicates that can be passed to the index manager to help qualify index entries, the following statement is used to show the number of these predicates:
```
   Sargable Index Predicate(s)
   |  #Predicates = n
```
When a statement indicates that qualifying rows are being identified and accessed through an index with an expression-based key, db2explain shows detailed information about the index.
- This is followed by one line of output for each column in the index. Valid formats for this information are as follows:
```
   n: column_name (Ascending)
   n: column_name (Descending)
   n: column_name (Include Column)
```
For example, if an index is created using the expression upper(name), salary+bonus, id, then the expression return the following db2expln output:
```
     |  |  Index Columns:
     |  |  |  1: K00[UPPER(NAME)] (Ascending)
     |  |  |  2: K01[SALARY+BONUS] (Ascending)
     |  |  |  3: ID (Ascending)
```
If the qualifying rows are being accessed through row IDs (RIDs) that were prepared earlier in the access plan, this is indicated by the following statement:
```
   Fetch Direct Using Row IDs
```
If the table has one or more block indexes that are defined on it, rows can be accessed by either block or row IDs. This is indicated by the following statement:
```
   Fetch Direct Using Block or Row IOs
```

Lock intent statements

For each table access, the type of lock that are acquired at the table and row levels is shown with the following statement:

   Lock Intents
   |  Table: xxxx
   |  Row  : xxxx

Possible values for a table lock are:

Exclusive
Intent Exclusive
Intent None
Intent Share
Share
Share Intent Exclusive
Super Exclusive
Update

Possible values for a row lock are:

Exclusive
Next Key Weak Exclusive
None
Share
Update

Predicate statements

There are three types of statement that provide information about the predicates that are used in an access plan.

The following statement indicates the number of predicates that are evaluated for each block of data that is retrieved from a blocked index:
```
   Block Predicates(s)
   |  #Predicates = n
```
The following statement indicates the number of predicates that are evaluated while the data is being accessed. This number does not include pushdown operations, such as aggregation or sort:
```
   Sargable Predicate(s)
   |  #Predicates = n
```
The following statement indicates the number of predicates that will be evaluated after the data is returned:
```
   Residual Predicate(s)
   |  #Predicates = n
```

The number of predicates that are shown in these statements might not reflect the number of predicates that are provided in the query statement, because predicates can be:

Applied more than once within the same query
Transformed and extended with the addition of implicit predicates during the query optimization process
Transformed and condensed into fewer predicates during the query optimization process

Miscellaneous table statements

The following statement indicates that only one row are accessed:
```
   Single Record
```
The following statement appears when the isolation level that is used for table access is different from the isolation level for the statement:
```
   Isolation Level: xxxx
```
There are a number of possible reasons for this. For example:
- A package that was bound with the repeatable read (RR) isolation level is impacting certain referential integrity constraints; access to the parent table for checking these constraints is downgraded to the cursor stability (CS) isolation level to avoid holding unnecessary locks on this table.
- A package that was bound with the uncommitted read (UR) isolation level includes a DELETE statement; access to the table for the delete operation is upgraded to CS.
The following statement indicates that some or all of the rows that are read from a temporary table are cached outside of the buffer pool if sufficient sortheap memory is available:
```
   Keep Rows In Private Memory
```
The following statement indicates that the table has the volatile cardinality attribute set:
```
   Volatile Cardinality
```