FAQs(151 to 165)

151) What is the COBOL picture clause for a DB2 column defined as DECIMAL(11,2)? - GS

A) PIC S9(9)V99 COMP-3.

Note: In DECIMAL(11,2), 11 indicates the size of the data type and 2 indicates the precision.

152) What is DCLGEN ?

A) DeCLarations GENerator: used to create the host language copy books for the table definitions. Also creates the DECLARE table.

153) What are the contents of a DCLGEN? 

A) EXEC SQL DECLARE TABLE statement which gives the layout of the table/view in terms of DB2 datatypes.

A host language copy book that gives the host variable definitions for the column names.

154) Is it mandatory to use DCLGEN? If not, why would you use it at all?

A) It is not mandatory to use DCLGEN. Using DCLGEN, helps detect wrongly spelt column names etc. during the pre-compile stage itself (because of the DECLARE TABLE ). DCLGEN being a tool, would generate accurate host variable definitions for the table reducing chances of error.

155) Is DECLARE TABLE in DCLGEN necessary? Why it used?

A) It not necessary to have DECLARE TABLE statement in DCLGEN. This is used by the pre-compiler to validate the table-name, view-name, column name etc., during pre-compile.

156) Will precompile of an DB2-COBOL program bomb, if DB2 is down?

A) No. Because the precompiler does not refer to the DB2 catalogue tables.

157) How is a typical DB2 batch program executed ?

A) There are two methods of executing a DB2-batch program

1. Use DSN utility to run a DB2 batch program from native TSO. An example is shown:

DSN SYSTEM(DSP3)

RUN PROGRAM(EDD470BD) PLAN(EDD470BD) LIB('EDGS01T.OBJ.LOADLIB')

END

2. Use IKJEFT01 utility program to run the above DSN command in a JCL.

158) Assuming that a site's standard is that pgm name = plan name, what is the easiest way to find out which

programs are affected by change in a table's structure?

A) Query the catalogue tables SYSPLANDEP and SYSPACKDEP.

159) Name some fields from SQLCA.

A) SQLCODE, SQLERRM, SQLERRD

160) How can you quickly find out the number of rows updated after an update statement?

A) Check the value stored in SQLERRD(3).

161) What is EXPLAIN? 

A) 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). The results of EXPLAIN is stored in U.PLAN_TABLE where U is the authorization id of the user

162) What do you need to do before you do EXPLAIN?

A) Make sure that the PLAN_TABLE is created under the AUTHID.

163) Where is the output of EXPLAIN stored? 

A) In USERID.PLAN_TABLE

164) EXPLAIN has output with MATCHCOLS = 0. What does it mean?

A) A nonmatching index scan if ACCESSTYPE = I.

165) How do you do the EXPLAIN of a dynamic SQL statement?

A) There are two methods to achieve this:

1. Use SPUFI or QMF to EXPLAIN the dynamic SQL statement

2. Include EXPLAIN command in the embedded dynamic SQL statements