A new SQL set option is available to IBM i5/OS Version 5, Release 4 to use with DB2®. Before this enhancement, the Close SQL cursor option (CLOSQLCSR) was not valid within SQL procedures, triggers, or functions (PTFs). The scoping used for these code bodies was internally set to *ENDACTGRP, meaning that SQL cursors were scoped to the activation group.
With this change, you can use the CLOSQLCSR option to create SQL code bodies that use *ENDMOD.
The importance of correct SQL cursor use
Understanding and using the SQL cursor resource is important for your SQL application to achieve the desired function and performance characteristics. You can create an SQL cursor either explicitly (using DECLARE CURSOR and OPEN statements) or implicitly (using PREPARE and EXECUTE statements). The cursor is the SQL basis on which an application gains access to the underlying files of a database. At a minimum, maintaining an open cursor implies existence locks over the underlying files and over the system storage needed within the job to access the file. Accumulation of SQL cursors can impede throughput, availability, and the storage footprint of the job. Conversely, closing and reopening SQL cursors are expensive operations.
Before this improvement, SQL triggers, functions, and procedures had no way to control how SQL cursors are scoped. The default behavior scopes any cursors to the activation group of the caller. Additionally, the code body is created with an activation group setting of *CALLER, which causes the User Default activation group to be used. In most cases, the User Default activation group does not end until the job is ended. By scoping SQL cursors to the User Default activation group, cursors can potentially conflict with other cursors of the same name if nested calls or recursion is used.
When an activation group ends, the database closes any active SQL cursors scoped to that activation group. While the activation is active, the application is unable to open the same named cursor more than once. This is one reason why it is a good programming practice to use descriptive names when declaring an SQL cursor.
When an application needs to use recursion and SQL cursors, the second level of recursion fails on the attempt to open the same named cursor. Consider what would happen if you used a recursive function and that function used an SQL cursor named C1. When the function is initially invoked, cursor C1 opens successfully and works as expected. When the second instance of the function is activated, the attempt to open cursor C1 a second time fails, resulting in an SQL0502 error, meaning Cursor C1 already open).
When middleware is packaged to use SQL triggers, functions, and procedures along with SQL cursors, a similar problem exists. The middleware fails if the cursor name conflicts with the name of something used outside of the middleware.
This change to i5/OS Version 5, Release 4, which enables the use of the CLOSQLCSR option, gives the application programmer a valuable control mechanism to allow SQL cursors to be scoped to the invocation (*ENDMOD) instead of the activation group.
The SQL reference manual (see Resources) defines the SET OPTION CLOSQLCSR (Close SQL cursor) option as follows:
- CLOSQLCSR
- Specifies when SQL cursors are implicitly closed; SQL prepared statements are implicitly discarded; and
LOCK TABLElocks are released. SQL cursors are explicitly closed when you issue theCLOSE,COMMIT, orROLLBACK(withoutHOLD) SQL statements. This option is ignored inREXX. Integrated Language Environment (ILE) programs and modules, SQL functions, SQL procedures, and SQL triggers use*ENDACTGRPand*ENDMOD. Non-ILE programs use*ENDPGM,*ENDSQL, and*ENDJOB. - *ENDACTGRP
- SQL cursors are closed; SQL prepared statements are implicitly discarded; and
LOCK TABLElocks are released when the activation group ends. - *ENDMOD
- SQL cursors are closed, and SQL prepared statements are implicitly discarded when the module is exited.
LOCK TABLElocks are released when the first SQL program on the call stack ends.
When the CREATE PROCEDURE (SQL), CREATE TRIGGER, or CREATE FUNCTION (SQL scalar or SQL table) statements are executed, an ILE C program is created. This program is built from ILE C source code that is generated by the SQL parser to implement the SQL statements within the procedure, trigger, or function body. The resulting ILE program always has an activation group attribute of *CALLER. Activation groups are unique to System i™, and they play an important role in application behavior. Through the use of *CALLER, an SQL program can scope its resources to the activation group its caller uses. For complex implementations, this can lead to a clash of resources.
PTFs needed to execute SQL statements that use CLOSQLCSR
The following V5R4M0 5722SS1 PTFs are available from the IBM System i Support Technical Database (see Resources) and should be installed to be able to execute CREATE PROCEDURE (SQL), CREATE TRIGGER, or CREATE FUNCTION (SQL scalar or SQL table) statements that use the CLOSQLCSR option:
- SI25320
- SI25321
- SI25322
- SI25323
To have SQL routines generate the code, leave cursors open when you specify the SET OPTION CLOSQLCSR = *ENDACTGRP option. Apply PTF SI26918 before using CREATE PROCEDURE to build the routine.
In other words, cursors scoped to *ENDACTGRP persist longer because the user default activation group remains active until the job ends or the activation group is reclaimed. Multiple invocations of the same module will all be aware of any open cursors. By using *ENDMOD, the cursors are scoped to the invocation of the module. Multiple invocations of the same module result in no cursor collision, because the cursors open for other invocations are not visible.
An application programmer can choose to use *ENDACTGRP because a cursor needs to remain open. For example, an SQL procedure might open a cursor on the first call and return a result. When the procedure is called a second time, the cursor would still be open and positioned to return the next row of data.
Example: SQL cursors in a recursive UDF implementation
The example in Listing 1 works from Run SQL Scripts within iSeries™ Navigator. The function uses SQL cursors in a recursive implementation of user-defined functions (UDFs). Note the following about this example:
- The caller provides the recursion level.
- The UDF1 function returns a variable length character result.
- The result is built up over each call to the function.
Listing 1. Using SQL cursors in a recursive UDF implementation
CREATE SCHEMA CURSORTST;
SET SCHEMA = "CURSORTST";
SET PATH = "CURSORTST";
CREATE PROCEDURE DriveIt(IN RecursionCount INTEGER,
OUT RecursionResult VARCHAR(32000) )
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
BEGIN
DECLARE UDFResult VARCHAR(32000);
SET RecursionResult = UDF1(RecursionCount);
END ;
CREATE FUNCTION UDF1(p1 INTEGER)
RETURNS VARCHAR(32000)
LANGUAGE SQL
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION CLOSQLCSR = *ENDMOD
BEGIN
DECLARE V1 VARCHAR(32000);
DECLARE localCursor CURSOR FOR
select CONCAT( CONCAT( CONCAT(
CONCAT(' Level ', STRIP(CAST(p1 AS CHAR(4)))),
' at second ') ,
SECOND(CURRENT TIMESTAMP)), udf1(p1-1)) FROM
sysibm.sysdummy1 ;
IF p1 > 1 THEN
OPEN localCursor ;
FETCH FROM localCursor INTO V1;
CLOSE localCursor;
ELSE
SET V1 = CONCAT( CONCAT(
CONCAT(' Level ', STRIP(CAST(p1 AS CHAR(4)))),
' at second ') ,
SECOND(CURRENT TIMESTAMP));
END IF;
RETURN(V1);
END ;
--
-- Set the current path and schema
--
SET SCHEMA = "CURSORTST";
SET PATH = "CURSORTST";
--
-- Call the procedure, requesting 3 levels of recursion
--
CALL DriveIt(3,?);
--
-- Call the procedure, requesting 30 levels of recursion
--
CALL DriveIt(30,?);
|
Note that each invocation of the UDF1 user-defined function that opens this cursor can use its instance of localCursor until returning control to the caller. Also, note that the ELSE leg is the lowest recursion level. At that point, results are returned to the levels above it.
Example output from Run SQL Scripts
The procedure returns a result set in the second parameter, shown in Listing 2. Run SQL Scripts displays the result set. The request to recurse three times results in a concatenated string showing the lowest level of recursion first and proceeding upwards until the top level is reached. To make the test more interesting, the function also returns the current second using the SECOND() built-in function. The example is able to process three levels of recursion within the same second, which is why 'second 36' is displayed in the output three times.
Listing 2: Result set output from Run SQL Scripts
> CALL DriveIt(3,?)
Return Code = 0
Output Parameter #2 = Level 3 at second 36 Level 2 at second 36
Level 1 at second 36
Statement ran successfully (1295 ms)
> CALL DriveIt(30,?)
Return Code = 0
Output Parameter #2 = Level 30 at second 39 Level 29 at second 39
Level 28 at second 39 Level 27 at second 40 Level 26 at second 40
Level 25 at second 40 Level 24 at second 40 Level 23 at second 40
Level 22 at second 41 Level 21 at second 41 Level 20 at second 41
Level 19 at second 41 Level 18 at second 41 Level 17 at second 41
Level 16 at second 41 Level 15 at second 41 Level 14 at second 42
Level 13 at second 42 Level 12 at second 42 Level 11 at second 42
Level 10 at second 42 Level 9 at second 42 Level 8 at second 42
Level 7 at second 42 Level 6 at second 42 Level 5 at second 42
Level 4 at second 43 Level 3 at second 43 Level 2 at second 43
Level 1 at second 43
Statement ran successfully (4076 ms)
|
The request to recurse 30 times results in a much longer result set. Run SQL Scripts shows that the request requires over 4 seconds to complete (4076 milliseconds = 4.076 seconds). The information displayed in the result set output shows the current second found at each recursion level.
SQL code body detail from iSeries Navigator
SQL procedures, functions, and triggers are easy to find using the iSeries Navigator Schema tooling. Once selected, the SQL build information can be viewed by selecting the Explain SQL action, shown in Figure 1. This action displays a new window with a formatted view of the SQL build choices (or SET OPTION values) in effect at the time when the code body was built. These choices are used when the code body is executed.
Figure 1. SQL build detail using iSeries Navigator Explain SQL action
Command level capability to view SQL code body detail
Another way to understand the SQL characteristics of a program or service program is to use the PRTSQLINF (Print SQL information) green screen command interface, as shown in Figure 2. This command prints a formatted description of the SQL build options the object is currently using. The description is in the spool file.
Figure 2. Formatted PRTSQLINF output for iSeries Navigator consumption
Note that when the CREATE FUNCTION SQL statement is executed, a service program CURSORTST/UDF1 is created. This service program houses the ILE C code that was generated to implement the function. The CLOSQLCSR option is correctly listed as *ENDMOD. Use WRKSPLF to display the results. See Listings 3 and 4.
Listing 3. Finding PRTSQLINF output under WRKSPLF
Work with All Spooled Files
Type options, press Enter.
1=Send 2=Change 3=Hold 4=Delete 5=Display 6=Release
7=Messages 8=Attributes 9=Work with printing status
Device or Total Cur
Opt File User Queue User Data Sts Pages Page Copy
5 UDF1 SCOTTF PRT01 PRTSQLINF RDY 2 1
|
Listing 4. PRTSQLINF output
Bottom Parameters for options 1, 2, 3 or command
===>
F3=Exit F10=View 4 F11=View 2 F12=Cancel F22=Printers
F24=More keys
Display Spooled File
File . . . . . : UDF1 Page/Line 1/1
Control . . . . . Columns 1 - 130
Find . . . . . .
*..+...1...+...2...+...3...+...4...+...5...+...6...+...7...+...8...+...9...+...0...
+....1....+....2....+....3
5722SS1 V5R4M0 060210 Print SQL information
Service program CURSORTST/UDF1 02/23/07 14:58:21 Page 1
Object name...............CURSORTST/UDF1
Object type...............*SRVPGM
CRTSQLCI
OBJ(CURSORTST/UDF1)
SRCFILE(QTEMP/QSQLSRC)
SRCMBR(UDF1)
COMMIT(*NONE)
OPTION(*SQL *PERIOD *NOCNULRQD)
TGTRLS(V5R4M0)
ALWCPYDTA(*OPTIMIZE)
CLOSQLCSR(*ENDMOD)
RDB(*LOCAL)
DATFMT(*ISO)
DATSEP('-')
TIMFMT(*ISO)
TIMSEP('.')
DFTRDBCOL(CURSORTST)
DYNDFTCOL(*NO)
More...
F3=Exit F12=Cancel F19=Left F20=Right F24=More keys
|
With the enhancement to i5/OS, you have greater control over SQL cursor scoping. With *ENDMOD, each invocation of the function gets its own instance of the cursor. The OPEN, CLOSE, and other cursor-specific SQL statements operate on the cursor scoped to the unique invocation of the procedure, function, or trigger.
You need the add function PTFs in this article to execute the SQL CREATE statements to build the executable objects. Once built, you can deploy the programs or service programs on any System i machine using the same i5/OS version and release. You can also use the TGTRLS (Target Release) option and deploy the application on a previous release.
This addition enables you to set CLOSQLCSR to *ENDACTGRP (the default) or *ENDMOD. The other CLOSQLCSR values (*ENDPGM, *ENDSQL and *ENDJOB) apply only to non-ILE programs, and they remain restricted for SQL-generated code bodies.
For the best performance, use the *ENDACTGRP setting for procedures, triggers, and functions that will be called multiple times within a database connection. Use the new support for *ENDMOD carefully.
Learn
- Find out more about IBM System i and DB2 for i5/OS by visiting
the developerWorks System i page and the IBM DB2 for i5/OS home page
- For more details about SQL, see the SQL Reference Manual.
-
Browse the technology bookstore for books on these and other technical topics.
- Want more? The developerWorks IBM Systems zone hosts hundreds of informative articles and introductory, intermediate, and advanced tutorials.
- Stay current with developerWorks technical events and webcasts.
Get products and technologies
-
Download the V5R4M0 5722SS1 PTFs required to execute
CREATE PROCEDURE(SQL),CREATE TRIGGER, orCREATE FUNCTION(SQL Scalar or SQL Table) statements, which use theCLOSQLCSRoption from the IBM System i Support Technical Database, as follows: - Build your next development project with IBM trial software for download directly from developerWorks.
Discuss
-
To talk about the techniques described in this article and learn from the i5/OS developer community, visit the IBM DB2 for i5/OS discussion forum.
- Exchange information with other developers on the IBM Systems forums and developerWorks blogs.
Comments (Undergoing maintenance)
Table of contents
Next steps from IBM
My developerWorks community
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
