Db2 Tools for z/OS

Using a query accelerator like the IBM Db2 Analytics Accelerator for z/OS, can speed up query processing of eligible queries run from QMF.   Dynamic queries run through the QMF RUN QUERY command can benefit from this performance boost.

Through special register, command and global variable support, QMF for TSO/CICS helps users access, maintain and create tables that reside on an accelerator.

Special register support

QMF currently supports the following Db2 for z/OS special registers directly related to accelerator usage:

• CURRENT QUERY ACCELERATION
• CURRENT GET_ACCEL_ARCHIVE
• CURRENT ACCELERATOR (new)

In order to access existing tables on an accelerator, the CURRENT QUERY ACCELERATION special register must be set to the appropriate value for query acceleration. If the default value is not set to the desired value, QMF helps by allowing users to change the special register value from within a QMF session.

The following query could be run to set the CURRENT QUERY ACCELERATION special register:

To specify whether a query that references a table that is archived on an accelerator should use archived data, the CURRENT GET_ACCEL_ARCHIVE special register can be set and managed from within QMF as well.

In support of Db2 FL509, to target DML statements on an accelerator-only table that is defined in multiple accelerators, the CURRENT ACCELERATOR special register must specify one of the accelerators in which the table is defined.  With QMF 12 APAR PH34437, QMF allows the CURRENT ACCELERATOR special register to be set from the SQL query panel. 

For more information on the CURRENT QUERY ACCELERATION, CURRENT GET_ACCEL_ARCHIVE and CURRENT ACCELERATOR special registers, see the Db2 documentation found in the IBM Knowledge Center. 

Creation of accelerator-only tables

With the QMF SAVE DATA command, users can now specify that the target table be created as an accelerator-only table (AOT).  Specifying the ACCELERATOR keyword on the SAVE DATA command will create an AOT on the specified accelerator.

The screenshot below shows an example QMF procedure that creates an AOT called NEWTABLE_AOT on accelerator IDAAS07:

The ACCELERATOR keyword is also available to users running the TABLE keyword on the RUN QUERY command.  Using this method to create an AOT will move the entire process of retrieving data and inserting rows completely to the data base, which can reduce storage requirements and additionally improve performance.  All tables in the query must also reside on the accelerator in order for the statement to execute.

The screenshot below shows an example QMF procedure which creates an AOT using the RUN QUERY method. The AOT created is named NEWTABLE2_AOT. The accelerator name is specified using the QMF global variable DSQEC_SAV_ACCELNM. Note that the ACCELERATOR keyword could also have been used.

The IMPORT TABLE may also be used with the ACCELERATOR keyword to create AOTs as well.

QMF global variables in support of accelerators

• DSQEC_SAV_ACCELNM – Specifies a default accelerator name when the accelerator keyword is not used.
• DSQEC_SAV_ALLOWED – Controls whether users can save data to a database or accelerator only table (or both).
• DSQEC_SAV_ACCELDB – Defines the database in which the AOT is created.