👁 0
Q: What is ROW_NUMBER()?
Answer:
ROW_NUMBER() assigns sequential numbers. ROW_NUMBER() OVER (ORDER BY col). Numbers restart per PARTITION BY group. Use for paging, deduplication (keep first), ranking. Result is numeric.
👁 0
Q: What is the difference between KSDS and ESDS?
Answer:
KSDS (Key Sequenced Data Set):
- Records accessed by unique key
- Records stored in key sequence
- Has both data and index components
- Supports random and sequential access
- Can delete and reinsert records
- Most commonly used VSAM type
ESDS (Entry Sequenced Data Set):
- Records stored in arrival order
- Accessed by RBA (Relative Byte Address)
- No index component
- Cannot delete records (only mark inactive)
- Similar to sequential files
- Good for logs, audit trails
Use KSDS when: You need key-based access, updates, deletes
Use ESDS when: Sequential processing only, append-only data
👁 0
Q: Explain PROCEDURE DIVISION USING
Answer:
PROCEDURE DIVISION USING defines parameters for called program. Parameters match LINKAGE SECTION definitions. Order matches calling program's USING clause. Establishes addressability to passed data. BY REFERENCE/VALUE/CONTENT options inherited from CALL.
👁 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: What is OBJECT-COMPUTER paragraph?
Answer:
OBJECT-COMPUTER describes execution machine. OBJECT-COMPUTER. IBM-3090. MEMORY SIZE clause deprecated. PROGRAM COLLATING SEQUENCE for sort order. SEGMENT-LIMIT for overlay. Mostly documentation now; compiler usually ignores.
👁 0
Q: What is JCLLIB ORDER?
Answer:
JCLLIB ORDER specifies procedure library search order. //JOBNAME JOB ... // JCLLIB ORDER=(PROC.LIB1,PROC.LIB2). Searched for INCLUDE and EXEC procname. Takes precedence over JES2/3 proclibs. Must be before first EXEC statement.
👁 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: Explain CREATE INDEX
Answer:
CREATE INDEX creates index on table. CREATE INDEX idx ON table(col1, col2). UNIQUE prevents duplicates. CLUSTER determines physical order. Include columns for index-only access. Drop unused indexes for insert performance.
👁 0
Q: What is clustered index?
Answer:
Clustered index determines physical row order. Only one per table. Other indexes are secondary. CLUSTER keyword on CREATE INDEX. Good for range queries on cluster key. REORG restores clustering after updates.
👁 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 use window functions?
Answer:
ROW_NUMBER() OVER (PARTITION BY col ORDER BY col2). RANK, DENSE_RANK for rankings. SUM/AVG/COUNT OVER for running totals. PARTITION BY groups within result. ORDER BY within partition. Powerful analytics.
👁 0
Q: What is SORT and MERGE in COBOL?
Answer:
SORT orders records: SORT SORT-FILE ON ASCENDING KEY-FIELD USING INPUT-FILE GIVING OUTPUT-FILE. INPUT/OUTPUT PROCEDURE allows processing during sort. MERGE combines pre-sorted files: MERGE SORT-FILE ON KEY USING FILE-1 FILE-2 GIVING OUTPUT-FILE.
👁 0
Q: What is COLLATING SEQUENCE?
Answer:
PROGRAM COLLATING SEQUENCE IS sequence-name. Affects comparisons and SORT order. Define custom alphabet in SPECIAL-NAMES. Standard is NATIVE (EBCDIC). ASCII for ASCII order. STANDARD-1 for ASCII collating.
👁 0
Q: What is ORDERED attribute?
Answer:
ORDERED on DEFINE allocates volumes in specified order. Without ORDERED, system chooses. ORDERED ensures predictable allocation. May be important for performance tuning.
👁 0
Q: How does MERGE work?
Answer:
DFSORT MERGE combines pre-sorted files. SORTIN01, SORTIN02, etc. inputs. MERGE FIELDS=(1,10,CH,A). Files must be sorted on merge key. Output one sorted file. Preserves input order for equal keys.
👁 0
Q: What causes -911 SQLCODE?
Answer:
-911 is deadlock or timeout. Two processes waiting for each other's resources. Solutions: consistent lock order, shorter transactions, COMMIT frequently, appropriate isolation level. -913 is similar (deadlock victim). Retry transaction.
👁 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: 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.