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