| Author |
Message
|
| arunkumar1989 |
 Posted: Wed Jun 12, 2013 10:43 pm Post subject: From Single Compute node cannot access multiple datbases |
 |
|
Voyager
Joined: 21 Nov 2012
Posts: 98
Location: Chennai
|
Hi All,
First DSN : GISPORTAL_DB2 [this is db2 dsn for sample1 db]
Second DSN : GISPORTAL_ORACLE [this is ORACLE dsn for sample2 db]
I wants to access both database in single compute node
| Code: |
SET Resultset.RechargeDetails[]=PASSTHRU('select RechargeAmount(?,?) as RechargeDetails from dual',Environment.RechargeDetails.MOBILENO,Environment.RechargeDetails.RECHARGEAMOUNT); --- ORACLE DSN
SET Resultset.RechargeDetails1[]=SELECT e.* FROM Database.GISPORTAL_DB2.ARUN.EMAILDETAILS AS e ; --- DB2 DSN
|
This is my error message :
| Code: |
ExceptionList
RecoverableException
File:CHARACTER:F:\build\S000_P\src\DataFlowEngine\ImbDataFlowNode.cpp
Line:INTEGER:1073
Function:CHARACTER:ImbDataFlowNode::createExceptionList
Type:CHARACTER:ComIbmSOAPInputNode
Name:CHARACTER:GISPortal_ServiceFlow#FCMComposite_1_2
Label:CHARACTER:GISPortal_ServiceFlow.SOAP Input
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Node throwing exception
RecoverableException
File:CHARACTER:F:\build\S000_P\src\DataFlowEngine\ImbComputeNode.cpp
Line:INTEGER:481
Function:CHARACTER:ImbComputeNode::evaluate
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:GISPortal_ServiceFlow#FCMComposite_1_9.RechargeService#FCMComposite_1_1
Label:CHARACTER:GISPortal_ServiceFlow.RechargeService1.Recharge
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Caught exception and rethrowing
RecoverableException
File:CHARACTER:F:\build\S000_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp
Line:INTEGER:611
Function:CHARACTER:SqlStatementGroup::execute
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:GISPortal_ServiceFlow#FCMComposite_1_9.RechargeService#FCMComposite_1_1
Label:CHARACTER:GISPortal_ServiceFlow.RechargeService1.Recharge
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2488
Text:CHARACTER:Error detected, rethrowing
Insert
Type:INTEGER:5
Text:CHARACTER:.RechargeService_Recharge.Main
Insert
Type:INTEGER:5
Text:CHARACTER:17.3
Insert
Type:INTEGER:5
Text:CHARACTER:SET Resultset.RechargeDetails1[ ] = (SELECT ROW () FROM DATABASE());
RecoverableException
File:CHARACTER:F:\build\S000_P\src\DataFlowEngine\ImbRdl\ImbRdlDatabaseSelect.cpp
Line:INTEGER:231
Function:CHARACTER:SqlDatabaseSelectBase::executeQuery
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:GISPortal_ServiceFlow#FCMComposite_1_9.RechargeService#FCMComposite_1_1
Label:CHARACTER:GISPortal_ServiceFlow.RechargeService1.Recharge
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2466
Text:CHARACTER:Unsuitable database
Insert
Type:INTEGER:5
Text:CHARACTER:
Insert
Type:INTEGER:5
Text:CHARACTER:1.1
Insert
Type:INTEGER:5
Text:CHARACTER:GISPORTAL_DB2
Insert
Type:INTEGER:5
Text:CHARACTER:GISPORTAL_ORACLE
|
Note : we can access different dsn from single database software.
_________________
Being in a crowd when you are alone is ignorance. Enlightenment is being alone in a crowd; a feeling of oneness in a crowd. |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Thu Jun 13, 2013 12:02 am Post subject: Re: From Single Compute node cannot access multiple datbases |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| arunkumar1989 wrote: |
I wants to access both database in single compute node
|
You can't do this. You can only access two different databases of the SAME TYPE in a single compute node.
This is documented.
_________________
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 |
|
|
| McueMart |
| Posted: Thu Jun 13, 2013 1:54 am Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
| You might be able to do it by calling a Java function from your ESQL which performs the query on the second database. |
|
| Back to top |
|
|
| Esa |
| Posted: Thu Jun 13, 2013 2:26 am Post subject: Re: From Single Compute node cannot access multiple datbases |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| arunkumar1989 wrote: |
I wants to access both database in single compute node
|
You want the wrong thing.
Take a round-trip to another compute node (PROPAGATE DELETE NONE) to access the other datasource and do something like this:
| Code: |
| SET InputLocalEnvironment.RechargeDetails1[]=SELECT e.* FROM Database.GISPORTAL_DB2.ARUN.EMAILDETAILS AS e ; --- DB2 DSN |
|
|
| Back to top |
|
|
| arunkumar1989 |
| Posted: Mon Jun 17, 2013 9:30 pm Post subject: |
|
|
Voyager
Joined: 21 Nov 2012
Posts: 98
Location: Chennai
|
Hi All,
In Infocenter : "it is possible to use multiple database in single compute node"
They given syntax like this :
SET ResultSet.records[]=SELECT * FROM Database.{DSNNAME}.{SCHEMANAME}.{TABLENAME};
if i use {} braces... it is supporting.... i have to do any configuration for this?
_________________
Being in a crowd when you are alone is ignorance. Enlightenment is being alone in a crowd; a feeling of oneness in a crowd. |
|
| Back to top |
|
|
| Esa |
| Posted: Mon Jun 17, 2013 10:29 pm Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| arunkumar1989 wrote: |
Hi All,
In Infocenter : "it is possible to use multiple database in single compute node"
They given syntax like this :
SET ResultSet.records[]=SELECT * FROM Database.{DSNNAME}.{SCHEMANAME}.{TABLENAME};
if i use {} braces... it is supporting.... i have to do any configuration for this? |
 Congratulations, arunkumar1989! Good work!
