Query performs table space scan unexpectedly

General Db2 application problems — wrong results, slow queries, connection issues — start with the SQLCODE and the access path.

What it means

• Most Db2 surprises are data, SQL text, or access-path issues — not DB2 bugs.
• SQLCODE after every statement is the first diagnostic.

Common causes

• SQL logic error (wrong JOIN, missing predicate).
• Stale statistics choosing a bad plan.
• Connection or authorization misconfiguration.
• Host variable mismatch with column definitions.

How to diagnose

• Check SQLCODE/SQLSTATE/SQLERRM first.
• Run the SQL in SPUFI with the same values.
• EXPLAIN for the access path.
• Compare DCLGEN with working storage.

How to fix it

• Correct the SQL or the data.
• RUNSTATS + REBIND for plan issues.
• Fix connection/auth configuration.

Example: Always check SQLCODE

Every EXEC SQL deserves a SQLCODE check.

Interview tip

• Q: How would you troubleshoot a Query performs table space scan unexpectedly in production?
• A: I would start from the SQLCODE, reproduce in SPUFI, check the access path with EXPLAIN, and compare host variables to the catalog.

Prevention checklist

• SQLCODE checks everywhere.
• Keep statistics current.
• Test SQL with production-like data.