Maximum File Wait Time of *IMMED Causes SQL0913

IBM DB2 for i uses a performance enhancement referred to as soft-closed or pseudo-closed cursors. When a cursor is closed by the application, the actual close operation is delayed, which leaves the files referenced by the cursor open. Certain types of operations (for example, some lock conflicts) can cause the close operation to complete. For the close operation to complete in time to avoid lock conflict errors, the database files must have a maximum file wait time value greater than *IMMED. A value of 1 second is sufficient for most systems (depending on overall system performance). The CHGPF command can be used to set this value:
Maximum file wait time . . . . . . . . . . : WAITFILE 1

Note some commands and operations do not attempt to close pseudo-closed cursors. The WAITFILE parameter has no effect in these situations.

One example of a failure involving a WAITFILE of *IMMED involves a pseudo-closed updatable cursor. This cursor holds a *SHRUPD lock member lock. If the application attempts an operation that requires a *SHRNUP (for example, a SELECT DISTINCT), a potential lock conflict arises. The IBM System i products database signals that the psuedo-closed should be closed; however, the operation requires a finite amount of time to complete. If the file wait time is *IMMED, an error is returned before the lock can be released. The failure is reported as SQL0913. The job log shows the following messages:

CPI430A COMMIT(*ALL) or COMMIT(*CS) specified but files locked *SHRNUP.
CPF4270 Cannot allocate member
MCH2603 Invalid unlock request
SQL0913 Row or object in use

After the failure, the member no longer has the *SHRUPD lock. The solution is to specify a nonzero wait time on the file.


V4R5 Equivalency

Some users might notice this problem only after an upgrading from R450. R510 APAR SE06874 fixes a problem affecting R450 systems where the WAITFILE parameter is not honored. To change the R510 behavior so that new tables and files have a WAITFILE time equivalent to the R450 value, run the following commands. These commands change the default WAITFILE value for the CRTPF and CRTLF commands in QSYS. On the operating system command line, type each command and press the Enter key.

Note: The following commands must be issued on each subsequent release.

CHGCMDDFT CMD(QSYS/CRTLF) NEWDFT('WAITFILE(30)')

CHGCMDDFT CMD(QSYS/CRTPF) NEWDFT('WAITFILE(30)')

The command changes makes the WAITFILE default equal to 30 seconds (same as V4R5) for following cases: