473,320 Members | 2,180 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

UPDATE CURRENT OF CURSOR

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
9 30950
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
"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
>>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
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
>>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
>>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
>>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
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
>>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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: M Wells | last post by:
Hi All, I've been checking the online javascript libraries for code that will show me how to insert characters at the current cursor point in a textarea form field to help me markup content I...
1
by: Dave Hammond | last post by:
I have an html element (a link) with an onclick handler which opens a popup window, and would like to position that window at the coordinates where the element appears on the page. I have...
4
by: Vivek | last post by:
How do I update a current item in the LIST<T>? Thanks
2
by: tony | last post by:
Hello! Assume I catch a double click in a form by using the event handler that forms designer create for me. Now to my question when I double click in the form the event handler is trigged...
2
by: mokazawa1 | last post by:
Hi, I'm using stored procedures in DB2 UDB 8.1.2. In this stored, I execute a select for update command, opening a cursor. Then I update the rows using fetch and current of. The problem is that...
26
kcdoell
by: kcdoell | last post by:
Hello: I have a continuous form that displays records. In the AfterUpdate event of one of the fields, Binding_Percentage, I have the following code: Private Sub...
20
by: Phil | last post by:
VB2008 I have a DataGridView with MultiSelect = True and SelectionMode=FullRowSelect. One of the columns is a checkbox column. I have a function that goes through all the selected rows and sets...
12
balabaster
by: balabaster | last post by:
How do I insert text into a textarea at the current cursor position in JavaScript?
1
by: samsanjay | last post by:
Hi everyone, I m a beginner in C#. I m working to create an editor in C#. I want to display the current cursor position in statusbar.. How to find the richtextbox current cursor postion...?
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.