👁 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: 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 to handle file status 22?
Answer:
Status 22 is duplicate key on WRITE or REWRITE attempt. KSDS primary key must be unique. Check program logic. May indicate data error. Use DUPLICATES option on AIX only if needed.
👁 0
Q: What causes file status 48?
Answer:
Status 48 is OPEN failure, file already open. Check logic - may have duplicate OPEN. CLOSE before re-OPEN. COBOL FILE-STATUS check important. May be another job has exclusive access.
👁 0
Q: What is file status 41?
Answer:
Status 41 is file already open. Duplicate OPEN attempted. Check program flow. CLOSE before re-OPEN. May be logic error in nested calls. STATUS after OPEN shows this.
👁 0
Q: Explain NONUNIQUEKEY?
Answer:
AIX can have NONUNIQUEKEY (duplicates). Base cluster KSDS always unique primary key. NONUNIQUEKEY AIX returns first match, use READ NEXT for others. UNIQUEKEY if one-to-one.
👁 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: 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 FILE STATUS and common codes?
Answer:
FILE STATUS is a 2-byte field capturing I/O operation results. 00=success, 10=end-of-file, 22=duplicate key, 23=record not found, 35=file not found, 39=file attribute mismatch, 41=file already open, 47=not opened input. Essential for error handling.
👁 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 REPLICATE option?
Answer:
REPLICATE duplicates sequence set on each track. Each track has own copy of sequence set index. Reduces contention. Obsolete with modern systems. Uses more space.
👁 0
Q: How to handle -803 SQLCODE?
Answer:
-803 is duplicate key violation. Primary key or unique index constraint. Check SQLERRD(3) for index ID. Solutions: check data, use IGNORE_DUPLICATE, handle in program logic. May indicate data quality issue.