SQL0913 on IBM i: Row or Object in Use and How to Investigate It

Not SQL0911. Material describing SQL0911 / SQLCODE -911 as a “deadlock victim” condition with “reason code 68” comes from other Db2 products (Db2 for Linux, UNIX, and Windows uses an “N” suffix, e.g. SQL0911N). IBM i’s own SQLSTATE listing for Db2 for i maps the lock-contention SQLSTATE 57033 to SQLCODE -913, and no -911 entry was found in that listing (see Sources Consulted). On IBM i, the message you will see in the job log for row- or object-level lock contention is SQL0913, and that is what this article covers.

This article is for RPG and SQL developers and IBM i operations staff who have hit SQLCODE -913 or message ID SQL0913 in a job log or an SQLRPGLE program, and need to understand what caused it, how to find who is holding the conflicting lock, and how to handle it safely in code.

Prerequisites: a basic understanding of Db2 for i record and object locking, commitment control concepts (lock duration under a unit of work), file wait-time attributes, and enough RPGLE/SQLRPGLE to follow an embedded SQL statement and an SQLCODE/SQLSTATE check.

What SQL0913 means

SQLCODE -913, which appears in the job log as message ID SQL0913, is Db2 for i’s way of reporting that a row or an object your statement needed is currently in use by another process and could not be locked within the time your job was willing to wait. An IBM support page shows a JDBC client receiving it in the form [SQL0913] Row or object F55500 in type *FILE in use. The fuller explanation below comes from third-party sources that quote the message help text; it could not be confirmed against IBM’s primary SQL message listing, so it is marked UNVERIFIED_SOURCE. According to that text, the message describes one of two related situations:

the object itself is in use by another application process, or
a row in the object is in use by either another application process or another cursor in the same application process (a “pseudo-closed” or soft-closed cursor case — see below).

SQLSTATE: 57033. Confirmed against IBM’s Db2 for i “Listing of SQLSTATE values” (IBM i 7.4), which describes 57033 as “Deadlock or timeout occurred without automatic rollback” and lists SQLCODE -913 for it. The “without automatic rollback” part matters: your program, not the database, decides whether to roll back the unit of work.

How SQL0913 differs from “just waiting longer”

This is the part that matters most for designing correct error handling, and it is confirmed by IBM’s own support documentation (see Sources Consulted): SQL0913 is not simply “the job waited its full lock-wait time and then failed,” the way some other lock-related SQLCODEs behave. IBM’s support note on this exact message describes a specific case where a file’s maximum wait-time attribute is set to *IMMED (no wait at all): Db2 for i uses soft-closed (“pseudo-closed”) cursors, meaning a cursor your program closed may still be holding a file open behind the scenes while the database finishes cleaning it up. If a later statement in the same or another job then needs a conflicting lock on that file, and the file’s wait time is *IMMED, the database returns SQL0913 immediately rather than waiting even briefly for that cleanup to finish.

In other words: whether you get SQL0913 at all, versus the database quietly waiting out the conflict and succeeding, can depend directly on a file-level wait-time attribute, not just on how long the underlying lock conflict actually lasts. A file with *IMMED wait time will surface SQL0913 for a conflict that a file with a one-second wait time might never surface at all, because the conflicting cursor-close finishes within that second.

The other common cause — described in third-party vendor documentation of the same message and consistent with IBM’s object/row-lock model, though not confirmed against IBM’s primary reference (UNVERIFIED_SOURCE for the exact framing) — is a genuinely different job or user holding a lock on the row or object for long enough that your statement cannot get its own lock. This is the “real contention” case, as opposed to the pseudo-closed-cursor timing case above.

How to investigate SQL0913

Start with the job log — DSPJOBLOG and the CPD6DEF diagnostic

Confirmed via IBM’s own support documentation (see Sources Consulted): for failed ALTER TABLE, DELETE, INSERT, UPDATE, MERGE, SELECT INTO, and VALUES INTO statements, a CPD6DEF diagnostic message appears in the job log immediately before the SQL0913 message, and it contains detail identifying the lock holder. Use DSPJOBLOG (or WRKJOB with OPTION(*JOBLOG), as described in IBM i Job Log and CPF Messages Reference) and look at the message immediately preceding the SQL0913 entry — do not skip past it. This is the single most useful first step.

WRKOBJLCK — who holds the object-level lock

WRKOBJLCK lets you look up the lock status of a specific object and see which job(s) currently hold a lock on it:

WRKOBJLCK OBJ(mylib/myfile) OBJTYPE(*FILE)

