Automate Conversion to Native SQL Procedures

Implement DB2 native SQL procedures to enhance cost-effective database performance

Today, organizations can modernize their enterprise online transaction processing (OLTP) environments and align them well with the increased activity of remote access transactions activity. They can accomplish this alignment by migrating external SQL procedures to native SQL procedures, and in addition help reduce general processor (GP) usage and software cost.

Simply stated, an SQL procedure is a sequence of program instructions that are packaged as a unit of work to perform a specific task for IBM® DB2® relational database management applications. Consider the progression of SQL procedures from when they began to where they are today and where DBAs using them should be to get the maximum benefits as SQL procedures continue to progress. DB2 external SQL procedures were first introduced in DB2 Version 4 and were implemented by embedding logic in high-level programming languages that had to be compiled. Organizations could derive benefits from the following features:

  • Reduced network traffic
  • Modular application development
  • Data processing in a consistent manner
  • Changes made in only one place
  • Improved application security
  • Defined common business rules

DBAs can implement native SQL procedures to extend the benefits of external SQL procedures. Native SQL procedures are compiled, executed, and run inside DB2, rather than accessing the SQL procedures from a load library and running them in a workload management (WLM) address space. If a native stored procedure is accessed from a remote access Distributed Relational Database Architecture (DRDA) and distributed data facility (DDF), it may be eligible to leverage the IBM System z® platform’s Integrated Information Processor (zIIP) specialty engines (SEs). Stored procedures constitute a DB2 functional enhancement that has continued to help client organizations consistently reduce IT cost and processor utilization.

Native SQL procedures were first introduced in IBM DB2 Version 9 for z/OS® data management. All the program logic was written in the SQL Procedural Language (SQL PL). Native SQL procedures streamlined the execution process of stored procedures, but they didn’t alter the invocation. The invocation of a native SQL procedure—through a CALL statement—is the same as invocation of an external SQL procedure, but it could result in higher processor usage when compared to the processor utilization required for a COBOL program. This elevated usage can occur when a large amount of COBOL logic has to be converted to SQL PL. COBOL is currently more efficient in compiling code than SQL PL.

For example, a System z server can be configured with optional zIIP processors—SEs—that are significantly more cost-effective than GP engines. Native SQL procedures are eligible for zIIP offload when called by a DRDA requestor, which can possibly shift significant processing from GP engines to zIIP engines. As a result, a significant number of organizations may be able to reduce their processor usage by simply converting their external SQL to native SQL procedures.


Converting stored procedures

The amount of zIIP redirect offload is the same for organizations running DB2 Version 9 or later versions, executing under enclave service request blocks (SRBs) in the DDF space. Organizations can expect zIIP offload up to 60 percent for native SQL procedures.1

The efficiency gain in converting remote access DRDA/DDF external SQL procedures to native SQL procedures may be a direct result of shifting the SQL—the 60 percent—from GP to zIIP engines. Many organizations deploying DB2 have hundreds to thousands of remote access DRDA/DDF external SQL procedures that could be converted to native SQL procedures, but many organizations have not yet made the conversion.

DB2 Version 11 offers many SQL PL enhancements, which can make the high-level language conversion to SQL PL quite easy. If an external SQL procedure accesses resources outside of DB2, it cannot be converted to a native SQL procedure. In addition, there may be many personnel resource or business reasons that inhibit or impede conversion.


Resolving obstacles to stored procedure conversion

There are several potential reasons why organizations have not converted external SQL procedures to native SQL procedures. But for each of the following challenges, there is a suggested strategy for getting past it and realizing the benefits of conversion:

  • Shifting processing from GP engines to zIIP engines: For some organizations, reduced transaction response time or throughput capacity may not be an important consideration. Further evaluation of the zIIP engines’ potential savings on million service unit (MSU) software charges may be a significant motivational factor.
  • Unknown or unconsidered zIIP redirect benefits: Organizations should look at the impact that native SQL procedures can have on zIIP engines, increased transaction throughput capacity, reduced MSU software charges, and the long-term benefits of SQL PL.
  • Other high-priority projects: Organizations should perform a comprehensive performance analysis of their remote access DRDA/DDF transactions and processor usage, including the impact of reducing their monthly peak MSUs’ four-hour rolling average software charges.
  • Personnel with limited native SQL procedure skills: Organizations can organize resources with limited skills into special project teams or hire skilled consultants.
  • Personnel with limited SQL PL versus COBOL expertise: Organizations can provide in-house training for project team members.
  • Large amounts of high-level language code and code complexity: Organizations should locate tools or services that can automate the migration process.
  • Lack of support for SQL PL in major source code management (SCM) tools: Organizations can utilize services supporting SCM that are provided through REXX routines in the DB2 for z/OS database Authorized Problem Analysis Report (APAR) PM292261 and IBM Data Studio in combination with Apache Subversion.2
  • Testing and tuning native SQL procedures: Organizations can use tools such as IBM InfoSphere® Optim™ Query Capture and Replay for DB2 for z/OS and InfoSphere Optim Query Tuner, which are designed to verify and validate whether a conversion was successfully implemented.

Developing or finding an automated solution to convert external SQL procedures to native SQL procedures can be a highly cost-effective, short-term return-on-investment (ROI) project for any organization deploying DB2 database environments.

Please share any thoughts or questions in the comments.

1APAR PM29226 and DB2 for z/OS Native SQL Procedure Source Code Management,” Robert’s DB2 Blog, Robert Catterall, IBM DB2 specialist, March 2012. The 60 percent zIIP offload can result if the server’s zIIP processors are not constrained in any way—such as if a zIIP engine were always available every time some zIIP-eligible work was ready for dispatch. Typically, evidence of some constraint—a small percentage of zIIP-eligible work—can end up running on GPs, and in practice the observed zIIP offload is generally in the 55–60 percent range.
2 Apache Subversion version control system software project.