Checking whether query routing is enabled by using EXPLAIN diagnostics

Use the EXPLAIN utility to generate extended diagnostic information if a statement is no longer cached in memory or you need more detailed query-routing information.

About this task

When you run the EXPLAIN statement against a query that you expected to be routed to a shadow table, returned information includes extended diagnostic information that you can use to determine why the shadow table was not used for query optimization.

Procedure

To use the EXPLAIN utility to check whether query routing is enabled:

Create the explain tables by issuing the following statement:
```
CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', NULL, NULL)
```
Set the value of the CURRENT EXPLAIN MODE special register to EXPLAIN.
```
SET CURRENT EXPLAIN MODE EXPLAIN
```
Run the statements for which diagnostic information is to be collected.
Set the value of the CURRENT EXPLAIN MODE special register to NO.
```
SET CURRENT EXPLAIN MODE NO
```
Format the explain output by issuing the db2exfmt explain table formatting command as follows:
```
db2exfmt -1 -d db2-database-name
```

Example

The following examples show diagnostic information that the EXPLAIN utility might generate:

The following output shows that the isolation level of the materialized query table prevented the table from being used in the query. Queries that run under isolation level RS or RR are not routed to shadow tables.

 Extended Diagnostic Information:
    --------------------------------

    Diagnostic Identifier:  1
    Diagnostic Details:     EXP0053W  The materialized query table "DBGUEST1".
                            "ROWT_SHADOW" was not considered for query
                            matching because the isolation level of the query
                            is higher than the isolation level of the
                            materialized query table.

The following output shows the message that is returned when a query references row-organized tables with associated shadow tables and row-organized tables without associated shadow tables. All row-organized tables that are referenced in a query must have associated shadow tables for the query to be routed to shadow tables.

Extended Diagnostic Information:
--------------------------------

Diagnostic Identifier:  1
Diagnostic Details:     EXP0076W  No materialized query table matching was
                        performed on the statement during query rewrite
                        because there is a shadow table defined on at least
                        one,but not every, row-organized table referenced 
                        in the query...

The following output shows the message that is returned when query routing to the shadow table does not happen because the current latency between the source table and the shadow table does not fall within the limit that is specified by the value of the CURRENT REFRESH AGE special register:

Extended Diagnostic Information:
--------------------------------

Diagnostic Identifier:  1
Diagnostic Details:     EXP0087W  No materialized query table matching was
                        performed on the statement during query rewrite
                        because there is a shadow table defined on one of 
                        tables in the query and the current replication
                        the latency is larger than the time value specified
                        in the CURRENT REFRESH AGE special register.