Finding open cursors within a job

It is possible to leave a SQL cursor when you exit a program.

Personally I prevent a cursor being left open by use of the SET OPTION SQL statement in my RPG program:

exec sql SET OPTION CLOSQLCSR=*ENDMOD ;

Or when I create the object I make sure that the Close SQL Cursor parameter, CLOSQLCSR, is set to *ENDMOD:

CRTSQLRPGI OBJ(MYLIB/MY_PROGRAM) SRCFILE(MYLIB/DEVSRC) CLOSQLCSR(*ENDMOD)

Not everyone is as vigilant or as cautious as I am. If a program ends abnormally, or the cursor is not closed with the SQL close statement:

exec sql CLOSE cursor_name ;

Then a cursor can be left open.

Fortunately, Db2 for i comes to the rescue with procedure, DUMP_SQL_CURSORS, that will list the open cursors for a particular job.

The syntax for the procedure is:

CALL QSYS2.DUMP_SQL_CURSORS('job name','outfile library','outfile name',report type)

1. Job name: Can be job name of another job, or asterisk ( * ) for the current job.
2. Outfile library: Name of the library that will contain the outfile
3. Outfile name: Name of the outfile
4. Report type: There are four types, 1 – 4, and I will describe in this post the differences between them

Before I start showing examples of this procedure in action I need a program that left cursors open after it completed.

01 **free 02 exec sql DECLARE C0 CURSOR FOR SELECT * FROM TESTFILE FOR READ ONLY ; 03 exec sql OPEN C0 ; 04 exec sql DECLARE C1 CURSOR FOR SELECT * FROM TABLE(QSYS2.HISTORY_LOG_INFO( CURRENT_TIMESTAMP - 1 DAY, CURRENT_TIMESTAMP)) ; 05 exec sql OPEN C1 ; 06 exec sql DECLARE C2 CURSOR FOR SELECT * FROM QSYS2.USER_INFO_BASIC ; 07 exec sql OPEN C2 ; 08 return ;

I am not going to go into too much detail with this program. There are three cursors that are declared, and then opened.

Lines 2 and 3: I defined a cursor and open it for a file I created in my library, TESTFILE.

Lines 4 and 5: This cursor is created for one of my favorite table functions, HISTORY_LOG_INFO.

Lines 5 and 6: This time I have created the cursor over a SQL view, USER_INFO_BASIC.

Notice there are no SQL close cursor statements.

Line 8: I use the RETURN operation code to exit the program.

I compile this source into a program, TESTPGM, with the CRTSQLRPGI command, ensuring that the CLOSQLCSR is *ENDACTGRP.

I call the program, and it completes successfully. But how do I know the cursors are still open?

I could use the Display Job command, DSPJOB, and then take option 12 to display the locks. But I cannot see if those objects are locked by a cursor or not.

I open ACS's Run SQL scripts, and I enter the following:

CALL QSYS2.DUMP_SQL_CURSORS('122830/SIMON/QPADEV0003','','',1)

In the parameters I have passed the job name of the job I ran TESTPGM in.

