Hopefully, you’ve been reading along in this series of articles on effective usage of the zIIP on the IBM System z mainframe. But even if you have not, this article focuses on a single topic that does not require in-depth knowledge of zIIP particulars. Herein we will discuss best practices for COBOL and Db2 users to bolster their usage of zIIPs.
Why do this?
As we have discussed in prior installations, the primary reason to work on redirecting workload to zIIPs is to reduce cost. Workloads on the zIIP are not chargeable against your monthly IBM software bill. So exploiting your installed zIIPs can result in significant cost savings.
How to do this?
Knowing how to improve your zIIP usage is actually pretty simple. It involves understanding what is zIIP-eligible and coding your applications to promote that type of usage. Your actual COBOL processing is not zIIP-eligible, but many types of activities enacted by your COBOL program can be zIIP-eligible.
Let’s dig into the opportunities for zIIP usage in Db2 (with a COBOL mindset).
The first important thing is to review and stay up-to-date on the types of Db2 workload that are zIIP-eligible. IBM maintains a list of what is zIIP-eligible. As of Db2 12 for z/OS, the list of authorized Db2 zIIP usage can be found here.
Design your new COBOL programs and applications for distributed processing. SQL requests that use DRDA to access Db2 for z/OS over TCP/IP connections are zIIP eligible, with up to 60% of their instructions running on the zIIP. Similarly, programs issuing native REST calls over HTTP fall into the same category, with up to 60% being zIIP-eligible. Although it is not extremely common, it is possible to call a REST API from COBOL. Or you might choose to forgo COBOL for REST calls instead of relying on z/OS Connect or another approach.
So, the first thing to consider is how the application is being designed and how it will be run. Using distributed SQL calls is probably the first, best thing that you can do to get COBOL programs to take advantage of your installed zIIP capacity.
But not everything can, or indeed should, be run as a distributed SQL request (or REST API call). Fortunately, there are other types of processing that can be run on the zIIP. Db2 parallel query child processes for long-running parallel queries can also be run on zIIPs. There is a threshold after which up to 100% of these processes can be run on the zIIP.
So how can you encourage parallelism? There is no way to explicitly say, “This query must use parallelism.” Instead, when the query is bound (either statically or dynamically), the Db2 Optimizer decides if parallelism will be beneficial and, therefore, potentially used to satisfy the query request. At bind time, you can tell Db2 to consider using parallelism. For static SQL, you must code the DEGREE(ANY) parameter when you BIND or REBIND. You must set the CURRENT DEGREE special register for dynamic SQL to ‘ANY .’ It is also possible to change the CURRENT DEGREE special register default from 1 to ANY for an entire Db2 subsystem by setting the value of the CDSSRDEF DSNZPARM parameter. This DSNZPARM sets the default for dynamic SQL only.
Keep in mind that parallelism is used for read only queries only. Therefore, it makes sense to identify cursors that are read only. If you bind your program using CURRENTDATA(YES) and Db2 cannot tell if the cursor is read-only, Db2 will not consider parallelism. Therefore, to optimize parallelism, it is a good practice to specify FOR READ ONLY or FOR FETCH ONLY for every CURSOR that will be used for reading data only.
Furthermore, queries run against partitioned table spaces will cause the Db2 Optimizer to more strongly consider parallelism. However, partitioning is not explicitly required for Db2 to invoke parallelism.
Many IBM Db2 utility processes are also zIIP-eligible. For example, up to 100% of the index maintenance tasks for LOAD, REORG, and REBUILD INDEX are zIIP-eligible. Portions of the RUNSTATS utility is also zIIP-eligible, but this typically has little to do with COBOL. So how can COBOL developers take advantage of the zIIP eligibility of IBM Db2 utilities?
One tactic to consider is to avoid creating COBOL programs that perform many Db2 SQL INSERTs and instead use the LOAD utility. Of course, if the program only inserts a few rows, then the LOAD utility is probably not the best solution. Additionally, if a lot of pre-processing or other activity is required before the data can be inserted, then the LOAD utility may not be suitable.
That said, using the LOAD utility is likely worth considering if you have a large data set of records that need to be read and inserted into a table. The LOAD utility is very efficient, and the index maintenance needed is all zIIP-eligible. This is not the case for COBOL programs performing INSERTs.
Note that other ISVs offer Db2 utilities with varying degrees of zIIP eligibility. So, if you have Db2 utilities from BMC or Broadcom, or InfoTel be sure to consult their documentation for details on their zIIP exploitation.
If your program is processing XML data, this too can be zIIP-eligible. Up to 100% of XML schema validation and non-validation parsing and up to 100% of the deletion of unneeded versions of XML documents can be run on the zIIP.
Another consideration to keep in mind is that as your COBOL programs run and access Db2 data, many other incidental Db2 system tasks and work may be zIIP-eligible.
Up to 100% of the work done by Db2 system agents processing under enclave SRBs that execute in the Db2 MSTR, DBM1, and DDF address spaces is zIIP-eligible with a few exceptions. So, things that Db2 does during normal operations, like buffer pool processing and log operations, can be run on the zIIP as your COBOL programs run.
When it comes to sorting, IBM DFSORT workload is not zIIP-eligible. IBM does market a product called DB2SORT that works with Db2 utilities to zIIP enable some of the sort processing used by utilities. But none of that helps sorting for use by COBOL programs. However, if you have a lot of data to sort before processing it by your COBOL program, you have Syncsort (from Precisely) and the add-on ZPSaver component of Syncsort. It can be used to enable sort, copy, and SMS compression to be zIIP-eligible. It pays to know what software you have and whether it is zIIP-enabled!
As a final consideration, if you are not getting sufficient zIIP utilization for your COBOL programs, you can consider converting to Java, all of which is zIIP-eligible. Of course, this requires significant planning and investment and using tools like those marketed by Cloudframe to do the conversion for you.
Craig Mullins, Copyright Craig Mullins Consulting, Inc
Find Out More
Learn more about Relocate.