This Db2 for z/OS News from the Lab blog entry was originally published on 2019-03-14.
By Marie Sumabat and Jennie Chang.
With the activation of function level 504, Db2 12 for z/OS introduces support for the following 16 built-in functions, which can be invoked when using IBM Db2 Analytics Accelerator (Accelerator):
- CUME_DIST
- FIRST_VALUE
- LAG
- LAST_VALUE
- LEAD
- NTH_VALUE
- NTILE
- PERCENT_RANK
- RATIO_TO_REPORT
- REGEXP_COUNT
- REGEXP_INSTR
- REGEXP_LIKE
- REGEXP_REPLACE
- REGEXP_SUBSTR
These functions are passthrough-only expressions, which means that they cannot run on the z/OS system and are passed through from Db2 for z/OS to the Accelerator. Db2 only verifies that the data types of the parameters are valid for these functions. The accelerator engine does all other function resolution processing and validation.
These passthrough-only expressions enable you to perform complex analytical queries on data for more elaborate statistical analysis and pattern identification. Previously, these expressions were supported by Db2 Warehouse and other database systems. Now, through function level 504, they are also available on Db2 for z/OS for users with the Accelerator.
Db2 for z/OS manages queries that contain passthrough-only expressions in various ways, depending on the query acceleration settings. The following values for the QUERY_ACCELERATION subsystem parameter, CURRENT QUERY ACCELERATION special register, or QUERYACCELERATION bind option determine the action that Db2 takes on queries that contain passthrough-only expressions:
| Value |
Effect on queries with passthrough-only expressions |
| ALL, ENABLE, or ELIGIBLE |
Db2 passes the query through to the accelerator server.
Db2 for z/OS does not provide cost estimates for passthrough-only expressions, which cannot run on z/OS. |
| NONE |
Db2 does not pass the query through to the accelerator server. Instead, Db2 uses the usual function resolution process to resolve to an existing user-defined function. Db2 then uses this function to run the query.
If a matching user-defined function does not exist, Db2 does not run the query and issues an error. |
| ENABLE WITH FAILBACK |
Db2 does not pass the query through to the accelerator server. Instead, Db2 issues an error, even if a user-defined function that matches the passthrough-only expression exists.
With this additional support for passthrough-only expressions, Db2 for z/OS can now effectively accelerate queries that contain these built-in functions, delivering new analytics capabilities and providing more comprehensive insight on data. |
Marie Sumabat is a software developer for IBM Db2 Analytics Accelerator for z/OS.
Jennie Chang is a technical writer for Db2 for z/OS.
#Db2forz/OS#db2z/os#Db2Znews