White Papers
Abstract
This document describes various procedures to assess whether an individual SQL query or a set of SQL queries is eligible for processing by IBM Db2 Analytics Accelerator for z/OS.
Content
- You have not used IBM Db2 Analytics Accelerator yet, but you want to analyze whether existing Db2 for z/OS analytic queries or workloads would benefit from acceleration.
- You already use IBM Db2 Analytics Accelerator, but you want to know whether new analytic queries or workloads would benefit from acceleration before you prepare accelerators for running these queries or workloads.
Procedure Overview
The accelerator modeling procedure requires the setting of the ZPARM parameter ACCELMODEL=YES. Eligibility for cached dynamic queries is reported in the DSN_STATEMENT_CACHE_TABLE table. It is most useful for a quick YES or NO eligibility assessment for all dynamic statements in the dynamic statement cache.
It can be used for static queries, too. In that case, it additionally requires a BIND or REBIND action for static queries, or an EXPLAIN statement for dynamic queries. This procedure is most useful for an eligibility assessment or reasons for non-eligibility for specific dynamic queries or static queries. Eligibility for explained queries is reported in the DSN_QUERYINFO_TABLE table.
IBM Optim Query Workload Tuner for z/OS V4.1.1 or later uses accelerator modeling to assess dynamic or static workloads. A graphical user interface allows you to assess the eligibility of dynamic and static workloads fast and easily. The product offers accelerator modeling in a single tool. Furthermore, it provides “what-if”-analysis capabilities to identify potential resource savings in a Db2 subsystem that could be realized by adding or moving certain tables to an accelerator and processing these tables on an accelerator.
With Db2 Query Monitor for z/OS V3.2 or later and APAR PI27548, it is possible to use accelerator modeling to assess dynamic or static workloads and provide the information in a graphical user interface of the tool. Db2 Query Monitor reports workloads as eligible if these would run on an accelerator. In addition, Db2 Query Monitor reports potential savings from the Db2 subsystem level down to the SQL statement level. Db2 Query Monitor offers all the previously mentioned accelerator modeling procedures.
With IBM Tivoli OMEGAMON XE for Db2 Performance Expert on z/OS V5.1.1 or later and APAR PM99691 or PI08288 (depending on the OMEGAMON version), it is possible to use accelerator modeling to estimate the CPU cost and the time needed to process eligible queries on an accelerator . OMEGAMON captures the CPU cost and the time needed to process eligible queries from the DB2 accounting record IFCID 3 and displays these metrics in accounting reports.
If you use IBM Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS V5.3 or later and APAR PI41417 and PI48095, you can assess query eligibility on the statement level using IFCID 316 or IFCID 401 and the RECTRACE or Performance Expert Client functionalities.
List of above described procedures:
- Accelerator modeling for assessing a dynamic query workload
- Accelerator modeling for assessing individual dynamic or static queries
- Using IBM Optim Query Workload Tuner for assessing dynamic or static query workloads
- Using Db2 Query Monitor for assessing dynamic or static query workloads
- Using IBM Tivoli OMEGAMON XE for Db2 Performance Expert on z/OS
The following table summarizes the main execution characteristics per procedure to help you choose the best procedure for your needs.
| Dynamic SQL support | Static SQL support | Assessment execution activity | Eligibility assessment result | Simulation of acceleration setting | |
| Workload | N/A | EXPLAIN STMTCACHE ALL | YES/NO per query in DSN_STATEMENT CACHE_TABLE | ENABLE (Default)
ELIGIBLE with CURRENT QUERY ACCELERATION special register and DB2 PTF |
|
| Individual queries | N/A | EXPLAIN ALL per query | Yes/No per query plus reason for ‘No’ in DSN_QUERYINFO_TABLE | ENABLE (Default)
ELIGIBLE with CURRENT QUERY ACCELERATION special register and DB2 PTF |
|
| N/A | All queries of a package | BIND/REBIND per package with EXPLAIN option | Yes/No per query plus reason for ‘No’ in DSN_QUERYINFO_TABLE | ENABLE (Default)
ELIGIBLE with QUERYACCELERATION BIND option and DB2 PTF |
|
| Optim Query Workload Tuner | Defined Workload | Defined Workload | Accelerator modeling or virtual accelerator in graphical user interface | Yes/No per query plus reason for ‘No’ in a graphical user interface combined with metrics for CPU cost, processing time and “what-if” analysis of eligibile workloads | ENABLE or ELIGBLE |
| Db2 Query Monitor | Workload | Workload | Accelerator modeling in graphical user interface | Yes/No per query plus reason for ‘No’ in a graphical user interface combined with metrics for CPU cost and processing time for eligible workloads | ENABLE |
| OMEGAMON for Performance Expert | Workload | Workload | Capturing of IFCID 3 accounting records and reporting | Metrics for CPU cost and processing time for eligible workloads per plan in accounting reports | ENABLE |
Procedure setup and execution steps
Accelerator modeling for assessing a dynamic query workload
Setup:
- For Db2 12 the APAR PH23848 fixes the occurrence of an extra row in DSN_QUERYINFO_TABLE when accelerator modeling is on.
Set the subsystem parameter ACCELMODEL to YES (the default is NO). This parameter can be changed online and activates the output of information, including information about potential query routing to an accelerator. If you use a data sharing group, set this parameter on all members.
In addition, set the CURRENT GET_ACCEL_ARCHIVE special register and the GET_ACCEL_ARCHIVE subsystem parameter to NO.
Accelerator modeling supports the values ENABLE, ENABLE WITH FAILBACK, or ELIGIBLE for the query acceleration setting for the following purposes:
- If you already have accelerators connected to your subsystem, accelerator modeling can be performed for new queries while other queries are routed to the accelerators.
- You can use the acceleration setting of ELIGIBLE to assess queries based on their SQL syntax. If the query acceleration setting is set to NONE, accelerator modeling assesses queries based on their cost (which corresponds to a query acceleration setting of ENABLE). This is the default behavior.
To collect and store information from the dynamic statement cache, create or re-create the table schema.DSN_STATEMENT_CACHE_TABLE. To do that, use the data-definition (DDL) SQL text provided by hlq.SDSNSAMP(DSNTESC). You must customize the DSNTESC job before executing it because new columns have been added that report acceleration eligibility, e.g. column ACCEL_OFFLOAD_ELIGIBLE.
Start tracing to store eligibility and performance metrics related to statements in the dynamic statement cache.
- –START TRACE(MON) CLASS(30) IFCID(316,317,318) DEST(SMF)
Run your dynamic query workload.
Before extracting the workload from the dynamic statement cache, set the schema or SQLID to the appropriate <schema> of your DSN_STATEMENT_CACHE_TABLE table.
For example, if you have created the table with the <schema> “DB2OSCA”, execute the following SQL command:
- SET CURRENT SCHEMA = 'DB2OSCA '
To extract the workload information from the dynamic statement cache, execute the following SQL command:
EXPLAIN STMTCACHE ALL
You can execute this command from SPUFI or from a job (DSNTEP2 for example). After the execution, the table schema.DSN_STATEMENT_CACHE_TABLE contains rows for each cached dynamic SQL statement in your Db2 subsystem.
The ACCEL_OFFLOAD_ELIGIBLE column in the DSN_STATEMENT_CACHE_TABLE table shows whether a statement is eligible for acceleration. If the value ‘Y’ is displayed, the statement can be accelerated. If ‘N’ is displayed, the statement is either not eligible or the query is already routed to an existing accelerator (accelerator modeling is not performed for queries that are routed to an accelerator).
You can use the following SQL statements to extract the eligible statements from the DSN_STATEMENT_CACHE_TABLE:
- SELECT STMT_ID, STMT_TEXT
FROM DSN_STATEMENT_CACHE_TABLE WHERE ACCEL_OFFLOAD_ELIGIBLE = ‘Y’;
- SELECT STMT_ID, STMT_TEXT
FROM DSN_STATEMENT_CACHE_TABLE WHERE ACCEL_OFFLOAD_ELIGIBLE = ‘N’ AND STMT_TEXT LIKE ‘%SELECT%’;
- SELECT STMT_ID
,INTEGER(STAT_ELAP) AS ELAPSED_TIME
,INTEGER(STAT_CPU) AS ELAPSED_CPU
,INTEGER(STAT_EXEC) AS EXECUTIONS
,CASE WHEN STAT_EXEC <> 0 THEN INTEGER(STAT_ELAP/STAT_EXEC) ELSE 0 END AS AVG_ELAPTIME
,CASE WHEN STAT_EXEC <> 0 THEN INTEGER(STAT_CPU/STAT_EXEC) ELSE 0 END AS AVG_CPUTIME
,STMT_TEXT
FROM DSN_STATEMENT_CACHE_TABLE WHERE ACCEL_OFFLOAD_ELIGIBLE = ‘Y’;
The ELAPSED_CPU and ELAPSED_TIME give you an approximation of the processing time and the Db2 CPU savings to be expected if these statements were run on an accelerator.
DSN_STATEMENT_CACHE_TABLE WHERE ACCEL_OFFLOAD_ELIGIBLE = 'Y';
, DECIMAL(SUM(STAT_ELAP),31,11), DECIMAL(SUM(STAT_ACC_CPU),31,11)
, DECIMAL(SUM(STAT_ACC_ELAP),31,11)
FROM DB2.DSN_STATEMENT_CACHE_TABLE group by CURSQLID,ACCEL_OFFLOAD_ELIGIBLE;
A2- To distinguish GP and zIIP on statement level in Db2 for z/OS, a Db2 for z/OS performance trace is required. In Omegamon Terms, SQL Activity Report on Statement Level is needed. It has the following details:
https://www.ibm.com/docs/en/om-db2-pe/5.5.0?topic=blocks-times-class-2-db2-time
SE CPU TIME is the accumulated and consumed class 2 time on zIIP.
In Accounting report there are relevant blocks too:
for CL1 Elapsed Time https://www.ibm.com/docs/en/om-db2-pe/5.5.0?topic=blocks-times-class-1-application-time4
First one, summarized for userid or other, easily readable also for the customer
Second one, more detailed, with SQL text and relative consumptions (both GP then zIIP)
https://www.ibm.com/docs/en/om-db2-pe/5.5.0?topic=layouts-accounting-trace-long
https://www.ibm.com/docs/en/om-db2-pe/5.5.0?topic=reporter-using-sql-activity-reports-monitor-sql-performance
Accelerator modeling for assessing individual dynamic queries or static queries
Setup:- For Db2 12 the APAR PH23848 fixes the occurrence of an extra row in DSN_QUERYINFO_TABLE when accelerator modeling in on.
Set the Db2 subsystem parameter ACCELMODEL to YES (the default is NO). This parameter can be changed online and activates the output of information, including information about potential query routing to an accelerator.
If you use a data sharing group, set this parameter on all members.
In addition, set the CURRENT GET_ACCEL_ARCHIVE special register and the GET_ACCEL_ARCHIVE subsystem parameter to NO.
Accelerator modeling supports the values ENABLE, ENABLE WITH FAILBACK, or ELIGIBLE for the query acceleration setting for the following purposes:
- If you already have accelerators connected to your subsystem, accelerator modeling can be performed for new queries while other queries are routed to the accelerators.
- You can use the acceleration setting of ELIGIBLE to assess queries based on their SQL syntax. If the query acceleration setting is set to NONE, accelerator modeling assesses queries based on their cost (which corresponds to a query acceleration setting of ENABLE). This is the default behavior.
To collect Db2 EXPLAIN information, create the EXPLAIN tables. You can use the DDL SQL text provided by hlq.SDSNSAMP(DSNTESC) for this purpose.
Before explaining SQL statements, the schema or SQLID must be set to the appropriate <schema> of your explain tables. For example, if the table <schema> is “DB2OSCA”, run the following
SQL command:- SET CURRENT SCHEMA = 'DB2OSCA '
For assessing a dynamic SQL query, explain it and obtain the assessment result from the DSN_QUERINFO_TABLE table by using the following sample statements:
- EXPLAIN ALL SET QUERYNO=4711 FOR <SQL query>;
SELECT REASON_CODE, QI_DATA FROM DSN_QUERYINFO_TABLE WHERE QUERYNO=4711;
If REASON_CODE 0 is returned, the SQL query is eligible for acceleration. If a REASON_CODE other than 0 is returned, the QI_DATA column contains an explanation why the statement is not eligible or why Db2 determined that it would not be advantageous to accelerate the statement (corresponds to an acceleration setting of ENABLE).
If you started with the procedure in the section Accelerator modeling for assessing a dynamic query workload, you can continue to use this procedure if you want to know why statements are not eligible. For example, after selecting the non-eligible SQL statements from the DSN_STATEMENT_CACHE_TABLE table including STMT_ID, you could use the following SQL statements for each statement that is not eligible:
- EXPLAIN ALL SET QUERYNO=<stmt_id> FOR <stmt_text>
SELECT A.REASON_CODE, A.QI_DATA, B.STMT_TEXT FROM DSN_QUERYINFO_TABLE A, DSN_STATEMENT_CACHE_TABLE B WHERE A.QUERYNO=B.STMT_ID;
Assessment of static SQL queries:
To collect EXPLAIN information, create the EXPLAIN tables with the user ID of the owner of the package that contains the static queries. You can use the DDL SQL text provided by hlq.SDSNSAMP(DSNTESC). For the assessment of static SQL statements, you must issue a BIND or REBIND PACKAGE command for the DB2 package that contains the queries. You can choose between the EXPLAIN(ONLY) and the EXPLAIN(YES) option to populate the corresponding EXPLAIN tables during the bind process. Run the BIND or REBIND PACKAGE command in an identified change window to minimize impacts.
Obtain the assessment result for the queries in the package by using the following statement:
- SELECT QUERYNO, PROGNAME, COLLID, REASON_CODE, QI_DATA FROM DSN_QUERYINFO_TABLE;
To limit the number of results, filter the queries by explain date. You can use the following statement to this end. Adjust the explain date before you submit the statement.
- SELECT QUERYNO, PROGNAME, COLLID, REASON_CODE, QI_DATA FROM DSN_QUERYINFO_TABLE WHERE EXPLAIN_TIME > ‘2015-04-01 00:00:00.00’;
Using the retrieved QUERYNO, PROGNAME and COLLID columns, you can obtain the statement text from the SYSIBM.SYSPACKSTMT table (requires DB2 for z/OS NFM):
- SELECT STATEMENT FROM SYSIBM.SYSPACKSTMT WHERE QUERYNO = <query_no> AND COLLID = <coll_id> and NAME = <progname>;
Alternatively, you can combine both statements:
- SELECT D.REASON_CODE, D.QI_DATA, S.STATEMENT FROM DSN_QUERYINFO_TABLE D, SYSIBM.SYSPACKSTMT S WHERE D.QUERYNO=S.QUERYNO AND D.COLLID=S.COLLID AND D.PROGNAME=S.NAME;
If REASON_CODE 0 is returned, the SQL query is eligible for acceleration. If a REASON_CODE other than 0 is returned, the QI_DATA column contains an explanation why the statement is not eligible or why Db2 determined that it would not be advantageous to accelerate it (corresponds to an acceleration setting of ENABLE).
Accel Elig Elapsed: The amount of elapsed time saved if the statement or call was run on an accelerator. This column contains a value only if the statement or call is eligible to run on an accelerator.
Accel Elig CPU: The amount of CPU time spent on a non-specialty engine that would be saved if the statement or call was run on an accelerator. This column contains a value only if the statement or call is eligible to run on an accelerator.
a. 9,604.98 seconds (~160 minutes or ~2.6 hours) of elapsed time
b. 5,727.16 seconds (~95 minutes or ~1.6 hours) of GCP time
2. If an accelerator had been available, all the candidate tables would have been moved to this accelerator, and Db2 would have routed all candidate queried to this accelerator. These numbers could have been reduced by the amount reported in the last 2 columns:
a. 4,542.85 seconds (~75 minutes or ~1.26 hours) of elapsed time – meaning it would have been executed faster
b. 3,883.66 seconds (~64 minutes or ~1 hour) of GCP time – meaning it would not have consumed this GCP time from the normal GCPs because the GCP usage would have been shifted to the accelerator.
-start trace(a) class(1,2,3,7,8) DEST(SMF)
Afterwards, Omegamon for Db2 Accounting Report on the collected SMF data provides summary based on Plan or Package level (not on statement level)
MEASURED/ELIG TIMES APPL (CL1) DB2 (CL2)------------------- ---------- ----------
ELAPSED TIME 0.666679 0.286790
ELIGIBLE FOR ACCEL N/A 0.112549
CP CPU TIME 0.104083 0.103901
ELIGIBLE FOR SECP 0.006273 N/A
ELIGIBLE FOR ACCEL N/A 0.039156
SE CPU TIME 0.023446 0.023446
ELIGIBLE FOR ACCEL N/A 0.000000
Summary
This document describes several procedures that assess the eligibility of single queries or a query workload for query acceleration. Each procedure serves one or more specific purposes, and one procedure might serve a particular purpose as well as another. Which procedure or combination of procedures you choose depends on your needs and on the system prerequisites, such as the availability of Db2 PTFs on the IBM Z or tools, such as IBM Db2 Query Monitor for z/OS.
Was this topic helpful?
Document Information
Modified date:
06 February 2024
UID
swg27045618