Q1
What is SQLCODE and what are common values?
SQLCODE is a return code in SQLCA indicating the result of SQL execution.
Common SQLCODE values:
| 0 | Successful execution |
| 100 | No data found / End of cursor |
| -803 | Duplicate key on insert |
| -811 | Multiple rows returned for singleton SELECT |
| -904 | Unavailable resource |
| -911 | Deadlock/timeout |
| -922 | Authorization failure |
| -927 | DB2 not available |
Negative = Error, 0 = Success, 100 = No data
Q2
What is SQLCODE and common values?
SQLCODE indicates SQL result. 0=success, 100=not found/EOF, negative=error. Common: -803 duplicate key, -811 multiple rows, -904 unavailable, -911 deadlock, -818 timestamp mismatch.
Q3
Explain INNER JOIN vs LEFT OUTER JOIN.
INNER returns only matching rows. LEFT OUTER returns all left rows plus matching right (NULL if no match). LEFT preserves all left table rows.
Q4
How to handle NULL values?
NULL means unknown/missing. Use IS NULL/IS NOT NULL. COALESCE(col,default) substitutes. Indicator variables in COBOL detect NULL. NVL alternative function.
Q5
How does cursor work?
DECLARE defines cursor. OPEN executes SELECT. FETCH retrieves rows. CLOSE releases resources. FOR UPDATE allows positioned UPDATE/DELETE. WITH HOLD survives COMMIT.
Q6
What is indicator variable?
Indicator detects NULL. Declare: 01 VAR-IND PIC S9(4) COMP. Use: INTO :VAR:VAR-IND. Negative means NULL. Required for nullable columns.
Q7
How does COMMIT work?
COMMIT makes changes permanent, releases locks. ROLLBACK undoes changes. Implicit COMMIT at program end. Frequent COMMIT reduces lock duration.
Q8
Explain the difference between INNER JOIN and LEFT OUTER JOIN.
INNER JOIN:
- Returns only matching rows from both tables
- If no match, row is excluded
LEFT OUTER JOIN:
- Returns all rows from left table
- Matching rows from right table
- NULL for right table if no match
-- INNER JOIN SELECT E.NAME, D.DEPT_NAME FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON E.DEPT_ID = D.DEPT_ID; -- LEFT OUTER JOIN SELECT E.NAME, D.DEPT_NAME FROM EMPLOYEE E LEFT OUTER JOIN DEPARTMENT D ON E.DEPT_ID = D.DEPT_ID;
Use LEFT JOIN when you want all employees even those without department.
Q9
What is EXPLAIN and how to use it?
EXPLAIN analyzes query access path. Results in PLAN_TABLE. Shows index usage, join method, sort operations. Review ACCESSTYPE, MATCHCOLS for tuning.
Q10
What causes -811 SQLCODE?
SELECT INTO returned multiple rows when expecting one. Solutions: Add WHERE for single row, use MAX/MIN, declare cursor for multiple rows.
Q11
Explain DB2 isolation levels.
UR: Uncommitted read (dirty read OK). CS: Cursor stability (current row locked). RS: Read stability (accessed rows locked). RR: Repeatable read (range locked, no phantom).
Q12
What is RUNSTATS and when to run it?
RUNSTATS collects statistics for optimizer. Run after significant data changes. Updates SYSTABLES, SYSINDEXES. Critical for optimal access path selection.
Q13
What is DB2 plan vs package?
Plan is bound executable SQL. Package is independent SQL unit. Modern approach: packages in collections with small plans. Packages allow separate rebind.
Q14
Explain -911 SQLCODE handling.
Deadlock or timeout. Two processes waiting for each other. Solutions: Retry transaction, consistent lock order, shorter transactions, appropriate isolation level.
Q15
What is REBIND and when needed?
REBIND updates access path without precompile. After RUNSTATS, index changes, performance issues. Can improve or degrade - test first.
Q16
Explain buffer pool importance.
Buffer pool caches data/index pages. Hit ratio critical. GETPAGE vs SYNCIO shows effectiveness. Size appropriately for workload.
Q17
What is dynamic SQL?
SQL constructed at runtime. PREPARE creates executable. EXECUTE runs it. More flexible but less efficient. Security risk (injection).