Wednesday, March 2, 2022

Finding open cursors within a job

looking for open sql cursors

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.

5 comments:

  1. Why would you force the hard closure of cursors with *EndMod? That can cause slow performance, and prevents the whole point of cached data in RAM for best performance. For the sake of performance, it is usually better to keep cursors opened till the end of the activation group, not *EndMod. Keeping cursors open between soft closes means that cache is there the next time that cursor is required in a subsequent call. You can soft close all you want, but do not hard close with *EndMod until the job ends (*EndActGrp). Keeping the cache in RAM between calls/cursor builds makes for faster execution, snappier programs. You can see these cached ODPs when you WrkJob, opt 14...those are good things to see, and never something that must be prevented.

    ReplyDelete
  2. Hi Simon, much appreciated for the effort although would be nice you include the cause/effect of something like the one your blog is referring to. What are the implications to leave SQL cursor opens?

    ReplyDelete
  3. To close the cursor I like the combination of on-exit and close cursor xyz

    ReplyDelete
  4. Simon, great read and very informative. A Open cursor can be a very big problem at the end of the cycle causing lots of problem.
    Thanks for sharing.

    ReplyDelete
  5. Thank you for this Simon. Another tool for the toolbox. Amazing.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.