A DB2 stored procedure consists of encapsulated logic that can be invoked from within your application that’s stored on the database server. This is one of the most underutilized features in the relational world today. It has taken DB2 stored procedures years and several releases of DB2 to reach full maturity (see Figure 1). Native SQL stored procedures will set you on the “Yellow Brick Road of Success.” For those of you who don’t understand this reference, it’s to the late Bob Yelavich (the Godfather of CICS), who was a perfectionist in everything he said or wrote.
This is how Linda Claussen of Themis Education Services Inc. described the creation, structure and execution of native SQL stored procedures in her August 2012 IBM Data Magazine article titled “DB2 Native SQL Procedures: The Future of Computing?” (see the “References” section):
“With DB2 V9 and V10 new function mode, when you create a native SQL procedure, its procedural statements are converted to a native representation that is stored in the DB2 catalog and directory. As with other SQL statements, this representation is bound into a DB2 package. The parameter list and procedure options are stored in the database catalog tables, as in the prior releases. When you CALL a native SQL procedure, DB2 loads the native representation (package) from the directory and the DB2 engine executes the procedure. No workload management (WLM) address space is required! Everything is stored in the package, under DB2 control. Native SQL procedures are simply packages that include runtime structures for the SQL statements to be executed. When you invoke a native SQL procedure, DB2 finds and loads the package and executes the statements (see Figure 2).”
Linda has been a long-time Themis Education Services instructor of native SQL stored procedures, and it’s easy to understand why. Native SQL stored procedures can be more CPU efficient than interfacing through WLM. You avoid waiting to be scheduled in WLM, then going across the WLM to DBM1 address space to address each individual SQL call. The only drawback is that COBOL is currently more efficient than SQL Procedural Language (PL). If you don’t have an enormous amount of SQL PL code to write, stick with native SQL stored procedures.
The Benefits of Native SQL Stored Procedures
Developing native SQL stored procedures wherever possible can provide many performance benefits, including:
• Reduced network traffic
• Increased application scalability
• Simplified application development
• Consistent performance, resulting in better data access control and access path stability
• Improved security
• Lower CP usage
• Lower mainframe software cost.
The IT industry has a tendency to approach the implementation of new DB2 database features with caution, especially when it involves modifying production applications that are meeting current service level agreements (SLAs). The old saying goes, “If it ain’t broke, don’t fix it.” Unfortunately, followers of that saying have created an environment that underutilizes the features and functions of System z mainframe hardware, software and operating systems. It also doesn’t help lower the TCO of the mainframe.
Therefore, many of today’s OLTP transactions were written or retrofitted to relational systems without the current benefits and operational efficiency functions of DB2 10 and 11 native SQL stored procedures.