| Author |
Message
|
| elenzo |
 Posted: Wed Jul 18, 2012 6:29 am Post subject: Problems with store procedures and temporally tables |
 |
|
Acolyte
Joined: 22 Aug 2006
Posts: 53
|
Hi, I am using WMB /MQ 7.0 and i am having a problem with a particular case calling a store procedure.
I have lots of flows that calls SP and they work fine, but in this case I can't get de answer of the SP. The difference between this and the others is that this SP uses a temporally table to return the resultSet, but when I read the resultSet in broker, it is empty.
If I call the SP directly from an SQL client, I can see the answer without problem
Do you have any ideas ? any configuration change I can do to access this data ?
My code is: (extract)
CALL SP1 (field1, Environment.ResultSet[]);
-- STORE PROCEDURES
CREATE PROCEDURE SP1(
IN field1 CHAR)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "dbo.SP1";
Thanks! |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Wed Jul 18, 2012 7:06 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Can you please use [c o d e] tags and edit your message above to reflect proper format? Also, what version of WMB toolkit and what version of WMB runtime? WMB versions have four digits not two.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| elenzo |
| Posted: Wed Jul 18, 2012 7:10 am Post subject: |
|
|
Acolyte
Joined: 22 Aug 2006
Posts: 53
|
I dont have the complete code rihgt now, there is no much code to add anyway, the rest of the code is the "while" moving through the resultSet.
The complete version of runtime is
BIP8996I: Version: 7002 |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jul 18, 2012 7:14 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Do you understand [c od e] tag in your post on this message board? Check the buttons above the editor pane.
What is the effective level of your runtime? Use mqsireportbroker to get it. Also, please post the toolkit version. Help -> About...
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| elenzo |
| Posted: Wed Jul 18, 2012 7:16 am Post subject: |
|
|
Acolyte
Joined: 22 Aug 2006
Posts: 53
|
| Code: |
CALL SP1 (field1, Environment.ResultSet[]);
-- STORE PROCEDURES
CREATE PROCEDURE SP1(
IN field1 CHAR)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "dbo.SP1"; |
[mqsiuser@lnxd704 ~]$ mqsiservice -v
BIPmsgs en_US
Console CCSID=1208, ICU CCSID=1208
Default codepage=UTF-8, in ascii=UTF-8
JAVA console codepage name=UTF-8
BIP8996I: Version: 7002
BIP8997I: Product: WebSphere Message Broker
BIP8998I: CMVC Level: S700-FP02
BIP8999I: Build Type: Production
Toolkti:
Version: 7.0.0
Build id: 7.0.0-20091028_2100 |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jul 18, 2012 7:22 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
You need to run mqsireportbroker to determine the "effective level" You need to update your toolkit.
These won't fix your problem, but other effort is not wise if you not using the latest version.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER
Last edited by lancelotlinc on Wed Jul 18, 2012 8:06 am; edited 1 time in total |
|
| Back to top |
|
|
| elenzo |
| Posted: Wed Jul 18, 2012 7:29 am Post subject: |
|
|
Acolyte
Joined: 22 Aug 2006
Posts: 53
|
Fixpack capability level = '' (effective level '7.0.0.1')
I cant change the toolkit, I am not the only developer here, in fact, this flow was not even made by my, but I know that everyone uses at least version 7 |
|
| Back to top |
|
|
| kimbert |
| Posted: Wed Jul 18, 2012 8:01 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| I'm no database expert, but could it be something to do with permissions / roles? If WMB doesn't have permissions to create temporary tables then that would prevent the SP from executing, right? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jul 18, 2012 8:02 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Change Dynamic Result sets to 4. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jul 18, 2012 8:07 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| elenzo wrote: |
Fixpack capability level = '' (effective level '7.0.0.1')
I cant change the toolkit, I am not the only developer here, in fact, this flow was not even made by my, but I know that everyone uses at least version 7 |
In general, you need to set the effective level to 7.0.0.4 at least.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| elenzo |
| Posted: Wed Jul 18, 2012 9:04 am Post subject: |
|
|
Acolyte
Joined: 22 Aug 2006
Posts: 53
|
| mqjeff wrote: |
| Change Dynamic Result sets to 4. |
It didnt work... |
|
| Back to top |
|
|
| elenzo |
| Posted: Wed Jul 18, 2012 9:07 am Post subject: |
|
|
Acolyte
Joined: 22 Aug 2006
Posts: 53
|
| kimbert wrote: |
| I'm no database expert, but could it be something to do with permissions / roles? If WMB doesn't have permissions to create temporary tables then that would prevent the SP from executing, right? |
It could be, but If I call the SP from an SQL client with the same user, it works fine...
On the other hand, the broker does't need to create a temporary table, is the SP who returns the info in a temp table. We did a change in the SP, only to return in a common table ,and it worked fine... unfortunalty the client said that the SP can not be change... so we have to find the solution in the broker side... |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jul 18, 2012 9:28 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| elenzo wrote: |
| mqjeff wrote: |
| Change Dynamic Result sets to 4. |
It didnt work... |
What does a User Trace show is returned? |
|
| Back to top |
|
|
| elenzo |
| Posted: Wed Jul 18, 2012 9:30 am Post subject: |
|
|
Acolyte
Joined: 22 Aug 2006
Posts: 53
|
| mqjeff wrote: |
| elenzo wrote: |
| mqjeff wrote: |
| Change Dynamic Result sets to 4. |
It didnt work... |
What does a User Trace show is returned? |
In the trace node I have this, to check the Environmnet (Environment.ResultSet[])
${Environment}
But it doesnt shows anything., the Environment is empty |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jul 18, 2012 9:56 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
mqjeff is asking about User Trace output, which would provide root cause information. This is different than a Trace node. You could also turn on ODBC logging to see the input/output to the driver.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
|
|
