Blogs

DB2 Native SQL Procedures: The Future of Computing?

IBM implements cross-platform SQL Persistent Stored Modules (SQL/PSM)

What’s so exciting about DB2 native SQL procedures? It’s a question I've been asked countless times. I can't help it—native SQL procedures excite me. I think they truly represent the future of computing.

To put it in perspective, think about your current development environment. How many lines of application code are you attempting to support with fewer and fewer experienced developers to maintain that code? Have you tried lately to hire a developer who knows COBOL, JCL, TSO, VSAM, or any of the skills it takes to develop traditional mainframe applications? Try doing a search on application development. What comes up? Experts in web, mobile, and desktop development. Certified PHP, Java, and .NET web developers. And don't forget Rapid Application Development (RAD), which uses minimal planning in favor of rapid prototyping.

My data lives—quite happily, I might add—on an IBM System z mainframe in IBM DB2 databases. It handles the terabytes of data required efficiently, securely, 24/7, and with full recoverability. The real question is: How can I continue providing access to that information anywhere, anytime, with minimal overhead, and cheaper than ever before? This is why native SQL procedures excite me—they can make it happen!

DB2 SQL Procedural Language (SQL PL) is a subset of the SQL Persistent Stored Modules (SQL/PSM) language standard. This standard is the basis for the structured programming languages used with SQL to write stored procedures, functions, triggers, and stand-alone code. Encapsulating complex business logic in database stored procedures provides several benefits:

  • Significantly improved application performance
  • Increased application scalability
  • Simplified application development
  • Reduced network traffic

The SQL/PSM standard combines the ease of SQL data access with the flow control structures of a simple programming language. It gives developers the ability to create compound SQL statements and procedures that need to be coded only once to run on multiple platforms. You can write SQL PL code that can run on DB2 for Linux, UNIX, and Windows, IBM i5/OS, and z/OS, which improves the portability of your code.

In addition, IBM supplies Eclipse-based development tools that allow you to code, test, deploy, tune, and debug your native SQL procedure from your desktop. Most mid-tier or desktop developers can be brought up to speed in a few days, without any retraining on mainframe tools. In fact, I downloaded the free version of Data Studio from IBM.com, installed it and had my first (very simple) native SQL procedure coded and tested in less than six hours. Thanks, IBM!

So how do stored procedures work? Let's take a look at how it all started.

DB2 external stored procedures are user-written application programs, usually COBOL, that are compiled, linked, and cataloged into a load library that is made available to a stored procedure workload manager (WLM)address space. The BIND of the DBRM (SQL statements) is performed and an administrator executes the CREATE PROCEDURE statement to define the procedure to DB2.

DB2 Native SQL Procedures: The Future of Computing – Figure 1

Figure 1. External stored procedure environment.

When the requesting application calls the stored procedure from a local or remote location, as shown in Figure 1, the DB2 database services address space (DBM1) must look up the procedure in the SYSIBM.SYSROUTINES catalog table, acquire an available task control block (TCB)to be used by the stored procedure, and the stored procedure address space is instructed to execute the stored procedure. If the procedure is not resident in the WLM address space, it will be loaded from the load library. Control is then passed to the stored procedure, which executes. When an SQL statement is encountered, control is passed back to the DBM1 address space to execute the bound package for the data access, and the result is passed back to the stored procedure address space. Once the stored procedure eventually completes, control is returned to the DBM1 address space and the final result is passed back to the caller.

In Version 5 of DB2, IBM introduced SQL procedures. Today we refer to these procedures as external SQL procedures. They removed the host language requirement, and can be coded in their entirety with the CREATE PROCEDURE statement. No host language coding skill is required. However, the CREATE statement that includes the definition information, SQL statements, and procedural code must be converted to a host language application. Once the external SQL procedure is coded, you can use the workbench tool set or execute the DSNHSQL procedure to translate the CREATE statement into a C program and perform normal external stored procedure preparation. External SQL C programs run in WLM address spaces just like any other external stored procedure.

Example:

CREATE PROCEDURE MYRAISE
( IN P_EMPNO CHAR(6)
, IN P_RAISEPCT DEC(6,2)
)
LANGUAGE SQL
UPDATE EMP
SET SALARY = SALARY * (1 + P_RAISEPCT/100)
WHERE EMPNO = P_EMPNO;

DB2 V9 introduced a new kind of SQL procedure: the native SQL procedure. Unlike the original external SQL procedures, there is no need to translate the procedure into a host language. When the CREATE PROCEDURE statement is executed, DB2 inserts the definition information into the DB2 catalog.

With the DB2 V9 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 WLM is required! Everything is stored in the package, under DB2 control.

DB2 Native SQL Procedures: The Future of Computing – Figure 2

Figure 2. Native SQL procedure execution.

Native SQL procedures are simply packages that include runtime structures for the SQL statements to be executed (see Figure 2). When you invoke a native SQL procedure, DB2 finds and loads the package and executes the statements.

Example:

CREATE PROCEDURE SPA80 (OUT p_CNT1  SMALLINT
,OUT p_SUMSAL DECIMAL(11,2)
,OUT p_SQLCODE INTEGER )
VERSION V1
ISOLATION LEVEL CS            VALIDATE BIND
QUALIFIER THEMIS1
RESULT SETS 0
LANGUAGE SQL
P1: BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
SELECT COUNT(*), SUM(SALARY)
INTO p_CNT1, p_SUMSAL
FROM EMP;
SET p_SQLCODE = SQLCODE;
END P1

DB2 Native SQL Procedures: The Future of Computing – Figure 3

Figure 3. Native SQL procedures and performance.

Unlike external stored procedures, native stored procedures do not execute in a WLM address space. WLM is only required for debugging.

With Native SQL procedures, everything runs under the caller’s task. When the stored procedure is called, the caller’s DB2 thread simply switches to the SQL procedures package—no queuing, no delays, and less dispatch overhead.

Native SQL procedures are System z Integrated Information Processor (zIIP)-eligible when called via distributed relational database architecture (DRDA). Work that runs on the zIIP does not incur software charges based on the service units consumed, which makes it a very attractive lower-cost alternative to running workloads on a general-purpose processor. With the zIIP capability, IBM System z9 or IBM System z10™ mainframes help minimize the need to maintain duplicate copies of data—which eliminates the requirement to pass the data between DBM1 and the DDF address space (see Figure 3).

For server threads that process SQL requests from applications that access DB2 by TCP/IP, a portion of the stored procedure executes under a dependent enclave SRB if it processes on behalf of an application that originated from an allied address space, or under an independent enclave SRB if the processing is performed on behalf of a remote application that accesses DB2 by TCP/IP. Shared memory is a relatively new type of virtual storage that allows multiple address spaces to easily address common storage that was introduced in z/OS V1.5. It is similar to ECSA, since it is always addressable and no address register mode or cross-memory moves are needed. However, it differs from ECSA in that it is not available to all address spaces on the system. Only those that are registered with z/OS as being able to share this storage have visibility to it. Shared memory resides above the 2 GB bar.

As you can see, native SQL procedures are exciting! They are simple to develop, easy to deploy, and they are cross-platform compliant while residing on the System z platform. They perform very well and are cost-effective.

What do you think? Let me know in the comments.

[followbutton username='IBMdatamag' count='false' lang='en' theme='light']