DB2 stored procedure development best practices)

Http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0604changhp/

The powerful functions provided by DB2 allow developers to create highly efficient and stable stored procedures. But for beginnersProgramIt is not easy. This article mainly discusses some common techniques and methods for developing efficient and stable DB2 stored procedures.

The reader is positioned as a developer with certain development experience in DB2 development.

Readers can learn how to write stable and efficient stored procedures from this article. And can be used directlyArticleDB2CodeTo save their development and debugging time and improve efficiency.

This article describes the precautions and skills for developing DB2 stored procedures from the perspective of DB2 developers. If a newbie can develop a stored procedure according to the best practices described in this article, it can avoid some common errors and write efficient programs. This article focuses on initialization parameters, cursors, exception handling, use of temporary tables, how to find and rebind illegal stored procedures, and other common issues, and provides sample code.

During storage, developers can declare and set SQL variables, implement process control, handle exceptions, and insert, update, or delete data. At the same time, parameters can be passed between a customer application (the application that calls a stored procedure here, it can be a JDBC call or ODBC or CLI call) and a stored procedure, and return the result set from the stored procedure. The DB2 stored procedure compiled using SQL is a common stored procedure during development. This article mainly discusses such stored procedures.

Best Practice 1: Provide necessary parameters in the creation of stored procedure statements

The create procedure statement can contain many parameters. Although they are not required in syntax, they can improve execution efficiency when creating a stored procedure. Below are some common parameters

Allow SQL (allowed-SQL)

The value of the SQL (allowed-SQL) clause specifies whether the stored procedure uses SQL statements. If so, what is the type of the SQL statement. Its possible values are as follows:

• No SQL: The Stored Procedure cannot execute any SQL statement.
• Contains SQL: indicates that the stored procedure can execute SQL statements, but does not read or modify SQL data.
• Reads SQL data: Contains SQL statements that do not modify SQL data during storage. That is to say, the stored procedure only reads data from the database.
• Modifies SQL data: indicates that the stored procedure can execute any SQL statement. You can add, delete, and modify data in the database.

If allowed-SQL is not explicitly declared, its default value is modifies SQL data. Different types of stored procedures have different execution efficiency, of which no SQL is the most efficient and modifies SQL data is the worst. If the stored procedure only reads data, but does not declare allowed-SQL to make it be executed as a stored procedure for data modification, this will obviously reduce the execution efficiency of the program. Therefore, when creating a stored procedure, you should explicitly declare its allowed-SQL.

Number of returned result sets (dynamic result sets N)

A stored procedure can return 0 or more result sets. To return a result set from a stored procedure, perform the following steps:

• In the dynamic result sets clause of the create procedure statement, declare the number of result sets to be returned by the Stored Procedure (number-of-result-sets ). If the number of returned result sets declared here is less than the number of actually returned result sets in the stored procedure, DB2 returns a warning when executing the stored procedure.
• Use the with return clause to declare the cursor in the Stored Procedure body.
• Open a cursor for the result set. When the stored procedure returns, keep the cursor open.

Specifying the number of returned result sets when creating a stored procedure can help the programmer verify whether the stored procedure has returned the expected number of result sets and improve the program integrity.

Back to Top

Best Practice 2: Perform necessary checks and preprocessing on input parameters

Either MethodProgramming LanguageThe input parameters are required. Correct parameter verification is a prerequisite to ensure the program runs well. Similarly, it is important to verify and process input parameters in DB2. Correct verification and preprocessing operations include:

• If the input parameter is incorrect, the stored procedure should return a clear value to inform the customer application. Then, the customer application can process the returned value or submit a new parameter to the stored procedure, or call other programs.
• Input parameters are pre-processed based on the business logic, such as case-sensitivity conversion, and conversion between null and null strings or 0.

In the development of DB2 storage process, if you need to initialize null, we can use the coalesce function. The coalesce function returns the first non-null parameter. The syntax is as follows:

Listing 1: coalesce Function

.---------------. (1) V | >>-COALESCE-------(--expression----,--expression-+--)----------><

The coalesce function checks the input parameters in sequence and returns the first parameter that is not null. The function returns NULL only when all the parameters passed into the coalesce function are null. For example, coalesce (piname, ''). If the piname variable is null, the function returns''. Otherwise, the piname value is returned.

The following example shows how to check and initialize parameters.

The person table is used to store basic personal information. Its definition is as follows:

Table 1: person

The following code pre-processes the parameters used to insert data to the table person:

SET poGenStatus = 0; SET piName = RTRIM(COALESCE(piName, '')); SET piRank = COALESCE(piRank, 0); -- make sure all required input parameters are not null IF ( piNum IS NULL OR piName = '' OR piAge IS NULL ) THEN SET poGenStatus = 34100; RETURN poGenStatus; END IF;

The num, name, and age fields in the table "person" are non-empty fields. For the name field, multiple spaces are also considered as null values. Therefore, we call rtrim and coalesce to process the field before judgment, and then use piname = '', perform non-null judgment on the rank field. For the rank field, we hope that if the user inputs null, we will set it to "0", and we will also use coalesce for initialization; for "Age" and "num", we can directly use is null for non-null judgment.

If the input parameter does not pass the non-null judgment, we will set a definite value for the output parameter pogenstatus (34100 in this example) to inform the caller that the input parameter is incorrect.

The following is a summary of the parameter initialization rules for your reference:

1. If the input parameter is of the character type and can be null, you can use coalesce (inputparameter, '') to convert null '';

2. If the input type is integer and null is allowed, coalesce (inputparameter, 0) can be used to convert null to 0;

3. If the input parameter is of the character type and is not empty or spaces, you can use coalesce (inputparameter, '') to convert null to'', and then determine whether the return value of the function is '';

4. The input type is integer and is not empty. You do not need to use the coalesce function to directly use is null for non-null judgment.

Back to Top

Best Practice 3: correctly set the cursor return type

We have discussed how to declare the returned result set of a stored procedure. Here we will discuss the returned type of the result set. There are two types of returned result sets: caller and client ). First, let's take a look at the example of declaring the two types of cursors:

CREATE PROCEDURE getPeople(IN piAge INTEGER) DYNAMIC RESULT SETS 2 READS SQL DATA LANGUAGE SQL BEGIN DECLARE rs1 CURSOR WITH RETURN TO CLIENT FOR SELECT name, age FROM person WHERE age<piAge; DECLARE rs2 CURSOR WITH RETURN TO CALLER FOR SELECT NAME, age FROM person WHERE age>piAge; OPEN rs1; OPEN rs2; END

In the code, the declause of the RS1 cursor contains the with return to client clause, indicating that the result set is returned to the client ). The declare Statement of rs2 cursor contains the with return to caller clause, indicating that the result set is returned to the caller (caller ).

The cursor is returned to the caller (caller), indicating that the caller of the stored procedure receives the result set, regardless of whether the caller is another stored procedure or a customer application. In figure (1), if the Stored Procedure proz is declared as with return to caller, The result assembly is returned to the stored procedure proy, and the client application does not get the result set returned by proz.

The cursor is returned to the client, indicating that the result set is received by the customer application that issued the initial call statement, even if the result set is sent by a layer-15 deep nested stored procedure in the nested hierarchy. In Figure 1, if the Stored Procedure proz is declared as with return to client, the result assembly is returned to the client application. The cursor declaration returned to the client is frequently used and the default result set type.

When declaring the return type, we need to seriously consider who we need to return the result set to avoid losing the return set and causing program errors.

Back to Top

Best Practice 4: Exception Handling

In the execution of stored procedures, exceptions are often caused by data or other problems ). According to the business logic, the stored procedure should handle exceptions or directly return them to the caller. Here, condition is translated as an exception for readers to understand. In fact, some exceptions (condition) are not caused by errors, which will be described in detail below.

When the sqlstate returned by the statement in the Stored Procedure exceeds 00000, it indicates that an exception (condition) is generated in the stored procedure ), it indicates that an error occurs, data is not found, or a warning is reported. To respond to and handle exceptions in stored procedures, we must declare the exception processor (condition handler) in the Stored Procedure body ), it determines how a stored procedure responds to one or more defined exceptions or predefined exception groups. The syntax for declaring a condition processor is as follows. It is located after the variable Declaration and the cursor declaration:

Listing 4: declaring an exception Processor

Declare Handler-type handler for condition Handler-action

The exception Processor type (Handler-type) has the following types:

• After the processor operation is complete, continue continues to execute the next statement after the exception statement is generated.
• After the processor operation is complete, exit terminates the stored procedure and returns the control to the caller.
• Before undo executes a processor operation, DB2 rolls back the SQL operations performed during the stored procedure. After the processor operation is complete, the stored procedure is terminated and the control is returned to the caller.

Exception processors can handle custom exceptions based on specific sqlstate values or classes with predefined exceptions. The predefined three types of exceptions are as follows:

• The not found flag causes an exception where the sqlcode value is + 100 or the sqlsate value is 02000. This exception usually occurs when select does not return rows.
• An error occurs when the sqlcode value is negative due to the sqlexceptioin ID.
• The sqlwarning flag causes a warning exception or an exception that causes a sqlcode value other than 100.

If a not found or sqlwarning exception is generated and no exception processor is defined for the exception, the exception is ignored and the control flow is switched to the next statement. If an sqlexception exception is generated and no exception processor is defined for this exception, the stored procedure fails and the control flow is returned to the caller.

The following example declares two exception processors. The exit processor is called when an sqlexception or sqlwarning exception occurs. Before terminating the SQL program, the exit processor sets the variable named stmt to "aborted" and returns the control flow to the caller. The undo processor rolls back the SQL Operations completed in the Stored Procedure body before returning the control flow to the caller.

Listing 5: exception processor example

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING SET stmt = 'ABORTED';

Declare undo handler for not found;

If the predefined exception set does not meet your requirements, you can customize the exception Declaration for the specific sqlstate value and then declare the processor for this custom exception. Syntax:

Listing 6: custom exception Processors

Declare unique-name condition for sqlsate 'sqlstate'

The processor can be defined by a separate Stored Procedure statement or by begin... End Block composite statement definition. Note that the values of sqlsate and sqlcode are changed when the statement is executed. If you need to retain the values of sqlsate and sqlcode before the exception, you need to assign sqlsate and sqlcode to local variables or parameters in the first statement that executes the compound statement.

Generally, we define an output parameter for the execution status of a stored procedure (for example, pogenstatus ).

The output status indicates whether the stored procedure is correctly executed. We need to define some exception processors to assign values to this output parameter. The following is an example:

Listing 7: An exception processor defined as an output parameter value

-- Generic Handler DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND BEGIN NOT ATOMIC -- Capture SQLCODE & SQLSTATE SELECT SQLCODE, SQLSTATE INTO hSqlcode, hSqlstate FROM SYSIBM.SYSDUMMY1; -- Use the poGenStatus variable to tell the procedure -- what type of error occurred CASE hSqlstate WHEN '02000' THEN SET poGenStatus=5000; WHEN '42724' THEN SET poGenStatus=3; ELSE IF (hSqlCode < 0) THEN SET poGenStatus=hSqlCode; END IF; END CASE; END;

The above exception processor will be triggered when an sqlexception, sqlwarning, and not found exception occurs. The exception processor extracts the current sqlcode and sqlstate, and then sets the value of the output parameter (pogenstatus) based on their values.

We can also customize some exception processors. For example, we can define some exception processors for parameter initialization. Here, the exception processor can be considered as an internal function that can be called by the stored procedure itself. The following is an example of this situation:

Listing 8: Internal functions for the stored procedure to call

