DB2 Analytics Accelerator for z/OS

Query acceleration in plain language

An accelerator server is a paired engine: historically a Netezza appliance, later including IBM Z based deployments. Db2 sends a query tree; worker nodes scan data slices in a shared-nothing layout. You do not rewrite SQL as MapReduce. You enable routing and load tables.

Applications still connect to Db2. If routing is on and the statement qualifies, Db2 ships it. If not, Db2 runs it locally. That is why ENABLE WITH FAILBACK exists: a down accelerator should not take down the nightly report if Db2 can still finish the SQL.

Table types on the accelerator

Accelerator-shadow tables

You add an existing Db2 base table to an accelerator. Catalog tables track the shadow. After load, SELECT against the Db2 table can be evaluated on the shadow. You can enable/disable acceleration per table so one reload does not stop the whole appliance. Views, clones, and some special table types are not valid shadows—IBM lists the exclusions in the User’s Guide.

Accelerator-only tables

CREATE TABLE … IN ACCELERATOR creates a table whose pages never sit in a Db2 VSAM linear data set. You set a distribution key on that CREATE so joins colocate. DML and SELECT require CURRENT QUERY ACCELERATION not NONE. AOTs are for data marts and in-database transformation that would be expensive to store twice.

Accelerator-archived tables

High Performance Storage Saver archives partitions to the accelerator and can remove them from Db2 DASD. Queries that need old years still run if they are accelerated. Restoring archived partitions is a documented reverse path when you need the data back on Db2.

CURRENT ACCELERATOR and QUERYACCELERATION

CURRENT QUERY ACCELERATION (special register) and ZPARM QUERY_ACCELERATION use the same value set. The ZPARM is the default for new threads. SET CURRENT QUERY ACCELERATION overrides for a session. JDBC/ODBC can set it on the URL or data source so you do not change application source.

CURRENT ACCELERATOR names which accelerator to prefer when more than one is paired (location/alias). Accelerator aliases and accelerator locations appear in catalog/accelerator admin tables and in Studio. SET CURRENT ACCELERATOR = NULL lets Db2 pick.

1
2
3
4
5
6
SET CURRENT QUERY ACCELERATION = ENABLE WITH FAILBACK;
SET CURRENT ACCELERATOR = 'ACCEL1';
 
SELECT YEAR, SUM(AMOUNT)
  FROM FIN.TRAN
 GROUP BY YEAR;

Static SQL can use QUERYACCELERATION bind options so packages offload without a special register. Profile tables can set the register for specific AUTHIDs or IP addresses.

Load, synchronization, incremental updates

After you define a shadow, state is often InitialLoadPending. Accelerator load copies rows (full table or partitions) until state is Loaded. LoadInProgress / Error are operational states you monitor.

Accelerator synchronization after the first load is either:

• Reload — another full or partition load (schema changes, catching up after replication was off)
• Incremental updates — log-based replication so inserts/updates/deletes on Db2 flow to the shadow with short delay

Archived data uses the archive workflow, not a normal INSERT. Always load before you expect accelerated results—empty shadows do not magically answer SUM().

Restrictions, data types, performance, troubleshooting

Accelerator-supported data types cover the usual CHAR/VARCHAR, integers, decimal, date/time, and many others; some types (certain LOBs, distinct types, XML in older levels) are skipped. If a referenced column is unsupported, that query is not accelerated even if other columns are fine.

Accelerator restrictions also include SQL constructs (some OLAP, some user-defined functions, row permissions interactions depending on level). Distribution keys and organizing keys matter for accelerator performance: colocate join keys on the same worker; organizing keys cluster filter columns so scans skip blocks.

Accelerator monitoring: DISPLAY THREAD shows accelerated work; EXPLAIN populates DSN_QUERYINFO_TABLE with YES/NO and a reason; statement cache column ACCEL_OFFLOAD_ELIGIBLE helps modeling even before you buy hardware. Accelerator Studio and stored procedures (ACCEL_* ) report disk, query queues, and table states.

Accelerator troubleshooting: if nothing offloads, check NONE vs ENABLE, whether the table is Loaded, whether the SQL references an unsupported column, and heuristics (tiny tables often stay on Db2 under ENABLE). If ENABLE WITH FAILBACK silently runs on Db2, look at SYSIBM.SYSACCEL_* and job logs for network/timeout on first OPEN.

1
2
3
4
5
6
EXPLAIN ALL SET QUERYNO = 100 FOR
  SELECT COUNT(*) FROM FIN.TRAN WHERE YEAR = 2024;
 
SELECT REASON, QI_DATA
  FROM DSN_QUERYINFO_TABLE
 WHERE QUERYNO = 100;

Explain It Like I'm Five

Db2 is the school office that takes every question. The accelerator is a giant gym full of kids who are extremely fast at counting. Shadow tables are photocopies of the classroom lists in the gym. Accelerator-only tables are lists that live only in the gym. Archived tables are last year’s lists moved to the gym so the office closet (DASD) is free. ENABLE means “use the gym if it is quicker.” FAILBACK means “if the gym lights are out, count in the office.” ALL means “the gym or bust.” Incremental update is a runner who whispers every new gold star to the gym so the photocopy stays almost current.

Exercises

1. Set CURRENT QUERY ACCELERATION to each of the five values and predict what happens if the accelerator is down during PREPARE.
2. Contrast a shadow table with CREATE TABLE … IN ACCELERATOR in three bullets.
3. Use EXPLAIN and DSN_QUERYINFO_TABLE for a GROUP BY query and record the reason code.
4. List two reasons ENABLE would keep a query on Db2 even though the table is Loaded.
5. Describe when you would reload a partition versus relying on incremental update.