| Author |
Message
|
| maurito |
 Posted: Thu Nov 12, 2015 3:03 am Post subject: |
 |
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| mqlover wrote: |
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? |
If you read the db2 documentation , you will find the answer to that.
I have made that suggestion 2 days ago, and you couldn't even be bothered to find out about it, so I am not answering any more of your questions. |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Thu Nov 12, 2015 3:25 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
As a general point, it is worth reading the different Database vendors Documentation especially on the subject of Row, table and page locking. Each of them are slightly differen AND moving a flow from one DB to another may not always give the same result.
_________________
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 6:17 pm Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
| Quote: |
If you read the db2 documentation , you will find the answer to that.
I have made that suggestion 2 days ago, and you couldn't even be bothered to find out about it, so I am not answering any more of your questions.
|
Sorry for missing out that. I had considered your suggestion and was trying out, wanted to try out and then reply.
intially while I did I faced few synatctic errors, and then am facing some semantic erros,so trying to solve that. Infact working on that approach itself. |
|
| Back to top |
|
|
| mqlover |
| Posted: Thu Nov 12, 2015 7:03 pm Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
While using passthru, am using as below
DECLARE passthruoutput CHARACTER
declar pSQL='select t.coulmnname from tablename as T where t.columnname2=abc for update'
set passthruOutput = passthru(pSQL)
But am getting the below DB exception which am not able to solve since few days
Text:CHARACTER:[IBM][CLI Driver][DB2/AIX64] SQL0206N "columnname2" is not valid in the context where it is used. |
|
| Back to top |
|
|
| mqlover |
| Posted: Thu Nov 12, 2015 8:27 pm Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
I tried to change the PASSTHRU like below
CREATE FIELD Environment.Variables.Data
declar pSQL='select coulmnname from tablename where columnname2=abc for update'
SET Environment.Variables.Data[]=passthru('pSQL' TO Database.DSN);
Now am unable to deploy and the error says that correlation name Environment.Variables.Data is not valid.
I am not gettign what am doing wrong with the passthru statement. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Nov 12, 2015 11:20 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| mqlover wrote: |
I tried to change the PASSTHRU like below
CREATE FIELD Environment.Variables.Data
declar pSQL='select coulmnname from tablename where columnname2=abc for update'
SET Environment.Variables.Data[]=passthru('pSQL' TO Database.DSN);
Now am unable to deploy and the error says that correlation name Environment.Variables.Data is not valid.
I am not gettign what am doing wrong with the passthru statement. |
IMHO it is all down to SCOPE. Are you by chance trying to use Environment is a bit of code (a Procedure or Function) that is outside a COMPUTE MODULE?
If you are then toy need to change the call to the procedure to include a reference that points to Environment.
| Code: |
declare envRef REFERENCE to Environment;
call MyProc(envRef)
...
create procedure MyProc(INOUT envRef REFERENCE) begin
create field envRef.DATA.retval;
set envref.DATA.retval[]= PASSTHRU(some string);
|
Oh, and by the way this may well have been identified in the Toolkit. Look at the 'warnings'.
I consider it good practice to try to eliminate ALL such notifications.
_________________
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: Fri Nov 13, 2015 12:43 am Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
Hi,
I did try the above but now am getting DBexception on the fly i.e it says the columnname used in PASSTHRU statement is not valid, rather I donot know how should I pass the db name and the column names to PASSTHRU statement. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Nov 13, 2015 12:50 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Check that the name you are using does indeed appear in the List of fields in the table.
Case may be the problem depending upon the DB being used.
_________________
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: Fri Nov 13, 2015 5:18 am Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
Yeah am able to set my passthru working but now my other query is I wanto to copy this passthru ouput which is a select columname(which is integer) into a variable which I have declared as integer. But this is not working as amd doing
SET ET Environment.Variables.Data[]=passthru(' ');
How can I achieve this? |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Nov 13, 2015 5:23 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| mqlover wrote: |
Yeah am able to set my passthru working but now my other query is I wanto to copy this passthru ouput which is a select columname(which is integer) into a variable which I have declared as integer. But this is not working as amd doing
SET ET Environment.Variables.Data[]=passthru(' ');
How can I achieve this? |
Take a usertrace and output the Environment (using a trace Node). It may be very informative.
_________________
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: Sun Nov 15, 2015 6:25 pm Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
Hi,
I realized that I cannot use Environment variables as am not passing a env tree from the CALL. Actually from the CALL I am jus passing the Input Root and this ESQL file is like an utility file which has all utility functions so I cannot use Output root also.
How do I copy my passthru fucntion?
Is my below passthru statement correct?
PASSTHRU('SELECT columname FROM tablename AS T where T.col1 = ? AND col2 = ? FOR UPDATE VALUES('abc', '123')
And this should return an integer. So to what do I copy to my passthru output.
Thanks |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Sun Nov 15, 2015 11:25 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Perhaps you need to re-think the whole purpose of this utility Procedure.
Why can't you add an Environment or Local Environment reference to the calling params?
Why not give this version a slightly different name?
There are other options. The choice is yours.
If it means modifying 1000+ flows then I'd create a V-2 version of the utility and make BOTH availaboe for use as required. sort of like a Library??????
now there an original thought. Perhaps I should patent it? 
I am sure that you can come up with a workable solution.
_________________
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 16, 2015 12:24 am Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
Thanks for all your help. I could solve this 
Thanks a ton for giving me so many suggesti=ions and waiting in patience for replies.
Thanks |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Nov 16, 2015 6:33 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| mqlover wrote: |
| Thanks for all your help. I could solve this |
Would you mind sharing how, so that others can learn from your experience?
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| mqlover |
| Posted: Thu Nov 19, 2015 1:40 am Post subject: |
|
|
Disciple
Joined: 25 Jul 2010
Posts: 176
|
Sure mqjeff,
I used the same approach suggested very first i.e select .. for update but wrapped in Passthru statement.
I followed the below approach
| Code: |
DECLARE Passthruoutput ROW
SET Passthruoutput.Data[1]=PASSTHRU('Select * for update where col1= ?, col2 = ?, '<columname>','<columname2>')
Set externalvar = Passthruoutput.Data[1].*[1]
update statement
|
|
|
| Back to top |
|
|
|
|
