Troubleshooting
Problem
Commonly asked questions about pseudo closed cursors and the locks that result from their use are documented.
Resolving The Problem
What Is a Pseudo Closed Cursor?
Pseudo closed cursors are a key part of a performance optimization feature of IBM DB2 for i SQL. When an application closes a cursor, Db2 for i normally closes the cursor and closes the file, deleting the ODP (Open Data Path). If the application runs the same statement multiple times, each new execution requires a full open of the target file. The idea behind pseudo closed cursors is to not fully close the cursor and file but rather to cache the cursor for potential future use. The cursor is left in a soft closed (or pseudo closed) state. When the cursor is pseudo closed, the underlying file and ODP are left open. All record locks are released; however, a shared lock still appears on the file. Db2 for i can then reuse the cursor as needed without the cost of a full open of the file. Db2 for i can also decide to hard close the pseudo closed cursor when needed.
Pseudo closed cursors are a key part of a performance optimization feature of IBM DB2 for i SQL. When an application closes a cursor, Db2 for i normally closes the cursor and closes the file, deleting the ODP (Open Data Path). If the application runs the same statement multiple times, each new execution requires a full open of the target file. The idea behind pseudo closed cursors is to not fully close the cursor and file but rather to cache the cursor for potential future use. The cursor is left in a soft closed (or pseudo closed) state. When the cursor is pseudo closed, the underlying file and ODP are left open. All record locks are released; however, a shared lock still appears on the file. Db2 for i can then reuse the cursor as needed without the cost of a full open of the file. Db2 for i can also decide to hard close the pseudo closed cursor when needed.
How to detect a pseudo closed cursor?
See DUMP_SQL_CURSORS procedure
When Is a Cursor Pseudo Closed?
This functionality varies depending on PTF level. In general, the first time a dynamic SQL statement is prepared, run, and closed, the cursor is hard closed. The ODP is deleted, and the file is closed. However, Db2 for i keeps track of the SQL statement and can detect when it is run a second time. Db2 for i tracks the number of times the statement is used against the number of times the program is invoked. These statistics are kept in the SQL statement area in the SQL package, Prepared Statement Area, or Program Associated Space. If the same statement is run a second time, the cursor is pseudo-closed and cached. It is left in the Open Cursor List but a bit is set showing the cursor as pseudo closed. Further running of the same SQL statement can then use the existing ODP (assuming that it is reusable). Note R420 APAR SA76930 enabled support for forcing cursors to pseudo close (rather than hard close) on the first execution. In R520 IBM OS/400, statements executed out of an SQL package might be pseudo-closed after the first execution.
How Many Pseudo Closed Cursors Are Cached? Can This Be controlled?
The number of pseudo closed cursors that are held is set by IBM and can be changed at any time. Internally, pseudo closed cursors are tracked differently depending on how they are named. Cursors that reuse an existing cursor's name are treated differently than cursors that have a unique name.
The current default setting for uniquely named cursors is no limit. The limit can be controlled in R450 operating system and later by the OPEN_CURSOR_THRESHOLD QAQQINI file option. For more information, see see QAQQINI query options. The limit is enforced only during a full open. Therefore, some applications might exceed the limit.
If the application reuses the same cursor name (close cursor, drop statement, allocate a new statement that uses the previous cursor name), the pseudo closed cursor is treated differently. Db2 for i must rename the cursor, cache the new name, and match a later run of the same statement with the renamed cursor. Currently, the limit to these pseudo closed cursors is 150. The limit is enforced when an SQL statement is prepared. Therefore, it is possible for jobs to exceed this limit. For example, statements executed from extended dynamic packages are not prepared. In situations like this, the 150 cursor limit can be exceeded.
What Happens When the Limit Is Reached?
By default, all cursors are hard closed and the entire list is cleared when the limit is reached (this limit is subject to change). The cursor close behavior can be adjusted by using the OPEN_CURSOR_CLOSE_COUNT QAQQINI file option. This option affects only the uniquely named cursor list. The list of 150 renamed ("dummy") cursors is always cleared when the limit is reached. For more information, see QAQQINI query options
What Are the Impacts of Pseudo Closed Cursors?
Increase use of storage: Each pseudo closed cursor represents a significant amount of storage used for the associated resources. A typical value is 1 Meg per cursor. The number of pseudo closed cursors can be reduced as previously discussed to decrease storage per job.
Shared file locks even when all cursors are closed:
As stated earlier, a pseudo closed cursor does not hold any record locks; however, it continues to hold a shared lock on the file. In theory, the shared lock left on the file has a minimal effect on other applications. Some operating system commands and all SQL statements that require an exclusive lock (DLTF, DROP TABLE, CLRPFM, and so on) cause a pseudo closed cursor to be hard closed so that the lock is released. Each command that wants to force closing of pseudo closed cursors causes an event to be signaled when a lock conflict occurs. This event causes a program to run that hard-closes any pseudo closed cursors that hold the object. For this feature to function correctly, the maximum file wait time (WAITFILE) must be set to a value other than *IMMED. In general, a value of 1 second or greater is sufficient (actual time depends on system performance).
Because hard closing of pseudo closed cursor depends on the implementation of each command, the behavior can vary between commands, operating system releases, and even PTFs. For example, the command ALCOBJ does not force pseudo closed cursors to hard close in R430 or later. It returns the message CPF3156 stating that the object cannot be allocated. Most IBM RPG/400® and COBOL programs that use native (non-SQL) database file functions that require an exclusive lock also fail when pseudo closed cursors exist.
How Do I Force Pseudo Closed Cursors to Hard close?
Force closing of the pseudo closed cursors of other jobs by specifying *RQSRLS for the Lock conflict action parameter of the ALCOBJ command. The syntax is:
When Is a Cursor Pseudo Closed?
This functionality varies depending on PTF level. In general, the first time a dynamic SQL statement is prepared, run, and closed, the cursor is hard closed. The ODP is deleted, and the file is closed. However, Db2 for i keeps track of the SQL statement and can detect when it is run a second time. Db2 for i tracks the number of times the statement is used against the number of times the program is invoked. These statistics are kept in the SQL statement area in the SQL package, Prepared Statement Area, or Program Associated Space. If the same statement is run a second time, the cursor is pseudo-closed and cached. It is left in the Open Cursor List but a bit is set showing the cursor as pseudo closed. Further running of the same SQL statement can then use the existing ODP (assuming that it is reusable). Note R420 APAR SA76930 enabled support for forcing cursors to pseudo close (rather than hard close) on the first execution. In R520 IBM OS/400, statements executed out of an SQL package might be pseudo-closed after the first execution.
How Many Pseudo Closed Cursors Are Cached? Can This Be controlled?
The number of pseudo closed cursors that are held is set by IBM and can be changed at any time. Internally, pseudo closed cursors are tracked differently depending on how they are named. Cursors that reuse an existing cursor's name are treated differently than cursors that have a unique name.
The current default setting for uniquely named cursors is no limit. The limit can be controlled in R450 operating system and later by the OPEN_CURSOR_THRESHOLD QAQQINI file option. For more information, see see QAQQINI query options. The limit is enforced only during a full open. Therefore, some applications might exceed the limit.
If the application reuses the same cursor name (close cursor, drop statement, allocate a new statement that uses the previous cursor name), the pseudo closed cursor is treated differently. Db2 for i must rename the cursor, cache the new name, and match a later run of the same statement with the renamed cursor. Currently, the limit to these pseudo closed cursors is 150. The limit is enforced when an SQL statement is prepared. Therefore, it is possible for jobs to exceed this limit. For example, statements executed from extended dynamic packages are not prepared. In situations like this, the 150 cursor limit can be exceeded.
What Happens When the Limit Is Reached?
By default, all cursors are hard closed and the entire list is cleared when the limit is reached (this limit is subject to change). The cursor close behavior can be adjusted by using the OPEN_CURSOR_CLOSE_COUNT QAQQINI file option. This option affects only the uniquely named cursor list. The list of 150 renamed ("dummy") cursors is always cleared when the limit is reached. For more information, see QAQQINI query options
What Are the Impacts of Pseudo Closed Cursors?
Increase use of storage: Each pseudo closed cursor represents a significant amount of storage used for the associated resources. A typical value is 1 Meg per cursor. The number of pseudo closed cursors can be reduced as previously discussed to decrease storage per job.
Shared file locks even when all cursors are closed:
As stated earlier, a pseudo closed cursor does not hold any record locks; however, it continues to hold a shared lock on the file. In theory, the shared lock left on the file has a minimal effect on other applications. Some operating system commands and all SQL statements that require an exclusive lock (DLTF, DROP TABLE, CLRPFM, and so on) cause a pseudo closed cursor to be hard closed so that the lock is released. Each command that wants to force closing of pseudo closed cursors causes an event to be signaled when a lock conflict occurs. This event causes a program to run that hard-closes any pseudo closed cursors that hold the object. For this feature to function correctly, the maximum file wait time (WAITFILE) must be set to a value other than *IMMED. In general, a value of 1 second or greater is sufficient (actual time depends on system performance).
Because hard closing of pseudo closed cursor depends on the implementation of each command, the behavior can vary between commands, operating system releases, and even PTFs. For example, the command ALCOBJ does not force pseudo closed cursors to hard close in R430 or later. It returns the message CPF3156 stating that the object cannot be allocated. Most IBM RPG/400® and COBOL programs that use native (non-SQL) database file functions that require an exclusive lock also fail when pseudo closed cursors exist.
How Do I Force Pseudo Closed Cursors to Hard close?
Force closing of the pseudo closed cursors of other jobs by specifying *RQSRLS for the Lock conflict action parameter of the ALCOBJ command. The syntax is:
ALCOBJ OBJ((QCUSTCDT *FILE *EXCL)) CONFLICT(*RQSRLS)Note: This command does not close pseudo closed cursors scoped to the current job. So, for example, if you run a command that requires the file be hard closed (in addition to an exclusive lock) and the current job has pseudo closed cursors, then the *RQSRLS option does not work. You must use one of the following techniques to hard close the cursors. The CHGPFCST command is one example of a command that requires a hard closed file.
To close all pseudo closed cursors in the current job the application can:
- Disconnect from the database server.
- Execute the statements DISCONNECT ALL; CONNECT RESET to essentially disconnect and reconnect to the local database.
Note: Both techniques have adverse effects on performance; avoid them if possible.
If a lock is still held after you try the ALCOBJ *RQSRLS, the lock is most likely held by an open cursor. Verify that the application issued the close cursor command and committed any open transaction. If you are using IBM i Access Client Solutions ODBC or IBM Toolbox for Java JDBC, verify that the lazy close option is disabled.
How Can I Track Pseudo Closed Cursor Reuse?
The database monitor utility, DBMON, can be used. The QQUCNT field on the open (1000 format) identifies a unique ODP (full open) while QVRCNT is incremented each time the ODP is used (a pseudo open). QQC11 of the 3010 records shows when the ODP is reusable.
If a lock is still held after you try the ALCOBJ *RQSRLS, the lock is most likely held by an open cursor. Verify that the application issued the close cursor command and committed any open transaction. If you are using IBM i Access Client Solutions ODBC or IBM Toolbox for Java JDBC, verify that the lazy close option is disabled.
How Can I Track Pseudo Closed Cursor Reuse?
The database monitor utility, DBMON, can be used. The QQUCNT field on the open (1000 format) identifies a unique ODP (full open) while QVRCNT is incremented each time the ODP is used (a pseudo open). QQC11 of the 3010 records shows when the ODP is reusable.
For more information about the DBMON tool, see Impact of collecting a DBMON trace - Start Database Monitor - STRDBMON.
The SQL Performance Monitor feature of System i Navigator can be used to easily check for statement reuse, reusable ODPs, number of full opens, and number of pseudo opens (reuse of pseudo closed cursor). Run the statement summary Basic Statement query from the Detailed Results tab.
Starting debug on a job (STRDBG command) adds debug messages to the job's job log. These messages can also be used to identify cursor reuse.
What are Common Reasons for Not Reusing a Pseudo Closed Cursor?
The query must be implemented with a reusable ODP. All attributes of the cursor must be the same (isolation level, ability to be updated, cursor type, and so on). The SQL Statement text must match exactly (including white space and the value of any literals). Anything that might cause an access plan rebuild can also prevent reuse.
Reuse of pseudo closed cursors is highly dependent on application design and to a lesser extent, operating system PTF levels and Client PTF levels (ODBC, JDBC, and so on). Ideally, the database always matches a later execution of the same SQL statement to an existing pseudo closed cursor when one is available. This behavior is not documented nor guaranteed (problems are not accepted as defect).
For best reuse, applications need to follow these guidelines:
- Use connection pooling.
- Use parameterized SQL statements.
- Use a statement cache.
[{"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"Host Servers","Platform":[],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB68","label":"Power HW"}}]
Historical Number
N1017851
Was this topic helpful?
Document Information
Modified date:
30 January 2025
UID
nas8N1017851
Manage My Notification Subscriptions