| Author |
Message
|
| mqlover |
 Posted: Sun Nov 08, 2015 9:46 pm Post subject: update(select into external variable) |
 |
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
Hi,
I have a project requirement where in I have a function to update the DB and also select the udpated value.
I cannot perform
update Databse.tablename as T set dbvarname=select(dbvarname+1 from Database.Tablename AS T where <condition>) where <conditions>
set externalvar=select dbvarname from Database.Tablename AS T where <condition>
There were lot of concurency issues. To avoid that I thought that I will assign to the external variable within the select statement which is withing update.
So now it is
update Databse.tablename as T set dbvarname=select(externalvar=dbvarname+1 from Database.Tablename AS T where <condition>) where <conditions>
This woudl avoid concurrency as update creates lock
But when I tested I am get DB exception as after assignng to externalvar the dbvarname has no value. And my db table cannot take NULL.
I searched a lot but found nothign similar. I am working on this past few days but in vain.
Could anybody pls help. I am stuck here.
Thansk in advance |
|
| Back to top |
|
 |
| maurito |
| Posted: Mon Nov 09, 2015 12:00 am Post subject: Re: update(select into external variable) |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| mqlover wrote: |
Hi,
I have a project requirement where in I have a function to update the DB and also select the udpated value.
I cannot perform
update Databse.tablename as T set dbvarname=select(dbvarname+1 from Database.Tablename AS T where <condition>) where <conditions>
set externalvar=select dbvarname from Database.Tablename AS T where <condition>
There were lot of concurency issues. To avoid that I thought that I will assign to the external variable within the select statement which is withing update.
So now it is
update Databse.tablename as T set dbvarname=select(externalvar=dbvarname+1 from Database.Tablename AS T where <condition>) where <conditions>
This woudl avoid concurrency as update creates lock
But when I tested I am get DB exception as after assignng to externalvar the dbvarname has no value. And my db table cannot take NULL.
I searched a lot but found nothign similar. I am working on this past few days but in vain.
Could anybody pls help. I am stuck here.
Thansk in advance |
define a SEQUENCE object ( in db2, but if you are using other database, there are equivalents ), and let the database generate the value.
http://www.ibm.com/developerworks/data/library/techarticle/0302fielding/0302fielding.html |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Nov 09, 2015 12:21 am Post subject: Re: update(select into external variable) |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| maurito wrote: |
| mqlover wrote: |
Hi,
I have a project requirement where in I have a function to update the DB and also select the udpated value.
I cannot perform
update Databse.tablename as T set dbvarname=select(dbvarname+1 from Database.Tablename AS T where <condition>) where <conditions>
set externalvar=select dbvarname from Database.Tablename AS T where <condition>
There were lot of concurency issues. To avoid that I thought that I will assign to the external variable within the select statement which is withing update.
So now it is
update Databse.tablename as T set dbvarname=select(externalvar=dbvarname+1 from Database.Tablename AS T where <condition>) where <conditions>
This woudl avoid concurrency as update creates lock
But when I tested I am get DB exception as after assignng to externalvar the dbvarname has no value. And my db table cannot take NULL.
I searched a lot but found nothign similar. I am working on this past few days but in vain.
Could anybody pls help. I am stuck here.
Thansk in advance |
define a SEQUENCE object ( in db2, but if you are using other database, there are equivalents ), and let the database generate the value.
http://www.ibm.com/developerworks/data/library/techarticle/0302fielding/0302fielding.html |
Oracle also uses a Squence whereas SQLServer uses IDENTITY.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| mqlover |
| Posted: Mon Nov 09, 2015 2:42 am Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
| The db is owned by a different vendor who wouldn't make changes hence it has to be handled by wmb. So could you help me how to handle this through Esql. |
|
| Back to top |
|
|
| maurito |
| Posted: Mon Nov 09, 2015 5:55 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| mqlover wrote: |
| The db is owned by a different vendor who wouldn't make changes hence it has to be handled by wmb. So could you help me how to handle this through Esql. |
In that case, start with the SELECT. you need it to return a scalar, not a variable if you are to input it into the UPDATE. |
|
| Back to top |
|
|
| mqlover |
| Posted: Tue Nov 10, 2015 2:21 am Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
Hi,
Yeah I did following
Set externalvar = select tablevar from table where conditions
Update
Still there is concurrency issue as two threads can do select same time and may select same value |
|
| Back to top |
|
|
| maurito |
| Posted: Tue Nov 10, 2015 5:00 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| mqlover wrote: |
Hi,
Yeah I did following
Set externalvar = select tablevar from table where conditions
Update
Still there is concurrency issue as two threads can do select same time and may select same value |
maybe use Passthru select for update ?... as I guess a stored procedure is out of the question. |
|
| Back to top |
|
|
| mqlover |
| Posted: Tue Nov 10, 2015 6:02 pm Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
Lemme try that. Tx. But I don't think db2 supports select for update
. |
|
| Back to top |
|
|
| mqlover |
| Posted: Wed Nov 11, 2015 5:44 pm Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
| It seems not to be working. Is there any other way? Can anybody guide me? |
|
| Back to top |
|
|
| mqlover |
| Posted: Wed Nov 11, 2015 6:59 pm Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
I was trying to do the below now :
select columnname into external var from tablename where <conditions>
But this throws error in ESQL. But is there a way if I can wrap in a passthru statemnt? Actually not getting how to do, even few googled things not helping me out really. |
|
| Back to top |
|
|
| maurito |
| Posted: Thu Nov 12, 2015 12:05 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| mqlover wrote: |
| It seems not to be working. Is there any other way? Can anybody guide me? |
what have you tried ?.
What errors you get ? |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Nov 12, 2015 12:14 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
something like this possibly?
use PASSTHRU as a FUNCTION
| Code: |
declare cSQL CHAR 'select * from X where X.COL2 = 23;
set Envritonment.Data.XXX[] = Passthru(cSQL);
|
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| mqlover |
| Posted: Thu Nov 12, 2015 1:49 am Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
I was trying to assign the select into a variable within update.
but in vain, it does not assign to the update.
| Code: |
update Databse.tablename as T set dbvarname=(select externalVar=dbvarname+1 from Database.Tablename AS T where <condition>) where <conditions>
|
But this assigns NULL to the set of the update command as it assigns to externalVar and becomes NULL.
Then I put the code in PASSTHRU function as below
| Code: |
PASSTHRU(update Database.tablename as T set dbvarname=(select dbvarname+1 INTO externalVar from Database.Tablename AS T where <condition>) where <conditions>)
|
This failed with the DB exception.
Then I am just trying the same approach with changing syntax so as to make it work with esql. |
|
| Back to top |
|
|
| maurito |
| Posted: Thu Nov 12, 2015 2:08 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| mqlover wrote: |
I was trying to assign the select into a variable within update.
but in vain, it does not assign to the update.
| Code: |
update Databse.tablename as T set dbvarname=(select externalVar=dbvarname+1 from Database.Tablename AS T where <condition>) where <conditions>
|
But this assigns NULL to the set of the update command as it assigns to externalVar and becomes NULL.
Then I put the code in PASSTHRU function as below
| Code: |
PASSTHRU(update Database.tablename as T set dbvarname=(select dbvarname+1 INTO externalVar from Database.Tablename AS T where <condition>) where <conditions>)
|
This failed with the DB exception.
Then I am just trying the same approach with changing syntax so as to make it work with esql. |
and what happened to the PASSTHRU ( SELECT FOR UPDATE ?!
| Code: |
Select-statement
Read syntax diagramSkip visual syntax diagram
>>-+-----------------------------------+--fullselect--●--------->
| .-,-----------------------. |
| V | |
'-WITH----common-table-expression-+-'
>--+------------------+--●--+---------------------+--●---------->
+-read-only-clause-+ '-optimize-for-clause-'
'-update-clause----'
>--+------------------+----------------------------------------><
'-isolation-clause-'
|
it certainly has a for update clause. |
|
| Back to top |
|
|
| mqlover |
| Posted: Thu Nov 12, 2015 2:58 am Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
Sorry, have a question, does this select for update not allow other thread to select the same value from the table?
I meant will the other thread take same value after one thread read it? Or will there any be any locks?
How about using external lock? |
|
| Back to top |
|
|
|
|
