| Author |
Message
|
| raphael.silva |
 Posted: Mon Mar 28, 2011 11:03 am Post subject: ODBC NOT RESPONDING |
 |
|
Newbie
Joined: 28 Mar 2011
Posts: 8
|
Hello!
I'm using MessageBroker 6.1.0.5 to connect to an Oracle and a SQLServer database.
The connection to the SQLServer works perfectly, but when I try to call stored procedures from Oracle, I'm having several issues.
When the flow calls the stored procedure from an ESQL compute node, the flow stops responding, I'm not able to do anything, and the broker has to be restarted.
It seems that it is waiting for the database, but is weird, because I checked the trace from ODBC, and apparently the stored procedure was never called. Nothing appears at the trace.For the SQLServer, the trace is written right away.
I made some other tests. I configured jdbc4 and made the call for the same Oracle stored procedure from a JAVA compute node. For the java compute, I got the results from the procedure in a few seconds.
The same procedure called from an ESQL node just doesnt work.
Sometimes i get the results, but after waiting 40 minutes. 40 minutes for the same procedure that the Java compute node returned in a few seconds.
My procedure is declared like this:
CREATE PROCEDURE findContract(IN contractCode CHARACTER) LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "TKT.TKT_FIND_CONTRACT";
I my esql node i've checked both options: Treat warnings as errors and Throw exception on database error.
I'm using DataDirect ODBC, and the configuration below:
[ERPTKT]
Driver=/opt/IBM/mqsi/6.1.0.5/ODBC64/V5.3/lib/UKora23.so
Description=DataDirect 5.3 64bit Oracle Wire Protocol
HostName=<IP>
PortNumber=1521
SID=DESE
CatalogOptions=0
EnableStaticCursorsForLongData=0
ApplicationUsingThreads=1
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
ProcedureRetResults=1
ColumnSizeAsCharacter=1
Please, can anyone help me with this problem?
I could migrate my project to use JavaCompute nodes only, but I don't think it would be a good idea. I just want to fix the ESQL->Oracle issue.
Thanks!! |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Mon Mar 28, 2011 11:06 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Can you otherwise establish a connection to the database from the broker server?
Do you see any error messages in the broker log about the Oracle database at all? |
|
| Back to top |
|
|
| raphael.silva |
| Posted: Mon Mar 28, 2011 11:16 am Post subject: |
|
|
Newbie
Joined: 28 Mar 2011
Posts: 8
|
Yes, I established the connection through the odbc test application running at the broker server.
And the javacompute node as well was able to connect to the same database.
In the log I didnt notice anything unusual.
It seems that the flow didnt throw an exception it simply is waiting for the database to respond... |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Mar 28, 2011 11:42 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
There are various fixes against Oracle and enhanced management of timeouts in later FPs of broker 6.1.
I encourage you to at least review the list of APARs between 6.1.0.5 and 6.1.0.9 (current). |
|
| Back to top |
|
|
| raphael.silva |
| Posted: Mon Mar 28, 2011 12:18 pm Post subject: |
|
|
Newbie
Joined: 28 Mar 2011
Posts: 8
|
I just run a test in my code like this:
SET OutputRoot.XMLNSC.XML.RETURN[] = SELECT 1 FROM Database.DUAL;
Everytime I run it, I got the response very fast.
That makes me think that the connection to the Database is ok.
I've checked the list of APARs. There were some fixes against Oracle, but none of them explains my problem.
I know that we should install all the FPs available. But here at my organization everything seems so hard. They're not going to allow the update unless i can prove for sure that there is a FP that fixes our issue. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Mar 28, 2011 12:26 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
My next step would be to enable ODBC tracing (in the ODBC.ini file) and then run the flow that issues the ESQL again.
This would enable you to see things like:-
- When was the command issued to the DB
- When the result set was returned to the Broker.
_________________
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 |
|
|
| mqjeff |
| Posted: Mon Mar 28, 2011 12:27 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
So if the connection is fine, then is the Stored Procedure taking too long to return?
You're sure you don't see the ODBC call to run the stored procedure?
You're sure that the database is able to resolve the location and schema of the stored procedure without significant issue?
And that the Broker has necessary permissions to run it? |
|
| Back to top |
|
|
| raphael.silva |
| Posted: Mon Mar 28, 2011 12:57 pm Post subject: |
|
|
Newbie
Joined: 28 Mar 2011
Posts: 8
|
I've already tried to enable ODBC trace.
Nothing appeared at the log. It seems that the command wasn't sent to the database.
The connection seems to be fine by the execution of SELECT 1 FROM Database.DUAL;
The broker has permissions to run the procedure. Even if it hasn't, the broker was supposed to show the permission denied error, right?
It still tricks me, why the select from dual command was sent so fast to the database and appeared automatically at the odbc trace, and the call to the procedure doesn't even seem to be executed by the broker.
If at least the broker showed me an error, I could start from that, but it simply seems to deadlock the esql process. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Mar 28, 2011 2:50 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Create a new EG.
Deploy *just* this flow to it.
enable service trace (not ODBC trace).
Run one single message through the flow.
See what service trace shows as a likely point of freeze.
Then open a PMR.... |
|
| Back to top |
|
|
| raphael.silva |
| Posted: Tue Mar 29, 2011 5:28 am Post subject: |
|
|
Newbie
Joined: 28 Mar 2011
Posts: 8
|
|
| Back to top |
|
|
|
|
