Db2 LUW - question on UPDATE WHERE CURRENT OF for a dynamically prepared cursor

Suresh Sane

Db2 LUW - question on UPDATE WHERE CURRENT OF for a dynamically prepared cursor
List,

I have always used UPDATE WHERE CURRENT OF rather than keyed updates when using a cursor. When declaring the cursor (preparing the statement dynamically), there is no option to specify FOR UDPATE OF. When it is left out, it results in an error shown below:


SQL0510N UPDATE or DELETE is not allowed against the specified cursor.

SQLSTATE=42828

Any ideas? Must we do keyed updates when using dynamic sql?


Thx
Suresh


Ian Bjorhovde

Db2 LUW - question on UPDATE WHERE CURRENT OF for a dynamically prepared cursor
(in response to Suresh Sane)
Suresh,

Your select statement will have the FOR UPDATE clause, not the DECLARE CURSOR statement.

Example:

   declare v_sql      varchar(1024);
   declare v_empno    char(6);
   declare v_workdept char(3);

   declare stmt statement;
   declare c cursor for stmt;

   set sql = 'select empno from employee where workdept = ? for update';
   prepare stmt from sql;

   set v_workdept = 'A00';
   open c using v_workdept;

   -- Give 1 lucky employee a 250% raise
   fetch from c into v_empno;
   update employee set salary = salary*2.5 where current of c;


Ian Bjorhovde
IBM Gold Consultant


On Aug 21, 2019, 12:27 PM -0700, suresh sane <[login to unmask email]>, wrote:
> List,
>
> I have always used UPDATE WHERE CURRENT OF rather than keyed updates when using a cursor.  When declaring the cursor (preparing the statement dynamically), there is no option to specify FOR UDPATE OF.  When it is left out, it results in an error shown below:
>
> SQL0510N  UPDATE or DELETE is not allowed against the specified cursor.
> SQLSTATE=42828
>
> Any ideas?  Must we do keyed updates when using dynamic sql?
>
> Thx
> Suresh
>
>
>
> Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
> ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
> http://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2

Suresh Sane

Db2 LUW - question on UPDATE WHERE CURRENT OF for a dynamically prepared cursor
(in response to Ian Bjorhovde)
Ian - We tried it and it works great. I knew SELECT FOR UPDATE (singleton) and SELECT FOR UDPATE OF (in a cursor), but did not realize the two could be combined via dynamic sql this way.

Your tip was very helpful!

BTW - the "lucky" employee stopped by to complain that is 250% raise was only 150% - his new salary is 250% of original, not the raise!..-:)

Thx
Suresh
________________________________
From: Ian Bjorhovde <[login to unmask email]>
Sent: Thursday, August 22, 2019 12:56 AM
To: db2 Listserv <[login to unmask email]>
Subject: [DB2-L] - RE: Db2 LUW - question on UPDATE WHERE CURRENT OF for a dynamically prepared cursor

Suresh,

Your select statement will have the FOR UPDATE clause, not the DECLARE CURSOR statement.

Example:

declare v_sql varchar(1024);
declare v_empno char(6);
declare v_workdept char(3);

declare stmt statement;
declare c cursor for stmt;

set sql = 'select empno from employee where workdept = ? for update';
prepare stmt from sql;

set v_workdept = 'A00';
open c using v_workdept;

-- Give 1 lucky employee a 250% raise
fetch from c into v_empno;
update employee set salary = salary*2.5 where current of c;


Ian Bjorhovde
IBM Gold Consultant


On Aug 21, 2019, 12:27 PM -0700, suresh sane <[login to unmask email]>, wrote:
List,

I have always used UPDATE WHERE CURRENT OF rather than keyed updates when using a cursor. When declaring the cursor (preparing the statement dynamically), there is no option to specify FOR UDPATE OF. When it is left out, it results in an error shown below:


SQL0510N UPDATE or DELETE is not allowed against the specified cursor.

SQLSTATE=42828

Any ideas? Must we do keyed updates when using dynamic sql?


Thx
Suresh



-----End Original Message-----

-----End Original Message-----

Michael Hannan

RE: Db2 LUW - question on UPDATE WHERE CURRENT OF for a dynamically prepared cursor
(in response to Suresh Sane)

In Reply to Suresh Sane:

Ian - We tried it and it works great. I knew SELECT FOR UPDATE (singleton) and SELECT FOR UDPATE OF (in a cursor), but did not realize the two could be combined via dynamic sql this way.

Suresh,

One can see why the confusion occurs. Even though we talk about Cursor for Update, officially the FOR UPDATE is part of the underlying SELECT Statement. 

Now only two types of Select come to mind. Singleton SELECT INTO (Static SQL), and Cursor style SELECT. "FOR UPDATE" can only be used in the later.

Now this makes sense because the Update Lock is only held on the row till the cursor moves on, or actual Update occurs, promoting it to an X Lock. If an Update lock was taken on a Singleton Select, it would be nearly pointless, as completion of the Select would release it again. Ha ha. Singleton Select does not stay current on the row if you like. 

I usually recommend WITH UR for query type processes with no Update. It ain't a "Dirty Read". 

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd