DUMP_SQL_CURSORS procedure

The DUMP_SQL_CURSORS procedure lists the open cursors for a job.

Authorization: None required if the caller's user profile is same as the job user identity of the qualified job for which the open cursors are being listed. Otherwise, the caller must have *JOBCTL special authority or be authorized to the QIBM_DB_SQLADM or QIBM_DB_SYSMON function usage ID.

To place the results in library-name.file-name, the caller must have:
  • *EXECUTE authority to the library, and
  • *OBJOPR and *ADD authorities to the file.

If the file does not exist, the caller must have *ADD authority to the library.

Read syntax diagramSkip visual syntax diagramDUMP_SQL_CURSORS( JOB_NAME => job-name,LIBRARY_NAME => library-name,FILE_NAME =>  file-name,OUTPUT_OPTION => output-option )

The schema is QSYS2.

job-name
A character string containing a qualified job name or a value of '*' to indicate the current job
library-name
A character string containing a system library name for the procedure output. An empty string is allowed.
file-name
A character string containing a system file name for the procedure output. An empty string is allowed.
output-option
An integer value that indicates how to return the output.
1
Ignore library-name and file-name parameters and return a result set.
2
Ignore library-name and file-name parameters and place the results in table QTEMP/SQL_CURSORS.
3
Place the results in file file-name in library library-name. If the file doesn't exist, it will be created. If the file already exists, the results will be appended to the existing file.
4
Place the results in file file-name in library library-name. If the file doesn't exist, it will not be created.

The result set that is returned or the file that is created contains the following columns:

Table 1. DUMP_SQL_CURSORS result table
Column Name System Column Name Data Type Description
SQL_IDENTITY SQL_I00001 INTEGER Unique identifier for the row.
DUMPTIME DUMPTIME TIMESTAMP Timestamp when row was inserted.
DUMP_BY_USER DUMPUSER VARCHAR(18) User ID used to insert row.
CURSOR_NAME CSRNAME VARCHAR(128) Name of the cursor.
PSEUDO_CLOSED PSEUDO VARCHAR(3) Pseudo close state of the cursor.
YES
Cursor is currently pseudo closed.
NO
Cursor is currently opened.
STATEMENT_NAME STMTNAME VARCHAR(128) Name of the statement corresponding to the cursor
OBJECT_NAME OBJNAME CHAR(10) Object containing the current SQL statement. Blank if current SQL statement is not in a program, service program, or package.
OBJECT_LIBRARY OBJLIB CHAR(10) Library for object containing the current SQL statement. Blank if current SQL statement is not in a program, service program, or package.
OBJECT_TYPE OBJTYPE CHAR(10) Type of object containing the current SQL statement. Blank if current SQL statement is not in a program, service program, or package.
JOBNAME JOBNAME CHAR(28) System job name for the cursor. Contains * if current job was specified for job-name argument.

Example

Populate QGPL/SQLCSR1 file with open SQL cursors for the current job.

  CALL QSYS2.DUMP_SQL_CURSORS('*', 'QGPL', 'SQLCSR1', 3);