ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » IIB SQL Procedure call failure

Post new topic  Reply to topic
 IIB SQL Procedure call failure « View previous topic :: View next topic » 
Author Message
meena05
PostPosted: Thu May 19, 2016 2:26 pm    Post subject: IIB SQL Procedure call failure Reply with quote

Apprentice

Joined: 26 Feb 2016
Posts: 39

Hi,
I am calling SQL Server procedure using following way in my ESQL module:

CALL getData(param1, param2, param3, param4, param5, param6, param7, param8, Environment.Variables.ResultSet[]);

CREATE PROCEDURE getData(IN PARAM1 CHARACTER,IN PARAM2 CHARACTER,IN PARAM3 CHARACTER,IN PARAM4 CHARACTER,IN PARAM5 CHARACTER,IN PARAM6 CHARACTER, IN PARAM7 CHARACTER,IN PARAM8 CHARACTER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL
NAME "dbo.proc1";

SQL Server Driver is DataDirect 7.1 SQL Server Wire Protocol "

Once the code is deployed and executed, the CALL function is invoked and EG is getting restarted automatically and I am not receiving any SOAP response back.

Below is the trace
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

2016-05-19 15:29:06.039104 17008 UserTrace BIP12080I: Preparing a database statement for ''SQL_DSN''.
Preparing a database statement against data source ''SQL_DSN''.
2016-05-19 15:29:06.039355 17008 UserTrace BIP12081I: Prepared a database statement for ''SQL_DSN''.
Prepared a database statement against data source ''SQL_DSN''.
2016-05-19 15:29:06.063778 17008 UserTrace BIP12074I: Executing a database statement for ''SQL_DSN''.
Executing a database statement against data source ''SQL_DSN''.
2016-05-19 15:29:15.004922 25491 Information BIP2152I: Configuration message received from broker.
An execution group received a command from the Broker.
No user action required.
2016-05-19 15:29:15.005062 25491 Information BIP2153I: About to ''Start'' an execution group.
An execution group is about to perform an action.
No user action required.
2016-05-19 15:29:15.273763 25491 UserTrace BIP3487W: ''Dispatcher org.apache.axis2.engine.SOAPActionBasedDispatcher is now deprecated.''
An embedded component has written the 'WARNING' level diagnostic message included here.
Refer to the appropriate message in the embedded component's documentation.
2016-05-19 15:29:15.273949 25491 UserTrace BIP3487W: ''Please edit axis2.xml and replace with the same class in org.apache.axis2.dispatchers package''
An embedded component has written the 'WARNING' level diagnostic message included here.
Refer to the appropriate message in the embedded component's documentation.
2016-05-19 15:29:15.276928 25491 UserTrace BIP3487W: ''Dispatcher org.apache.axis2.engine.SOAPMessageBodyBasedDispatcher is now deprecated.''
An embedded component has written the 'WARNING' level diagnostic message included here.
Refer to the appropriate message in the embedded component's documentation.
2016-05-19 15:29:15.277018 25491 UserTrace BIP3487W: ''Please edit axis2.xml and replace with the same class in org.apache.axis2.dispatchers package''
An embedded component has written the 'WARNING' level diagnostic message included here.
Refer to the appropriate message in the embedded component's documentation.
2016-05-19 15:29:15.277865 25491 UserTrace BIP3487W: ''Dispatcher org.apache.axis2.engine.SOAPActionBasedDispatcher is now deprecated.''
An embedded component has written the 'WARNING' level diagnostic message included here.
Refer to the appropriate message in the embedded component's documentation.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Then I tried using PASSTHRU call
SET Environment.Variables.ResultSet[] = PASSTHRU('call dbo.Proc1(?,?,?,?,?,?,?,?)',param1,param2,param3,param4,param5,param6,param7,param8);

and received below SOAPFault
<faultstring>Id Number:BIP2322E:Child SQL exception:60:0:[IBM][ODBC 20101 driver]1254</faultstring>

Trace is
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

2016-05-19 17:16:49.491064 55117 RecoverableException BIP2488E: ('FlowName.getData_Compute.CopyEntireMessage', '22.2') Error detected whilst executing the SQL statement ''SET Environment.Variables.ResultSet[] = DEFAULTPASSTHRU('call dbo.Proc1(?,?,?,?,?,?,?,?)', param1,param2,param3,param4,param5,param6,param7,param8);''.
The message broker detected an error whilst executing the given statement. An exception has been thrown to cut short the SQL program.
See the following messages for details of the error.
2016-05-19 17:16:49.491068 55117 DatabaseException BIP2321E: Database error: ODBC return code '-1' using ODBC driver manager ''/opt/ibm/IE02/2.0.1/lib/libodbcinterface.so''.
The message broker encountered an error when processing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database concerning this error.
Use the following messages to determine the cause of the error. Typical problems are an incorrect datasource or table names. Correct either the database or message broker configuration.
2016-05-19 17:16:49.491068 55117 DatabaseException BIP2322E: Database error: SQL State ''60''; Native Error Code '0'; Error Text ''[IBM][ODBC 20101 driver]1254''.
The error has the following diagnostic information: SQL State ''60'' SQL Native Error Code '0' SQL Error Text ''[IBM][ODBC 20101 driver]1254''

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

I am working to verify the issue. Meanwhile, please let me know any solutions to fix this issue.
Back to top
View user's profile Send private message
mayheminMQ
PostPosted: Fri May 20, 2016 1:24 am    Post subject: Reply with quote

Voyager

Joined: 04 Sep 2012
Posts: 77
Location: UK beyond the meadows of RocknRoll

The BIP error code points towards character conversion or something in the data that the DB cannot understand. What does the search on Google or on Oracle state about the error code?

EG restart generally means it has run out of memory and has no more memory it can take from. I have encountered this if I load up my Environment with msg really really big and then run a whole tree spanning operation on it. Or a my favo culprit - Infinite loop in the code.
_________________
A Colorblind man may appear disadvantaged but he always sees more than just colors...
Back to top
View user's profile Send private message
meena05
PostPosted: Fri May 20, 2016 7:18 am    Post subject: Reply with quote

Apprentice

Joined: 26 Feb 2016
Posts: 39

Thanks for your reply. The DB procedure call is working from IIB flow on local machine. It is failing once deployed to environment.
Version IIB local is 9.0 and on environment it is 9005. Not sure if it is version issue or driver issue.
Back to top
View user's profile Send private message
Vitor
PostPosted: Fri May 20, 2016 7:25 am    Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA

meena05 wrote:
Thanks for your reply. The DB procedure call is working from IIB flow on local machine. It is failing once deployed to environment.
Version IIB local is 9.0 and on environment it is 9005. Not sure if it is version issue or driver issue.


Or an OS issue, if the deployed environment is not Windows and/or uses a different default code page.
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
mqjeff
PostPosted: Fri May 20, 2016 7:34 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447

Or a configuration error with the ODBC DSN in the non-working environment...
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
meena05
PostPosted: Fri May 20, 2016 8:39 am    Post subject: Reply with quote

Apprentice

Joined: 26 Feb 2016
Posts: 39

procedure call on same DSN which returns result set is working fine. For one procedure it is failing and restarting the execution group. Any work around will fix this?
Back to top
View user's profile Send private message
mqjeff
PostPosted: Fri May 20, 2016 8:46 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447

meena05 wrote:
. Any work around will fix this?

Perform troubleshooting.

In general, if an EG crashes, then you should probably open a PMR.
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » IIB SQL Procedure call failure
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.