👁 0
Q: What is CTE (Common Table Expression)?
Answer:
CTE defines temporary result set. WITH cte_name AS (SELECT...) SELECT * FROM cte_name. Improves readability. Can be recursive for hierarchies. Multiple CTEs comma-separated. Referenced in main query. Scope is single statement.
👁 0
Q: What is UDF (User Defined Function)?
Answer:
UDF extends SQL with custom functions. CREATE FUNCTION name(params) RETURNS type AS BEGIN logic END. Scalar returns single value. Table function returns rows. Use in SELECT, WHERE like built-in functions.
👁 0
Q: What is SQLCODE and what are common values?
Answer:
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
👁 0
Q: Explain the difference between INNER JOIN and LEFT OUTER JOIN.
Answer:
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.
👁 0
Q: Explain VIEW creation
Answer:
VIEW is saved query. CREATE VIEW name AS SELECT... WITH CHECK OPTION enforces WHERE on updates. Can be updatable if simple. Views for security, simplification. Materialized views (MQT) store data.
👁 0
Q: What is MQT?
Answer:
MQT (Materialized Query Table) stores computed results. CREATE TABLE name AS (SELECT...) DATA INITIALLY DEFERRED REFRESH DEFERRED. Improves query performance. REFRESH TABLE updates. Use for aggregations, joins.
👁 0
Q: How to handle -551 SQLCODE?
Answer:
-551 is authorization failure. No privilege for operation. Check GRANT statements. May need SELECT, UPDATE, DELETE, INSERT authority. GRANT privilege ON object TO user/role. Check SYSIBM.SYSTABAUTH.
👁 0
Q: What is GRANT and REVOKE?
Answer:
GRANT gives privileges: GRANT SELECT ON table TO user. REVOKE removes: REVOKE SELECT ON table FROM user. Privileges: SELECT, INSERT, UPDATE, DELETE, EXECUTE. WITH GRANT OPTION allows re-granting. Essential for security.
👁 0
Q: How to handle SQLCODE 100?
Answer:
SQLCODE 100 means not found or end of data. For SELECT INTO: no matching row. For FETCH: no more rows. For UPDATE/DELETE: no rows affected. Check context - may be normal condition, not error.
👁 0
Q: What is OPTIMIZE FOR n ROWS?
Answer:
OPTIMIZE FOR n ROWS hints expected rows. SELECT * FROM t ORDER BY c OPTIMIZE FOR 1 ROW. Influences access path. Low n favors index. High n may favor scan. Use when you know actual row count.
👁 0
Q: Explain EXEC SQL statements
Answer:
EXEC SQL marks embedded DB2 SQL. EXEC SQL SELECT col INTO :host-var FROM table WHERE key = :key-var END-EXEC. Colon prefix for host variables. SQLCODE in SQLCA indicates result. Precompiler converts to COBOL calls.
👁 0
Q: How to define FILE-CONTROL?
Answer:
SELECT file-name ASSIGN TO ddname. ORGANIZATION IS INDEXED. ACCESS MODE IS DYNAMIC. RECORD KEY IS key-field. ALTERNATE RECORD KEY IS alt-key. FILE STATUS IS ws-status. Maps COBOL file to physical dataset.
👁 0
Q: How to handle VSAM in batch?
Answer:
Define cluster with IDCAMS. JCL DD statement references cluster. COBOL SELECT maps to DD. ACCESS MODE matches operations. Close properly. STATUS check after operations.
👁 0
Q: What is PRTY parameter?
Answer:
PRTY sets selection priority. PRTY=15 on JOB card. Values 0-15 (15 highest). Affects when job selected from queue. JES2 processes higher PRTY first within class. Different from PERFORM (execution priority).
👁 0
Q: What is RUNSTATS and when to use it?
Answer:
RUNSTATS collects table/index statistics for optimizer. Run after significant data changes (loads, deletes). Updates catalog tables (SYSTABLES, SYSINDEXES). Optimizer uses for access path selection. RUNSTATS TABLESPACE db.ts INDEX(ALL).
👁 0
Q: How to use UNION?
Answer:
UNION combines SELECT results. UNION removes duplicates, UNION ALL keeps all. Column count and types must match. ORDER BY at end only. UNION expensive due to sort. Use UNION ALL if duplicates OK or impossible.
👁 0
Q: What is DISTINCT keyword?
Answer:
DISTINCT eliminates duplicate rows. SELECT DISTINCT col FROM table. Applies to entire row, not single column. Causes sort for duplicate elimination. Performance impact. Avoid if possible. Sometimes indicates bad design.
👁 0
Q: What is CURRENT DATE function?
Answer:
CURRENT DATE returns today's date. CURRENT TIME for time. CURRENT TIMESTAMP for both. No parentheses needed. Used in SELECT, WHERE, INSERT. Can compare: WHERE hire_date > CURRENT DATE - 30 DAYS.
👁 0
Q: Explain UPDATE with JOIN
Answer:
UPDATE table SET col = value FROM table t1 INNER JOIN table t2 ON... WHERE condition. Or: UPDATE t1 SET col = (SELECT col FROM t2 WHERE t2.key = t1.key). Merge statement is alternative.
👁 0
Q: How to delete with JOIN?
Answer:
DELETE FROM table WHERE key IN (SELECT key FROM other WHERE condition). Or: DELETE FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.key = t2.key). Correlated subquery common for conditional delete.
👁 0
Q: What is INSERT...SELECT?
Answer:
INSERT INTO table SELECT cols FROM other_table WHERE condition. Bulk insert from query. Column count/types must match. Faster than row-by-row. Can transform data during insert. Good for data migration.
👁 0
Q: What is FETCH FIRST?
Answer:
FETCH FIRST n ROWS ONLY limits result set. SELECT * FROM t ORDER BY col FETCH FIRST 10 ROWS ONLY. Optimizes query - doesn't retrieve all. OFFSET for paging. WITH TIES includes equal values.
👁 0
Q: How to write recursive CTE?
Answer:
WITH RECURSIVE cte AS (base-case UNION ALL recursive-case referencing cte) SELECT * FROM cte. For hierarchies: start with root, join to find children. DEPTH limit prevents infinite recursion.
👁 0
Q: How to prevent SQL injection?
Answer:
Use parameter markers (?), not concatenation. PREPARE stmt FROM 'SELECT * FROM t WHERE c = ?'. EXECUTE stmt USING :hostvar. Never build SQL with user input directly. Validate input. Use static SQL when possible.
👁 0
Q: Explain access path selection
Answer:
Optimizer chooses access path based on statistics, predicates, indexes. Index scan vs tablespace scan. Join methods: nested loop, merge scan, hybrid. Sort operations. EXPLAIN reveals choice. Tuning influences path.
👁 0
Q: Explain ALTERNATE RECORD KEY
Answer:
ALTERNATE RECORD KEY defines secondary indexes for INDEXED files. SELECT file-name ALTERNATE RECORD KEY IS ALT-KEY WITH DUPLICATES. Allows access by multiple keys. DUPLICATES permits non-unique values. Define path in VSAM cluster.
👁 0
Q: What is OPTIONAL file clause?
Answer:
OPTIONAL allows missing files: SELECT OPTIONAL input-file. OPEN succeeds even if file absent. READ immediately gets end-of-file. Useful for conditional processing. FILE STATUS 35 if accessed without OPTIONAL.
👁 0
Q: How to handle status 96?
Answer:
Status 96 is no DD statement for file. Check JCL has correct DD name. May be dynamic allocation failure. DD name must match SELECT ASSIGN. Case sensitive in some systems.
👁 0
Q: What is PROCEDURE-POINTER?
Answer:
USAGE PROCEDURE-POINTER stores program addresses. SET proc-ptr TO ENTRY 'PROGNAME'. CALL proc-ptr. Enables dynamic program selection. Similar to function pointers. Used in table-driven designs. Check ADDRESS OF for validity.
👁 0
Q: How does IEBCOPY work?
Answer:
IEBCOPY copies/merges PDS members. SYSUT1=input PDS, SYSUT2=output PDS. SYSIN: COPY OUTDD=SYSUT2,INDD=SYSUT1 copies all. SELECT MEMBER=name for specific members. REPLACE option overwrites existing. Can compress PDS.
👁 0
Q: What is EXPLAIN and how to use it?
Answer:
EXPLAIN analyzes query access path. EXPLAIN PLAN SET QUERYNO=1 FOR SELECT... Results in PLAN_TABLE. Shows index usage, join method, sort operations. Use for performance tuning. Review ACCESSTYPE, MATCHCOLS, PREFETCH columns.
👁 0
Q: Explain DECLARE CURSOR syntax
Answer:
DECLARE cursor-name CURSOR FOR SELECT... WITH HOLD keeps open after COMMIT. WITH RETURN returns to caller. FOR UPDATE OF allows positioned update. FOR READ ONLY optimizes read. ORDER BY for sorting. Static or dynamic declaration.
👁 0
Q: How to do paging with DB2?
Answer:
Use FETCH FIRST n ROWS ONLY with OFFSET or ROW_NUMBER(). Modern: SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY col) AS rn, t.* FROM table t) WHERE rn BETWEEN start AND end. Or cursor with FETCH FIRST.
👁 0
Q: Explain EXISTS vs IN
Answer:
EXISTS checks for row existence: WHERE EXISTS (SELECT 1 FROM...). IN checks value in list: WHERE col IN (SELECT...). EXISTS usually faster with correlated subquery. IN better for small static lists. EXISTS stops at first match.
👁 0
Q: What is GROUP BY clause?
Answer:
GROUP BY aggregates rows. SELECT col, COUNT(*) FROM t GROUP BY col. All non-aggregate SELECT columns must be in GROUP BY. HAVING filters groups (vs WHERE filters rows). GROUP BY ROLLUP/CUBE for subtotals.
👁 0
Q: What is CASE expression?
Answer:
CASE provides if-then logic. CASE WHEN cond1 THEN val1 WHEN cond2 THEN val2 ELSE default END. Simple CASE: CASE col WHEN 'A' THEN 'Active' END. Use in SELECT, WHERE, ORDER BY. Powerful for transformations.
👁 0
Q: Explain ORDER BY options
Answer:
ORDER BY sorts results. ORDER BY col1 ASC, col2 DESC. ASC default. NULLS FIRST/LAST controls NULL position. Ordinal: ORDER BY 1, 2 uses column positions. ORDER BY CASE for custom order. Affects performance.
👁 0
Q: Explain NOFOR option in SELECT?
Answer:
NOFOR prevents FOR UPDATE locking. SELECT ... NOFOR. Read-only access without lock overhead. For display-only queries. Cannot UPDATE WHERE CURRENT OF cursor with NOFOR.