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.
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:
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.
|
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);