SQLCode=-905, SQLState=57014: File Locking Error Fix for DB2 Databases

Running into SQLCODE=-905, SQLSTATE=57014 means your DB2 database can’t lock a file—usually because permissions are too tight or a table got corrupted. I’ve fixed this exact error on three production systems this year, and the solutions are surprisingly straightforward once you know where to look.

The good news? No data loss or complex recovery needed in most cases.

The root causes boil down to three things: missing SYSCADM or CONNECT privileges on the schema, a locked table from a failed transaction, or DB2 hitting its internal file-locking limits. I’ve seen this pop up after schema changes, failed imports, or even after a system reboot when temporary locks linger.

The fix depends on which one you’re dealing with—and I’ll show you how to tell them apart.

You’ll need db2cmd access and basic SQL skills, but no deep DB2 expertise. The solutions take under 10 minutes once you identify the blocker: a quick GRANT, a REORG TABLE, or adjusting LOCKTIMEOUT values.

I’ll walk through each scenario with exact commands and verification steps so you can confirm the fix worked without guessing.

This works for DB2 11.5, 12.1, and 13.0 on Linux/Windows. If you’re on an older version, the principles stay the same—just the syntax tweaks slightly. Let’s get this locked file unlocked and back to normal operations.

Why it happens

When you encounter a SQLCODE=-905, SQLSTATE=57014 error in DB2, it’s almost always tied to how your database manages file access and permissions. These errors disrupt operations because DB2 can’t lock or unlock files as expected—leaving critical resources stuck or inaccessible.

Below, we break down the most common causes with clear, actionable explanations.

🔒 Insufficient File Permissions

DB2 relies on the underlying operating system to manage file locks. If the database instance or user lacks proper permissions, DB2 can’t create, modify, or release locks on files. This often happens when:

• File ownership is misconfigured: The DB2 instance user (e.g., db2inst1) doesn’t own the file or directory.
• Read/write restrictions block DB2: Files are set to read-only or lack write permissions for the DB2 user group.
• SELinux/AppArmor policies interfere: On Linux, security modules may deny DB2’s access to lock files, even if permissions seem correct.

Why it matters: Without proper permissions, DB2 treats the file as "unlockable," triggering SQLCODE=-905. Use ls -la (Linux) or icacls (Windows) to verify ownership and permissions.

🔄 Stale or Orphaned Locks

Locks aren’t always released cleanly—especially during crashes, abrupt terminations, or long-running transactions. When DB2 can’t detect or remove these "zombie locks," it fails to acquire new ones, leading to conflicts.

• Unexpected shutdowns: Power failures or kill -9 commands leave locks dangling.
• Transaction timeouts: Uncommitted transactions hold locks indefinitely if DB2’s locktimeout isn’t configured.
• Corrupted lock tables: The sysibm.syslocktab or sysibm.syslockwaits tables may contain invalid entries.

Why it matters: DB2’s lock manager assumes it can control all locks, but orphaned locks create "phantom" conflicts. Run db2pd -locks to identify stuck locks and use db2 force application to resolve them.

📁 Filesystem or Storage Issues

Not all filesystems handle locks the same way. Some (like NFS or network-attached storage) introduce latency or fail to support mandatory locks, causing DB2 to time out or fail entirely.

• NFS-mounted databases: NFSv3/v4 lacks strong lock consistency, leading to "broken lock" errors.
• Storage quotas or limits: Filesystems may silently reject lock operations if they hit inode or block limits.
• Disk I/O bottlenecks: Slow storage delays lock acquisition, triggering timeouts in DB2’s lock manager.

Why it matters: DB2 expects mandatory locking (e.g., flock or fcntl on Linux). If your storage doesn’t support it, locks appear "lost" to DB2. Check mount options for noexec, nodev, or nosuid, which can interfere.

🔧 DB2 Configuration Mismatches

DB2’s lock-related parameters must align with your environment. Misconfigurations—like aggressive lock timeouts or incorrect shared memory settings—force DB2 into a state where it can’t resolve conflicts.

• Lock timeout too short: Default locktimeout (e.g., 45 seconds) may be too aggressive for high-contention environments.
• Shared memory limits: If DB2COMM or DB2LOCKS buffers are too small, lock requests spill to disk, slowing operations.
• Mixed workloads: OLTP and batch jobs competing for the same locks without isolation.

