还是那只鱼-newcoin's blog

Q41) How are write I/Os from the buffer pool executed?

A41) Asynchronously.

Q42) What is a DB2 catalog?

A42) The DB2 catalog is a set of tables that contain information about all of the DB2 objects(tables, views, plans etc.).

Q43) In which column of which DB2 catalog would you find the length of the rows for all tables?

A43) In the RECLENGTH column of SYSIBM.SYSTABLES

Q44) What information is held in SYSIBM.SYSCOPY?

A44) The SYSIBM.SYSCOPY table contains information about image copies made of the tablespaces.

Q45) What information is contained in a SYSCOPY entry?

A45) Included is the name of the database, the table space name, and the image copy type(full or incremental etc.,) as well as the date and time each copy was made.

Q46) What information can you find in SYSIBM.SYSLINKS table?

A46) The SYSIBM.SYSLINKS table contains information about the links between tables created by referential constraints.

Q47) Where would you find information about the type of database authority held by the user?

A47) SYSIBM.SYSDBAUTH.

Q48) Where could you look if you had a question about whether a column has been defined as an index?

A48) This information can be found in SYSIBM.SYSINDEXES.

Q49) Once you create a view, where would information about the view be stored?

A49) When a view is created, system information about the view is stored in SYSIBM.SYSVIEWS.

Q50) What is the SQL Communications Area and what are some of its key fields?

A50) It is a data structure that must be included in any host-language program using SQL. It is used to pass feedback about the sql operations to the program. Fields are return codes, error messages, handling codes and warnings. （文章来源：http://www.newcoin.info）

Q51) What is DCLGEN?

A51) DCLGEN stands for declarations generator; it is a facility to generate DB2 sql data structures in COBOL or PL/I programs.

Q52) How do you leave the cursor open after issuing a COMMIT? (for DB2 2.3 or above only)

A52) Use WITH HOLD option in DECLARE CURSOR statement. But, it has not effect in psuedo-conversational CICS programs.

Q53) Give the COBOL definition of a VARCHAR field.

A53) A VARCHAR column REMARKS would be defined as follows:

…

10 REMARKS.

49 REMARKS-LEN PIC S9(4) USAGE COMP.

49 REMARKS-TEXT PIC X(1920).

Q54) What is the physical storage length of each of the following DB2 data types: DATE, TIME, TIMESTAMP?

A54) DATE: 4bytes

TIME: 3bytes

TIMESTAMP: 10bytes

Q55) What is the COBOL picture clause of the following DB2 data types: DATE, TIME, TIMESTAMP?

A55) DATE: PIC X(10)

TIME : PIC X(08)

TIMESTAMP: PIC X(26)

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

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

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

Q57) What is DCLGEN ? – GS

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

Q58) What are the contents of a DCLGEN? – GS

A58) 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.

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

A59) It is not mandat

ory 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.

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

A60) 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.

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

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

Q62) How is a typical DB2 batch program executed ?

A62) 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.

Q63) Assuming that a site’s standard is that pgm name = plan name, what is the easiest way to find out which （文章来源：http://www.newcoin.info）

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

A63) Query the catalogue tables SYSPLANDEP and SYSPACKDEP.

Q64) Name some fields from SQLCA.

A64) SQLCODE, SQLERRM, SQLERRD

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

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

Q66) What is EXPLAIN? – GS

A66) 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

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

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

Q68) Where is the output of EXPLAIN stored? – GS

A68) In USERID.PLAN_TABLE

Q69) EXPLAIN has output with MATCHCOLS = 0. What does it mean? – GS

A69) A nonmatching index scan if ACCESSTYPE = I.

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

A70) 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

Q71) How do you simulate the EXPLAIN of an embedded SQL statement in SPUFI/QMF? Give an example with a host variable in WHERE clause)

A71) Use a question mark in place of a host variable (or an unknown value). For instance,

SELECT EMP_NAME FROM EMP WHERE EMP_SALARY > ?

Q72) What are the isolation levels possible ? – GS

A72) CS: Cursor Stability

RR: Repeatable Read

Q73) What is the difference between CS and RR isolation levels?

A73) CS: Releases the lock on a page after use

RR: Retains all locks acquired till end of transaction

Q74) When do you specify the isolation level? How?

A74) During the BIND process(ISOLATION LEVEL is a parameter for the bind process). ISOLATION ( CS/RR )…

Q75) I use CS and update a page. Will the lock be released after I am done with that page?

A75) No.

Q76) What are the various locking levels available?

A76) PAGE, TABLE, TABLESPACE

Q77) How does DB2 determine what lock-size to use?

A77) There are three methods to determine the lock-size. They are:

1. Based on the lock-size given while creating the tablespace

2. Programmer can direct the DB2 what lock-size to use

3. If lock-size ANY is specified, DB2 usually choses a lock-size of PAGE

Q78) What are the disadvantages of PAGE level lock?

A78) High resource utilization if large updates are to be done

Q79) What is lock escalation?

A79) Promoting a PAGE lock-size to table or tablespace lock-size when a transaction has aquired more locks than specified in NUMLKTS. Locks should be taken on objects in single tablespace for escalation to occur.

Q80) What are the various locks available?（文章来源：http://www.newcoin.info）

A80) SHARE, EXCLUSIVE, UPDATE