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 » Unknown DB Procedure

Post new topic  Reply to topic
 Unknown DB Procedure « View previous topic :: View next topic » 
Author Message
HOMETOWN47
PostPosted: Fri Jun 06, 2008 4:00 am    Post subject: Unknown DB Procedure Reply with quote

Apprentice

Joined: 25 Mar 2003
Posts: 34
I'm using WMB 5 and have a flow that calls two compute nodes one after the other. Each node has a different DSN property defined to access two different databases.

The first compute node calls a procedure:-

CALL ProcA(param1) EXTERNAL SCHEMA user1

This procedure is defined in common_code.esql which is as follows:-

CREATE PROCEDURE ProcA(IN param1 INT)
EXTERNAL NAME "%.HUBEXEC.ProcA";

CREATE PROCEDURE ProcB(IN other CHAR)
EXTERNAL NAME "%.HUBEXEC.ProcB";

these two procedures exist in the 1st database.

The second compute node accesses a table in the 2nd database:-

SET i = (SELECT COUNT(*) FROM Database.table1);


The DSN used in the first compute node has all the correct priveleges and grants etc to accesss the two procedures (ProcA and ProcB) in the 1st DB
Likewise, the DSN used in the second compute node has all the correct priveleges and grants to accesss the table in the 2nd database.


The problem is that at runtime the broker is throwing an exception at the point of processing the 2nd compute node:-

'The procedure is unknown to the database and no definition could be found' and it's complaining about ProcA and ProcB.

I think that this is happening because WMB includes common_code.esql into the flow and that at the point of the 2nd compute node it is trying to resolve the two procedures (ProcA and ProcB) against the 2nd database even though I do not refer to the procedures in the 2nd compute node !

How does WMB resolve DB names at runtime ? Is this a limitation of having DB procedures in a common piece of ESQL ? I'm assuming that if the DB procedures (ProcA and ProcB) were defined within the scope of the 1st compute node then this would not be an issue.


Any thoughts or ideas ?
Back to top
View user's profile Send private message
smdavies99
PostPosted: Fri Jun 06, 2008 10:42 pm    Post subject: I've seen this before Reply with quote

Jedi Council

Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
With Broker V5.
The issue is (if I recall the PMR Answer correctly) that the broker seems to do a 'look ahead' for DB calls and Stored procs in particular.
In the system I was working on some 2.5 years ago, we had a local DB2 Database and TWO remote (not on this broker server box) SQL Server Databases.
The issue was with the two SQL Server Databases.
The broker threw a wobbly when it was working with one SQL Server DB and reported the non existence of a Stored proc which was defined in the second SQL Server Db but not in the first.
We solved this by defining a shell Stored Proc in the first DB. Then the 'look ahead' issues were resolved. The real Stored proc was used when it was called in the second Compute Node.

Ironically, we had no problems with this type of error between SQL Server & DB2 Databases. It was only with two different SQL Server DB's. I don't know if this is a problem with two databases of the same type ( eg Two different Oracle DB's) or just SQL Server.

AFAIK, this does not seem to be a problem with V6.x Brokers.

Steve D
_________________
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
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 » Unknown DB Procedure
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.