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:
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:
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.
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".