DB2 FAQS, Interview Questions and Answers:Part 2

10/13/2009 No Comment

Interview Questions Answers related to DB2 Mainframe.

What is the result of this query if no rows are selected:
Answer : SELECT SUM(SALARY)
FROM EMP
WHERE QUAL=‘MSC’;
NULL

Why SELECT * is not preferred in embedded SQL programs?
Answer : For three reasons:
If the table structure is changed ( a field is added ), the program will have to be modified
Program might retrieve the columns which it might not use, leading on I/O over head.
The chance of an index only scan is lost.

What are correlated subqueries?
Answer : A subquery in which the inner ( nested ) query refers back to the table in the outer query. Correlated subqueries must be evaluated for each qualified row of the outer query that is referred to.

What is a cursor? Why should it be used?

Answer : Cursor is a programming device that allows the SELECT to find a set of rows but return them one at a time.Cursor should be used because the host language can deal with only one row at a time.

How would you retrieve rows from a DB2 table in embedded SQL?

Answer : 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? Answer : Single row SELECTs.

How do you specify and use a cursor in a COBOL program?

Answer : 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?

Answer : 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?

Answer : No.

Can you have more than one cursor open at any one time in a program? –
Answer : Yes, we can have

When you COMMIT, is the cursor closed? 

Answer : Yes

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

Answer : 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.

Answer : 
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:

Answer : DATE, TIME, TIMESTAMP?
DATE: 4bytes
TIME: 3bytes
TIMESTAMP: 10bytes

What is the COBOL picture clause of the following DB2 data types:

Answer : 
DATE, TIME, TIMESTAMP?
DATE: PIC X(10)
TIME : PIC X(08)
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.
Answer : Note: In DECIMAL(11,2), 11 indicates the size of the data type and 2 indicates the precision.
Related Posts


No comments :

 

Aired | The content is copyrighted and may not be reproduced on other websites. | Copyright © 2009-2016 | All Rights Reserved 2016

Contact Us | About Us | Privacy Policy and Disclaimer