DB2 Cursors Tricky interview Question

Q) Can we open the same cursor multiple times, when you use more than one DB2 subsystem ?

A) The answer is ‘Yes”. Many programmers said ‘No’ to this question. But this is a tricky interview question. Recently my friend faced same question.

Usually, same cursor can be opened only one time in a program, and Fetch will be in a loop to retrieve rows one by one. Let us see, in which scenario this kind of question comes. When you use multiple DB2 subsystems in a program, then, you can open same cursor multiple times, then you will not get SQL error code like OPENed a Cursor, which is already opened.

DECLARE C CURSOR FOR …
CONNECT TO X
OPEN C
FETCH C INTO …
CONNECT TO Y
OPEN C
FETCH C INTO ..

Here, first time you are connecting to ‘X’ instance. Second time you are connecting to instance ‘Y’. The same is applicable in a Single locations and packages are in different collections.

Warning: When your program is using only one sub-system, then, if you open a cursor more than once, you will get SQL error.

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.