Real-World DB2 Scenarios

Theory is not enough in a real job. This page walks through situations DB2 developers and support analysts face every week on live mainframe systems.

Scenario 1: month-end batch abends with -911

• A billing job abends at 2 AM with SQLCODE -911 (deadlock or timeout) while online users are also updating the same tables.
• First check how long the job held its locks: a missing COMMIT inside a loop is the usual cause.
• Add commits every few thousand rows so locks are released regularly.
• If online traffic is the conflict, move the job to a quieter batch window or ask the DBA about lock size and isolation options.

Scenario 2: urgent data fix with full audit

• Business reports that 50 employee rows have a wrong department code after a bad feed load.
• Never fix production with a blind UPDATE. First SELECT the rows and save them to a backup table.
• Run the fix inside a transaction, verify counts, then commit. Example:-
  CREATE TABLE EMP_BKP_20261008 AS (SELECT * FROM EMPLOYEE WHERE WORKDEPT = 'D99') WITH DATA; UPDATE EMPLOYEE SET WORKDEPT = 'D11' WHERE WORKDEPT = 'D99'; SELECT COUNT(*) FROM EMPLOYEE WHERE WORKDEPT = 'D11'; COMMIT;
• Keep the backup table until business confirms the fix, then drop it.

Scenario 3: new report runs for hours

• A new management report with five joins runs for hours and times out.
• Run EXPLAIN first and check whether the joins use indexes or fall back to tablespace scans.
• Run RUNSTATS on the tables so the optimizer has fresh statistics to pick the right access path.
• If one join explodes the row count, rewrite with EXISTS or add a filtering predicate early.

Scenario 4: moving code from test to production

• Bind the production packages from the same DBRM used in tested code; never recompile at the last minute.
• Verify GRANTs exist for the production authorization IDs before the deployment window.
• Keep a rollback plan: previous package versions and a data backup in case the new logic misbehaves.