👁 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 are the four divisions of a COBOL program?
Answer:
- IDENTIFICATION DIVISION - Program identification (PROGRAM-ID)
- ENVIRONMENT DIVISION - Hardware/software environment, file assignments
- DATA DIVISION - Data definitions (FILE, WORKING-STORAGE, LINKAGE SECTION)
- PROCEDURE DIVISION - Executable code and business logic
Only IDENTIFICATION and PROCEDURE DIVISION are mandatory.
👁 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: What is RECOVER utility?
Answer:
RECOVER restores data from backup/logs. RECOVER TABLESPACE db.ts. Options: TOCOPY, TORBA, TOLOGPOINT. Image copy is backup. RECOVER applies logs to copy. Essential for disaster recovery.
👁 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: How to create image copy?
Answer:
COPY utility creates backup. COPY TABLESPACE db.ts. FULL or INCREMENTAL. SHRLEVEL REFERENCE or CHANGE. Store in GDG for versions. Regular copies essential for recovery. COPYTOCOPY duplicates copies.
👁 0
Q: How does SEARCH ALL differ from SEARCH?
Answer:
SEARCH performs a sequential/linear search from the current index position. SEARCH ALL performs a binary search requiring the table to be sorted (ASCENDING/DESCENDING KEY clause) and indexed. SEARCH ALL is faster for large tables but requires sorted data.
👁 0
Q: What is UNLOAD utility?
Answer:
UNLOAD extracts data to sequential file. UNLOAD FROM TABLE name. Output format matches LOAD input. Used for data migration, backup, extract. Can include WHERE clause for filtering.
👁 0
Q: What is index-only access?
Answer:
Index-only access retrieves data from index without table access. All needed columns in index (including by include clause). Best performance. EXPLAIN shows INDEXONLY=Y. Design indexes for common queries.
👁 0
Q: How to tune DB2 queries?
Answer:
Check EXPLAIN output. Look at: access type (index vs scan), join method, sort operations. Add indexes for predicates. Rewrite with hints. Update statistics. Check for full tablespace scans. Monitor actual execution.
👁 0
Q: What is identity column?
Answer:
Identity column auto-generates values. col INTEGER GENERATED ALWAYS AS IDENTITY. Or GENERATED BY DEFAULT (allows override). Start, increment configurable. Alternative to sequence for single-table keys.
👁 0
Q: How to handle large objects (LOB)?
Answer:
BLOB/CLOB/DBCLOB for large data. Stored in auxiliary tablespace. Use LOB locators for efficiency. FETCH with INTO :lobvar. INSERT with CLOB(text). LOG NO for LOB tablespace optional.
👁 0
Q: What is temporal table?
Answer:
Temporal tables track historical data. SYSTEM_TIME versioning automatic. APPLICATION_TIME for business time. Query AS OF for point-in-time. History table stores old versions. Built-in time travel.
👁 0
Q: What is dynamic SQL?
Answer:
Dynamic SQL constructed at runtime. PREPARE creates executable. EXECUTE runs it. EXECUTE IMMEDIATE for one-time. DECLARE CURSOR for queries. More flexible but less efficient than static. Security concerns (injection).
👁 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: 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: What is referential integrity?
Answer:
RI ensures foreign key values exist in parent. CREATE TABLE child ... REFERENCES parent(key). ON DELETE CASCADE/SET NULL/RESTRICT. DB2 enforces automatically. Constraint violations return -530/-531. Design carefully.
👁 0
Q: How to alter table structure?
Answer:
ALTER TABLE name ADD COLUMN col type. ALTER TABLE name DROP COLUMN col. ALTER TABLE name ALTER COLUMN col SET DATA TYPE. Some changes need REORG. Adding columns easy; changes may need unload/reload.
👁 0
Q: What is tablespace?
Answer:
Tablespace is storage container for tables. CREATE TABLESPACE name IN database USING STOGROUP. Contains one or more tables. Segmented/universal tablespace types. Management at tablespace level (REORG, COPY, RECOVER).
👁 0
Q: What is XML support in DB2?
Answer:
DB2 stores XML natively. XMLTYPE column. XMLPARSE, XMLSERIALIZE for conversion. XQuery for querying. XMLTABLE extracts relational from XML. XML indexes for performance. Powerful for document storage.
👁 0
Q: How to use EXPLAIN tables?
Answer:
PLAN_TABLE primary output. DSN_STATEMNT_TABLE for cost. DSN_FUNCTION_TABLE for functions. INSERT EXPLAIN before statement. Query tables after. PLANNO, ACCESSTYPE, MATCHCOLS important columns.
👁 0
Q: What is Distributed Data Facility (DDF)?
Answer:
DDF enables remote DB2 access. TCP/IP and SNA connectivity. DRDA protocol. Location name identifies target. Three-part names: location.schema.table. Network security considerations.
👁 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 handle OCCURS INDEXED tables?
Answer:
Indexed tables use INDEXED BY: 05 TBL OCCURS 10 INDEXED BY IDX. SET IDX TO 1 initializes. SET IDX UP BY 1 increments. SEARCH uses index implicitly. More efficient than subscript-index conversion.
👁 0
Q: Explain SYMBOLIC CHARACTERS
Answer:
SPECIAL-NAMES. SYMBOLIC CHARACTERS NULL-CHAR IS 1. Assigns name to ordinal position in collating sequence. Position 1 is X'00' in EBCDIC. Use: MOVE NULL-CHAR TO field. Define non-printable characters readably.
👁 0
Q: What is ALTER statement?
Answer:
ALTER changes GO TO target dynamically. ALTER para-1 TO PROCEED TO para-2. Obsolete-causes maintenance nightmares. Makes flow unpredictable. Use EVALUATE or SET/IF instead. Some shops prohibit. Still legal but strongly discouraged.
👁 0
Q: What is PPT?
Answer:
PPT (Processing Program Table) defines programs. Now RDO PROGRAM definition. Contains: program name, language, resident status. CEDA DEFINE PROGRAM creates entry.
👁 0
Q: Explain PCT
Answer:
PCT (Program Control Table) defines transactions. Now RDO TRANSACTION. Links TRANSID to initial program. Security, priority, other options. CEDA DEFINE TRANSACTION.
👁 0
Q: What is the difference between INNER JOIN and LEFT OUTER JOIN?
Answer:
INNER JOIN returns only matching rows from both tables. LEFT OUTER JOIN returns all rows from left table plus matching rows from right (NULL if no match). LEFT preserves all left table rows regardless of match. INNER excludes non-matching rows from both sides.
👁 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 does REORG work?
Answer:
REORG physically reorganizes tablespace or index. Eliminates fragmentation, reclaims space, restores clustering. REORG TABLESPACE db.ts. Options: SHRLEVEL (REFERENCE/CHANGE), LOG (YES/NO). Schedule during low activity. Run RUNSTATS after.
👁 0
Q: What is a DB2 plan vs package?
Answer:
Plan is executable form of program's SQL, bound from DBRM. Package is independent unit, can be shared. Plans contain packages or direct SQL. Packages allow separate rebind. Modern approach: package collections with small plans.
👁 0
Q: What is DB2 catalog?
Answer:
Catalog tables describe DB2 objects. SYSIBM.SYSTABLES for tables, SYSIBM.SYSCOLUMNS for columns, SYSIBM.SYSINDEXES for indexes. Query catalog for metadata. Read-only except through DDL. Essential for documentation and analysis.
👁 0
Q: Explain CICS DB2 connection
Answer:
CICS connects to DB2 via thread. EXEC SQL in CICS program. RCT (Resource Control Table) defines. Thread pool for efficiency. DB2 subsystem parameter.
👁 0
Q: Explain DB2 locking
Answer:
DB2 locks at row, page, table, tablespace level. X (Exclusive) for writes, S (Share) for reads. IX/IS for intent. Lock escalation moves to higher level. LOCK TABLE statement forces mode. Timeout if wait too long.
👁 0
Q: How to handle -904 SQLCODE?
Answer:
-904 is resource unavailable. Tablespace/index in restricted state, stopped, or unavailable. Check display status. May need START DATABASE command. Check for REORG, RECOVER, LOAD running. Wait and retry.
👁 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: 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: 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: Explain DB2 triggers
Answer:
Trigger fires automatically on INSERT/UPDATE/DELETE. CREATE TRIGGER name AFTER/BEFORE/INSTEAD OF event ON table FOR EACH ROW/STATEMENT. Use for audit, validation, derived columns. Can impact performance.
👁 0
Q: What is DCLGEN?
Answer:
DCLGEN generates COBOL declarations from table. DCLGEN TABLE(name) LIBRARY(dataset) STRUCTURE(host-struct-name). Creates copybook with field definitions. Essential for maintaining table-program consistency.
👁 0
Q: What is CHECK utility?
Answer:
CHECK validates data integrity. CHECK DATA TABLESPACE db.ts. Finds constraint violations, orphan rows. CHECK INDEX validates index structure. CHECK LOB for LOB issues. Run periodically or after issues.
👁 0
Q: Explain LOAD utility
Answer:
LOAD inserts large data volumes. LOAD DATA INTO TABLE name. Options: REPLACE, RESUME, LOG NO. Input is SYSREC dataset. LOAD faster than INSERT. Puts tablespace in COPY PENDING after LOG NO.
👁 0
Q: What is VOLATILE table?
Answer:
VOLATILE TABLE hints optimizer to expect different row counts. Useful for varying cardinality. CREATE TABLE ... VOLATILE CARDINALITY. Or ALTER TABLE ... VOLATILE. Helps when statistics mislead optimizer.
👁 0
Q: How to use OCCURS DEPENDING ON?
Answer:
OCCURS DEPENDING ON creates variable-length tables. 01 WS-TABLE. 05 WS-COUNT PIC 99. 05 WS-ITEM OCCURS 1 TO 100 DEPENDING ON WS-COUNT. Only variable entries allocated based on counter. Used with variable length records.
👁 0
Q: What is table compression?
Answer:
Compression reduces storage. Huffman encoding in dictionary. CREATE TABLE ... COMPRESS YES. REORG to compress existing. CPU trade-off for I/O savings. Good for read-heavy, large tables.
👁 0
Q: What is ALIAS?
Answer:
ALIAS is alternate name for table. CREATE ALIAS alias FOR table. Useful for cross-subsystem access. Synonym is similar. PUBLIC alias visible to all. ALIAS can point to table in different schema.
👁 0
Q: How to define and use indexes?
Answer:
INDEX defined with OCCURS: 05 WS-TABLE OCCURS 10 INDEXED BY WS-IDX. SET WS-IDX TO 5 sets position. SET WS-IDX UP/DOWN BY 1 changes position. Use SEARCH verb with index. More efficient than subscripts for table access.
👁 0
Q: Explain storage group
Answer:
Storage group defines volumes for data. CREATE STOGROUP name VOLUMES(vol1, vol2). Tablespaces assigned to stogroups. DB2 manages space within. PRIQTY/SECQTY control allocation. Foundation of storage management.
👁 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: How to work with OCCURS indexed tables?
Answer:
Define: 01 WS-TABLE. 05 WS-ENTRY OCCURS 100 INDEXED BY WS-IDX. 10 WS-NAME PIC X(20). 10 WS-VALUE PIC 9(5). Access: MOVE WS-NAME(WS-IDX) TO output. Search: SEARCH WS-ENTRY WHEN WS-NAME(WS-IDX) = 'VALUE' perform action.
👁 0
Q: What is profile table?
Answer:
Profile tables store user-specific optimizer settings. DSN_PROFILE_TABLE, DSN_PROFILE_ATTRIBUTES. Override defaults for specific statements. Use QUERYNO correlation. Advanced tuning technique.
👁 0
Q: What is VALUE clause?
Answer:
VALUE initializes data items. 05 WS-FLAG PIC X VALUE 'Y'. 05 WS-COUNT PIC 9(3) VALUE ZEROS. 05 WS-TABLE OCCURS 5 VALUE 'INIT'. Figurative constants: SPACES, ZEROS, LOW-VALUES, HIGH-VALUES, QUOTES. Cannot use with REDEFINES target.
👁 0
Q: How to define table with KEY?
Answer:
For binary search, tables need KEY: 05 WS-TABLE OCCURS 100 ASCENDING KEY WS-CODE INDEXED BY WS-IDX. 10 WS-CODE PIC X(5). 10 WS-DESC PIC X(20). SEARCH ALL requires sorted data per KEY. Multiple keys for complex sorts.
👁 0
Q: What is TITLE statement?
Answer:
TITLE 'Page Header' sets listing page headers. Appears on each page of compilation listing. Useful for documentation. Multiple TITLES change as encountered. After IDENTIFICATION DIVISION start. Compiler directive, not executable.
👁 0
Q: How to define OCCURS ASCENDING?
Answer:
05 TABLE OCCURS 100 ASCENDING KEY IS CODE-FIELD INDEXED BY IDX. 10 CODE-FIELD PIC X(5). 10 DATA-FIELD PIC X(20). ASCENDING means lower values first. Required for SEARCH ALL. DESCENDING also available. Key must be within occurrence.
👁 0
Q: What is WORKING-STORAGE SECTION?
Answer:
WORKING-STORAGE SECTION declares program variables. Initialized at program load. Retains values between CALL invocations unless INITIAL program. Define all work areas, flags, counters, tables here. 01-49 levels for data, 77 for independent items.
👁 0
Q: What is LINEAR dataset?
Answer:
LINEAR VSAM is byte-addressable. No record structure. Used by DB2 tablespaces, system software. DEFINE CLUSTER LINEAR. Accessed via DIV (Data-in-Virtual) or memory mapping.
👁 0
Q: Explain EXPORT utility
Answer:
EXPORT creates portable copy with catalog info. IDCAMS EXPORT ds.name OUTFILE(dd). Includes cluster definition. IMPORT recreates on target system. Good for migration.
👁 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: 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 to access VSAM via CICS?
Answer:
Define FILE in CICS FCT (File Control Table). Or RDO DEFINE FILE. EXEC CICS READ FILE. VSAM must be closed to batch when CICS owns. NSRV or LSR buffering. BROWSE for sequential.
👁 0
Q: Explain RESTART parameter
Answer:
RESTART=stepname restarts job at specified step. RESTART=(stepname,checkid) for checkpointed restart. Use after abend to continue from failure point. Prior steps skipped. Data must be recoverable or recreatable.
👁 0
Q: What is FCT?
Answer:
FCT (File Control Table) defines files. Now RDO FILE. Maps name to VSAM cluster. Status, options, recovery. CEDA DEFINE FILE creates. LSR/NSR buffering options.
👁 0
Q: How to use MODIFY parameter?
Answer:
MODIFY references FCB image. MODIFY=(fcb,trc) on OUTPUT. FCB controls forms. TRC is table reference character. For print formatting. Installation-defined FCBs. Used with special forms.
👁 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 DB2 isolation levels
Answer:
UR (Uncommitted Read) reads dirty data. CS (Cursor Stability) locks current row. RS (Read Stability) locks all accessed rows. RR (Repeatable Read) locks range, prevents phantom. Higher isolation = more consistency but more locking. CS most common.
👁 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 BIND command?
Answer:
BIND creates plan/package from DBRM. BIND PLAN(name) MEMBER(dbrm). Options: ISOLATION, VALIDATE, EXPLAIN. REBIND updates existing. BIND PACKAGE for packages. Precompile creates DBRM, BIND makes executable.