• Create SQL statements that execute within a loop
• Group SQL statements into executable blocks
• Test for specific conditions and perform one set of SQL statements when the condition is true, another set when the condition is false (IF...ELSE)
• Branch to other areas within the procedural code.

The procedural commands also allow you to create a more flexible SQL application development environment. Often major applications can be written completely in SQL PL. As more applications are developed in native SQL stored procedures, developers will become more proficient in the use of the SQL PL features.

Common Complaints About DB2 SQL/PL

Some developers don’t like to use SQL PL because of current limitations in the SQL procedural language. They’re typically making the comparison to other high-level languages that were first developed decades ago, such as COBOL (1959) and PL/1 (’60s). Among the functions developers would like to have are the ability to include external files and a PERFORM capability. Like any new or emerging programming language, these capabilities will come with time. The existing high-level languages, such as COBOL and PL/1, initially didn’t have many of the features developers think must be there to write efficient applications. Every release of DB2 since the introduction of SQL PL has included SQL PL enhancements. There are ways to work around current limitations, but that’s a topic for another day.

Sample SQL Stored Procedures

To better understand SQL PL, take a look at the example SQL stored procedure in Figure 4. This SQL stored procedure declares a cursor on the EMP table and fetches the rows from the cursor.

Creating native SQL stored procedures is quite simple. All that’s required is to write the SQL and submit it to DB2. The process used to create an SQL procedure is made even easier if you use a tool, such as IBM Data Studio, to guide you through the steps of specifying the source statements for the SQL procedure, defining the SQL procedure to DB2 and deploying the SQL procedure for execution.


Just as early gold prospectors found gold in the streams at the bottom of hills and mountains, DB2 10 and 11 offer many new opportunities to claim zIIP processor redirect gold. Remote access DRDA/DDF native SQL stored procedures are just one of those streams that contain a high content of high-quality zIIP processor redirect gold nuggets.

You have to determine the maximum CPU savings that can be derived from converting external to native SQL stored procedures and its impact on leveling off your LPAR 4 hour rolling average mountain of monthly mainframe software charges. The IBM Optim Query Capture and Replay tool can be used to validate that the newly converted native SQL stored procedures will run and perform successfully in your production environment. The IBM InfoSphere Optim Workload Replay tool can be used to validate that the newly converted native SQL stored procedures will run and perform successfully in your production environment. It offers a solution that helps enterprises deploy infrastructure changes with confidence, on time and within budget. InfoSphere Optim Workload Replay for z/OS is designed to deliver realistic and repeatable workload testing, helping to minimize risk in production environments and reduce time and effort involved in creating realistic enterprisewide testing.

You have zIIP processor redirect gold dust just waiting to be reclaimed. Happy prospecting!


• DB2 expert Robert Catterall’s blog post at http://robertsdb2blog.blogspot.com
• IBM Data Magazine article titled “DB2 Native SQL Procedures: The Future of Computing?” by Linda Claussen, August 2012, http://ibmdatamag.com/2012/08/db2-native-sql-procedures-the-future-of-computing
• Robert Catterall blog titled, “DB2 for z/OS: Get Your DDF-Related Dispatching Priorities Right,” Aug. 9, 2013, http://robertsdb2blog.blogspot.com/
• DB2 Developer's Guide: A Solutions-Oriented Approach to Learning the Foundation and Capabilities of DB2 for z/OS (6th Edition) by Craig Mullins (Kindle Locations 17863-17876).

5 Pages