MVSFORUMS.com

[CRaviKota]

Hi,

Given a cursor C1 on table T1. For every row fetched from T1, open a cursor C2 on table T2. For every row fetched from T2, update column C1 in table T2 and issue a COMMIT.

How can we define a Cursor C1 in this regard. Does "DECLARE c1 CURSOR WITH HOLD FOR SELECT * FROM t1" would do the job for us. Do we need to mention anything about the cursor C2 here ? Some where down in my heart, I feel we actually need not mention anything about Cursor C2, but would like to confirm about it.


Thanks
RaviCKota

[dbzTHEdinosauer]

hopefully your are doing a positioned update on cursor C1. To do so, requires a 'FOR UPDATE OF' clause.

suggest:

• DECLARE C1 CURSOR WITH HOLD FOR SELECT A,B,C FROM T1 WHERE ... for UPDATE of C
  • where A,B,C are the necessary columns needed to declare cursor C2
  • and are updated - C- based on C2 results
• would not use SELECT *

WITH HOLD will keep a cursor open after a COMMIT.

no, you do not mention the second cursor within either the DECLARE or FETCH of the first cursor.

you are only updating each row of C1 once?

COMMITs are very resource intensive. Unless your C2 Fetchs thousands of rows for each row of C1, I would increase my LUW and not COMMIT after every positioned cursor update of C1.

Have you made provisions for RESTART?
_________________
Dick Brenholtz
American living in Varel, Germany

[blitz]

There is some confusion here. I think the update is on T2. So the 'WITH HOLD', 'FOR UPDATE' should be in C2 cursor.. I agree with Dick on his comments on Commit.

[blitz]

Note of caution: Hope that your 'update' cursor does not involve joins, ORDER BY, etc .. because UPDATE CURSOR wouldn't work in that case. Read this link for more,

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls693.htm

[CRaviKota]

Thanks to all for your quick replies,

But as I mentioned earlier "Given a cursor C1 on table T1. For every row fetched from T1, open a cursor C2 on table T2. For every row fetched from T2, update column C1 in table T2 and issue a COMMIT. "

For every row fetched from T1 : It means a cursor C1 needs to be opened for table T1 initially.
open a cursor C2 on table T2 : Open a Cursor C2 on table T2.
For every row fetched from T2, update column C1 in table T2 : That means using the cursor C2, and for every row we fetch we need to update column of Table T2.

Does it not mean that, irrespective of cursor C1 on Table 1, we need to have a cursor open on Table T2 and update its columns.


As Dick mentioned,
DECLARE C1 CURSOR WITH HOLD FOR SELECT A,B,C FROM T1 WHERE ... for UPDATE of C
where A,B,C are the necessary columns needed to declare cursor C2
and are updated - C- based on C2 results

what does "where A,B,C are the necessary columns needed to declare cursor C2" mean here. How can be the columns of Table T1 be used to declare the cursor C2 which is needed in Table T2. Can you elaborate on this.

Also Blitz, no joins are being used here.

Actually this is one of the questions, which I faced in a sample DB2 703 exam and following are the options given for the above question:

DECLARE c1 CURSOR WITH HOLD FOR SELECT * FROM t1

DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM t1

DECLARE c1 CURSOR FOR SELECT * FROM t1 FOR UPDATE OF t2

DECLARE c1 CURSOR FOR SELECT * FROM t1 FOR UPDATE OF c1

This may provide you more information.

I'm sure that I'm confused and also confusing you too . But Now I must have provided a better information.

Can you please clarify me on this.

Thanks

[dbzTHEdinosauer]

as blitz said, I got it backwords: the update is C2, no update in C1.

For your quiz CURSOR C1 is defined by:
DECLARE c1 CURSOR WITH HOLD FOR SELECT * FROM t1

[CRaviKota]

Thanks for your replies again