DB2 Interview Questions and Answers:Part 4
How would you retrieve rows from a DB2 table in embedded SQL?
Either by using the single row SELECT statements, or by using the CURSOR
Apart from cursor, what other ways are available to you to retrieve a row from a table in embedded SQL?
Single row SELECTs
How do you specify and use a cursor in a COBOL program?
Use DECLARE CURSOR statement either in working storage or in procedure division(before open cursor), to specify the SELECT statement Then use OPEN, FETCH rows in a loop and finally CLOSE
What happens when you say OPEN CURSOR?
If there is an ORDER BY clause, rows are fetched, sorted and made available for the FETCH statement Other wise simply the cursor is placed on the first row
Is DECLARE CURSOR executable?
No
Can you have more than one cursor open at any one time in a program?
Yes
When you COMMIT, is the cursor closed?
Yes
How do you leave the cursor open after issuing a COMMIT?
( for DB2 23 or above only)
Use WITH HOLD option in DECLARE CURSOR statement But, it has not effect in psuedo-conversational CICS programs
Give the COBOL definition of a VARCHAR field
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)
What is the physical storage length of each of the following DB2 data types:
DATE, TIME, TIMESTAMP and its picture clause in COBOL. DATE: 4bytes DATE: PIC X(10) TIME: 3bytes TIME PIC X(08) TIMESTAMP: 10bytes TIMESTAMP: PIC X(26)
What is the COBOL picture clause for a DB2 column defined as DECIMAL(11,2)?
PIC S9(9)V99 COMP-3 Note: In DECIMAL(11,2), 11 indicates the size of the data type and 2 indicates the precision
What is DCLGEN ?
Declaration Generator: used to create the host language copy books for the table definitions Also creates the DECLARE table
What are the contents of a DCLGEN?
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.
it mandatory to use DCLGEN? If not, why would you use it at all?
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.
Is DECLARE TABLE in DCLGEN necessary?Why it used?
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.
Will pre-compile of an DB2-COBOL program bomb, if DB2 is down?
No Because the pre-compiler does not refer to the DB2 catalogue tables
How is a typical DB2 batch Program gm executed ?
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('ED01TOBJLOADLIB') END 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
Name some fields from SQLCA.
SQLCODE, SQLERRM, SQLERRD
How can you Quickly find out the # of rows updated after an update statement?
Check the value stored in SQLERRD(3)
What is EXPLAIN?
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 ).
What do you need to do before you do EXPLAIN?
Make sure that the PLAN_TABLE is created under the AUTHID
Where is the output of EXPLAIN stored?
In userid PLAN_TABLE
EXPLAIN has output with MATCHCOLS = 0 What does it mean?
A non matching index scan if ACCESSTYPE = I
How do you do the EXPLAIN of a dynamic SQL statement?
1. Use SPUFI or MF to EXPLAIN the dynamic SQL statement 2. Include EXPLAIN command in the embedded dynamic SQL statements
How do you simulate the EXPLAIN of an embedded SQL statement in SPUFI/MF? Give an example with a host variable in WHERE clause)
Use a Question mark in place of a host variable ( or an unknown value ) eg SELECT EMP_NAME FROM EMP WHERE EMP_SALARY > ?
What are the isolation levels possible ?
CS: Cursor Stability RR: Repeatable Read
What is the difference between CS and RR isolation levels?
CS: Releases the lock on a page after use RR: Retains all locks acquired till end of transaction
Where do you specify them ?
ISOLATION LEVEL is a parameter for the bind process
When do you specify the isolation level?How?
During the BIND process ISOLATION ( CS/RR )
I use CS and update a page Will the lock be released after I am done with that page?
No
What are the various locking levels available?
PAGE, TABLE, TABLESPACE
How does DB2 determine what lock-size to use?
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
What are the disadvantages of PAGE level lock?
How would you retrieve rows from a DB2 table in embedded SQL?
Either by using the single row SELECT statements, or by using the CURSOR
Apart from cursor, what other ways are available to you to retrieve a row from a table in embedded SQL?
Single row SELECTs
How do you specify and use a cursor in a COBOL program?
Use DECLARE CURSOR statement either in working storage or in procedure division(before open cursor), to specify the SELECT statement Then use OPEN, FETCH rows in a loop and finally CLOSE
What happens when you say OPEN CURSOR?
If there is an ORDER BY clause, rows are fetched, sorted and made available for the FETCH statement Other wise simply the cursor is placed on the first row
Is DECLARE CURSOR executable?
No
Can you have more than one cursor open at any one time in a program?
Yes
When you COMMIT, is the cursor closed?
Yes
How do you leave the cursor open after issuing a COMMIT?
( for DB2 23 or above only)
Use WITH HOLD option in DECLARE CURSOR statement But, it has not effect in psuedo-conversational CICS programs
Give the COBOL definition of a VARCHAR field
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)
What is the physical storage length of each of the following DB2 data types:
DATE, TIME, TIMESTAMP and its picture clause in COBOL. DATE: 4bytes DATE: PIC X(10) TIME: 3bytes TIME PIC X(08) TIMESTAMP: 10bytes TIMESTAMP: PIC X(26)
What is the COBOL picture clause for a DB2 column defined as DECIMAL(11,2)?
PIC S9(9)V99 COMP-3 Note: In DECIMAL(11,2), 11 indicates the size of the data type and 2 indicates the precision
What is DCLGEN ?
Declaration Generator: used to create the host language copy books for the table definitions Also creates the DECLARE table
What are the contents of a DCLGEN?
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.
it mandatory to use DCLGEN? If not, why would you use it at all?
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.
Is DECLARE TABLE in DCLGEN necessary?Why it used?
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.
Will pre-compile of an DB2-COBOL program bomb, if DB2 is down?
No Because the pre-compiler does not refer to the DB2 catalogue tables
How is a typical DB2 batch Program gm executed ?
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('ED01TOBJLOADLIB') END 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
Name some fields from SQLCA.
SQLCODE, SQLERRM, SQLERRD
How can you Quickly find out the # of rows updated after an update statement?
Check the value stored in SQLERRD(3)
What is EXPLAIN?
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 ).
What do you need to do before you do EXPLAIN?
Make sure that the PLAN_TABLE is created under the AUTHID
Where is the output of EXPLAIN stored?
In userid PLAN_TABLE
EXPLAIN has output with MATCHCOLS = 0 What does it mean?
A non matching index scan if ACCESSTYPE = I
How do you do the EXPLAIN of a dynamic SQL statement?
1. Use SPUFI or MF to EXPLAIN the dynamic SQL statement 2. Include EXPLAIN command in the embedded dynamic SQL statements
How do you simulate the EXPLAIN of an embedded SQL statement in SPUFI/MF? Give an example with a host variable in WHERE clause)
Use a Question mark in place of a host variable ( or an unknown value ) eg SELECT EMP_NAME FROM EMP WHERE EMP_SALARY > ?
What are the isolation levels possible ?
CS: Cursor Stability RR: Repeatable Read
What is the difference between CS and RR isolation levels?
CS: Releases the lock on a page after use RR: Retains all locks acquired till end of transaction
Where do you specify them ?
ISOLATION LEVEL is a parameter for the bind process
When do you specify the isolation level?How?
During the BIND process ISOLATION ( CS/RR )
I use CS and update a page Will the lock be released after I am done with that page?
No
What are the various locking levels available?
PAGE, TABLE, TABLESPACE
How does DB2 determine what lock-size to use?
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
What are the disadvantages of PAGE level lock?
