By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,885 Members | 1,469 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,885 IT Pros & Developers. It's quick & easy.

UPDATE CURRENT OF CURSOR

P: n/a
New to SQL here...

We have a CURSOR declared thusly:

EXEC SQL
DECLARE ALL-ADJSTMTS-CSR CURSOR FOR
SELECT ACCT.ACCOUNT_ID
, ACCT.APPL_ID
, ACCT.BRANCH_NUMBER
, ACCT.CATEGORY_CODE
, ACCT.OPEN_DATE
, ADJ.STATUS_IND
, ADJ.PRICE_GROUP_DETAIL_ID
, ADJ.ADJUSTMENT_AMT
, ADJ.INVOICE_ID
FROM ACCOUNTS.ACCOUNT_ADJUSTMENTS ADJ
JOIN ACCOUNTS.ACCOUNT_MASTER ACCT
ON ACCT.ACCOUNT_ID = ADJ.ACCOUNT_ID
WHERE ACCT.APPL_CODE = :ACCT-APPL-CODE
AND ACCT.BRANCH_NUMBER = :BCH-BRANCH-NBR
AND ADJ.STATUS_IND = 'O'
END-EXEC

After fetching a row we may need to update the following columns:
ADJ.STATUS_IND
ADJ.INVOICE_ID
ADJ.LAST_ID

The first thought was to use WHERE CURRENT OF <cursor-name>, ie:
EXEC SQL
UPDATE ACCOUNTS.ACCOUNT_ADJUSTMENTS
SET STATUS_IND = :ACCT-ADJ-STATUS-IND
, INVOICE_ID = :ACCT-ADJ-INVOICE-ID
, LAST_ID = :ACCT-ADJ-LAST-ID
WHERE CURRENT OF ALL-ADJSTMTS-CSR
END-EXEC

You can't use the FOR UPDATE clause in the DECLARE, however, if the SELECT
joins two tables (and other reasons, of course). This makes sense. So
instead we just do a read-only cursor and update, when necessary, like
this:

EXEC SQL
UPDATE ACCOUNTS.ACCOUNT_ADJUSTMENTS
SET STATUS_IND = :ACCT-ADJ-STATUS-IND
, INVOICE_ID = :ACCT-ADJ-INVOICE-ID
, LAST_ID = :ACCT-ADJ-LAST-ID
WHERE ADJUSTMENT_ID = :ACCT-ADJ-ADJUSTMENT-ID
END-EXEC

Is this standard practice, or am I missing an (obvious) other way to do it?

Thanks,
Frank

Aug 14 '08 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Frank Swarbrick wrote:
New to SQL here...

We have a CURSOR declared thusly:

EXEC SQL
DECLARE ALL-ADJSTMTS-CSR CURSOR FOR
SELECT ACCT.ACCOUNT_ID
, ACCT.APPL_ID
, ACCT.BRANCH_NUMBER
, ACCT.CATEGORY_CODE
, ACCT.OPEN_DATE
, ADJ.STATUS_IND
, ADJ.PRICE_GROUP_DETAIL_ID
, ADJ.ADJUSTMENT_AMT
, ADJ.INVOICE_ID
FROM ACCOUNTS.ACCOUNT_ADJUSTMENTS ADJ
JOIN ACCOUNTS.ACCOUNT_MASTER ACCT
ON ACCT.ACCOUNT_ID = ADJ.ACCOUNT_ID
WHERE ACCT.APPL_CODE = :ACCT-APPL-CODE
AND ACCT.BRANCH_NUMBER = :BCH-BRANCH-NBR
AND ADJ.STATUS_IND = 'O'
END-EXEC

After fetching a row we may need to update the following columns:
ADJ.STATUS_IND
ADJ.INVOICE_ID
ADJ.LAST_ID

The first thought was to use WHERE CURRENT OF <cursor-name>, ie:
EXEC SQL
UPDATE ACCOUNTS.ACCOUNT_ADJUSTMENTS
SET STATUS_IND = :ACCT-ADJ-STATUS-IND
, INVOICE_ID = :ACCT-ADJ-INVOICE-ID
, LAST_ID = :ACCT-ADJ-LAST-ID
WHERE CURRENT OF ALL-ADJSTMTS-CSR
END-EXEC

You can't use the FOR UPDATE clause in the DECLARE, however, if the SELECT
joins two tables (and other reasons, of course). This makes sense. So
instead we just do a read-only cursor and update, when necessary, like
this:

EXEC SQL
UPDATE ACCOUNTS.ACCOUNT_ADJUSTMENTS
SET STATUS_IND = :ACCT-ADJ-STATUS-IND
, INVOICE_ID = :ACCT-ADJ-INVOICE-ID
, LAST_ID = :ACCT-ADJ-LAST-ID
WHERE ADJUSTMENT_ID = :ACCT-ADJ-ADJUSTMENT-ID
END-EXEC

Is this standard practice, or am I missing an (obvious) other way to do it?
Depending on your isolation level one of two things can happen:
* The row may change under your feet and you wipe someone elses updates.
* You get a deadlock when the read lock get upgraded

You can right that by adding WITH RS USE AND KEEP UPDATE UPDATE LOCKS to
the end of the SELECT query.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Aug 14 '08 #2

P: n/a
"Frank Swarbrick" <Fr*************@efirstbank.comwrote in message
news:48******************@efirstbank.com...
New to SQL here...

We have a CURSOR declared thusly:

EXEC SQL
DECLARE ALL-ADJSTMTS-CSR CURSOR FOR
SELECT ACCT.ACCOUNT_ID
, ACCT.APPL_ID
, ACCT.BRANCH_NUMBER
, ACCT.CATEGORY_CODE
, ACCT.OPEN_DATE
, ADJ.STATUS_IND
, ADJ.PRICE_GROUP_DETAIL_ID
, ADJ.ADJUSTMENT_AMT
, ADJ.INVOICE_ID
FROM ACCOUNTS.ACCOUNT_ADJUSTMENTS ADJ
JOIN ACCOUNTS.ACCOUNT_MASTER ACCT
ON ACCT.ACCOUNT_ID = ADJ.ACCOUNT_ID
WHERE ACCT.APPL_CODE = :ACCT-APPL-CODE
AND ACCT.BRANCH_NUMBER = :BCH-BRANCH-NBR
AND ADJ.STATUS_IND = 'O'
END-EXEC

After fetching a row we may need to update the following columns:
ADJ.STATUS_IND
ADJ.INVOICE_ID
ADJ.LAST_ID

The first thought was to use WHERE CURRENT OF <cursor-name>, ie:
EXEC SQL
UPDATE ACCOUNTS.ACCOUNT_ADJUSTMENTS
SET STATUS_IND = :ACCT-ADJ-STATUS-IND
, INVOICE_ID = :ACCT-ADJ-INVOICE-ID
, LAST_ID = :ACCT-ADJ-LAST-ID
WHERE CURRENT OF ALL-ADJSTMTS-CSR
END-EXEC

You can't use the FOR UPDATE clause in the DECLARE, however, if the SELECT
joins two tables (and other reasons, of course). This makes sense. So
instead we just do a read-only cursor and update, when necessary, like
this:

EXEC SQL
UPDATE ACCOUNTS.ACCOUNT_ADJUSTMENTS
SET STATUS_IND = :ACCT-ADJ-STATUS-IND
, INVOICE_ID = :ACCT-ADJ-INVOICE-ID
, LAST_ID = :ACCT-ADJ-LAST-ID
WHERE ADJUSTMENT_ID = :ACCT-ADJ-ADJUSTMENT-ID
END-EXEC

Is this standard practice, or am I missing an (obvious) other way to do
it?

Thanks,
Frank
Yes this is common practice, but whether it is "standard" depends on what
you are trying accomplish and what level of locking and concurrent access
you need.