This is useful when the CPD6DEF detail (or your own suspicion) points at a specific file or object. Two limitations to know:

It does not show record locks. IBM’s CL reference for WRKOBJLCK states: “This command does not show record locks for database files.” If the conflict is on a row, use DSPRCDLCK (below) instead.
It may not always name the holder, and can be slow. A practitioner source reports that WRKOBJLCK does not always report the name of the locking job, and that the command can sometimes appear to hang while gathering lock information. IBM’s command documentation does not mention this, so treat it as a field report rather than guaranteed behavior (UNVERIFIED_SOURCE).

DSPRCDLCK — who holds record-level locks on a file

DSPRCDLCK is the record-level counterpart. Per IBM’s CL reference, it shows “the current record lock status of a particular database physical file member”, either for one relative record number or for all locked records in the member.

DSPRCDLCK FILE(mylib/myfile)

Its output goes to the display (OUTPUT(*)) or to spooled output (OUTPUT(*PRINT)); IBM documents no option to write it to a database file, so it cannot be read directly into a program. If you need record-lock information as queryable rows, IBM documents the QSYS2.RECORD_LOCK_INFO SQL view, which “returns one row for every record lock for the partition”. IBM advises restricting it with a WHERE clause (for example on the schema and table name) to avoid excessive resource use. The caller needs *EXECUTE authority to the file’s library and *OBJOPR and *READ authority to the file. Practitioner sources date the view to IBM i 7.2 TR3 / 7.1 TR11; the IBM page does not state the introduction release (UNVERIFIED_SOURCE for that release), so confirm availability on your system before depending on it.

Once you have found the holder

After WRKOBJLCK or DSPRCDLCK identifies the job holding the conflicting lock, use WRKJOB on that job (or its own job log) to understand what it is doing and why it still holds the lock — for example, a long-running batch unit of work that has not yet committed. See IBM i Job Log and CPF Messages Reference for how to read a job’s message history in general.

File wait-time settings and commitment control

Two separate knobs affect how often SQL0913 shows up and how it interacts with your application:

The file’s wait-time attribute (WAITFILE on CRTPF/CHGPF, and WAITRCD for the record-level equivalent). Confirmed by IBM’s support documentation: a file set to *IMMED wait time can produce SQL0913 for a conflict that would otherwise clear itself within a second or two, because of the pseudo-closed-cursor interaction described above. IBM’s own recommendation (quoted in Sources Consulted) is to set a nonzero wait time — the note gives one second as “sufficient for most systems” — rather than leaving production files at *IMMED. Example: CHGPF FILE(mylib/myfile) WAITFILE(30). Per IBM’s CRTPF CL reference (IBM i 7.4 and 7.5), WAITFILE accepts *IMMED, *CLS (use the job default wait time), or 1–32767 seconds, with a shipped default of 30 seconds; WAITRCD (“the number of seconds that the program waits for a record being changed or deleted”) accepts *IMMED, *NOMAX (32,767 seconds), or 1–32767 seconds, with a shipped default of 60 seconds. Check the current value on an existing file with DSPFD before changing it.
Commitment control. Under commitment control, locks acquired during a unit of work are generally held until COMMIT or ROLLBACK rather than released as each statement finishes. A long-running unit of work — particularly a batch job that updates many rows before committing — holds its locks longer, which increases the chance that another job’s statement collides with one of those locks and surfaces as SQL0913 (if that job’s own wait-time setting is short) rather than simply waiting it out. See Commitment Control with STRCMTCTL: Commit, Rollback, and Savepoints Across Multiple Files for commit/rollback mechanics, and Journaling and Commitment Control Explained for the broader journaling context.

In short: SQL0913 frequency is shaped by both how long other jobs hold their locks (commitment control scope, batch design) and how long your own job is willing to wait before giving up (file-level wait-time attributes). Fixing only one side of that equation may not be enough.

A conservative SQLRPGLE example

STATIC CHECKED — this example has not been compiled or run on a live IBM i or PUB400 system. It is reviewed for syntactic plausibility against known SQLRPGLE/embedded-SQL conventions but is not confirmed to compile or execute correctly. Treat it as an illustrative starting point, not production-ready code, and test it before use.

The pattern below deliberately does not retry automatically. Unlike a simple lock-wait timeout that resolves itself once the blocking job finishes, SQL0913 can reflect a structural mismatch (a file’s wait-time attribute set too aggressively, or a genuinely long-held lock from a batch job) that a tight retry loop will not fix — and retrying immediately into the same contention can make things worse for every other job waiting on that resource. The safer default is to capture the diagnostic detail for investigation and let a human or a carefully designed, rate-limited retry policy decide what to do next.

**FREE
ctl-opt main(UpdateBalance);

dcl-proc UpdateBalance;
  dcl-pi *n;
    custNo packed(7:0) const;
    amount packed(11:2) const;
  end-pi;

  dcl-s sqlStateW char(5);
  dcl-s sqlCodeN  int(10);
  dcl-s reasonTxt char(70);

  exec sql
    UPDATE CUSTMAST
       SET BALANCE = BALANCE + :amount
     WHERE CUSTNO = :custNo;

  sqlCodeN = sqlcode;
  sqlStateW = sqlstate;

  if sqlCodeN = 0;
    exec sql COMMIT;

  elseif sqlCodeN = -913;
    // SQL0913: row or object in use by another process, or a
    // pseudo-closed cursor conflict against a file whose wait-time
    // attribute (WAITFILE/WAITRCD) is too short to let the conflict
    // clear on its own. Do NOT assume this is safe to retry blindly --
    // unlike a plain lock-wait timeout, the underlying cause here can
    // be a long-held lock from another job (e.g. an uncommitted batch
    // unit of work) that will not clear soon, and hammering it with
    // retries adds load to an already-contended resource.
    //
    // Capture the diagnostic detail for investigation: check the job
    // log for the CPD6DEF message that IBM i places immediately
    // before SQL0913 for INSERT/UPDATE/DELETE/MERGE/ALTER TABLE/
    // SELECT INTO/VALUES INTO statements -- it names the lock holder.
    // Cross-check with WRKOBJLCK (object-level) or DSPRCDLCK
    // (record-level) against the specific file.
    reasonTxt = sqlerrmc;
    exec sql ROLLBACK;

    // Log reasonTxt, sqlStateW, and sqlCodeN to an application log or
    // message queue here for operations follow-up. This routine
    // intentionally does not loop and retry automatically -- that
    // decision is left to a caller-level policy that has visibility
    // into whether the conflict is a brief timing issue (adjust
    // WAITFILE/WAITRCD) or a structural batch-overlap problem
    // (requires changing the batch job's commit frequency, not a
    // tighter retry loop in this routine).
    return;

  else;
    // Some other SQLCODE entirely -- not a lock-contention issue.
    exec sql ROLLBACK;
    return;
  endif;

end-proc;

Points to note about this example:

It branches on SQLCODE, and separately captures SQLSTATE and the raw SQLERRMC reason text for the log entry rather than assuming a specific literal string — that text should always be read from your own job log/SQLERRMC output, not copied from this or any other article. SQLSTATE is not itself part of the branching logic here; it’s recorded alongside SQLCODE as extra diagnostic detail.
It does not implement automatic retry-with-backoff as a general-purpose fix. Treating lock-related SQLCODEs as uniformly safe to retry does not hold for SQL0913: the conservative default here is to log and stop, leaving the decision of whether and how to retry to a caller or an operations process that understands the specific cause (wait-time tuning versus a genuinely long-held lock elsewhere).
If you do build a retry policy on top of this, it should be rate-limited, capped, and ideally informed by actually checking whether the lock holder (found via WRKOBJLCK/DSPRCDLCK) is still active, rather than retrying blindly on a fixed schedule.

Common mistakes and troubleshooting

Assuming SQL0913 behaves like a generic lock-wait timeout. It does not always represent “the job waited its full configured time.” With *IMMED file wait time, it can fire essentially instantly against a pseudo-closed cursor that would have cleared itself in well under a second with a nonzero wait time set.
Retrying automatically without investigating. Because SQL0913 can reflect either a short-lived timing artifact or a genuinely long-held lock from another job, a blind retry-with-backoff loop is not a universally safe default the way it might be for some other conditions. Investigate with WRKOBJLCK/DSPRCDLCK/DSPJOBLOG first.
Skipping the CPD6DEF message. The diagnostic message immediately preceding SQL0913 in the job log is specifically documented by IBM as containing the lock-holder detail for the common DML statement types. Reading past it without checking is a common way to miss the actual cause.
Leaving files at *IMMED wait time. The current CRTPF CL reference lists 30 seconds as the shipped WAITFILE default, but files can still end up at *IMMED: created with an explicit WAITFILE(*IMMED), created while a shop-changed command default was in effect, or created on a release where the default differed (IBM’s support note on this topic recommends CHGCMDDFT CMD(QSYS/CRTPF) NEWDFT('WAITFILE(30)'), and the same for CRTLF, to restore a 30-second default that also applies to SQL CREATE TABLE and CREATE VIEW). Per that note, *IMMED increases exposure to SQL0913 from ordinary pseudo-closed-cursor timing rather than genuine long-held contention. Check suspect files with DSPFD.
Not checking commitment control scope. A batch job that updates many rows before its first COMMIT holds its locks far longer than necessary, raising the odds that an interactive job’s shorter wait time surfaces SQL0913 during the overlap window.
Applying “SQL0911” guidance from other Db2 platforms. Material describing “-911 reason code 68” deadlock-victim handling comes from a different Db2 product family’s message set. On IBM i, lock contention is reported as SQL0913 / SQLSTATE 57033, and IBM’s Db2 for i SQLSTATE listing does not show a -911 entry for it.

Performance and security considerations

File wait-time tuning is a genuine lever, not just a workaround. Per IBM’s own guidance, raising a file’s wait time from *IMMED to a small nonzero value (IBM’s example: one second) can eliminate a meaningful share of SQL0913 occurrences that are really pseudo-closed-cursor timing artifacts rather than real, sustained contention — without masking genuine long-held-lock problems, which will still surface as SQL0913 (or a visible delay) regardless of this setting.
Do not over-widen lock scope to make the error go away. As with any lock-contention SQLCODE, resist the temptation to grab a broader lock (e.g., an object-level lock instead of relying on row-level locking) just to prevent SQL0913 from surfacing. That trades a recoverable, visible error for a much larger availability problem for every other job touching that object.
Commit more frequently in batch processes. Reducing the span of a unit of work directly reduces how long a batch job holds locks that could otherwise collide with interactive access.
Log the diagnostic detail, not just the SQLCODE. Capturing SQLERRMC, SQLSTATE, and ideally the preceding CPD6DEF text when you log an SQL0913 occurrence gives you an audit trail that supports both root-cause analysis and capacity-planning decisions about batch windows.

A realistic practical use case

A common real-world trigger: a nightly batch job updates customer balance records across a large table under commitment control, committing only once at the end of the run to keep the logic simple. During early-morning hours when interactive customer-service staff are also updating the same table row by row, two things can go wrong at once. First, the batch job’s long-held locks (not yet committed) can directly block an interactive update, which — if the interactive job’s file has a short or *IMMED wait time — surfaces as SQL0913 rather than a brief, invisible wait. Second, even outside the batch window, an interactive program that repeatedly opens and closes the same cursor against a file left at *IMMED wait time can occasionally hit SQL0913 purely from pseudo-closed-cursor timing, with no other job involved at all. The fix is two-pronged: commit the batch job in smaller chunks so its locks are held for seconds rather than for the whole run, and confirm with DSPFD that production files are not set to *IMMED wait time.

FAQ

Is SQL0913 the same as a lock-wait timeout?
Not exactly. A classic lock-wait timeout implies the job waited its full configured time and then gave up. SQL0913 can happen that way, but it can also happen almost immediately against a file with *IMMED wait time, because of how Db2 for i handles pseudo-closed cursors. Treat SQL0913 as “the lock request failed,” not strictly as “the job waited and then failed.”

Is it safe to just retry automatically when I see SQL0913?
Not as a universal policy. This article deliberately does not present blind retry-with-backoff as a safe default for SQL0913, unlike some framings used for other lock-related SQLCODEs. Because SQL0913 can reflect a genuinely long-held lock from another job (for example, an uncommitted batch unit of work), retrying immediately and repeatedly just adds load to an already-contended resource without addressing the cause. Investigate first with WRKOBJLCK/DSPRCDLCK/DSPJOBLOG, and only build a retry policy once you understand which kind of conflict you are actually seeing.

Where do I find the exact IBM message text for SQL0913?
The full cause and recovery text could not be confirmed against IBM’s primary SQL message listing for this article; the explanation summarized above comes from third-party sources quoting the message and is marked UNVERIFIED_SOURCE. IBM documents that SQL message text is stored in the QSQLMSG message file, so the authoritative text for your release is available on your own system: display the SQL0913 entry’s second-level text in the job log (DSPJOBLOG), or use DSPMSGD RANGE(SQL0913) MSGF(QSYS/QSQLMSG).

What is CPD6DEF and why does it matter?
CPD6DEF is a diagnostic message that IBM i places in the job log immediately before SQL0913 for failed ALTER TABLE, DELETE, INSERT, UPDATE, MERGE, SELECT INTO, and VALUES INTO statements. It contains detail identifying the job holding the conflicting lock, confirmed via IBM’s own support documentation (see Sources Consulted). Always check it before concluding you don’t have enough information to find the lock holder.

Does changing a file’s wait time fix SQL0913 permanently?
It fixes the subset of SQL0913 occurrences caused by pseudo-closed-cursor timing against a file left at *IMMED wait time — IBM’s own guidance is to set a nonzero value such as one second. It does not fix occurrences caused by a genuinely long-held lock from another job; those require finding and addressing that job’s behavior (commit frequency, batch design) via WRKOBJLCK/DSPRCDLCK/DSPJOBLOG.

Is there an SQL0911 on IBM i that this article should also cover?
No. The “-911 / SQL0911N” deadlock-victim framing belongs to a different Db2 product family. IBM’s Db2 for i SQLSTATE listing maps 57033 (“Deadlock or timeout occurred without automatic rollback”) to -913, and no -911 entry was found there. Genuine deadlock handling on IBM i is a separate topic and is out of scope for this article.

Sources Consulted

IBM Documentation, Db2 for i “Listing of SQLSTATE values” (IBM i 7.4): https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzala/rzalaccl.htm — Confirms: SQLSTATE 57033, “Deadlock or timeout occurred without automatic rollback,” SQLCODE -913. No -911 entry was found in the listing as retrieved.
IBM Documentation, “SQLCODEs and SQLSTATEs” (IBM i 7.4): https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzala/rzalaco.htm — Confirms: the SQL message identifier is formed from the absolute value of the SQLCODE, and SQL message text is stored in the QSQLMSG message file.
IBM Support: https://www.ibm.com/support/pages/node/1167922 — Confirms: the CPD6DEF diagnostic message contains lock-holder detail and appears immediately before SQL0913 for ALTER TABLE, DELETE, INSERT, UPDATE, MERGE, SELECT INTO, and VALUES INTO statements.
IBM Support: https://www.ibm.com/support/pages/maximum-file-wait-time-immed-causes-sql0913 — Confirms: a file maximum wait time of *IMMED can cause SQL0913 because the close of a pseudo-closed cursor is delayed, leaving the file open; files should have a wait time greater than *IMMED, and “a value of 1 second is sufficient for most systems”; suggests CHGCMDDFT CMD(QSYS/CRTPF) NEWDFT('WAITFILE(30)') (and the same for CRTLF) so the 30-second default also applies to SQL CREATE TABLE/CREATE VIEW.
IBM Support: https://www.ibm.com/support/pages/node/577425 — shows a JDBC client receiving [SQL0913] Row or object F55500 in type *FILE in use. Abstract only; does not give the full cause/recovery text.
IBM Documentation, CRTPF command (IBM i 7.5 and 7.4): https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/cl/crtpf.html — Confirms: WAITFILE values 30 (default), *IMMED, *CLS, 1–32767; WAITRCD values 60 (default), *IMMED, *NOMAX (32,767 seconds), 1–32767, and their descriptions.
IBM Documentation, WRKOBJLCK command (IBM i 7.5): https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/cl/wrkobjlck.html — Confirms: OBJ and OBJTYPE are the required parameters; “This command does not show record locks for database files.” Does not mention the command hanging or omitting the holder.
IBM Documentation, DSPRCDLCK command (IBM i 7.5): https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/cl/dsprcdlck.html — Confirms: shows the current record lock status of a physical file member, for one relative record number or all locked records; OUTPUT(*) displays and OUTPUT(*PRINT) prints to spooled output; no database-file output option is documented.
IBM Documentation, RECORD_LOCK_INFO view (IBM i 7.5): https://www.ibm.com/docs/en/i/7.5?topic=services-record-lock-info-view — Confirms: one row for every record lock for the partition; authority requirements as stated above. Does not state the introduction release.
Third-party sources (not IBM documentation): a Hibernate forum post quoting the SQL0913 help text (“object in use by another application process, or a row in it in use by another application process or another cursor in this application process”); a practitioner report that WRKOBJLCK does not always name the locking job and can appear to hang; rpgpgm.com posts dating QSYS2.RECORD_LOCK_INFO to IBM i 7.2 TR3 / 7.1 TR11; a Syntax Consulting blog post recommending DSPRCDLCK and the locking job’s own job log. Claims that rest only on these sources are marked UNVERIFIED_SOURCE in the text.

The full SQL0913 entry in IBM’s “Listing of SQL messages” page could not be retrieved for this article, so the exact cause and recovery text remains UNVERIFIED_SOURCE. Confirm it against that page, or with DSPMSGD against QSQLMSG on your own system, before coding production logic against specific literal text.