👁 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 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: 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: Explain DD PATH statement?
Answer:
PATH specifies Unix/USS file. PATH='/u/user/file'. Used instead of DSN. PATHDISP for disposition. PATHOPTS for options. PATHMODE for permissions. Integrates USS files into batch JCL.
👁 0
Q: What is PATHDISP parameter?
Answer:
PATHDISP=(normal,abnormal) for USS files. Options: KEEP, DELETE. PATHDISP=(KEEP,DELETE). Used with PATH parameter. Similar to DISP for MVS datasets. Controls USS file retention.
👁 0
Q: What is FILEDATA parameter?
Answer:
FILEDATA specifies DCB for NFS/USS. FILEDATA=TEXT or BINARY. TEXT handles line endings. BINARY transfers unchanged. On PATH DD statements. Affects how data converted between systems.
👁 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 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: 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: 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 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 DEFINE PATH for VSAM?
Answer:
PATH provides alternate access to VSAM cluster via alternate index. IDCAMS: DEFINE PATH NAME(path.name) PATHENTRY(aix.name). Reference PATH in JCL, not AIX directly. Allows secondary key access.
👁 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: What is DEFINE ALTERNATEINDEX syntax?
Answer:
DEFINE AIX(NAME(aix.name) RELATE(base.cluster) KEYS(len offset)) DATA(NAME(aix.data)) INDEX(NAME(aix.index)). UNIQUEKEY or NONUNIQUEKEY. UPGRADE to maintain automatically. Must DEFINE PATH and BLDINDEX after.