Why it matters: DB2’s lock manager uses these settings to predict when locks will be available. If predictions fail (e.g., due to external delays), DB2 throws SQLCODE=-905. Audit settings with db2 get dbm cfg and adjust locklist or locktimeout as needed.

🐛 Application-Level Deadlocks

While rare, poorly written applications can create circular lock dependencies (deadlocks) that DB2’s lock escalation can’t resolve. These often involve:

• Nested transactions: Outer transactions holding locks while inner ones fail.
• Implicit locks: Applications locking tables via SELECT FOR UPDATE without proper rollback logic.
• Third-party tools: ETL jobs or backup utilities holding locks longer than expected.

Why it matters: DB2’s lock escalation assumes locks are short-lived. If an app holds locks across transactions, DB2 may abort the session entirely, leaving files in an ambiguous state. Use db2exfmt -d to analyze deadlock traces.

Quick Fixes for Database Locking Errors

Encountering SQLCode=-905, SQLState=57014 can feel like hitting a brick wall—especially when your DB2 database operations grind to a halt due to file locking conflicts. But don’t panic! Below, we break down the most common causes and their practical solutions, so you can get your database back on track fast.

We’ve also included prevention tips to keep these errors from creeping back in.

🔥 Cause 1: Concurrent Write Conflicts

When multiple processes or users try to modify the same database file simultaneously, DB2 throws a SQLCode=-905 error. This is the most common culprit behind locking issues.

🍳 Fix: Adjust Lock Timeout Settings

DB2 allows you to configure how long a process waits before aborting due to a lock conflict. Here’s how to tweak it:

1. Check current lock wait settings: Run this command to see your existing lock timeout:

   db2 get dbm cfg | grep -i lock

   Look for locklist and locktimeout values.
2. Increase the lock timeout (temporarily): Use db2 update dbm cfg to extend the wait time (e.g., to 60 seconds):

   db2 update dbm cfg using locktimeout 60

3. Restart the DB2 instance: Apply changes with:

   db2stop && db2start

⚠️ Warning: Increasing the timeout masks the issue—address the root cause (see prevention tips below).

💡 Prevention Tip:

Implement row-level locking instead of table-level locks by optimizing your queries. Use WITH (HOLDLOCK) sparingly and ensure transactions are as short as possible.

👨‍🍳 Cause 2: Corrupted or Unavailable Files

If the database file is corrupted, locked by an external process (e.g., antivirus), or on a network drive with connectivity issues, DB2 will throw this error.

🔪 Fix: Release Locks and Verify File Integrity

1. Kill rogue processes: Use OS commands to identify and terminate processes locking the file:

   lsof | grep "yourdatabasefile"  # Linux/Unix
       tasklist | findstr "db2"                         # Windows

   Force-kill the process if needed (e.g., kill -9 PID).
2. Check file permissions: Ensure the DB2 user has read/write access:

   chmod 664 /path/to/your/file.db2

3. Restore from backup (if corrupted): If the file is corrupted, restore it from a recent backup and run:

   db2 restore database YOURDB from /backup/path

1. Force-rollback stalled transactions: Use the DB2 Command Center or CLI to force a rollback:

   db2 "connect to YOURDB"
       db2 "rollback transaction"

   If that fails, restart the database:

   db2 force applications all

2. Increase log file size: Check current log settings:

   db2 get db cfg for YOURDB | grep -i log

   Increase logfilsiz (e.g., to 1024) and logprimary (e.g., to 50):

   db2 update db cfg for YOURDB using logfilsiz 1024
       db2 update db cfg for YOURDB using logprimary 50

3. Archive and reuse logs: Enable automatic log archiving:

   db2 update db cfg for YOURDB using logarchmeth1 DISK:/path/to/archives

🎯 Prevention Tip:

Schedule regular log backups during off-peak hours and monitor log usage with:

db2pd -db YOURDB -logs

1. Take the database offline:

   db2 deactivate db YOURDB

2. Run a RECOVER DATABASE:

   db2 recover db YOURDB

3. Reactivate the database:

   db2 activate db YOURDB

⚠️ Warning: This may result in data loss if uncommitted transactions exist. Always back up first!

Frequently asked questions

ls -la /path/to/your/database/files

icacls "C:\path\to\your\database"

db2 force application 

db2 force applications all