Control SQL cursor scoping within SQL procedures, triggers, and functions

Using the CLOSQLCSR option to create SQL code bodies that use *ENDMOD

A new i5/OS® SQL set option for use with DB2 enables better control of SQL cursor scoping. Learn about the new CLOSQLCSR option, why it is important, and how to use it. Also discover pointers to SQL procedures, triggers, and functions (PTFs) that you can use to run the new option.

Share:

Scott L. Forstie (forstie@us.ibm.com), Senior Software Engineer, IBM

Photo of Scott ForstieScott Forstie is a senior software engineer at IBM, and he is the SQL development leader for DB2 UDB for i5/OS in Rochester, Minnesota. Before working on DB2, he worked on UNIX(R) enablement for the AS/400(R) and S/390(R) systems.



Sue Romano (slromano@us.ibm.com), Software Engineer, IBM

Photo of Sue RomanoSue Romano is a software engineer at IBM. She works on the SQL parser for DB2 UDB for i5/OS.



27 March 2007

Also available in Russian

Introduction

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.

Cursor scoping explained

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 TABLE locks are released. SQL cursors are explicitly closed when you issue the CLOSE, COMMIT, or ROLLBACK (without HOLD) SQL statements. This option is ignored in REXX. Integrated Language Environment (ILE) programs and modules, SQL functions, SQL procedures, and SQL triggers use *ENDACTGRP and *ENDMOD. Non-ILE programs use *ENDPGM, *ENDSQL, and *ENDJOB.
*ENDACTGRP
SQL cursors are closed; SQL prepared statements are implicitly discarded; and LOCK TABLE locks 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 TABLE locks 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
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
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

Conclusion

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.

Resources

Learn

Get products and technologies

  • Download the V5R4M0 5722SS1 PTFs required to execute CREATE PROCEDURE (SQL), CREATE TRIGGER, or CREATE FUNCTION (SQL Scalar or SQL Table) statements, which use the CLOSQLCSR option 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

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=204456
ArticleTitle=Control SQL cursor scoping within SQL procedures, triggers, and functions
publish-date=03272007