Gear up with IDAA: IBM DB2 Analytics Accelerator

The IBM® DB2® Analytics Accelerator for IBM z/OS® (IDAA) combines IBM System z®, DB2 for z/OS and IBM PureData™ System for Analytics technologies to gear up complex queries in a DB2-for-z/OS environment and deliver unparalleled, mixed-workload performance for complex business analytics. IBM System z solutions process more than two-thirds of the world’s business data—and the portion is increasing. By using IDAA with System z, you get a modern environment that helps create fast insights from large volumes of data and deliver those insights at the point of contact.

When do you need IDAA?

Some organizations will opt for IDAA to alleviate end-user frustration with slow queries. However, IDAA can also address a number of additional challenges. For example, it might be the right solution when you need to:

• Analyze large quantities of data quickly
• Eliminate DBA and data design work in analysis, index design, query rewriting, and so on
• Reduce monthly licensing charges by offloading complex query workloads

IDAA is an appliance that adds another Resource Manager to DB2 for z/OS, just like Lock Manager or Data Manager. It can be seen as an additional access path for dynamic SQL statements in DB2 for z/OS. No changes are required to existing applications because neither users nor applications are, or need to be, aware of its existence to take advantage of its capabilities. Whenever queries are eligible for being processed by IDAA, users will immediately benefit from shortened response times without any further actions.

Loading data into IDAA

Keep in mind that data is loaded into IDAA from DB2 for z/OS—the accelerator contains a snapshot of data. Queries accessing data on IDAA need to tolerate this characteristic. Data is loaded into IDAA using stored procedure ACCEL_LOAD_TABLES. This stored procedure calls the UNLOAD utility to unload data from DB2 for z/OS tables and push them through UNIX System Services pipes to the IDAA.

Validating queries without IDAA

The correct value in the special register CURRENT QUERY ACCELERATION determines if the query is going to be executed in IDAA or not. The default value is NONE, which specifies that no query acceleration is done. ENABLE means that queries are accelerated only if DB2 determines that it is advantageous to do so.

With the help of virtual accelerators, you can check if your workloads are suitable for acceleration and how beneficial acceleration will be. You don’t need a real accelerator to test workloads on a virtual accelerator, because the results produced by virtual accelerators are written to EXPLAIN tables. After you install a virtual accelerator in an environment where the IDAA is not yet installed, you do a usual EXPLAIN with SET CURRENT QUERY ACCELERATION ENABLE. If the REASON_CODE column of the DSN_QUERYINFO_TABLE is 0, it means the query is eligible for IDAA offload.

Monitoring IDAA

DB2 instrumentation support for IDAA does not require any special traces, additional classes, or instrumentation facility component identifiers (IFCIDs) to be started. Data collection is implemented first by applying the required software maintenance and new performance counters in IFCID 2 and 3 that can be reported in OMEGAMON® XE for DB2 Performance Expert on z/OS (OMPE) Batch Reports, and then by adding new fields to the existing trace classes.

Historically, when a thread is executed in DB2, you should look for the Not Accounted time in the header of the OMPE Accounting Long Trace—a high Not Accounted time may indicate insufficient CPU capacity or that the workload is being executed under a low-priority service class. Distributed requests being offloaded to the IDAA will also report the time spent in the accelerator as Not Accounted time. You must refer to the accelerator section of the OMPE report to determine if the reported Not Accounted time is time spent by requests in the accelerator or another situation, such as CPU constraints.

While OMPE reports are one way of monitoring IDAA, you can also do it through DB2 commands. The DISPLAY ACCEL command displays information about accelerator servers. In particular situations and through specific interfaces, you can change the status of the PureData System for Analytics server, which might be required for software maintenance. This means that DB2 Analytics Accelerator may be running while PureData System for Analytics is unavailable. You can use the DIS ACCEL command to inspect the status of the PureData System for Analytics server.

DISPLAY THREAD shows current status information about DB2 threads, with the extended ACCEL option limiting the list to threads with active accelerator processes executing within the specified accelerator server. With the logical-unit-of-work identifier assigned to the database access thread and IP address, the originating requester server and appliance details can be identified easily.

Identifying queries that qualify for IDAA offload

When does a query qualify for IDAA offload? DB2 Optimizer uses a set of rules to determine if a given query is better off being executed in the DB2 core engine or routed to the accelerator.

For example:

• Typical online transaction processing (OLTP) access path patterns (such as equal-unique access and one-fetch access) are not routed to the accelerator.
• If none of the WHERE, GROUP BY, or ORDER BY aggregate functions is specified (that is, all rows are to be returned), the query is not routed to the accelerator.
• If all the tables referred to in the query are “small,” the query is not routed to the accelerator. SMALLTABLE_THRESHOLD (the number of pages) defines a “small” table.
• If a “large” result set is expected, the query is not routed to the accelerator. RESULTSET_THRESHOLD (the number of rows) defines a “large” result set.

A query can be routed to IDAA if:

• The associated cursor is not defined as a scrollable or a rowset cursor.
• The query is defined as read-only.
• The query is dynamic.
• The query is a SELECT statement.
• The private protocol is not in effect.

Conclusion

Contrary to a widely-held belief, no architecture can handle all types of analytics workloads well; each architecture and technology comes with strengths and weaknesses. However, DB2 Analytics Accelerator can significantly speed execution of queries and delivery of key information to the users and applications that need it.

Does your organization need to analyze large quantities of data on an ad hoc basis? Could you reduce your monthly licensing charges by offloading complex query workloads? Let us know in the comments.