Db2 Data Gate blog series part 3 :Accelerate your queries with Data Gate 2.1
By Vassil Dimov, Sowmya Kameswaran and Eirini Kalogeiton
IBM Db2 for z/OS Date Gate 2.1 introduces a new major feature — Query Acceleration.
Why is this big news and what does it mean? For those familiar with IBM Db2 Analytics Accelerator for z/OS (IDAA), the ability to accelerate analytical queries that target Db2 for z/OS data by routing to IDAA has been game changer. Support to accelerate analytical queries by routing to Db2 Data Gate, enables cloud-native applications that depend on business critical Db2 for z/OS data to send queries from Db2 for z/OS through Data Gate to Db2 Warehouse in Cloud Pak for Data in the private or public cloud.
The target database, IBM Db2 Warehouse is optimized for analytics. Applications always connect to Db2 for z/OS to execute queries, all result sets are also returned back to Db2 for z/OS, making query acceleration and execution completely transparent to the client application. This allows to achieve improved application performance with minimal to no changes required to the application itself, which is a key differentiator.
Not familiar with Db2 Data Gate? Learn more about it and how it is cloud enabling Db2 for z/OS data here.
Note: Query acceleration has been available as a Beta feature within IBM Db2 for z/OS Date Gate 2.0 since 2021.
Technical details
The query acceleration functionality is only supported when Db2 Warehouse has been configured as target database during the provision of Db2 Data Gate. If the feature is enabled, the character encoding of the target database will be kept the same as the encoding on Db2 for zOS (EBCDIC). This ensures that a query routed to Db2 Warehouse to be accelerated delivers the same result set as when executed by Db2 for z/OS.
Setting up Data Gate for Query Routing
- Provision Data Gate with Query Routing enabled
To use the query routing feature with Db2 Data Gate, the first step is to select a Db2 Warehouse instance that is pre-provisioned on the Cloud Pak for Data platform and then enable the “Use this data gate instance for query acceleration” checkbox.
2. Set up Db2 for z/OS for query acceleration
The following zPARMs have to be set to enable query acceleration when Db2 for z/OS subsystem starts:
ACCEL=AUTO
Additionally, you can define what queries can be routed by setting QUERY_ACCEL_OPTIONS. An example setting is:
QUERY_ACCEL_OPTIONS=(1,2,3,5,6)
3. Connect Db2 Data Gate to Db2 for z/OS source database
4. Manage query acceleration for Db2 Data Gate
5. Add tables from Db2 for z/OS to Db2 Data Gate
6. Load data from Db2 for z/OS to Db2 Data Gate and enable acceleration
When adding the tables to Data Gate, users have the option to also load data and enable data synchronization. If this option is not exercised, then data load can be initiated from the table list on the Tables tab. Once the data load is complete, query acceleration can be enabled from the same location for the selected tables.
7. Get information about queries being accelerated
Once query acceleration is enabled on Db2 Data Gate instance level and on table level, queries targeting Db2 for z/OS can be accelerated to Db2 Data Gate (to execute against the data in the backend Db2 Warehouse). Similar to the IBM Db2 Analytics Accelerator for z/OS, this is enabled by using the SET CURRENT QUERY ACCELERATION ALL statement:
After the query is executed, corresponding monitoring information will be displayed in the Queries section of the Data Gate User interface.
Why static images? Let’s see some live action!
See blog for information on all key Data Gate 2.1 functional highlights.
For information on install and configuration IBM Documentation for Data Gate