I then have two null parameters for the outfile library and name. These parameters are null as they contain two apostrophes ( ' ) next to one another with nothing in-between.

I have a report type of 1, which means I want to display my results.

The interesting columns from the results are:

JOBNAME DUMPTIME DUMP_BY_USER ----------------------- -------------------------- ------------ 122830/SIMON/QPADEV0003 2022-01-23 20:28:15.758678 SIMON 122830/SIMON/QPADEV0003 2022-01-23 20:28:15.759247 SIMON 122830/SIMON/QPADEV0003 2022-01-23 20:28:15.759295 SIMON CURSOR_NAME PSEUDO_CLOSED OBJECT_LIBRARY OBJECT_NAME OBJECT_TYPE ----------- ------------- -------------- ----------- ----------- C2 NO MYLIB TESTPGM PGM C1 NO MYLIB TESTPGM PGM C0 NO MYLIB TESTPGM PGM

The columns I chose are:

• JOBNAME:  Name of the job, which I passed to the procedure
• DUMPTIME:  Time this row was inserted into the results
• DUMP_BY_USER:  The person who inserted this row. In other words the person who called the procedure
• CURSOR_NAME:  Name of the open cursor
• PSEUDO_CLOSED:  "NO" indicates that the cursor is open
• OBJECT_LIBRARY:  The name of the library the program is in
• OBJECT_NAME:  The name of the object
• OBJECT_TYPE:  Object type of the object

There are three other report types, all of which write the same results to an output file.

If report type 2 is used then the object library and file must be null, as they are in report type. The results are written to the SQL table SQL_CURSORS in QTEMP.

CALL QSYS2.DUMP_SQL_CURSORS('122830/SIMON/QPADEV0003','','',2)

Now I have to use the following Select statement to see my results:

SELECT * FROM QTEMP.SQL_CURSORS

The results are:

SQL_IDENTITY DUMPTIME DUMP-BY_USER ------------ -------------------------- ------------ 1 2022-01-23 20:28:15.758678 SIMON 2 2022-01-23 20:28:15.759247 SIMON 3 2022-01-23 20:28:15.759295 SIMON CURSOR_NAME PSEUDO_CLOSED OBJECT_LIBRARY OBJECT_NAME OBJECT_TYPE ----------- ------------- -------------- ----------- ----------- C2 NO MYLIB TESTPGM PGM C1 NO MYLIB TESTPGM PGM C0 NO MYLIB TESTPGM PGM JOBNAME ----------------------- 122830/SIMON/QPADEV0003 122830/SIMON/QPADEV0003 122830/SIMON/QPADEV0003

The output files all contain a column that is not found with the report type 1:

• SQL_INDENTITY:  A unique generated number for the results

If I run report type 2 again the outfile is replaced with a new copy of results.

Report type 3 allows me to choose where I want my outfile to be as I can now use the library and object name parameters:

In this example I am going to run DUMP_SQL_CURSORS with report type 3, twice:

CALL QSYS2.DUMP_SQL_CURSORS('123052/SIMON/QPADEV0016','QTEMP','OUTFILE',3) ; CALL QSYS2.DUMP_SQL_CURSORS('123052/SIMON/QPADEV0016','QTEMP','OUTFILE',3) ;

I can use the OBJECT_STATISTICS table function to get a list of the files in QTEMP to see if my outfile was created:

SELECT OBJLIB AS "Library", OBJNAME AS "Obj name", OBJLONGNAME AS "Long name", OBJTYPE AS "Type" FROM TABLE(QSYS2.OBJECT_STATISTICS('QTEMP','FILE'))

I am just passing two parameters to OBJECT_STATISTICS the library name, QTEMP, and the object type I want returned, FILE. My results are:

Library Obj name Long name Type ------- ---------- ----------- ----- QTEMP OUTFILE OUTFILE *FILE QTEMP SQL_C00001 SQL_CURSORS *FILE

In the first result I can see the outfile I created with report type 3, and I can see the outfile, SQL_CURSORS, I created with report type 2.

I am only interested in three of the columns in the outfile to illustrate what happened:

SELECT SQL_IDENTITY,CURSOR_NAME,DUMPTIME FROM QTEMP.OUTFILE

The results are:

SQL_IDENTITY CURSOR_NAME DUMPTIME ------------ ----------- -------------------------- 1 C0 2022-01-23 20:58:44.900025 2 C1 2022-01-23 20:58:44.900189 3 C2 2022-01-23 20:58:44.900256 1 C0 2022-01-23 20:58:47.493314 2 C1 2022-01-23 20:58:47.493479 3 C2 2022-01-23 20:58:47.493546

As I ran the DUMP_SQL_CURSORS twice I have two, repeated, sets of results in the file.

The fourth report type when executed it will only add to an existing outfile. If one of the paased name is not found it will not create one.

If DUMP_SQL_CURSORS with report type 4 to an outfile that does not exist:

CALL QSYS2.DUMP_SQL_CURSORS('123052/SIMON/QPADEV0016','QTEMP','OUTFILE1',4)

No error is returned. When I look to see if the outfile was created:

SELECT * FROM TABLE(QSYS2.OBJECT_STATISTICS('QTEMP','FILE',OBJECT_NAME => 'OUTFILE1'))

No results are returned, therefore, the outfile does not exist.

But if I use an existing output file:

CALL QSYS2.DUMP_SQL_CURSORS('123052/SIMON/QPADEV0016','QTEMP','OUTFILE',4)

Then results are added to the existing outfile:

SELECT SQL_INDENTITY FROM QTEMP.OUTFILE

The results from this statement show a third set of results, which were added by the report type 4 statement:

SQL_IDENTITY ------------ 1 2 3 1 2 3 1 2 3

When you run these statements if you see the following additional result:

JOBNAME DUMPTIME DUMP-BY_USER ----------------------- -------------------------- ------------ 122830/SIMON/QPADEV0003 2022-01-23 20:21:25.804584 SIMON CURSOR_NAME PSEUDO_CLOSED ----------- ------------- SQEXC NO

This result is returned as you are using STRSQL. Stop using old fashioned STRSQL! And start using the more flexible and simpler Run SQL Scripts.

 

You can learn more about the DUMP_SQL_CURSORS SQL procedure from the IBM website here.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.