We use Db2 Profiles to manage queries that are candidates for acceleration due to their execution cost, but that originate from ETL processes distributed across all departments of our company. Processes such as Sqoop, NiFi, Kafka, Spark, PowerCenter, and others generate queries that return large volumes of data, which we do not want to consume accelerator resources. We prefer to reserve accelerator capacity for analytical workloads by nature. However, some ETL-originated queries should still be accelerated because, although complex, they return only a small amount of data ("etlShortVol").
To achieve this objective, we use PROFILES defined with AUTHID + LOCATION that point to ATTRIBUTES containing ACCEL_RESULTSIZE_THRESHOLD, allowing only "etlShortVol" queries to execute on IDAA. Using SET CURRENT QUERY ACCELERATION = NONE would completely prevent acceleration of ETL-originated queries identified by AUTHID + LOCATION.
⚠️Currently, the only viable alternative is to create and maintain thousands of individual profiles using AUTHID + LOCATION with specific IP addresses. The use of LOCATION with the exact IP address of the connection source creates a very large number of AUTHID + LOCATION combinations.
For example, a new NiFi server may be provisioned with a new IP address that belongs to an already known ETL network range. Since the existing profile uses an exact LOCATION value, queries originating from the new server are not matched by the current rules and may become accelerated unexpectedly. In many cases, this can lead an IDAA server to a partial or complete freeze condition. To resolve the issue, a new profile must be created. ⚠️ In our environment, this results in thousands of invidual profiles that must be maintained, along with continuous monitoring to identify and create new AUTHID + LOCATION combinations as infrastructure evolves. Although we use automation, the operational cost continues to grow whenever new servers or IP addresses are introduced.
This situation impacts Db2 for z/OS database administrators and has the potential to affect all users of our enterprise analytics environment. Because we do not have full control over when an ETL query may be routed to an accelerator, 🚨unexpected workload acceleration can cause partial or complete accelerator freezes, significantly degrading performance and availability. The issue is experienced whenever new ETL servers, applications, or IP addresses are introduced into the environment and requires ongoing operational monitoring.
We know the IP address ranges and AUTHIDs associated with ETL workloads.
Current limitation: although Db2 accepts LOCATION values defined with wildcards or subnet-based IP addressing, these definitions do not effectively work as filtering criteria for Query Accelerator Offload profiles.
✅We propose enhancing Db2 Profiles so that LOCATION values defined with wildcards, subnet masks, or CIDR notation are fully supported and enforced for Query Accelerator Offload profiles (DSN_PROFILE_ATTRIBUTES with ACCEL_* type keywords). This would allow a single profile to control acceleration behavior for an entire IP range instead of requiring individual profiles for each source IP address.
Expected Benefits:
• Reduction from thousands of profiles to only a few dozen.
• Lower operational effort and maintenance costs.
• Reduced risk of configuration errors.
• Less need for continuous monitoring and profile creation.
• Improved governance of accelerator resource utilization.
• Better segregation between ETL and analytical workloads.
• Improved IDAA stability and availability by preventing uncontrolled ETL workload acceleration.
