👁 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: 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: Explain START statement for VSAM
Answer:
START positions for sequential READ. START filename KEY IS EQUAL/GREATER/NOT LESS THAN key-field. After successful START, READ NEXT retrieves records sequentially from that position. INVALID KEY handles not-found condition.
👁 0
Q: What is ORGANIZATION clause?
Answer:
ORGANIZATION specifies file structure: SEQUENTIAL (default), INDEXED (VSAM KSDS), RELATIVE (RRDS). Determines access methods available. INDEXED requires RECORD KEY. ACCESS MODE can be SEQUENTIAL, RANDOM, or DYNAMIC.
👁 0
Q: How to create VSAM backup?
Answer:
REPRO to sequential file: REPRO INFILE(vsam) OUTFILE(backup). Or EXPORT for full backup with catalog info. Can also use DFSMS backup. REPRO most common for simple backup.
👁 0
Q: How to reorganize VSAM?
Answer:
REPRO out then back: REPRO to sequential, DELETE cluster, DEFINE new cluster, REPRO back. Alternatively, IDCAMS export/import. Reclaims space, restores free space distribution. Schedule regularly.
👁 0
Q: What is CISIZE calculation?
Answer:
CISIZE must hold at least one record plus overhead. Formula complex. Let system default usually best. Common sizes: 4096, 8192, 16384. Larger CI can improve sequential, hurt random.
👁 0
Q: How to improve KSDS performance?
Answer:
Appropriate CISIZE, FREESPACE. Adequate buffers (BUFND/BUFNI). Index in cache if possible. IMBED obsolete. Keep file organized (REORG). Monitor CA/CI splits. Sequential access for bulk.
👁 0
Q: How to load VSAM from sequential?
Answer:
REPRO INFILE(seqfile) OUTFILE(vsamfile). REPLACE option to overwrite existing. Records must match VSAM format. For KSDS, records must be sorted by key. IDCAMS job.
👁 0
Q: Explain MASSINSERT?
Answer:
MASSINSERT optimizes bulk sequential inserts. System defers CI splits. Better performance for loads. Implicit with REPRO. COBOL can hint with APPLY WRITE-ONLY style.
👁 0
Q: What is backward READ?
Answer:
READ PREVIOUS for backward sequential. Must establish position first (START or READ). KSDS and ESDS support backward. Useful for end-of-file processing or range searches.
👁 0
Q: How to handle status 44?
Answer:
Status 44 is boundary violation on sequential WRITE. Record too large for file definition. Check RECORDSIZE parameter. May need variable length definition. Truncation doesn't happen automatically.
👁 0
Q: What is IEBGENER utility?
Answer:
IEBGENER copies sequential datasets. SYSUT1=input, SYSUT2=output, SYSIN=control. Simple copy needs only SYSUT1/SYSUT2/SYSIN DD DUMMY. Can reformat, generate, edit records with SYSIN statements. Being replaced by ICEGENER.
👁 0
Q: How to do file BROWSE?
Answer:
BROWSE reads sequentially. EXEC CICS STARTBR FILE(name) RIDFLD(key). READNEXT/READPREV in loop. ENDBR ends browse. TOKEN for concurrent browses. Can use generic key to position.
👁 0
Q: Explain BUFNO parameter
Answer:
BUFNO sets number of I/O buffers. DCB=(BUFNO=5). More buffers improve sequential performance but use memory. System default often 5. VSAM uses different buffering (BUFNI, BUFND). Too many can waste memory.
👁 0
Q: What is REPRO utility?
Answer:
REPRO copies VSAM files. IDCAMS REPRO INFILE(in) OUTFILE(out). Can REPRO between VSAM and sequential. Options: FROMKEY/TOKEY, SKIP/COUNT, REPLACE. Used for backup, conversion, extraction.
👁 0
Q: How to read VSAM sequentially?
Answer:
OPEN INPUT file. START if positioning needed. READ NEXT repeatedly until status 10 (end of file). CLOSE file. START optional - defaults to beginning. READ NEXT gets records in key sequence for KSDS.
👁 0
Q: What is CONTROLINTERVALSIZE?
Answer:
CONTROLINTERVALSIZE sets CI size explicitly. CONTROLINTERVALSIZE(4096). Powers of 2, max 32768. Let system default usually. Larger CI better sequential, worse random. Match to workload.
👁 0
Q: How to create temporary VSAM?
Answer:
Use REUSE attribute. Or define/delete in same job. Or use GDG-like naming. JCL doesn't support && for VSAM. IDCAMS DELETE at job end. Cannot be truly temporary like sequential.
👁 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: What is transient data?
Answer:
TD queues for sequential I/O. EXEC CICS WRITEQ TD QUEUE(name) FROM(data). Intrapartition for CICS internal. Extrapartition for external datasets. Defined in DCT. Automatic trigger level option.