----------------------------------------------------- -- CONDITION declaration ----------------------------------------------------- -- (80100~80199) SQLCODE & SQLSTATE DECLARE sqlReset CONDITION for sqlstate '80100'; ----------------------------------------------------- -- EXCEPTION HANDLER declaration ----------------------------------------------------- -- Handy Handler DECLARE CONTINUE HANDLER FOR sqlReset BEGIN NOT ATOMIC SET hSqlcode = 0; SET hSqlstate = '00000'; SET poGenStatus = 0; END; ………… ----------------------------------------------------- -- Procedure Body ----------------------------------------------------- SIGNAL sqlreset; -- insert the record …………

The exception processor customized above initializes the hsqlcode, hsqlstate, and pogenstatus parameters. When we need to initialize them in the program, we only need to call signal sqlreset.

Back to Top

Best practice 5: rational use of temporary tables

We often use temporary tables during storage development. Reasonable Use of temporary tables can simplify program writing and provide execution efficiency. However, misuse of temporary tables also reduces program running efficiency.

Temporary tables are generally used in the following situations:

1. Temporary tables are used to store temporary data during the running of the program. For example, if the execution result of the first query statement in a program is used by subsequent query statements, we can store the results of the first query in a temporary table for subsequent query statements, instead of re-querying in subsequent query statements. If the first query statement is very complex and time-consuming, the above policy is very effective.

2. Temporary tables can be used to store results that need to be returned multiple times in a program. For example, if the program has a resource-consuming multi-Table query and the query needs to be executed multiple times in the program, the result set of the first query can be stored in the temporary warranty, you only need to query the temporary table for subsequent queries.

3. Temporary tables can also be used to allow SQL to access non-relational databases. For example, if you can write a program to insert data from a non-relational database to a global temporary table, you can query the data.

We can use the declare global temporary TABLE statement to define a temporary table. DB2 temporary tables are session-based and isolated between sessions. When the session ends, the data in the temporary table is deleted, and the temporary table is implicitly detached. The following is an example of defining a temporary table in syscat. Tables:

Listing 9: defining temporary tables

DECLARE GLOBAL TEMPORARY TABLE gbl_temp LIKE person ON COMMIT DELETE ROWS NOT LOGGED IN usr_tbsp

This statement creates a user temporary table named gbl_temp. The names and descriptions of columns used to define this user temporary table are identical to those of the person column.

Listing 10: creating a temporary table with two fields

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP2 ( ID INTEGER default 3, NAME CHAR(30) ) --WITH REPLACE NOT LOGGED; --IN USER_TEMP_01;

This statement creates a temporary table with two fields.

Theoretically, the temporary table does not need to show drop because it is session-based. When the temporary table is closed based on the connection, the temporary table does not exist. However, in actual development, we need to pay attention to temporary tables.

One case is that the returned value of the called stored procedure is a result set based on a temporary table. When the stored procedure is executed, the temporary table will not disappear because the returned result set is equivalent to a pointer pointing to the memory address of the temporary table. In this case, the temporary table will not be dropped. In this case, the temporary table cannot be deleted during the storage process, or the temporary table displayed by the customer application should be deleted, which is prone to some problems. Here is an example to illustrate this problem.

The following sample code returns the stored procedure of the temporary table (get_temp_table ):

Listing 11: returning the stored procedure of a temporary table

----------------------------------------------------- -- TEMPORARY TABLE & CURSOR declaration ----------------------------------------------------- DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP ( ID INTEGER, NAME CHAR(30) ) --WITH REPLACE NOT LOGGED; P2: BEGIN DECLARE R_CRSR CURSOR WITH RETURN TO CLIENT FOR SELECT * FROM SESSION.TEMP FOR READ ONLY; INSERT INTO SESSION.TEMP VALUES(1,piName); OPEN R_CRSR; END P2;

During the storage process, the temporary table temp with two fields is declared, a cursor r_crsr is declared to return all records in the temporary table, and two records are inserted in the temporary table.

The first execution result of the program is as follows:

We can see that the running result is what we expect. So what will happen if we run it again? Is the running result:

During the second execution, the program failed because the temporary table was not dropped in the same connection, therefore, an error occurs when the stored procedure is called for the second time.

In other cases, for example, the connection pool technology is used in WebSphere to connect to the database through JDBC, which improves the efficiency, at the same time, it is easy to misunderstand in some situations. The customer application closes the database connection, but does not necessarily close the database connection. If the customer application uses a temporary table and the database connection is not closed, the temporary table will not be dropped. When the connection pool assigns this connection to another client program, the new client program can still use the old temporary table, which is not what we want. To avoid this problem, you can add with replace when creating a temporary table, or drop a temporary table where appropriate based on the business logic.

The following describes how to use with replace to create a temporary table.

It can be seen that in a connection, the get_temp_table stored procedure is called multiple times, and no problem occurs. Temporary tables must be avoided in some cases. As you know, a temporary table is stored in the memory. If a temporary table has tens of thousands or tens of thousands of records, and the concurrency of the program is large, the temporary tables created in the memory consume a large amount of resources. At this time, the performance of the database will decrease sharply and even cause the database to crash. Therefore, when using a temporary table, you must consider its resource consumption to avoid blind use of the temporary table.

Back to Top

Best Practice 6: Find and rebind illegal stored procedures

The stored procedure is invalid because the objects involved and referenced are changed. For example, if the table structure is modified, the stored procedure for referencing the table is invalid, or re-compile a stored procedure, the parent stored procedure that calls the stored procedure is invalid. In this case, rebind the invalid stored procedure ). However, when rebind an invalid stored procedure, make sure that the referenced object is legal. Otherwise, the stored procedure cannot rebind successfully.

Here we will introduce how to discover and rebind illegal stored procedures. We find the invalid stored procedure by judging the value of the valid field in syscat. routines. The following code finds an invalid stored procedure:

Listing 12: Searching for invalid stored procedures

SELECT RTRIM(r.routineschema) || '.' || RTRIM(r.routinename) AS spname , ' ( '|| RTRIM(r.routineschema) || '.' || 'P'||SUBSTR(CHAR(r.lib_id+10000000),2)||' )' FROM SYSCAT.routines r WHERE r.routinetype = 'P' AND ((r.origin = 'Q' AND r.valid != 'Y') OR EXISTS ( SELECT 1 FROM syscat.packages WHERE pkgschema = r.routineschema AND pkgname = 'P'||SUBSTR(CHAR(r.lib_id+10000000),2) AND valid !='Y' ) ) ORDER BY spname;

The result is as follows:

Listing 13: query results of invalid stored procedures

SPNAME ---------------------------------- TEST.DEMO_INFO_8 (TEST. P3550884)

You can use the following command to rebind them

Listing 14: Invalid Stored Procedure Syntax of rebind

Rebind package packagename resolve any @

Packagename is the value in brackets in the query results. For example, if the stored procedure is found on the rebind. We only need to execute the following statement

Listing 15: Invalid rebind Stored Procedure

Rebind package test. p3550884 resolve any @

Of course, if there is a problem with this stored procedure program, you need to modify the Stored Procedure Code before compiling.

Similarly, the following code can be used to obtain an invalid view.

Listing 16: Retrieving invalid views

SELECT RTRIM(viewschema) || '.' || RTRIM(viewname) AS viewname FROM SYSCAT.views WHERE valid = 'X' ORDER BY viewname;

Back to Top

Conclusion

This article describes some of the techniques we often use in DB2 stored procedure development. These skills are also the basic requirements for writing excellent stored procedures. Some tips described in this article only reveal the tip of the iceberg for using DB2 efficiently. DB2 provides us with rich and powerful functions. When using DB2, we should thoroughly understand its principles and find out more best practices to share with you.

References

• Red Book: IBM DB2 UDB command reference version 8

  
  

• Red Book: IBM DB2 UDB Application Development Guide

Author Profile

Chang Wei is a software engineer working in ibm csdl and is currently engaged in the development of enterprise e-commerce applications. You can contact him through the changwei@cn.ibm.com.

Chang hongping is an ibm csdl software engineer, IBM certified DB2 dBA and IBM certified DB2 developer. He is currently engaged in the development of enterprise e-commerce applications. You can contact him through the changhp@cn.ibm.com.