DB2 10.5 for Linux, UNIX, and Windows

Enablement of query routing to shadow tables

Certain settings are required to enable query routing to shadow tables. Enabling query routing to shadow tables requires that intrapartition parallelism is enabled. Also, it requires that the optimizer is directed to consider shadow tables for query optimization when the replication latency is within user-defined limit.

Enabling intrapartition parallelism allows runtime access to the column-organized tables and shadow tables.

Replication latency is the amount of time that it takes for a transaction against a source table to be applied to a shadow table. Latency-based routing is a performance improvement technique that directs a query to a shadow table when the replication latency is within a user-defined limit. If you create a shadow table with ENABLE QUERY OPTIMIZATION clause, each of the following conditions must be true to optimize query processing that is based on a latency period:

The CURRENT QUERY OPTIMIZATION special register is set to 2 or a value greater than or equal to 5.
The CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register is set to contain only REPLICATION.
The CURRENT REFRESH AGE special register is set to a duration other than zero or ANY.
This special register specifies the refresh age as a timestamp with a format of yyyymmddhhmmss where the parts of the timestamp are defined as follows:
- yyyy indicates the year (0-9999)
- mm indicates the month (0-11)
- dd indicates the day (0-30)
- hh indicates the hour (0-23)
- mm indicates the minute (0-5)
- ss indicates the second (0-59)
The timestamp value can be truncated on the left. This left truncation means that you do not have to specify year, month, day, and so on, if you want a refresh age of only one second. However, individual elements that are preceded by another element must include any leading zeros. For example, a refresh age of 10705 represents 1 hour, 7 minutes, and 5 seconds.

Replication latency information is communicated to the DB2® instance through the SYSTOOLS.REPL_MQT_LATENCY table that is updated by InfoSphere® CDC to take advantage of latency-based routing.

You can enable the support for shadow tables and query routing to shadow tables in the following ways:

Automatic enablement through a stored procedure that is invoked when you connect to the database. For more information, see Customizing the connect procedure for shadow tables.
Manual enablement through the direct execution of the SQL statements that are required to enable query routing to shadow tables for the current connection. For more information, see Manually enabling shadow table support and query routing.

Example of query routing when shadow tables are implemented

This example demonstrates the conditions under which the DB2 optimizer chooses to route a query to a shadow table instead of the source table.

The example refers to the DTW.TRADE source table and the DTW.TRADE_SHADOW table that were defined in Creating shadow tables.

Example view to show current latency

The following example shows how to define a view to check the current latency between the source table and the shadow table. The view also checks whether this latency falls within the limit that is specified by the CURRENT REFRESH AGE special register.

Figure 1. Example view to show current latency

CREATE OR REPLACE VIEW SHADOW_LATENCY AS
  (SELECT CASE
     WHEN LATENCY <= REFRESH_AGE THEN 1
     ELSE 0
     END
     AS LATENCY_CHECK, V2.*
   FROM TABLE (SELECT CURRENT REFRESH AGE AS REFRESH_AGE,
                      CUR_TS - REFRESH_TS AS LATENCY, V1.*
                 FROM TABLE (SELECT CURRENT TIMESTAMP AS CUR_TS,
                   (TIMESTAMP('1970-01-01')
                    + (COMMIT_POINT-DELAY_OFFSET) SECONDS)
                    + CURRENT TIMEZONE AS REFRESH_TS,
                    COMMIT_POINT, DELAY_OFFSET FROM SYSTOOLS.REPL_MQT_LATENCY)
                 AS V1)
   AS V2);

Data in the source table

The following text consists of a query and its output to show you a sample of the data in the DTW.TRADE source table:

SELECT T_ID, T_DTS, T_QTY, T_BID_PRICE, T_TRADE_PRICE 
   FROM DTW.TRADE 
   FETCH FIRST 10 ROWS ONLY

T_ID            T_DTS                      T_QTY T_BID_PRICE T_TRADE_PRICE
--------------- -------------------------- ----- ----------- -------------
200000000000003 2000-01-03-09.00.03.207000   800       21.76         20.37
200000000000005 2000-01-03-09.00.03.422000   100       27.03         27.04
200000000000002 2000-01-03-09.00.03.599000   100       27.86         25.12
200000000000004 2000-01-03-09.00.03.956000   100       20.00         20.02
200000000000008 2000-01-03-09.00.04.853000   200       24.75         24.74
200000000000006 2000-01-03-09.00.04.969000   200       23.95         23.97
200000000000007 2000-01-03-09.00.05.452000   100       22.01         21.99
200000000000010 2000-01-03-09.00.06.068000   400       27.62         27.61
200000000000013 2000-01-03-09.00.07.055000   100       27.20         27.20
200000000000011 2000-01-03-09.00.07.437000   200       28.80         28.83

  10 record(s) selected.

