Module 15: Advanced DB2 and Interview Preparation

Production Support Scenarios

Production support means keeping live DB2 systems running: fixing abends fast, handling on-call tickets, and preventing repeat outages.

Find the SQLCODE in the job log or SYSLOG; the DSN message names the program and statement.
Match the SQLCODE to its meaning: -911 deadlock/timeout, -904 resource unavailable, -805 package missing, -818 timestamp mismatch, -551 no authority.
Check whether the failure is data, code, or environment before changing anything.
If the job is restartable, restart from the checkpoint instead of rerunning from scratch.

-911 deadlock or timeout :- Retry the job; add frequent commits; check for a competing online transaction holding locks.
-904 unavailable resource :- A tablespace, index, or utility is stopped or in a restrictive state. Ask the DBA to start or recover it.
-805 program not found in plan :- The DBRM was not bound into the plan. Rebind the plan including the new DBRM.
-818 plan/package timestamp mismatch :- The load module and the bound package are out of sync. Rebind to match the current compile.
-551 authorization failure :- The production ID lacks a privilege that existed in test. Get the GRANT applied and rerun.

After a large batch load, refresh optimizer statistics so daytime queries stay fast:-
//RUNSTATS EXEC PGM=IKJEFT01,DYNAMNBR=20 //STEPLIB DD DSN=DB2.SDSNLOAD,DISP=SHR //SYSTSIN DD * DSN SYSTEM(DB2P) RUNSTATS TABLESPACE DBSALES.TS_ORDERS TABLE(ALL) INDEX(ALL) END /*
Schedule RUNSTATS right after bulk loads and REORGs, not hours later.
For very large tablespaces, use TABLESAMPLE SYSTEM(10) to keep the utility fast.

Document every incident: SQLCODE, root cause, fix, and ticket number.
Turn repeated manual fixes into scheduled jobs or monitoring alerts.
Keep test data and GRANTs in sync with production so deployments do not surprise you.
Review long-running jobs monthly; data growth silently turns fast jobs into slow ones.