Mainframe DB2 Interview Questions and Answers Part-5
1. Use DSN utility to run a DB2 batch program from native TSO. An example is shown:
RUN PROGRAM(EDD470BD) PLAN(EDD470BD) LIB(‘ED 01T.OBJ.LOADLIB’)
2. Use IKJEFT01 utility program to run the above DSN command in a JCL.
Assuming that a siteï¿½s standard is that pgm name = plan name, what is the easiest way to find out which pgms are affected by change in a tableï¿½s structure ?
Query the catalogue tables SYSPLANDEP and SYSPACKDEP.
42) Name some fields from SQLCA.
SQLCODE, SQLERRM, SQLERRD
43) How can you quickly find out the # of rows updated after an update statement?
Check the value stored in SQLERRD(3).
44) What is EXPLAIN? ï¿½ drona questions
EXPLAIN is used to display the access path as determined by the optimizer for a SQL statement. It can be used in SPUFI (for single SQL statement ) or in BIND step (for embedded SQL ).
45) What do you need to do before you do EXPLAIN?
Make sure that the PLAN_TABLE is created under the AUTHID.
46) Where is the output of EXPLAIN stored? ï¿½
47) EXPLAIN has output with MATCHCOLS = 0. What does it mean? ï¿½
a nonmatching index scan if ACCESSTYPE = I.
48) How do you do the EXPLAIN of a dynamic SQL statement?
1. Use SPUFI or QMF to EXPLAIN the dynamic SQL statement
2. Include EXPLAIN command in the embedded dynamic SQL statements
49) How do you simulate the EXPLAIN of an embedded SQL statement in SPUFI/QMF? Give an example with a host variable in WHERE clause.)
Use a question mark in place of a host variable ( or an unknown value ). e.g.
WHERE EMP_SALARY > ?
DB2 Interview Questions – PART III www.mainframegurukul.com
50) What are the isolation levels possible ?
CS: Cursor Stability
RR: Repeatable Read