As most z/OS practitioners know, zIIP processors can provide significant benefits in cost savings and improved performance. And one of the most significant areas for taking advantage of the benefits that zIIPs can provide is within Db2 for z/OS. As such, the role of Db2 DBAs in promoting zIIP usage can be critical.
It is important to note that not all features of Db2 for z/OS can run on zIIP processors and that the extent to which a particular workload can be offloaded to zIIP processors depends on several factors, including the workload characteristics, system configuration, and the Db2 for z/OS version and licensing.
Of course, you must ensure the system is configured to utilize zIIPs. The first step must be to procure a license from IBM to use zIIPs. But you must also ensure that you have configured your system appropriately for zIIPs, usually done by the system programming team. Configuration issues include:
• Ensuring that sufficient zIIP weight is defined for LPARs where Db2 for z/OS workloads run, and
• Using simultaneous multi-threading in the z/OS LPAR on z13 or later processors to increase zIIP capacity (when only one zIIP engine can be dedicated to the LPAR)
Once the system is configured correctly, Db2 DBAs need to immerse themselves in understanding what types of workloads are zIIP-eligible. IBM documents the authorized zIIP uses for Db2 processing for each version of Db2 for z/OS. DBAs should bookmark this page (for Db2 13 for z/OS) and return to it for clarification as needed.
As a high-level guide, the following Db2 processing can run on zIIPs.
• Up to 100% of Db2 system agents processing running under enclave SRBs that execute in the MSTR, DBM1, and DDF address spaces are zIIP-eligible (except for P-lock negotiation). This includes buffer pool processing, log reading and writing, index pseudo-deletes, etc. In other words, things that Db2 will be doing as part of its general operation. These are not generally things that DBAs can influence or encourage much one way or the other, but they can deliver benefits by offloading work from the general purpose CP to zIIPs.
• Up to 60% of distributed SQL that uses DRDA to access Db2 over TCP/IP and native REST calls over HTTP are zIIP-eligible. This can be a significant source for offloading work to zIIPs. DBAs can work with development teams to encourage using distributed SQL to further your organization’s usage of zIIPs.
• Up to 100% of parallel query child processes can be run on zIIPs after you have reached a preset CPU threshold (which is defined by IBM for each specific model of IBM Z mainframe). DBAs can help to encourage parallelism, where appropriate, to further zIIP usage. This can be done by binding packages using DEGREE(ANY) or setting CURRENT DEGREE to ANY. Furthermore, since parallelism can only be used by read-only queries, encourage developers to identify appropriate cursors as FOR READ ONLY.
• Up to 100% of XML processing for XML schema validation and non-validation parsing, as well as for the deletion of unneeded versions of XML documents. So, if you use XML in your Db2 databases and applications, certain processing-intensive XML operations can be run on zIIPs.
• Many IBM Db2 utility processes are also zIIP-eligible. Up to 100% of the index maintenance tasks for LOAD, REORG, and REBUILD INDEX are zIIP-eligible. And up to 100% of the statistics-gathering portion of RUNSTATS is also zIIP-eligible. Planning and executing IBM Db2 utilities is something else that DBAs can do to encourage zIIPs usage. For example, encourage developers to use the LOAD utility instead of writing programs to load or bulk insert a lot of data whenever possible.
• And up to 100% of the new SQL AI functions (SQL Data Insights) in Db2 13 for z/OS that is eligible to be run as a parallel query child process are zIIP-eligible. It is a bit more complex than that because some SQL statements that reference AI functions but are ineligible to run as a parallel query child process may still be eligible if the SQL request is made through DRDA. Therefore, encouraging the use, where appropriate, of the new AI functions (AI_ANALOGY, AI_SEMANTIC_CLUSTER, and AI_SIMILARITY) can help utilize zIIP processors.
I would be remiss if I did not mention that other ISVs offer Db2 utilities with varying degrees of zIIP eligibility; for example, BMC Software, Broadcom, and InfoTel. So, if you have Db2 utilities from vendors other than IBM, consult their documentation for details on their zIIP exploitation and proceed accordingly.
Furthermore, components of other types of system software may be zIIP-eligible, so be sure to investigate and document which products you regularly use may be able to utilize zIIPs. For example, if you have heavy sorting requirements, Precisely’s Syncsort MFSort can offload a good percentage of the sort workload to zIIPs.
And remember Java! Applications written in Java can be redirected to run on zIIPs. So, instead of writing a new application in COBOL (or another language that is not zIIP-eligible), consider using Java to create more zIIP-eligible workloads. You might even look at where and when it makes sense to convert some existing workloads to run on a JVM to expand your zIIP usage.
On-going DBA zIIP Responsibilities
The DBA’s involvement with zIIPs does not end after the workload has been made eligible. Ongoing activity is required to ensure effective zIIP usage. Db2 DBA must:
• Monitor performance: Db2 DBAs should monitor system performance to ensure that zIIP processors are used effectively. This involves tracking zIIP processor utilization, general-purpose CPU utilization, and overall system performance.
• Optimize performance: Db2 DBAs can help optimize performance by changing the system configuration or adjusting workload placement to improve zIIP processor utilization and overall system performance.
And finally, Db2 DBAs should take it upon themselves to educate other team members about zIIPs, their benefits, and how Db2 workloads can take advantage of zIIPs to reduce cost.
The Bottom Line
In summary, the role of a Db2 DBA in promoting zIIP usage involves identifying eligible workloads, ensuring the system is configured appropriately, monitoring performance, optimizing performance, and educating other team members on the benefits of zIIP processors. It can also include encouraging zIIP usage by educating and training developers on what types of processes are zIIP eligible. By promoting zIIP usage, a Db2 DBA can help improve system performance, reduce costs, and improve overall efficiency on IBM Z mainframes.