| Author |
Message
|
| wmb_wps_soa |
 Posted: Wed Mar 02, 2011 3:43 pm Post subject: Calling DB2 Stored Procedure from compute node. |
 |
|
Acolyte
Joined: 19 Feb 2010
Posts: 65
Location: Detroit,Michigan,USA.
|
Hi,
I have an requirement where I have to call the DB2 stored procedure from compute node.
In the compute node, there are many different dbcalls to which the compute node connects thru DSN it is assosiated with (DSN was set in the node property). Can we call DB2 stored procedure (Database A) from compute node which was set up with different DSN (Database B)in the node property?
Guys, please guide.
Jeba |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Wed Mar 02, 2011 8:02 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
The database configured on the node and the ones called from within the node need to be of the same type (all db2 or all oracle etc...) 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| wmb_wps_soa |
| Posted: Wed Mar 02, 2011 8:21 pm Post subject: |
|
|
Acolyte
Joined: 19 Feb 2010
Posts: 65
Location: Detroit,Michigan,USA.
|
Yes, both are DB2, but different databases running on different servers. In the CREATE PROCEDURE for DB2 stored procedure, we are not passing any DSN/Database name but we are passing only the schema name. How does the broker knows the DSN/Database name?
The stored procedure call is as below:
CREATE PROCEDURE db2storedproc(IN empNo CHARACTER,IN empType CHARACTER,OUT p_sqlcode INTEGER) LANGUAGE DATABASE EXTERNAL NAME "db2schema1.db2storeprocname";
Jeba |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Mar 03, 2011 2:38 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| How does the broker knows the DSN/Database name |
In the scenario you describe the DSN used would be the one on the node... You can provide one dynamically if required instead...
_________________
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 |
|
|
| wmb_wps_soa |
| Posted: Thu Mar 03, 2011 5:54 am Post subject: |
|
|
Acolyte
Joined: 19 Feb 2010
Posts: 65
Location: Detroit,Michigan,USA.
|
Yes,
The compute node is having DSN which points to different DB2 database than what is there in the DB2 stored procedure. Hence the database for the DB2 stored procedure needs to be set dynamically. But how can it be done?
Is there anyway we can make the DB2 stored procedure call to DatabaseB while compute node points to DatabaseA? If yes, could you please give the details?
Thank you guys.
Jeba |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Mar 03, 2011 6:46 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| wmb_wps_soa wrote: |
| Is there anyway we can make the DB2 stored procedure call to DatabaseB while compute node points to DatabaseA? If yes, could you please give the details? |
Yes.
By reading the documentation on the CREATE PROCEDURE and CALL statements, to determine how to dynamically provide the DSN.
Or looking at the other recent threads on this subject. |
|
| Back to top |
|
|
| wmb_wps_soa |
| Posted: Thu Mar 03, 2011 7:11 am Post subject: |
|
|
Acolyte
Joined: 19 Feb 2010
Posts: 65
Location: Detroit,Michigan,USA.
|
Thank you for the reply.
We could perform multiple database operations by defining the DSN name dynamically. But how can we set the DSN/Database name dynmailcally while calling the stored procedure? I could not find anything from the infocenter.
Could anybody help please?
Jeba |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Thu Mar 03, 2011 10:38 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| wmb_wps_soa wrote: |
| I could not find anything from the infocenter |
Check:- the IN clause for the CALL Statement
- the TO clause for the SELECT function or PASSTHRU statement
- the TableReference for the DELETE FROM or INSERT INTO statements
... among possibly other places.
| fjb_saper wrote: |
| The database configured on the node and the ones called from within the node need to be of the same type (all db2 or all oracle etc...) |
That ought to be documented somewhere too. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Mar 03, 2011 11:26 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| rekarm01 wrote: |
| fjb_saper wrote: |
| The database configured on the node and the ones called from within the node need to be of the same type (all db2 or all oracle etc...) |
That ought to be documented somewhere too. |
It is. Well at least it was in V6.0. There are some links to it from old posts in this forum.
_________________
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 |
|
|
| rekarm01 |
| Posted: Fri Mar 04, 2011 1:57 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
There are quite a few posts over the last several years mentioning the restriction that static dynamic DSNs must match static DSNs, but I have yet to find any supporting documentation for this restriction, in either the WMB 6.0, 6.1, or 7.0 InfoCenters.
I have found documentation that a node that uses a database must provide a static DSN, even if it uses only dynamic DSNs, but that's as far as it goes. |
|
| Back to top |
|
|
| mgk |
| Posted: Fri Mar 04, 2011 2:41 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hello.
| Quote: |
| I have yet to find any supporting documentation for this restriction, in either the WMB 6.0, 6.1, or 7.0 InfoCenters. |
The wording you are looking for in the docs is:
| Quote: |
| All databases accessed from the same node must have the same ODBC functions as the database specified on the Data Source property on that node. This requirement is always satisfied if the databases are of the same type (for example, DB2® or Oracle), at the same release level (for example, release 9.1), and on the same platform. Other database combinations might have the same ODBC functions. If a node tries to access a database that does not have the same ODBC functions as the database specified on the Data Source property on that node, the broker generates an error message. |
And it is documented in topic: ac07000 (http://publib.boulder.ibm.com/infocenter/wmbhelp/v7r0m0/topic/com.ibm.etools.mft.doc/ac07000_.htm)
I believe the above has been in the docs since V6 but I could be out by a release or two 
Also, in 7.0.0.2 the docs in the same topic have been updated to say:
| Quote: |
| You can use the mqsicvp command as an ODBC test tool. This test tool can be run against two databases simultaneously, and tells you whether those two datasources are eligible to be used together in the same node. |
This means you can use mqsicvp to test two different DSN's outside of the Broker to see if the Broker will let you use both in the same Compute node.
I hope this helps,
Kind 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 |
|
|
| rekarm01 |
| Posted: Fri Mar 04, 2011 9:41 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| mgk wrote: |
The wording you are looking for in the docs is:
| Quote: |
| All databases accessed from the same node must have the same ODBC functions as the database specified on the Data Source property on that node. This requirement is always satisfied if the databases are of the same type (for example, DB2® or Oracle), at the same release level (for example, release 9.1), and on the same platform. Other database combinations might have the same ODBC functions. If a node tries to access a database that does not have the same ODBC functions as the database specified on the Data Source property on that node, the broker generates an error message. |
|
Yes, that's exactly what I was looking for. It's also in the same topic for WMB 6.0 and WMB 6.1.
I was looking in the wrong section; I expected to find something in the "Reference" section, under "built-in nodes" or "ESQL", where dynamic DSNs are used or described. As we had already resolved our immediate issues with dynamic DSNs at the time, (thanks to this forum), I didn't look hard enough elsewhere, but it's good to know that it's documented.
Thanks, mgk. |
|
| Back to top |
|
|
|
|
