You are currently on IBM Systems Media’s archival website. Click here to view our new website.

MAINFRAME > Administrator > Performance

Accelerator-Only Tables Enhance Speed on IBM DB2 Analytics Accelerator

Setup: Subsystem Parameter

Next, we’ll discuss something that confused me terribly. I spent a ton of time reading, rereading and sharing notes with my counterparts with the Analytics on z Systems Center of Excellence at IBM Boeblingen Laboratory in Germany. I have to thank Patric and Ute for straightening me out. It wasn’t easy for them I’m sure.

Enough on the tangent, it’s time to get back to the topic at hand. My issues started with a subsystem parameter (ZPARM) that I think will also get your attention.

The subsystem parameter QUERY_ACCEL_OPTIONS on the DSN6SPRM macro, and on the DSNTIP82 installation panel as the ACCELERATION OPTIONS field, enables a couple of possible functions. Its default is NONE which basically turns off this ZPARM. The value we’re interested in, or rather should probably not be interested in, is 2 and was added to this ZPARM by APAR PM60921 in September 2013. By setting QUERY_ACCEL_OPTIONS=2, DB2 allows the SELECT portion of an INSERT… WITH SELECT to be run on an Analytics Accelerator. The primary way one might load an accelerator-only table is using an INSERT… WITH SELECT. I assumed it had to be set to 2.

However, it turns out that’s not always true. If you’re using accelerator-only tables, and because the INSERT… WITH SELECT can only run on an Analytics Accelerator, it doesn’t matter how you set QUERY_ACCEL_OPTIONS. In fact, you can set it to NONE and still use the SELECT… WITH INSERT to copy rows into your accelerator-only table. That means that selecting from an accelerator-only table and inserting into an accelerator-only table or selecting from an accelerator-shadow DB2 table and inserting into an accelerator-only table, the value of QUERY_ACCEL_OPTIONS does not come into play. You can always perform an SQL INSERT, UPDATE or DELETE against an accelerator-only table. In addition, the INSERT and SELECT portions will both run on the accelerator.

If you throw non-accelerated DB2 tables into the mix, changing the value specified on the QUERY_ACCEL_OPTIONS subsystem parameters does affect the outcome. Specifying a non-accelerated DB2 table on the SELECT and either an accelerator-shadow DB2 table or non-accelerated DB2 table will cause both parts of the INSERT… WITH SELECT to run on DB2 for z/OS. There are other combinations that will cause different situations to occur, all with different results. They can be found in the “Special Register, ZPARM and BIND options” section of the DB2 Analytics Accelerator documentation.

The last setup step you might have to consider completing is only necessary if you want to use an accelerator-archived table as input to the INSERT... WITH SELECT. There are three methods available to enable INSERT... WITH SELECT. In all three choices, the subsystem parameter, bind option or special register should be set to YES in enable a query to read from an accelerator-archived table. Setting them to NO will cause the SQL statement to fail.

In order of precedence, the special register CURRENT GET_ACCEL_ARCHIVE is checked first. If set to YES, accelerator-archived table reads are allowed. If the special register is not specified, the initial value is set to whatever is coded on the bind option or the subsystem parameter. If the bind option GETACCELARCHIVE is specified, it is used rather than the subsystem parameter. There is no default for the bind option. The value used if the previous two are not specified is the subsystem parameter GET_ACCEL_ARCHIVE on the DSN6SPRM macro and on the DSNTIP82 installation panel.

This concludes the introduction to accelerator-only tables. In part two I talk about how accelerator-only tables can be put to use.

Willie Favero is currently an IBM Senior Certified IT Software Specialist and the DB2 for z/OS SME for the Data Warehouse on System z Swat Team at IBM's Silicon Valley Lab.

Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.



2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Accelerating Enterprise Application Modernization

Modernizing existing applications rather than replacing them is a time-tested approach to competitive advantage in the financial-services industry.

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
Mainframe News Sign Up Today! Past News Letters