Accelerator-Only Tables Enhance Speed on IBM DB2 Analytics Accelerator
This is part one of a two-part series on accelerator-only tables, a recent enhancement to the IBM DB2 Analytics Accelerator appliance. Part two talks about how they can be put to use.
Since I spend much of my time running performance measurements on the IBM DB2 Analytics Accelerator, with most of those measurements usually around loading data into the Analytics Accelerator, it’s probably not all that difficult to understand why I get so excited (and a bit carried away) talking (writing) on the subject. This latest DB2 Analytics Accelerator for z/OS enhancement delivered by PTF 5 is no exception and is the motivation behind this article. This is fascinating technology that’s going to benefit anyone who likes “fast.”
Of course, I realize that the Analytics Accelerator is a specialized device that not everyone has, at least not yet (but give them time). If you don’t own a couple yet or even if you’re not looking, you may still be wondering what this thing called an Analytics Accelerator is. Let me give a quick description using just a few catchphrases that:
- You can run complex queries up to 2,000 times faster. Yup, that is 2,000 times.
- It can be an alternative less expensive mechanism for storing less frequently used data
- It could improve your z Systems capacity by moving query workload to the accelerator
- It will definitely provide the superior security you get by not moving data off platform
- You can use it to implement both hybrid transactions and analytical processing on z Systems
Everyone should take advantage of any opportunity to hear why this appliance is so cool. See a list of resources in the “reference material” box.
For those who already have an accelerator or those that are thinking about getting one, IBM has delivered some pretty awesome enhancements recently; something the lab refers to as PTF 5 for the IBM DB2 Analytics Accelerator V4.1. Find a list of what’s in PTF 5. In this article I’m discussing one specific enhancement: accelerator-only tables.
A simple definition of an accelerator-only table is an object (a place to store data) that can exist only on the IBM DB2 Analytics Accelerator. Even though an accelerator-only table is created using SQL Data Definition Language (DDL) on DB2 for z/OS, and the CREATE process does insert an entry into SYSIBM.SYSTABLES, SYSIBM.SYSCOLUMNS, and SYSACCEL.SYSACCELERATEDTABLES, the accelerator-only table itself can only be referenced from within the accelerator and data for that accelerator-only table will only exists in the Analytics Accelerator. When working with an accelerator-only table, no data is stored in DB2 for z/OS at any time. Any SQL statement that references an accelerator-only table (SELECT, INSERT, UPDATE or DELETE), must run on the accelerator. If it attempts to run anywhere else, it will receive an error.
There are many restrictions when using accelerator-only tables that you should be aware and I’ll get to those in just a minute. A detailed list of restrictions and other rules that exist around the use of an accelerator-only table can be found in the “What to Watch For” box, although the list is not all inclusive. However, for now there is one restriction that I think you have to be aware of right up front and that’s the DB2 release level that supports accelerator-only tables. When accelerator-only tables were first delivered they were only available with DB2. However, accelerator-only table support for DB2 11 for z/OS recently became available via the maintenance stream. The PTFs required to enable accelerator-only tables in DB2 11 are listed in the “Reference Materials” box.
Accelerator-Only Table Uses
Using accelerator-only tables is going to be very exciting. The list of possibilities of what you could accomplish with them goes on. First, create an accelerator-only table. The create process is very similar to creating any other kind of table in DB2 with the exception of the restrictions you have to watch for. To create an accelerator-only table:
CREATE TABLE table_name
(all of the column definitions)
IN ACCELERATOR accelerator_name
IN DATABASE database_name
Of course, you'll probably have a much more extensive and descriptive column definition section. You may also choose to include other allowable CREATE keywords like CCSID. This could be good to include because data cannot move between EBCDIC and Unicode tables.
So far, this is pretty much like creating most any other table in DB2 except that:
- The CREATE TABLE clause identifies the name of the accelerator-only table.
- The (all the column definitions) will be your list of on or more columns included in the accelerator-only table.
- The IN ACCELERATOR clause tells DB2 it’s an accelerator-only table and the accelerator_name tells DB2 on which attached Analytics Accelerator to create the accelerator-only table. DB2 will mark this as an accelerator-only tablewith a “D” in the TYPE column of SYSIBM.SYSTABLES.
- The IN DATABASE clause is optional. However, I strongly suggest coding it so you can control the name of the database implicitly created rather than having DB2 generate a database name. This is going to make managing the table later a bit more straightforward.
- Although optional, you should consider using a schema name with the table name to make manageability of your accelerator-only table easier.
Once the DDL CREATE SQL statement completes successfully all actions against or with this table will occur only on the named accelerator. Remember that this table cannot be referenced in any SQL DML statement outside of it.
A few other things necessary to watch out for are that CREATE statements have to be in their own unit of work, the accelerator-only table cannot be enabled for incremental update or selected for archiving, and multi-row INSERT is not allowed.
You now have an accelerator-only table defined in the Analytics Accelerator. This is the fun part: getting data into your accelerator-only table. The table doesn’t exist outside the Analytics Accelerator so the traditional method of running a stored procedure load won’t be of help. Our table, although defined in DB2, contains no data (rows) on the DB2 for z/OS subsystem.
Setup: Query Acceleration
There are a few more steps that need to be completed before you can start messing around with accelerator-only tables if you expect to get all this to work correctly. Two subsystem parameters/bind options/special registers must be considered before proceeding and there’s one optional subsystem parameters/bind options/special registers that you may want to look at.
The first step is to establish when and if DB2 should be sending SQL to the Analytics Accelerator. There are three methods to enable query acceleration described here in order of precedence. Enabling query acceleration can be set explicitly with the CURRENT QUERY ACCELERATION special register for dynamic SQL.
For static SQL, the bind option QUERYACCELERATION can be set. This bind option will also set the current value of the QUERY ACCELERATION special register for any dynamic SQL in this package. This is an optional bind option with no default. If not specified, static SQL is not considered for acceleration and dynamic SQL will use whatever value is coded in the subsystem parameter QUERY_ACCELERATION.
You can set enablement for dynamic SQL implicitly with the use of the subsystem parameter QUERY_ACCELERATION on the DSN6SPRM macro. This value can also be set on installation panel DSNTIP82. When the CURRENT QUERY ACCELERATION special register isn’t specified, this become the value use to determine the behavior of dynamic SQL.
The above variations of enabling query acceleration use the NONE or ALL set of values.
NONE tells DB2 that SQL won’t run on the Analytics Accelerator; not a good choice if you are going to be using accelerator-only tables having ALL of the data in the Accelerator only. Specifying this option will cause the SQL statement to fail. The subsystem parameter QUERY_ACCELERATION will use a 1 rather the word NONE.
ALL can be tricky that you'll have to give some thought. Although it is valid for accelerator-only tables, you have to keep it mind that by specifying ALL you are telling DB2 to run all eligible SQL on the accelerator. This is all fine and dandy as long as all of the tables the SQL will need are on the accelerator. If one or more of those necessary tables aren’t on the accelerator, the SQL statement will fail.
Plus there's that "eligible" thing. With ALL, if a SQL statement is not eligible to run on the Analytics Accelerator, it doesn't run at all. Of course, the good news is if you want all your SQL to run on the accelerator and all of the necessary tables are on it, ALL becomes a terrific option. DB2 doesn't spend time trying to determine if it’s more or less costly (CPU wise) to run the SQL statement on the accelerator. DB2 can send all eligible statements directly to the accelerator.
There is also another reason for specifying ALL that many might find of interest: how it could affect a BIND or REBIND PACKAGE. When ALL is specified on the BIND’s QUERYACCLERATION keyword, it’s guaranteed that the package’s static SQL statements will always access the data in the accelerator. This will prevent some of the SQL SELECT statements from running on DB2 and others running in the Analytics Accelerator, potentially returning different answer sets. With ALL specified, if it’s determined that a SQL needs to reference data located on DB2, the BIND operation fails.
Specifying the BIND keyword QUERYACCELERATION with ENABLE, ELIGIBLE or ENABLEWITHFAILBACK could potentially up an application’s logic and/or data if rows are found by one statement then are not there for another. The subsystem parameter QUERY_ACCELERATION will use a 5 rather the word ALL.
The three values you want to use are ENABLE, ENABLE WITH FAILBACK and ELIGIBLE. Both ENABLE values tell DB2 to send the query to the Analytics Accelerator if DB2 determines they might perform better there. The WITH FAILBACK option on the ENABLE value tells DB2 what to do if the SQL statement fails in the PREPARE or OPEN phase. If just ENABLE is specified the query fails and the application is notified. If ENABLE WITH FAILBACK is used, DB2 will attempt to run the query in the DB2 without trying to use the Accelerator.
ENABLEWITHFAIL is how the options looks when specified on a bind. ELIGIBLE simply sends the query to the accelerator if it's an eligible query without giving the query's cost any consideration. The subsystem parameter QUERY_ACCELERATION will use a 2 in place of ENABLE, 3 in place of ENABLE WITH FAILBACK and 4 for ELIGIBLE.
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.
comments powered by