What are correlated subqueries?

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?  

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?

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.

 Where would you specify the DECLARE CURSOR statement?

See answer to next question.

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? – drona questions

Yes.

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

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

Advertisements