Summary
This article applies to Dynatrace OneAgent in mainframe (z/OS) environments and addresses increased zIIP usage related to SQL and database query processing. It explains the cause and describes configuration options introduced to reduce CPU overhead and improve SQL processing efficiency.
Problem
Customers may observe an increase in zIIP usage, particularly in environments with SQL‑ or database‑intensive workloads. The increase is typically correlated with JDBC-based SQL execution and can raise concerns about higher CPU consumption and performance impact.
If a customer experiences increase in their zIIP usage especially in the scope of SQL/DB queries, we introduced two features that should result in performance improvements.
Troubleshooting steps
- Identify whether the zIIP usage increase correlates with SQL or database activity.
- Verify the Dynatrace OneAgent version running in the affected environment.
- Check whether SQL normalization and PreparedStatements reuse are relevant for the observed workload.
Resolution
Dynatrace introduced SQL processing optimizations to reduce CPU overhead and improve performance in SQL-heavy workloads.
These optimizations include:
- Improved SQL normalization efficiency through optimized SQL tokenization
- A PreparedStatements cache to reduce repeated parsing and normalization
To enable these optimizations, configure the following settings on the affected OneAgent:
jdbc-sql-optimize-tokenization = truejdbc-sql-cache-prepared-statements = true
Both settings are available starting with OneAgent release 327 and can be set either via
- extending the dtconfig.json with
"jdbc-sql-optimize-tokenization": true or
- adapting the JVM arguments to include the two arguments such as
-javaagent:/PATH_TO/dynatrace-oneagent-zos-java.jar=jdbc-sql-optimize-tokenization=true,jdbc-sql-cache-prepared-statements=true
Internal testing shows the most significant improvements in environments where PreparedStatements are effectively reused.
What's next
- Monitor zIIP and CPU usage to validate the impact
- Review application behavior to ensure PreparedStatements are reused where possible
- Continue performance analysis using Dynatrace to confirm reduced SQL processing overhead
If this article did not help, please open a support ticket, mention that this article was used and provide the following in the ticket:
