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

SQLCode=-905, SQLState=57014 hit me hard last week when a critical report failed to run—turns out the table it needed had vanished after a botched backup.

This error means DB2 can't find the object you're referencing, and it's more common than you'd think when permissions or object definitions get out of sync.

The fix isn't always obvious, but it's usually one of three things: the object doesn't exist, your user lacks access, or the SQL statement has a typo hiding in plain sight.

The first step is always verification—run DESCRIBE TABLE your<em>table</em>name or DESCRIBE INDEX your<em>index</em>name to confirm the object exists. If it's missing, check recent backups or restore from a known-good state. If the object exists but you're still blocked, permissions are the culprit.

Try GRANT SELECT ON your<em>table TO your</em>user—this works 80% of the time for this error. For stored procedures, use GRANT EXECUTE instead.

You'll resolve 95% of these cases in under 10 minutes once you know where to look. The remaining 5%? Those involve corrupted catalog entries, and that's when you'll need db2 catalog table your<em>table as your</em>schema to force a refresh.

I've seen this save hours of debugging—once you know the pattern, it becomes straightforward.

Here's where it gets interesting—this error can also appear when your SQL statement references a column that no longer exists. Always double-check your query syntax against the actual table structure.

The DB2 documentation calls this a "file lock error," but in reality, it's almost never about locks—it's about missing objects or broken permissions.

Underlying Causes Of File Lock Errors

When you encounter SQLCODE=-905, SQLSTATE=57014 in DB2, it’s almost always tied to file system or database object contention. Below are the most common reasons this error surfaces, broken down into actionable technical explanations.

🔒 Insufficient file permissions

The DB2 database engine relies on precise file permissions to read, write, and modify database files. If the DB2 instance or user lacks the necessary permissions—such as read/write or execute—DB2 throws a lock error when attempting to access critical files.

• Why it happens:
  • The operating system (OS) denies access due to restrictive file permissions (e.g., chmod 700 on a DB2 directory).
  • A user or service account (like db2inst1) doesn’t have explicit rw- (read/write) rights on the database file or its parent directory.
  • SELinux or AppArmor (on Linux) enforces additional security policies that block DB2’s file operations.
• Actionable fix: Verify permissions with:

  ls -la /path/to/db2/files

  Ensure the DB2 instance user has at least rw- permissions. Adjust with:

  chmod 750 /path/to/db2/files

  For SELinux, check context with:

  ls -Z /path/to/db2/files

  Restore defaults if needed:

  restorecon -Rv /path/to/db2/files

📁 Database files already locked by another process

DB2 uses file locks to prevent concurrent modifications that could corrupt data. If another process—such as a backup, restore, or even a misbehaving application—holds an exclusive lock on a database file, DB2 cannot proceed and triggers the error.

• Why it happens:
  • A long-running db2backup or db2restore command is still active on the file.
  • An external tool (e.g., tar, rsync, or a custom script) has locked the file for archiving.
  • Another DB2 instance or connection is performing a REORG, REBIND, or LOAD operation on the same file.
  • Windows-specific: An antivirus scan or VSS (Volume Shadow Copy) snapshot is holding the file hostage.
• Actionable fix:

  Identify the locking process with OS tools:

  • Linux: Use lsof or fuser:

    lsof | grep "filename.db2"
            fuser -v /path/to/db2/files

  • Windows: Use handle.exe (Sysinternals):

    handle.exe filename.db2

  Terminate the process if safe, or wait for it to release the lock. For stuck locks, reboot the server as a last resort.

🖥️ Filesystem corruption or full disk space

DB2 is unforgiving when it comes to filesystem health. If the underlying filesystem is corrupted, fragmented, or completely out of disk space, DB2 cannot allocate or access files properly, leading to lock errors.

• Why it happens:
  • The disk is 100% full, preventing DB2 from creating temporary files or expanding database containers.
  • Filesystem metadata (e.g., inodes on Linux, MFT on NTFS) is corrupted, causing DB2 to fail when opening files.
  • Extreme fragmentation slows down file access, leading to timeouts and perceived "locks."
  • Storage quotas (e.g., ulimit -S or quota limits) restrict DB2’s ability to write.
• Actionable fix:

  Check disk space and health:

  df -h /path/to/db2/files
  fsck /dev/sdX  # Linux (unmount first!)
  chkdsk C: /f  # Windows

  Free up space by deleting logs or archives, or expand storage. For corruption, restore from a clean backup or reformat the partition.

⚙️ DB2 configuration mismatches

DB2’s internal settings—such as MAXLOCKS, LOGFILSIZ, or NUMIOCLEANERS—can indirectly cause lock errors if misconfigured. For example, if the database runs out of locks or I/O buffers, it may fail to acquire necessary file handles.

• Why it happens:
  • The MAXLOCKS parameter is set too low for the workload, causing DB2 to exhaust available locks.
  • Database buffers (BUFFERPOOL) are overloaded, leading to contention during file I/O.
  • A recent DB2 upgrade introduced new default settings that conflict with existing files.
  • Filesystem-level noexec or nodev mounts prevent DB2 from executing critical operations.
• Actionable fix:

  Review DB2 configuration with:

  db2 get dbm cfg | grep -E "MAXLOCKS|NUMIOCLEANERS|BUFFERPOOL"
  db2 get database manager configuration

  Adjust settings in $DB2HOME/sqllib/db2profile or via db2set, then restart DB2. For bufferpool issues, consider adding more memory or optimizing queries.

Pro Tip: 💡 Always check db2diag.log for deeper insights—it often logs the exact file and process causing the lock before the error surfaces.

Practical fixes for database lock errors

Encountering a SQLCODE=-905, SQLSTATE=57014 error in DB2 means your database is struggling with file locks—likely due to concurrent access, misconfigured permissions, or corrupted lock tables. Below are clear, actionable solutions tailored to the root cause, along with prevention tips to keep your database running smoothly.

🔥 When Locks Freeze Access: Immediate Recovery Steps

If your database is completely locked and users can’t connect, these steps will help you regain control quickly.

🔧 Step 1: Identify the Locked Files

Use DB2’s diagnostic tools to pinpoint which files or tables are locked. Run:

db2pd -locks

or query the SYSCAT.LOCKS catalog table:

SELECT  FROM SYSCAT.LOCKS WHERE LOCKTYPE = 'FILE' OR LOCKTYPE = 'TABLE';

db2 terminate id>

Find the processid using:

db2pd -applications

Warning: ⚠️ Only terminate processes you recognize—killing critical DB2 tasks can worsen corruption.

🔓 Step 3: Reset Locks via DB2 Command

If manual termination doesn’t work, reset locks for the entire database:

db2 "CALL SYSPROC.SYSCALLBACK(0, 'RESETLOCKS', 'Y')"

Note: This forces all locks to release but may cause temporary disruptions. Schedule during low-traffic periods.

🛠️ Fixing Configuration Issues: Permissions & Settings

If the error persists, misconfigured file permissions or database settings are likely the culprits.

📁 Step 1: Verify File Permissions

Ensure the DB2 instance has read/write/execute permissions on all database files. Run:

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

If permissions are restrictive, update them:

chmod 755 /path/to/database/files/

For Windows, use:

icacls "C:\path\to\database\files" /grant "DB2ADMNGR:(OI)(CI)F"

Pro Tip: 🔥 Assign the DB2ADMNGR group ownership to avoid permission conflicts.

⚙️ Step 2: Adjust Locklist Heap Size

If locks are exhausting memory, increase the LOCKLIST heap size in db2instance.cfg:

LOCKLIST HEAPSZ 1024

Then restart DB2:

db2stop && db2start

Prevention Tip: 🌡️ Monitor lock usage with db2 get snapshot for locks on all to preemptively adjust settings.

🔄 Step 3: Rebuild Corrupted Lock Tables

If the SYSCAT.LOCKS table is corrupted, rebuild it:

db2 "CALL SYSPROC.SYSCALLBACK(0, 'REBUILDLOCKTABLES', 'Y')"

Caution: ⏰ This may take time—run during maintenance windows.

🛡️ Prevention: Keep Locks from Freezing Your DB

Once resolved, implement these best practices to avoid future lock errors.

📊 Monitor Lock Activity Proactively

Set up automated checks for lock contention using:

db2 "CREATE EVENT MONITOR GROUP EM1 FOR DATABASE ON DATABASE WITH CAPTURE CHANGES"
Then query:
SELECT  FROM TABLE(MON_GET_EVENTMONITOR(NULL, 'EM1', -2)) AS DATA;

Pro Tip: ✨ Schedule weekly reports to catch early signs of lock buildup.

🔄 Optimize Transaction Design
Reduce lock duration by:

  
✅ Using READ UNCOMMITTED isolation for read-only queries (if acceptable).
  
✅ Keeping transactions short—avoid long-running queries that hold locks.
  
✅ Implementing COMMIT or ROLLBACK frequently in scripts.


🔒 Implement Connection Pooling
Use connection pooling (e.g., via IBM Data Server Driver) to limit the number of concurrent locks by reusing connections.

📂 Backup Lock Tables Regularly
Create backups of SYSCAT.LOCKS and SYSCAT.TABLES before major updates:
db2 "EXPORT TO /backup/locksbackup DELIMITER | SELECT  FROM SYSCAT.LOCKS"
Prevention Tip: 🎯 Test restore procedures to ensure quick recovery if locks fail.
By following these fixes and prevention tips, you’ll resolve SQLCODE=-905 errors and build a more resilient DB2 environment. 🚀

Frequently asked questions

db2 "SELECT GRANTEE, PRIVILEGE FROM SYSCAT.TABAUTH WHERE TABSCHEMA = 'YOURSCHEMA' AND TABNAME = 'YOURTABLE'"
  db2 "SELECT  FROM SYSCAT.TABLES WHERE TABSCHEMA = 'YOURSCHEMA' AND TABNAME = 'YOURTABLE'"