👁 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: 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: 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 alternate index?
Answer:
AIX provides secondary access path. DEFINE AIX over base cluster. Different key. DEFINE PATH to access. BLDINDEX populates. UPGRADE option maintains AIX on base updates. Multiple AIX per cluster.
👁 0
Q: How to update KSDS record?
Answer:
READ record (optionally FOR UPDATE). Modify record area. REWRITE record. Key cannot change on REWRITE. Status 00 if successful. ACCESS MODE must allow updates.
👁 0
Q: Explain UPGRADE path AIX?
Answer:
UPGRADE AIX maintained automatically on base cluster changes. PATH allows access but not update. PATH with UPDATE allows updates via alternate key. UPGRADE adds overhead but keeps AIX current.
👁 0
Q: What is EXEC CICS READ?
Answer:
READ retrieves VSAM record. EXEC CICS READ FILE(name) INTO(area) RIDFLD(key) LENGTH(len). Optionally: UPDATE for update intent, KEYLENGTH for partial key. Generic key with GENERIC/KEYLENGTH. Returns NOTFND if missing.
👁 0
Q: How to SYNCPOINT?
Answer:
SYNCPOINT commits changes. EXEC CICS SYNCPOINT. Makes updates permanent. Or SYNCPOINT ROLLBACK undoes since last sync. Implicit syncpoint at task end. Use for transaction consistency.
👁 0
Q: How to WRITEQ with REWRITE?
Answer:
WRITEQ TS with REWRITE updates existing item. EXEC CICS WRITEQ TS QUEUE(name) FROM(data) REWRITE ITEM(n). Without REWRITE, adds new item. ITEM must exist for REWRITE.
👁 0
Q: How to handle DUPKEY?
Answer:
DUPKEY on WRITE means key exists. Check: IF ws-resp = DFHRESP(DUPKEY). Handle appropriately - maybe update instead. Or error to user. Common for insert logic.
👁 0
Q: Explain DATAONLY option
Answer:
DATAONLY sends only changed data. EXEC CICS SEND MAP DATAONLY FROM(data). No map formatting. Faster for updates. Fields must exist on screen. Use after initial MAPONLY.
👁 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: 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: 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 REBIND and when needed?
Answer:
REBIND updates access path without precompile. REBIND PACKAGE after RUNSTATS, index changes. REBIND PLAN for plan-level changes. Can improve or degrade performance - test first. EXPLAIN before/after.
👁 0
Q: What is OPEN mode options?
Answer:
OPEN INPUT for read, OUTPUT for write (creates), I-O for read/update, EXTEND for append. Multiple files per OPEN. OPEN OUTPUT deletes existing file! EXTEND preserves and adds. File must be closed before reopening in different mode.
👁 0
Q: Explain UPGRADE attribute
Answer:
UPGRADE keeps AIX synchronized with base cluster. Changes to base automatically update upgraded AIX. Adds overhead to writes. Non-upgrade AIX needs manual BLDINDEX. Use UPGRADE for real-time currency.
👁 0
Q: What is DEFINE PATH?
Answer:
PATH associates AIX for access. DEFINE PATH(NAME(path.name) PATHENTRY(aix.name)). Required to access via AIX. Open PATH in program, not AIX directly. UPDATE option allows updates via path.
👁 0
Q: How to access AIX?
Answer:
Open PATH to AIX, not base cluster. READ via alternate key. Can browse by alternate key sequence. Updates depend on PATH UPDATE option. Non-unique AIX returns first, then READ NEXT for others.
👁 0
Q: Explain REWRITE command
Answer:
REWRITE updates record. Must READ with UPDATE first. EXEC CICS REWRITE FILE(name) FROM(data) LENGTH(len). Changes record held from READ. Cannot change key. TOKEN matches UPDATE-READ.
👁 0
Q: What is DELETE command?
Answer:
DELETE removes record. EXEC CICS DELETE FILE(name) RIDFLD(key). Or after READ UPDATE: DELETE FILE(name). KEYLENGTH/GENERIC for range delete. NOTFND if key missing. NUMREC returns count deleted.
👁 0
Q: What causes NOT DEFINED TO CATALOG?
Answer:
Dataset exists on volume but not cataloged. Solutions: DISP=OLD,VOL=SER=volume, or catalog with IDCAMS DEFINE NONVSAM. Check correct catalog alias. May need ICF catalog update.
👁 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: 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.
👁 0
Q: What is MERGE statement?
Answer:
MERGE updates or inserts conditionally. MERGE INTO target USING source ON condition WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT. Combines INSERT/UPDATE logic. Efficient for sync operations.
👁 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.