| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| WBIMB, EXTERNAL NAME, Oracle Stored Procedure(BIP2920E) |
« View previous topic :: View next topic » |
| Author |
Message
|
| catwood2 |
 Posted: Fri Jul 15, 2005 2:25 pm Post subject: WBIMB, EXTERNAL NAME, Oracle Stored Procedure(BIP2920E) |
 |
|
Centurion
Joined: 17 May 2002
Posts: 108
|
Thanks in advance for pointers.
I am trying to get an oracle stored procedure executed. I have verified via toad that the package exists(schema browser) and am confident that the External Name is correct. I used mqsisetdbparms command to set userid and passwd. The proc can be executed outside of wbimb with those credentials. On csd4.
I am getting the following error:
| Quote: |
2005-07-15 14:26:42.935287 6420 RecoverableException BIP2920E: Whilst attempting to obtain a procedure definition, the database reported that the procedure 'PORTAL.Ldap_Custom_Interface2.updateUser' does not exist, or cannot be accesed.
If the procedure 'PORTAL.Ldap_Custom_Interface2.updateUser' does not exist it will need to be created before it can be called. Otherwise ensure that the EXTERNAL NAME matches the name by which it is known to the database, including any necessary schema/package qualifiers.
|
When the following code is being executed/called:
| Code: |
Create Procedure updateUser (
IN parm1 Character,
IN parm2 Integer,
IN parm3 Character,
IN parm4 Character,
IN parm5 Integer,
IN parm6 Character,
IN parm7 Character,
INOUT parm8 Character
) EXTERNAL NAME "portal.LDAP_CUSTOM_INTERFACE2.updateUser";
|
thanks!
catwood |
|
| Back to top |
|
 |
| catwood2 |
| Posted: Fri Jul 15, 2005 3:33 pm Post subject: |
|
|
Centurion
Joined: 17 May 2002
Posts: 108
|
Ok. Had the dba give the mqsicreatebroker userid execute rights to the procedure. Different behavior. Error:
| Quote: |
2005-07-15 16:26:49.915222 8408 RecoverableException BIP2599E: The ESQL d
eployed to the node 'prototype.CATWOOD_PROTO_Compute' is invalid.
The message could not be processed becaus
e the ESQL deployed to the node is invalid. Examine previous error messages for
details of the problem.
|
Of course, the esql passes the syntax checker.
thx
catwood |
|
| Back to top |
|
|
| catwood2 |
| Posted: Tue Jul 19, 2005 10:48 am Post subject: |
|
|
Centurion
Joined: 17 May 2002
Posts: 108
|
| This saga continues. The previous post appears to be intermittent behavior when a new deploy takes place. That is, I get the invalid esql has been deployed - while all the following messages are related to the schema name and/or authorizations. |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Jul 19, 2005 11:53 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
BIP2599 is a known issue which has been fixed, but the fix is not yet in a CSD at the time of writing (CSD5).
The reason you get it is because there is a database statement that is in error in your ESQL, and the first time you get the BIP2599 you will also get other messages telling you what the problem is. But subsequently you will always get the BIP2599 until you redeploy / restart the EG. This means that, to fix it the problem you must redeploy the ESQL or restart the broker each time you change the SP so that the changes are picked up by the broker.
With the fix, you will not have to redeploy / restart each time (assuming that the DB syntax error is not in the ESQL itself, which would mean a redeploy. An example of this would be missing a datasource of a compute node that talks to a DB, and this kind of error needs a redeploy to fix.)
Note that changes to a SP after the SP definition has been read by the EG are not picked up by the broker without a redeploy or restart of the execution group with or without the fix. That is to say if the first deploy succeds in finding the SP, then after this the definition is cached until the next redeploy / or restart of the broker.
To get the fix either raise a PMR or wait until the next CSD
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| catwood2 |
| Posted: Tue Jul 19, 2005 2:54 pm Post subject: |
|
|
Centurion
Joined: 17 May 2002
Posts: 108
|
mgk-
Thanks for the info.
I am no longer getting the esql errors. I'm hoping somebody can point me to what the following may mean:
I can execute an oracle stored proc from the broker when it is not part of a package:
EXTERNAL NAME = schema.proc
When I go after the proc in a package:
EXTERNAL NAME = schema.package.proc
I am erroring out that it does not exist or I'm not authorized. Admins advise me it is all set to go. I view thru toad and it looks like it's there (name matches my package name) and I have rights to execute. Trying to look up the "right" question to ask the dba side. Any thoughts appreciated.
thx
catwood2 |
|
| Back to top |
|
|
| catwood2 |
| Posted: Tue Jul 19, 2005 3:33 pm Post subject: |
|
|
Centurion
Joined: 17 May 2002
Posts: 108
|
I'm starting to wonder if the package name isn't a factor - it contains underscores..from the wbimb guide:
| Quote: |
The clause below is modified by the broker and mySchema.Proc\_ is passed to the database (this assumes that the database escape character is a backslash);
...EXTERNAL NAME "mySchema.Proc_" |
[/quote] |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Jul 20, 2005 2:45 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi, Have a look at an odbc trace. I would expect that underscores are escaped.
Cheeers,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| catwood2 |
| Posted: Wed Jul 20, 2005 3:25 pm Post subject: RESOLVED WBIMB, EXTERNAL NAME, Oracle Stored Procedure(BIP29 |
|
|
Centurion
Joined: 17 May 2002
Posts: 108
|
We had the dba do a couple things:
1. Move on of the stored procs outside of the package
2. Create a new package lacking the underscore character.
First test was against the stored proc outside the package and we got the following error:
| Quote: |
| 2005-07-20 14:57:42.517402 5324 DatabaseException BIP2322E: Database error: SQL State 'HYC00'; Native Error Code '0'; Error Text '[DataDirect][ODBC Oracle driver]Optional feature not implemented.'. |
As I had executed a different stored proc in the db prior to the change..finally began suspecting the odbc driver. This environment had the WBI VSAM adapter configured and (I'm suspecting) this is why the odbc entry was: MQSeries DataDirect 4.10 32-Bit Oracle 8.
It was noted there was an Oracle9 driver in the available driver list (the db is ora10g) we decided to try that on the stored proc outside the db. The result was then:
| Quote: |
2005-07-20 15:48:41.341690 6624 RecoverableException BIP2922E: The databa
se reports that the procedure 'portal.userSearch' in schema 'PORTAL' has '5' ove
rloaded versions. This is not supported, as it cannot be determined which of the
'5' definitions is required.
|
In toad, I only saw 1 defined in the general proc section. Still a minor mystery.
Then figured this driver version might be related to the visibility to the package.storedproc and tested that with the result that the procedure was overloaded. Which, indeed it was by wbimb definitions.
major mystery solved.
This is long and from my searches I'm certain pretty obscure. But this board has helped me out with a couple of those so...figured it was worth documenting.
catwood2 |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
