IBM first introduced external stored procedures in DB2 Version 7, which were imbedded in high-level languages and ran under the task control block (TCB) of the language, such as COBOL. TCB workloads aren’t IBM z/OS Integrated Information Processor (zIIP)-eligible; therefore, neither are external stored procedures. Native SQL stored procedures were first introduced in DB2 9. IBM declared part of the native SQL stored procedure workload zIIP redirect-eligible if invoked via remote access DRDA/DDF because it used an enclave service request block.
What Are zIIPs?
In 2006, IBM introduced zIIPs, which are specialty engine (SE) microprocessors designed to allow selected types of workloads to be redirected from the CP mainframe to execute on the lower cost zIIP microprocessor. Typically, zIIP processors are priced at 25 percent the cost of the CP. They were available on all IBM zEnterprise, System z10 and System z9 servers. The zIIP processors were designed to free up general computing capacity and lower overall total cost of computing for select data and transaction processing workloads for business intelligence (BI), enterprise resource planning (ERP), customer relationship management (CRM) and select network encryption workloads on the mainframe. CP workloads that are redirected to be executed on the zIIP microprocessor aren’t included as part of the price of the mainframe software monthly usage charges. The process of defining zIIP processor-eligible workloads has been an evolutionary journey totally controlled by IBM. Several years ago, it was fairly uncommon for a customer’s zIIP processor workload to exceed 25 percent of their zIIP processor capacity. With the increase in zIIP processor eligible workloads, it’s not uncommon to find customers exceeding 50 percent of their zIIP processor capacity. DB2 native SQL stored procedures are one type of DB2 for z/OS workload that has benefited from zIIP processor redirect (see Figure 3). Until recently, customers’ zIIP processor processing capacity wasn’t allowed to exceed the capacity of their CP. With the introduction of the zEnterprise BC12 and EC12 machines, IBM will increase the zIIP processor to CP capacity ratio to 2:1. That means you will be able to have two zIIP specialty engines with the same capacity as your CP.
Finding zIIP Gold in DRDA/DDF Remote Access Transactions
External SQL stored procedures generate very few zIIP processor redirect benefits. If you’re currently executing high-volume external stored procedures via remote access DRDA/DDF, you could save significant CP resource by converting them to native SQL stored procedures. Because IBM has deemed this workload as zIIP processor-eligible, this means up to 60 percent of the native SQL stored procedure’s CPU execution time can be redirected to the less expensive zIIP processor machine in DB2 10 (see Figure 3). In practice, 55 percent zIIP redirect is common with DB2 10 new function mode.
How much CP resource could you save by converting external stored procedures accessed from a remote access DRDA/DDF location to native SQL stored procedures? Only you can answer that question, but it’s fairly quick and simple to answer. It just requires turning on the SMF accounting trace class 2 records for a short period during a peak online transaction period.
You can trace the DDF transactions that invoke external stored procedures using your favorite DB2 performance monitoring tool. The DB2 monitor will show you the amount of general-purpose-engine CPU time consumed, on average, by external stored procedures invoked via your DDF transactions. Utilize your daily transaction rates to estimate your potential general-purpose CPU savings if your DDF external stored procedures workload was converted to Native SQL stored procedures in DB2 10 (general-purpose CPU time x 60 percent). The daily numbers can then be extrapolated to project potential monthly and yearly CPU savings.
Robert Catterall wrote an extremely detailed blog describing this process in detail (see the “References” section).
How to Harvest the zIIP Processor Gold
Now that you’ve calculated how much potential gold there is in converting from external to native SQL stored procedures, it’s time to develop a low-risk, high-ROI re-engineering plan to convert the stored procedures. Remember the 80/20 rule, which says 80 percent of the benefits will come from 20 percent of the transactions. Irrespective of how many external stored procedure transactions you re-engineer, here are some suggestions:
• Automate the conversion of external stored procedures to native wherever possible
• Automate the identification of native SQL stored procedure candidates in legacy DDF transactions
• Rewrite external stored procedure DDF transactions containing small amounts of high-level language code in SQL/PL
• Wherever possible, write all new DDF transactions as native SQL stored procedures
• Train programming staff in SQL PL.
There are numerous benefits to developing repeatable processing code, such as Assembler macros, including: