DB2 acceleration and optimization special registers

Two optimisation stories

Accelerator registers decide whether a dynamic query is shipped to an accelerator appliance (or appliance cluster), which appliance is preferred, whether archived partitions are visible, and how long to wait for replication.

MQT registers decide whether Db2 may rewrite a dynamic query to read a materialized query table instead of the base tables. They do not send work off-platform.

Static SQL does not read these registers for the actual bound statements. Use bind options QUERYACCELERATION, GETACCELARCHIVE, ACCELERATOR, and ACCELERATIONWAITFORDATA. Those bind options can also seed the dynamic special registers when the package runs, unless the application already issued SET.

Precedence for the acceleration registers is typically: subsystem parameter (lowest), bind option if specified, then an explicit SET in the application (highest).

CURRENT QUERY ACCELERATION

CURRENT QUERY ACCELERATION says when Db2 sends dynamic SQL to an accelerator and what happens if that accelerator fails. Data type VARCHAR(255). It does not apply to static SQL.

ENABLE and ENABLE WITH FAILBACK use cost and heuristics (including table size and profile estimates) so a tiny lookup is not sent to the accelerator. ELIGIBLE and ALL skip that cost test: if the query is eligible, it goes. ALL is the strictest — queries that cannot be accelerated fail instead of running in Db2. That is useful when the data lives only on the accelerator (accelerator-only tables) and a silent fallback would be wrong.

Accelerator-only tables require a register value other than NONE or the statement cannot succeed. ENABLE WITH FAILBACK has a restriction with passthrough-only expressions (function level 504): Db2 returns an error and does not accelerate even if a matching user-defined function exists.

1
2
3
4
5
SET CURRENT QUERY ACCELERATION NONE;
SET CURRENT QUERY ACCELERATION ENABLE;
SET CURRENT QUERY ACCELERATION = 'ENABLE WITH FAILBACK';
SET CURRENT QUERY ACCELERATION ELIGIBLE;
SET CURRENT QUERY ACCELERATION ALL;

Initial value: ZPARM QUERY_ACCELERATION (IBM default NONE), then QUERYACCELERATION bind option if present, then SET.

CURRENT QUERY ACCELERATION WAITFORDATA

CURRENT QUERY ACCELERATION WAITFORDATA is the maximum time, if any, that the accelerator delays a dynamic query while it waits for replication of committed Db2 changes that happened before the query ran. This is the HTAP “do not read yesterday’s copy” knob.

Data type DECIMAL(5,1) as nnnn.m seconds. Valid range 0.0–3600.0 (up to 60 minutes). You may also SET an INTEGER 0–3600; Db2 converts it to DECIMAL(5,1).

• 0.0 (default) — do not delay; run immediately; other WAITFORDATA behaviour is not applied
• 20.0 — wait up to 20 seconds (20000 milliseconds)
• 30.5 — wait up to 30.5 seconds

1
2
SET CURRENT QUERY ACCELERATION WAITFORDATA 180;
-- wait up to three minutes for replication

Initial value: ZPARM QUERY_ACCEL_WAITFORDATA (default 0.0), then ACCELERATIONWAITFORDATA bind option if specified, then SET.

CURRENT ACCELERATOR

CURRENT ACCELERATOR names a preferred accelerator for dynamic SQL. Data type VARCHAR(8). Default is blank: no preference, so workload balancing can pick a server by queue length.

When the same tables are accelerated on more than one appliance, SET CURRENT ACCELERATOR = ACCEL1 tells Db2 to consider that server first. If ACCEL1 is down, Db2 still considers other available accelerators. Use this to send high-priority work to a local, high-capacity box and keep remote disaster-recovery accelerators out of the first hop (WLB does not model that extra latency).

For DML on an accelerator-only table defined on multiple accelerators (function level 509), CURRENT ACCELERATOR must name one of the accelerators that actually hold the table. Static SQL uses the ACCELERATOR bind option; that option has no default value.

1
2
SET CURRENT ACCELERATOR = ACCEL1;
SELECT CURRENT ACCELERATOR FROM SYSIBM.SYSDUMMY1;

CURRENT GET_ACCEL_ARCHIVE

CURRENT GET_ACCEL_ARCHIVE controls whether a dynamic query that references a table archived on an accelerator (High-Performance Storage Saver) uses that archived data. Data type VARCHAR(255). Does not apply to static SQL (use GETACCELARCHIVE bind).

• NO — archived data is not included (IBM default for ZPARM GET_ACCEL_ARCHIVE)
• YES — the table reference includes archived data

