Developing Error-Free Native Stored Procedures
Enterprise IT can apply SQL Procedural Language best practices to optimize DB2 for z/OS native stored procedures
Stored procedures in relational database management systems (RDBMSs) have been around a long time, and they merely call another program that performs some action or actions for the calling program. There are many benefits for having a called stored procedure do some of the work. It helps minimize network traffic, reduce maintaining code by keeping logic and processing in one place, and tighten security by granting callers the authorization to perform a process or query data only through a stored procedure.
Stored procedures can be written in a number of different languages, and they can be called from any language that can execute an SQL statement because the stored procedure is initiated—called—though an SQL call command. Since the release of IBM® DB2® Version 9 for the IBM z/OS® platform, the SQL Procedural Language (SQL PL) has been enhanced to code stored procedures, which can provide many benefits. There are several best practices that developers can take advantage of for coding stored procedures on the DB2 for z/OS platform.
Several specific practices for developing native stored procedures—DB2 executable code objects that can be called by other programs—use SQL PL on the z/OS platform. Since native stored procedures were first introduced in DB2 Version 9, an increasing number of IT departments are implementing and rewriting stored procedures in SQL PL. But are they doing it correctly? Are they using a good set of standards, guidelines, and best practices?
There are a number of ways IT organizations can improve on their practices and ensure their stored procedures are complete, correct, consistent, and error free. And they have several reasons for making a move to native stored procedures:
- A simplified build-and-deploy process: No external address space environment or compilers are needed. The IBM Data Studio tool provides a well-suited editor window and deployment for SQL PL, making it very easy to code, test, debug, and deploy.
- Enhanced performance, at times: There is no guarantee that a native stored procedure will always outperform an external stored procedure. But the fact that it runs entirely in the DB2 engine versus an external address space and without an assigned workload management (WLM) environment gives it a boost. SQL PL is a procedural language that is converted to an internal code and stored in the package. At runtime, it is then compiled further and executed. This approach is not as efficient as a compiled language such as C or COBOL. Thus, native stored procedures containing a lot of processing code may not be as efficient. This process has improved to a degree with the REGENERATE statement, which rebuilds the internal SQL PL control structures for enhanced efficiency at runtime. DB2 10 is supposed to be more efficient than previous versions for running native stored procedures, so regenerating after migration is encouraged.
- zIIP eligibility: A native SQL procedure is eligible for offloading to IBM System z® Integrated Information Processors (zIIPs) if it is called from a Distributed Relational Database Architecture (DRDA) client using TCP/IP—often called a distributive thread. If DB2 finds that a stored procedure is initiated through a call from a distributive thread, then it will look to offload and direct a portion of the needed processing to a zIIP-eligible processor. As a result, many IT organizations have moved to using native stored procedures to offload the CPU to these zIIP-eligible processors cost-effectively.
- Compatibility: The z/OS platform offers SQL PL that is now more compatible with DB2 for Linux, UNIX and Windows (LUW) and other platforms than it was previously.
- Ease of learning: SQL PL is an easy language for developers at any level to learn.
Naming and authorizing stored procedures
A native stored procedure is like any other DB2 object. Its name consists of two parts—an owner/schema and the stored procedure name. Native stored procedures always take the owner/schema of the stored procedure and use it as the collection ID in generating the package. A good practice is creating a native stored procedure with an owner/schema that matches the owner/schema of the tables being processed in the programs that are calling it.
For example, if the tables being processed are under an owner/schema = STESTID, then the stored procedure should be created with the same owner/schema = STESTID. The bind in the process will then put the bound package into a collection = STESTID. This approach may go against an IT organization’s naming conventions for collections. However, it makes coding unqualified stored procedure calls easy for developers, as well as having the call to the stored procedure fall under the same assigned qualifier that is specified in their own bind parameters.
Note that in Data Studio, the target schema specified in the Deploy Routines wizard controls both the owner of the stored procedure and the collection ID into which the stored procedure is bound (see Figure 1).
Figure 1. Stored procedure owner and collection ID control by the target schema
Native stored procedures do not need to have their collection bound to a plan, unless the stored procedure is being called from packages that execute under a plan. Typically, a developer first writes native stored procedures to be called from distributed threads in which binding the collection ID to a plan does not come into play. For native stored procedures, the bind parameters are now part of the stored procedure header information because in deploying the stored procedure, the package gets generated.
For developers to deploy their own coded stored procedures, a database administrator (DBA) may have to execute GRANT CREATEIN ON SCHEMA STESTID TO XXXXXXXX. The CREATEIN privilege will be required to create a stored procedure into a given schema.
There may be many application developers and/or DBAs creating native stored procedures in the same schema. Therefore, granting the CREATEIN privilege on the schema to a secondary authorization ID that represents a group of users who create stored procedures may be preferable. Each application developer could then issue a SET CURRENT SQLID statement to the secondary authorization ID or set it in Data Studio as part of the Deploy Routines wizard.
Because a creation of a package exists, the BINDADD system privilege is required to create SQL PL native stored procedures in a DB2 subsystem, so there must also be an EXECUTE privilege on the stored procedure.
Coding native stored procedures—best practices
Like many programming languages, naming variables and parameters in SQL PL must follow a set of rules (see Figure 2). When it comes to naming conventions in SQL PL, I particularly like to have all variable names begin with V_ and all parameters begin with P_IN_, P_OUT_, and P_INOUT_. Variables, parameters, and column names in the body of the program can all look the same unless a developer has a convention that makes them easily identifiable. SQL PL provides the following naming rules:
- Names may be up to 128 characters. Try to make names as meaningful as possible.
- The first character of a name must be either a lowercase or an uppercase ASCII letter. Note that SQL PL is not case sensitive.
- A name can then contain numbers, underscores, and dollar signs, but must follow the first character. Do not make variable names hard to read or understand. Make them easy for others to understand and maintain in the future.
- Dashes are not allowed.
- The variable and parameter names are available only to the procedure itself.
- Definitions are identical to the manner in which DB2 table columns are defined and must use the same data types—for example, CHAR(3), DATE, VARCHAR(50), DEC(9,2), and so on.
- Any variable name must be defined before any error handler or declare cursor. Placing it right away in the SQL body—for example, P1: Begin…—is recommended.
- Variable names can be the same names as columns, but doing so is not recommended. If the name is ambiguous to the compiler, it assumes the name is a parameter or variable name.
- Names are not case sensitive; for example, there cannot be two variables named V_LASTNAME and v_lastname.
- Variable names use underscores; dashes create errors. For example, a declared variable for V-LASTNAME will result in an error.
- In SQL PL all variables are considered SQL variables, and they do not need to be preceded by a colon within the source code.
- Variables should be defined with default values, or set to an initialized value early in the processing code.
Figure 2. Code sample demonstrating application of SQL PL naming rules
When coding SQL PL stored procedures, getting the processing code in the correct order is extremely important; otherwise, the procedure will not get a good deploy. The following order represents the organization in which the code must be written:
- Declare all variables, including SQLCODE and SQLSTATE.
- Declare any conditions.
- Declare any cursors.
- Declare all error handlers.
- Process code.
At the beginning of processing code, initializing all output parameters first is recommended. Doing so helps ensure no output parameters go back to the calling program as nulls, if they do not get set within the stored procedure processing code.
If any of these coding steps takes place out of order, illegal or undefined errors that resemble a syntax error in the code will occur, even though having the code out of order is really the cause for the error. Setting up sample programs that contain comments at the points where each section of the program begins is generally a good idea.
Optimizing stored procedures
Armed with a set of standards, guidelines, and best practices helps developers ensure the DB2 for z/OS native stored procedures are complete, correct, consistent, and error free. The scope of this discussion covers the beginning of these standards and guidelines. But other areas to think about include handling errors, error information that should be captured, handling SQLCODE +100 (not found) conditions, what happens with unhandled errors and when errors occur in the error-handling logic, and how to force an error. In addition, how to set up and share shell programs, ways to handle looping logic, and If and Case statement gotchas should also be considered. Look for further details in upcoming articles. In the meantime, please share any thoughts or questions in the comments.