I guess you may want to externalise SHEMANAME to an EXTERNAL variable, because the schema may be vary between environments.
It should work with the same curly brace syntax. I haven't tested it.
DSNNAME should remain the same accross environments, the whole point od DSN is to be able to use the same name accross environments nad let OCBC map it to the actual databases.
But you sometimes see that happening too, DSN names getting overrridden in bar files  |
|
| Back to top |
|
|
| arunkumar1989 |
| Posted: Mon Jun 17, 2013 10:42 pm Post subject: |
|
|
Voyager
Joined: 21 Nov 2012
Posts: 98
Location: Chennai
|
Hi smdavies99,
| Quote: |
You can't do this. You can only access two different databases of the SAME TYPE in a single compute node.
This is documented.
|
I tried to access which you are saying documented .... but i could not get can you share with us.
_________________
Being in a crowd when you are alone is ignorance. Enlightenment is being alone in a crowd; a feeling of oneness in a crowd. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Jun 17, 2013 10:52 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| arunkumar1989 wrote: |
I tried to access which you are saying documented .... but i could not get can you share with us. |
There is a secion in the InfoCentre that discusses this. This topic has also been covered in this forum before. Have you tried to search for it?
Try a search for something simple like
Multiple Database Access
_________________
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 |
|
|
| arunkumar1989 |
| Posted: Mon Jun 17, 2013 10:58 pm Post subject: |
|
|
Voyager
Joined: 21 Nov 2012
Posts: 98
Location: Chennai
|
This is the source from IBM :
http://www-01.ibm.com/support/docview.wss?uid=swg21449152
In ESQL
If you wish to use ODBC, you will need to use a Compute node:
1. Ensure that all of the databases are of the same type (since they will all be called through the same driver)
2. In the compute node, you can use ESQL like -
Database.{Datasource}.{SchemaName}.{Table} in the FROM clause
Here the problem is
| Code: |
Database.{Datasource}.{SchemaName}.{Table} in the FROM clause
|
this is not supporting
_________________
Being in a crowd when you are alone is ignorance. Enlightenment is being alone in a crowd; a feeling of oneness in a crowd. |
|
| Back to top |
|
|
| Esa |
| Posted: Mon Jun 17, 2013 11:15 pm Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
If you need to access databases of different types,you need to have two compute nodes.
Compute1 accesses DSN1, then propagates to compute2 that accesses DSN2 and copies the result set to Environment and returns, then compute1 continues with both result sets.
What is so difficult with this? |
|
| Back to top |
|
|
|
|
