ROWID

This topic shows detailed information about Accounting - ROWID.

This block shows information about the row identifier (ROWID).

The following example shows both layouts, the report on the left, and the trace layout on the right.

Report:                              Trace:

ROWID           AVERAGE     TOTAL    ROWID          TOTAL
-------------  --------  --------    ----------  --------
DIRECT ACCESS      0.00         0    DIR ACCESS         0
INDEX USED         0.00         0    INDEX USED         0
TS SCAN USED       0.00         0    TS SCAN            0

DIRECT ACCESS (DIR ACCESS)

The number of times that direct row access was successful.

Field Name: QXROIMAT

INDEX USED

The number of times that direct row access failed and an index was used to find a record.

Background and Tuning Information

This can happen, for example, when a REORG is performed between the read of the ROWID column and the use of the host variable in the WHERE clause of the SQL statement. This causes the RID value in the host variable to be incorrect.

Field Name: QXROIIDX

TS SCAN USED (TS SCAN)

The number of times that an attempt to use direct row access reverted to using a table-space scan because DB2 was unable to use a matching index scan.

Background and Tuning Information

Ideally, this value should be 0.

Table-space scans can happen, for example, when a REORG is performed between the read of the ROWID column and the use of the host variable in the WHERE clause of the SQL statement. This causes the RID value in the host variable to be incorrect. DB2 first tries a matching-index scan before using a table-space scan.

To avoid table space scans, you can force the access path of an unsuccessful direct row access to use a matching index scan on the primary-index key by adding PKCOL to the WHERE clause in the SQL statement. .... WHERE ROWIDCOL=:HVROWID AND PKCOL=:HVPK .....

Field Name: QXROITS