CURRENT QUERY ACCELERATION must still be something other than NONE or the moved data cannot be accessed. YES without acceleration enabled does not magically read archive partitions from Db2 DASD that is no longer there.

1
2
SET CURRENT GET_ACCEL_ARCHIVE = NO;
SET CURRENT GET_ACCEL_ARCHIVE = YES;

CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION

This register identifies which materialized query table types may be considered to optimize dynamic SQL. Static embedded SQL never consults it. Data type VARCHAR(255).

SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION (the example form SET CURRENT MAINTAINED TABLE TYPES ALL is valid) takes a keyword:

• SYSTEM — system-maintained MQTs (usual installation default from CURRENT MAINT TYPES / MAINTYPE on DSNTIP8)
• USER — user-maintained MQTs (LOAD / INSERT / UPDATE / DELETE as well as REFRESH TABLE)
• ALL — both system- and user-maintained
• NONE — do not consider MQTs for rewrite

1
2
SET CURRENT MAINTAINED TABLE TYPES ALL;
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION = SYSTEM;

An MQT created with DISABLE QUERY OPTIMIZATION is never eligible, regardless of this register. Automatic query rewrite applies to dynamically prepared read-only queries that meet IBM’s block-level rules (no outer join in the block, no RAND, no EXTERNAL ACTION / NON-DETERMINISTIC user function, and so on).

CURRENT REFRESH AGE

CURRENT REFRESH AGE is a timestamp duration: the maximum time since REFRESH TABLE on a system-maintained REFRESH DEFERRED MQT such that the MQT can still be used to optimize a query. Data type DECIMAL(20,6). It also affects dynamic statement cache matching.

ANY is the practical “consider deferred MQTs” setting. It is stored as duration 99999999999999 (9999 years, 99 months, 99 days, 99 hours, 99 seconds). Installation default is often 0, which does not treat refresh-deferred MQTs as current.

User-maintained MQTs still need CURRENT REFRESH AGE ANY before deferred data is considered. SET both this register and MAINTAINED TABLE TYPES when you test rewrite.

1
2
3
SET CURRENT REFRESH AGE ANY;
VALUES (CURRENT REFRESH AGE) INTO :CURMAXAGE;
-- ANY appears as 99999999999999.000000

If you set the register through a JDBC/CLI specialRegisters interface, specify ANY; the 14-digit numeric form is not supported on that path.

Putting a session together

1
2
3
4
5
6
7
SET CURRENT QUERY ACCELERATION ELIGIBLE;
SET CURRENT ACCELERATOR = ACCEL1;
SET CURRENT GET_ACCEL_ARCHIVE = NO;
SET CURRENT QUERY ACCELERATION WAITFORDATA 5.0;
 
SET CURRENT MAINTAINED TABLE TYPES ALL;
SET CURRENT REFRESH AGE ANY;

That session prefers accelerator ACCEL1 for eligible dynamic queries, does not wait long for replication, ignores HPSS archives, and allows MQT rewrite for both system and user MQTs. Production shops usually set these with profile tables or JDBC URL specialRegisters= so application source stays unchanged.

Explain It Like I'm Five

Imagine two shortcuts for a long homework question. The accelerator is a super-fast friend in another room. CURRENT QUERY ACCELERATION is the rule for when you are allowed to pass the question to that friend: never, only when the teacher thinks it is worth the walk, or always if the friend can do that kind of question. CURRENT ACCELERATOR is which friend’s desk you try first. GET_ACCEL_ARCHIVE is whether the friend may look in the dusty box of last year’s papers. WAITFORDATA is how long you wait for the friend to copy today’s answers before they start. MQTs are cheat-sheets already filled in. MAINTAINED TABLE TYPES says whose cheat-sheets you may use, and REFRESH AGE says how old a cheat-sheet is allowed to be before you must work from the real textbook again.

Exercises

1. Select CURRENT QUERY ACCELERATION, CURRENT ACCELERATOR, CURRENT GET_ACCEL_ARCHIVE, CURRENT REFRESH AGE, and CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION on your system. Match them to ZPARMs with your DBA.
2. Explain to a teammate the difference between ENABLE, ELIGIBLE, and ALL using a reporting query versus an OLTP primary-key lookup.
3. Write the SET pair required before you expect automatic query rewrite to a user-maintained REFRESH DEFERRED MQT.
4. Predict what happens if GET_ACCEL_ARCHIVE is YES but QUERY ACCELERATION is NONE for a table whose partitions were moved with HPSS.
5. Find whether your JDBC data source already sets queryAcceleration or specialRegisters in the connection URL.