| Author |
Message
|
| santy |
 Posted: Thu Nov 30, 2006 2:28 am Post subject: accessing multiple databases |
 |
|
Centurion
Joined: 03 Nov 2006
Posts: 141
|
Hi
I want to access Multiple databases through my flow.
Can I do it using single compute node?
If yes then how ..I mean we can give only one datasource name in compute node properties.
If no is there any way other than using multiple compute nodes?
Regards,
santy |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Thu Nov 30, 2006 2:46 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You can only do this in version 6.
The documentation on the Select statement includes instructions on how to do it.
You really don't need to worry about using one compute node or more than one compute node in most cases.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Nov 30, 2006 3:10 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
You should also be aware that all the DB's you access from within one compute node must all be of the same type (e.g. all DB2 or all Oracle etc)
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 |
|
|
| santy |
| Posted: Thu Nov 30, 2006 9:44 pm Post subject: |
|
|
Centurion
Joined: 03 Nov 2006
Posts: 141
|
Thanks a lot for your prompt reply..
I read about SELECT FUNCTION in ESQL but didnt get any useful information..
Please help ..
Regards,
santy |
|
| Back to top |
|
|
| gregop |
| Posted: Fri Dec 01, 2006 12:25 am Post subject: |
|
|
Voyager
Joined: 24 Nov 2006
Posts: 81
|
| SELECT * FROM Database.Datasource.SchemaName.Table As A |
|
| Back to top |
|
|
| santy |
| Posted: Fri Dec 01, 2006 1:51 am Post subject: |
|
|
Centurion
Joined: 03 Nov 2006
Posts: 141
|
Hi
What am I supposed to write In compute node properties "Data Source " tag?.. I doesnt accept two data source names
regards,
santy |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Dec 01, 2006 1:56 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| gregop wrote: |
| SELECT * FROM Database.<Datasource>.<SchemaName>.<Table> As A |
Database is a required key word there. Depending on username of your ODBC source you may not be able to use the SchemaName (oracle can set it so that you should not use the schema name and it defaults for the user (permissions restrictions)
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| santy |
| Posted: Fri Dec 01, 2006 2:53 am Post subject: |
|
|
Centurion
Joined: 03 Nov 2006
Posts: 141
|
| gregop wrote: |
| SELECT * FROM Database.Datasource.SchemaName.Table As A |
Here we can give different Datasource name if we want to access different database...
BUT what should I write in compute node DATA SOURCe propetrties..
It is not able to recognize if I put TWO database names separated by "," or ";" ...
What shouldI mention in compute node Data Source Properties.
Please help
regards,
santy |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Dec 01, 2006 3:04 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
In V6 you don't need the datasource properties as you specify the datasource name in the select statement. This is what allows you to access multiple datasources in the same node.
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| m.schneider |
| Posted: Thu Jul 12, 2007 11:31 pm Post subject: |
|
|
Centurion
Joined: 10 Apr 2007
Posts: 132
Location: Germany
|
I can't access multiple databases in the described way. I receive the following error message.
Ereignistyp: Fehler
Ereignisquelle: WebSphere Broker v6003
Ereigniskategorie: Keine
Ereigniskennung: 2580
( TMBROKER.EXEGRP1 ) ('.gather_BLB_data.getAcquirer', '9.21'): Keine Angabe einer externen Datenquelle für diesen Knoten.
Es wurde versucht, auf eine externe Datenbanktabelle zuzugreifen, aber für diesen Rechen-, Filter- bzw. Datenbankknoten sind keine Datenbankeingaben angegeben.
Korrigieren Sie die Syntax des ESQL-Ausdrucks im Knoten ''.gather_BLB_data.getAcquirer'', in der Gegend von Zeile und Spalte ''9.21'', und implementieren Sie den Nachrichtenfluss erneut: Fügen Sie für jede Tabelle, auf die zugegriffen wird, Datenbank-Eingaben hinzu, und implementieren Sie den Nachrichtenfluss erneut.
Daten:
Meaning there is missing a external datasource in this node. They tried to access an external db, but there is no external datasource for this node, ...
Can someone help? |
|
| Back to top |
|
|
| AkankshA |
| Posted: Fri Jul 13, 2007 1:22 am Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Jul 13, 2007 2:58 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| fjb_saper wrote: |
| In V6 you don't need the datasource properties as you specify the datasource name in the select statement. This is what allows you to access multiple datasources in the same node. |
However you still need to define the datasource in ODBC....
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| m.schneider |
| Posted: Mon Jul 16, 2007 1:25 am Post subject: |
|
|
Centurion
Joined: 10 Apr 2007
Posts: 132
Location: Germany
|
Thanks a lot, now it works.
The important fact that is not mentioned in the pdf-file, is that you need to declare ONE of the databases in the datasource. |
|
| Back to top |
|
|
| trongbx |
| Posted: Mon Sep 07, 2009 2:24 am Post subject: |
|
|
Novice
Joined: 23 Nov 2008
Posts: 21
|
Hi
My message flow can not access to 2 Database from the single compute node. I did specify one of the database i am using in the Data source Property and another i provided in the query. The both DSN are the same type DB2. I could connect to both of them in the two separate compute nodes.
Here is ESQL code:
| Code: |
[i]/* Get Customer PN (Qualcom_PN) from database based on IBM_PN and CUST_ID --CUST_PN_BASE */
DECLARE Qualcomm_PN_Check CHARACTER;
SET Qualcomm_PN_Check = THE (SELECT ITEM P.CUST_PN_BASE FROM Database.EpubDS.EDATA.PUB_PN_CNTRL AS P WHERE (P.CUST_ID='0000000278' AND P.IBM_PN = IBM_PN));
-- Get Customer Lotlabel for Insert statement
DECLARE LOTLABEL CHARACTER;
SET LOTLABEL = CAST(InputRoot.XMLNS.ns:SemiconductorProcessDataNotification.ns:LotReport.dm:Lot.dm:CustomerLotNumber.dm:ManufacturingID AS CHARACTER);
DECLARE LOTID CHARACTER;
SET LOTID = THE( SELECT ITEM P.lot_id from Database.DMIW_IBM.product AS P WHERE P.lotlabel='D7H07A0KFB');[/i] |
This is the error i got:
| Quote: |
Sep 7 06:05:50 btvxmdepub01n2 user:err|error WebSphere Broker v6102[381180]: (BRKEMIWPUB2.default)[9002]BIP2488E: (.MultiDatabaseAcess_Compute.Main, 25.3) Error detected whils
t executing the SQL statement 'SET Qualcomm_PN_Check = THE (SELECT ITEM COLUMN(0) FROM DATABASE(, IBM_PN));'. : BRKEMIWPUB2.21f7a110-2001-0000-0080-ce02932520dc: /build/S610_P/s
rc/DataFlowEngine/ImbRdl/ImbRdlStatementGroup.cpp: 586: SqlStatementGroup::execute: ComIbmComputeNode: MultiDatabaseAcess#FCMComposite_1_2
Sep 7 06:05:50 btvxmdepub01n2 user:err|error WebSphere Broker v6102[381180]: (BRKEMIWPUB2.default)[9002]BIP2321E: Database error: ODBC return code '-1'. : BRKEMIWPUB2.21f7a110-
2001-0000-0080-ce02932520dc: /build/S610_P/src/DataFlowEngine/ImbOdbc.cpp: 327: ImbOdbcHandle::checkRcInner: :
Sep 7 06:05:50 btvxmdepub01n2 user:err|error WebSphere Broker v6102[381180]: (BRKEMIWPUB2.default)[9002]BIP2322E: Database error: SQL State 'IM002'; Native Error Code '0'; Erro
r Text '[DataDirect][ODBC lib] System information file not found. Please check the ODBCINI environment variable.'. : BRKEMIWPUB2.21f7a110-2001-0000-0080-ce02932520dc: /build/S61
0_P/src/DataFlowEngine/ImbOdbc.cpp: 456: ImbOdbcHandle::checkRcInner: :
[b] |
[/b] |
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Mon Sep 07, 2009 2:42 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
| trongbx wrote: |
[9002]BIP2322E: Database error: SQL State 'IM002'; Native Error Code '0'; Error Text '[DataDirect][ODBC lib] System information file not found. Please check the ODBCINI environment variable.'. : BRKEMIWPUB2.21f7a110-2001-0000-0080-ce02932520dc: /build/S610_P/src/DataFlowEngine/ImbOdbc.cpp: 456: ImbOdbcHandle::checkRcInner: :
|
Did you do this?
If you connect to each datasource one at a time using the compute nodes property does this work?
Did you search for this error also? This link references it:
http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r1m0/index.jsp?topic=/com.ibm.etools.mft.doc/au16620_.htm |
|
| Back to top |
|
|
|
|
