Mainframe / DB2 Interview Questions

DB2 Interview Questions

Cobol | DB2 | JCL | JCL2 | VSAM | VSAM2

In the WHERE clause what is BETWEEN and IN?

BETWEEN supplies a range of values while IN supplies a list of values.

Is BETWEEN inclusive of the range values specified?

Yes.

What is 'LIKE' used for in WHERE clause? What are the wildcard characters?

LIKE is used for partial string matches. ‘%’ ( for a string of any character ) and ‘_’ (for any single character ) are the two wild card characters.

When do you use a LIKE statement?

To provide partial search facility e.g. to search employee by name, you need not specify the complete name, using LIKE, you can search for partial string matches.

What is the meaning of underscore ( ‘_’ ) in the LIKE statement?

Match for any single character.

6. What do you accomplish by GROUP BY .... HAVING clause?

Can think of the HAVING as a ‘WHERE’ clause on the GROUP

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.

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.

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

PIC S9(9)V99 COMP-3.

What is DCLGEN ?

DeCLarations 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?

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

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

Is 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 precompile stage itself ( because of the DECLARE TABLE ). DCLGEN being a toll, would generate accurate host variable definitions for the table reducing chances of error.

Is DECLARE TABLE in DCLGEN necessary? Why it used?

It is 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.

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

Where is the output of EXPLAIN stored?

In userid.PLAN_TABLE

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

Using Spufi. At bind time, accesspath are not defined. So all permutation and combinations of SQLs need to be examined separately in spufi

Where do you specify isolation levels ?

ISOLATION LEVEL is a parameter for the bind process.

What are the isolation levels possible?

Cursor Stability(CS) – A read lock is released as soon as access is moved to next page

Repeatable Read (RR)– Lock is released only after commit or end of execution

Mainframe / DB2 Interview Questions