Query on the source table

The following SQL statements are used to set the value of CURRENT REFRESH AGE to 1 and to issue a query that uses aggregation on the DTW.TRADE source table:

SET CURRENT REFRESH AGE 1;
SELECT COUNT(*) AS N_TRADES,
       SUM(T_TRADE_PRICE) AS TRADE_PRICE,
       YEAR(T_DTS) AS YEAR
  FROM DTW.TRADE
  GROUP BY YEAR(T_DTS)
  ORDER BY YEAR(T_DTS)

N_TRADES    TRADE_PRICE                       YEAR       
----------- --------------------------------- -----------
     428086                      214069815.84        2000
     429319                      214904598.96        2001
     428091                      213836538.04        2002
     427418                      213392163.70        2003
     427779                      213586501.48        2004
     427428                      213545692.04        2005
     426273                      213278197.45        2006
     428841                      214201725.10        2007
     428797                      214487297.14        2008
     427968                      214029900.08        2009

  10 record(s) selected.

Query is not routed to the shadow table

A query against the SHADOW_LATENCY view that was created in Figure 1 shows that the latency of the replication between the source table and the shadow table is 4.64 seconds. Because the value of CURRENT REFRESH AGE in this example is 1 (second), the latency check fails (1 < 4.64) and the query is not routed to the shadow table.

SELECT LATENCY_CHECK, REFRESH_AGE, LATENCY FROM shadow_latency;

LATENCY_CHECK REFRESH_AGE LATENCY
------------- ----------- --------
            0    1.000000 4.638021 

  1 record(s) selected.

The following access plan shows that the total cost of running this query is 767030 timerons.

Access Plan:
-----------
        Total Cost:             767030
        Query Degree:           8

      Rows 
     RETURN
     (   1)
      Cost 
       I/O 
       |
       12 
     GRPBY 
     (   2)
     767030 
     152935 
       |
       12 
     LMTQ  
     (   3)
     767029 
     152935 
       |
       12 
     TBSCAN
     (   4)
     767016 
     152935 
       |
       12 
     SORT  
     (   5)
     767016 
     152935 
       |
    4.28e+06 
     TBSCAN
     (   6)
     467262 
     152935 
       |
    4.28e+06 
 TABLE: DTW     
      TRADE
       Q1

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 replication-maintained
                        materialized query table defined on one of the
                        tables in the query and the current replication
                        latency is larger than the time value specified in
                        the CURRENT REFRESH AGE special register.
Diagnostic Identifier:  2
Diagnostic Details:     EXP0148W  The following MQT or statistical view was
                        considered in query matching: "DTW     ".
                        "TRADE_SHADOW".

Query is routed to a shadow table

After changing the value of CURRENT REFRESH AGE to 500 (5 minutes), the SHADOW_LATENCY view that was created in Figure 1 shows that the latency of the replication between the source table and the shadow table (3.57 seconds) falls within the user-defined limit. The latency check succeeds because 3.57 seconds is less than 5 minutes. When the query is reissued, it is routed to the shadow table.

SET CURRENT REFRESH AGE 500;

SELECT LATENCY_CHECK, REFRESH_AGE, LATENCY FROM SHADOW_LATENCY;

LATENCY_CHECK REFRESH_AGE LATENCY  
------------- ----------- -------- 
            1  500.000000 3.572204 

  1 record(s) selected.

The access plan shows that the total cost of running this query is 175823 timerons. The cost is 77% less than the total cost for the access plan that does not route to the shadow table.

Access Plan:
-----------
        Total Cost:             175824
        Query Degree:           8

        Rows 
       RETURN
       (   1)
        Cost 
         I/O 
         |
         12 
       LMTQ  
       (   2)
       175824 
       2274.4 
         |
         12 
       TBSCAN
       (   3)
       175810 
       2274.4 
         |
         12 
       SORT  
       (   4)
       175810 
       2274.4 
         |
         12 
       CTQ   
       (   5)
       175809 
       2274.4 
         |
         12 
       GRPBY 
       (   6)
       175809 
       2274.4 
         |
      4.28e+06 
       TBSCAN
       (   7)
       79080.6 
       2274.4 
         |
      4.28e+06 
 CO-TABLE: DTW     
    TRADE_SHADOW
         Q1

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

Diagnostic Identifier:  1
Diagnostic Details:     EXP0148W  The following MQT or statistical view was
                        considered in query matching: "DTW     ".
                        "TRADE_SHADOW".
Diagnostic Identifier:  2
Diagnostic Details:     EXP0149W  The following MQT was used (from those
                        considered) in query matching: "DTW     ".
                        "TRADE_SHADOW".