473,854 Members | 1,581 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_NUM BER
, ACCT.CATEGORY_C ODE
, ACCT.OPEN_DATE
, ADJ.STATUS_IND
, ADJ.PRICE_GROUP _DETAIL_ID
, ADJ.ADJUSTMENT_ AMT
, ADJ.INVOICE_ID
FROM ACCOUNTS.ACCOUN T_ADJUSTMENTS ADJ
JOIN ACCOUNTS.ACCOUN T_MASTER ACCT
ON ACCT.ACCOUNT_ID = ADJ.ACCOUNT_ID
WHERE ACCT.APPL_CODE = :ACCT-APPL-CODE
AND ACCT.BRANCH_NUM BER = :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.ACCOUN T_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.ACCOUN T_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 30994
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_NUM BER
, ACCT.CATEGORY_C ODE
, ACCT.OPEN_DATE
, ADJ.STATUS_IND
, ADJ.PRICE_GROUP _DETAIL_ID
, ADJ.ADJUSTMENT_ AMT
, ADJ.INVOICE_ID
FROM ACCOUNTS.ACCOUN T_ADJUSTMENTS ADJ
JOIN ACCOUNTS.ACCOUN T_MASTER ACCT
ON ACCT.ACCOUNT_ID = ADJ.ACCOUNT_ID
WHERE ACCT.APPL_CODE = :ACCT-APPL-CODE
AND ACCT.BRANCH_NUM BER = :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.ACCOUN T_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.ACCOUN T_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.co mwrote in message
news:48******** **********@efir stbank.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_NUM BER
, ACCT.CATEGORY_C ODE
, ACCT.OPEN_DATE
, ADJ.STATUS_IND
, ADJ.PRICE_GROUP _DETAIL_ID
, ADJ.ADJUSTMENT_ AMT
, ADJ.INVOICE_ID
FROM ACCOUNTS.ACCOUN T_ADJUSTMENTS ADJ
JOIN ACCOUNTS.ACCOUN T_MASTER ACCT
ON ACCT.ACCOUNT_ID = ADJ.ACCOUNT_ID
WHERE ACCT.APPL_CODE = :ACCT-APPL-CODE
AND ACCT.BRANCH_NUM BER = :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.ACCOUN T_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.ACCOUN T_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.individua l.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.ACCOUN T_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.ACCOUN T_ADJUSTMENTS ID = 9,775
| Index Scan: Name = ACCOUNTS.ADJUST MENTS_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.ACCOUN T_ADJUSTMENTS ID = 9,775
---------------------------------------------------------------
Statement:

UPDATE ACCOUNTS.ACCOUN T_ADJUSTMENTS AS ADJ SET STATUS_IND =:H9_0,
INVOICE_ID =:H9_1, LAST_ID =:H9_2
WHERE RID_BIT(ADJ)=:H 9_3
Section Code Page = 1252

Estimated Cost = 10.647691
Estimated Cardinality = 1.000000

Access Table Name = ACCOUNTS.ACCOUN T_ADJUSTMENTS ID = 9,775
| #Columns = 3
| Single Record
| Fetch Direct Using Row IDs
| Lock Intents
| | Table: Intent Exclusive
| | Row : Exclusive
Update: Table Name = ACCOUNTS.ACCOUN T_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
2947
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 write for my site. Unfortunately, all I've been able to locate are plugin applets that give wysiwig functionality, which is not what I'm looking for. So I'm wondering if someone can provide some code, or point me to some
1
10123
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 installed a document.onclick event handler which saves the eventX and eventY values, then use those values to set the window top/left coordinates. The problem is that the eventX and eventY values always seem to be the previous coordinates, not the...
4
27902
by: Vivek | last post by:
How do I update a current item in the LIST<T>? Thanks
2
5786
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 but when I'm in this event handler I want to find out the cursor position(x,y cordinates) where the double click occurred how is that done? //Tony
2
7733
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 I want to return the rows for my Java application. But if I set the cursor of the select for WITH RETURN, I get the values, but I cant execute the update. If I dont use WITH return, I execute the update, but I cant get the result set. Any ideas.
26
4170
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 Binding_Percentage_AfterUpdate() 'Updates the Total calculation in the control "SumGWP" and "SUMNWP"on the quick reference 'table on the Forecast form With Me! And Me! And Me! And Me!
20
20367
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 the value of this column. For Each row In DataGridView1.SelectedRows row.Cells("MyCheckboxColumn").Value = False Next
12
19329
balabaster
by: balabaster | last post by:
How do I insert text into a textarea at the current cursor position in JavaScript?
1
7992
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
9751
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10682
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10371
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9513
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7082
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5743
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5942
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4562
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4159
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.