I often do a separate update/delete instead of "where current of cursor"
even when there is only one table involved, simply because I want to have
maximum concurrency (minimize locking issues). I declare the cursor as READ
ONLY, and use the WITH HOLD option (so that the cursor does not close upon
commit, and then issue a separate update or delete statement (followed by
frequent commits). Sometimes I may even select the cursor WITH UR if I know
it will not create a data integrity problem (I know that no other programs
are updating the columns in the predicate).
Aug 14 '08 #3

P: n/a
>>On 8/13/2008 at 7:00 PM, in message
<6g************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:
Depending on your isolation level one of two things can happen:
* The row may change under your feet and you wipe someone elses updates.
* You get a deadlock when the read lock get upgraded

You can right that by adding WITH RS USE AND KEEP UPDATE UPDATE LOCKS to
the end of the SELECT query.
Great! Thanks for the tip. Makes sense.

Can you clarify the meaning of the following?

"Share: Limits concurrent application processes to read-only operations on
the data."

How is WITH RS USE AND KEEP SHARED LOCKS different than just WITH RS and no
lock-request-clause and how is it different then an UPDATE lock?

I guess I am wondering, if another process attempt to access a table that I
have declared a cursor on, and that other process wants to update it, what
will the other process see if I have SHARED locks on my cursor, and what
will he see if I have UPDATE locks on my cursor? I assume if I have UPDATE
locks then the other process will simply wait until my cursor is closed.
How is SHARED different from this?

Also, does it make sense to specify "FOR READ ONLY"? I guess in my
particular case it is superflulous, since that particular SELECT is already
a read only select. But I'm guessing it's probably a good idea to specify
FOR READ ONLY any time that one is not doing positioned updates/deletes. Is
this a fair statement?

Thanks!
Frank

Aug 14 '08 #4

P: n/a
Frank Swarbrick wrote:
>>>On 8/13/2008 at 7:00 PM, in message
<6g************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:
>Depending on your isolation level one of two things can happen:
* The row may change under your feet and you wipe someone elses updates.
* You get a deadlock when the read lock get upgraded

You can right that by adding WITH RS USE AND KEEP UPDATE UPDATE LOCKS to
the end of the SELECT query.

Great! Thanks for the tip. Makes sense.

Can you clarify the meaning of the following?

"Share: Limits concurrent application processes to read-only operations on
the data."

How is WITH RS USE AND KEEP SHARED LOCKS different than just WITH RS and no
lock-request-clause and how is it different then an UPDATE lock?

I guess I am wondering, if another process attempt to access a table that I
have declared a cursor on, and that other process wants to update it, what
will the other process see if I have SHARED locks on my cursor, and what
will he see if I have UPDATE locks on my cursor? I assume if I have UPDATE
locks then the other process will simply wait until my cursor is closed.
How is SHARED different from this?

Also, does it make sense to specify "FOR READ ONLY"? I guess in my
particular case it is superflulous, since that particular SELECT is already
a read only select. But I'm guessing it's probably a good idea to specify
FOR READ ONLY any time that one is not doing positioned updates/deletes. Is
this a fair statement?
Specifying FOR READ ONLY is generally good practice since it assures
that DB2 can use "blocking" of rows (send more than one row to the
consumer). As you note: In this case it is irrelevant.
I don't think the KEEP SHARED LOCK Sis useful for anything but
completeness of the options.
What you want is USE AND KEEP UPDATE LOCKS

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Aug 14 '08 #5

P: n/a
>>On 8/13/2008 at 11:55 PM, in message
<lJ******************@bignews2.bellsouth.net>, Mark A<xx****@xxxxx.com>
wrote:
>
Yes this is common practice, but whether it is "standard" depends on
what
you are trying accomplish and what level of locking and concurrent
access
you need.

I often do a separate update/delete instead of "where current of cursor"

even when there is only one table involved, simply because I want to
have
maximum concurrency (minimize locking issues). I declare the cursor as
READ
ONLY, and use the WITH HOLD option (so that the cursor does not close
upon
commit, and then issue a separate update or delete statement (followed
by
frequent commits). Sometimes I may even select the cursor WITH UR if I
know
it will not create a data integrity problem (I know that no other
programs
are updating the columns in the predicate).
This information is very helpful. Thank you!
Frank

Aug 14 '08 #6

P: n/a
>>On 8/14/2008 at 11:43 AM, in message
<6g************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:
Frank Swarbrick wrote:
>>>>On 8/13/2008 at 7:00 PM, in message
<6g************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:
>>Depending on your isolation level one of two things can happen:
* The row may change under your feet and you wipe someone elses
updates.
>>* You get a deadlock when the read lock get upgraded

You can right that by adding WITH RS USE AND KEEP UPDATE UPDATE LOCKS to
>
>>the end of the SELECT query.

Great! Thanks for the tip. Makes sense.

Can you clarify the meaning of the following?

"Share: Limits concurrent application processes to read-only operations
on
>the data."

How is WITH RS USE AND KEEP SHARED LOCKS different than just WITH RS and
no
>lock-request-clause and how is it different then an UPDATE lock?

I guess I am wondering, if another process attempt to access a table
that I
>have declared a cursor on, and that other process wants to update it,
what
>will the other process see if I have SHARED locks on my cursor, and what
will he see if I have UPDATE locks on my cursor? I assume if I have
UPDATE
>locks then the other process will simply wait until my cursor is closed.
>
>How is SHARED different from this?

Also, does it make sense to specify "FOR READ ONLY"? I guess in my
particular case it is superflulous, since that particular SELECT is
already
>a read only select. But I'm guessing it's probably a good idea to
specify
>FOR READ ONLY any time that one is not doing positioned updates/deletes.
Is
>this a fair statement?
Specifying FOR READ ONLY is generally good practice since it assures
that DB2 can use "blocking" of rows (send more than one row to the
consumer). As you note: In this case it is irrelevant.
I don't think the KEEP SHARED LOCK Sis useful for anything but
completeness of the options.
What you want is USE AND KEEP UPDATE LOCKS
Great! Thanks again.
Aug 14 '08 #7

P: n/a
>>On 8/14/2008 at 11:43 AM, in message
<6g************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:
Specifying FOR READ ONLY is generally good practice since it assures
that DB2 can use "blocking" of rows (send more than one row to the
consumer). As you note: In this case it is irrelevant.
I don't think the KEEP SHARED LOCK Sis useful for anything but
completeness of the options.
What you want is USE AND KEEP UPDATE LOCKS

A further question... Below are two explain plans for an UPDATE. The first
does a regular update using the primary key in the predicate. The second
uses the RID_BIT() function to do a 'direct read' of the record.

---------------------------------------------------------------
Statement:

UPDATE ACCOUNTS.ACCOUNT_ADJUSTMENTS AS ADJ SET STATUS_IND =:H9_0,
INVOICE_ID =:H9_1, LAST_ID =:H9_2
WHERE ADJUSTMENT_ID =:H9_3
Section Code Page = 1252

Estimated Cost = 21.316250
Estimated Cardinality = 1.000000

Access Table Name = ACCOUNTS.ACCOUNT_ADJUSTMENTS ID = 9,775
| Index Scan: Name = ACCOUNTS.ADJUSTMENTS_PK ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ADJUSTMENT_ID (Ascending)
| #Columns = 0
| Single Record
| Fully Qualified Unique Key
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: ?
| | Stop Key: Inclusive Value
| | | | 1: ?
| Data Prefetch: Eligible 0
| Index Prefetch: None
| Lock Intents
| | Table: Intent Exclusive
| | Row : Exclusive
Update: Table Name = ACCOUNTS.ACCOUNT_ADJUSTMENTS ID = 9,775
---------------------------------------------------------------
Statement:

UPDATE ACCOUNTS.ACCOUNT_ADJUSTMENTS AS ADJ SET STATUS_IND =:H9_0,
INVOICE_ID =:H9_1, LAST_ID =:H9_2
WHERE RID_BIT(ADJ)=:H9_3
Section Code Page = 1252

Estimated Cost = 10.647691
Estimated Cardinality = 1.000000

Access Table Name = ACCOUNTS.ACCOUNT_ADJUSTMENTS ID = 9,775
| #Columns = 3
| Single Record
| Fetch Direct Using Row IDs
| Lock Intents
| | Table: Intent Exclusive
| | Row : Exclusive
Update: Table Name = ACCOUNTS.ACCOUNT_ADJUSTMENTS ID = 9,775
---------------------------------------------------------------

The second is obviously "twice as efficient" as the first. Seems to me the
second is the way to go in all situations where you have already done a
SELECT to retrieve information about the row you end up updating. This is a
slightly different example, I think, than is described in the article
"Improve concurrency with DB2 9.5 optimistic locking: New optimistic locking
feature to avoid maintaining long-lived locks"
(http://www.ibm.com/developerworks/db...0801schuetz/in
dex.html) Technically I am not using this to avoid a long-lived lock, in
the sense that the article intends. I'm just using it to fetch the record
quicker during a batch update process.

Seems almost too good to be true. Am I missing some reason that I should
not do this?

Thanks,
Frank

Aug 14 '08 #8

P: n/a
Frank Swarbrick wrote:
Seems almost too good to be true. Am I missing some reason that I should
not do this?
It's not relational. Also, in theory the rid of a row can change under
some circumstances (e.g. a reorg).

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Aug 15 '08 #9

P: n/a
>>On 8/14/2008 at 8:49 PM, in message
<6g************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:
Frank Swarbrick wrote:
>Seems almost too good to be true. Am I missing some reason that I
should
>not do this?
It's not relational. Also, in theory the rid of a row can change under
some circumstances (e.g. a reorg).
Yes, it's "not relational". Not sure if I care, though, if it's more
efficient.

As for reorg, I am pretty sure I would not be doing a reorg during a batch
processing window (hopefully!).

:-)

Frank
Aug 15 '08 #10

This discussion thread is closed

Replies have been disabled for this discussion.