| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| IIB9: Mapping Node / DB Select / Schema Name |
« View previous topic :: View next topic » |
| Author |
Message
|
| akil |
 Posted: Thu Nov 06, 2014 11:30 pm Post subject: IIB9: Mapping Node / DB Select / Schema Name |
 |
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
My scenario is as follows:
I have a mapping node that selects from a table called PHONEEMAIL.
This owner of the table is CRMUSER.
The JDBCProvider is configured with a user called FINESB.
The database is Oracle.
The user FINESB has rights to CRMUSER.PHONEEMAIL.
The mapping node however finds that the table is missing ( see error below ).
| Code: |
<RecoverableException>
<File>MbErrorHandler.java</File>
<Line>154</Line>
<Function>evaluate</Function>
<Type/>
<Name/>
<Label/>
<Catalog>BIPmsgs</Catalog>
<Severity>3</Severity>
<Number>3947</Number>
<Text>Caught BrokerXCIDynamicException</Text>
<Insert>
<Type>5</Type>
<Text>{default}:buildGenerateOTPRequest</Text>
</Insert>
<Insert>
<Type>5</Type>
<Text>Declare external Java method calls: let $dbselect1 := jdbc:select('FINACLE', 'select CRMUSER.PHONEEMAIL.ORGKEY, CRMUSER.PHONEEMAIL.EMAIL, CRMUSER.PHONEEMAIL.PHONENOLOCALCODE, CRMUSER.PHONEEMAIL.PREFERREDFLAG from CRMUSER.PHONEEMAIL where CRMUSER.PHONEEMAIL.ORGKEY = ? AND CRMUSER.PHONEEMAIL.PREFERREDFLAG = ''Y''', ($LocalEnvironment0/Variables/io:validateRecharge/validateRechargeRequest/customerID), xs:int('0'), false(),'', xs:int('1') )</Text>
</Insert>
<RecoverableException>
<File>MbErrorHandler.java</File>
<Line>318</Line>
<Function>throwableToMbException</Function>
<Type/>
<Name/>
<Label/>
<Catalog>BIPmsgs</Catalog>
<Severity>3</Severity>
<Number>3949</Number>
<Text>Caught BrokerXCIDynamicException</Text>
<Insert>
<Type>5</Type>
<Text>java.sql.SQLException: ORA-00942: table or view does not exist
ORA-00942: table or view does not exist</Text>
</Insert>
</RecoverableException>
</RecoverableException>
|
I can fix this by creating a synonym in FINESB.
I am not able to figure out why this happens, the query as seen in the trace uses the schema name and should work without any problems. What's causing this exception to occur?
_________________
Regards |
|
| Back to top |
|
 |
| martinb |
| Posted: Fri Nov 14, 2014 1:25 am Post subject: |
|
|
Master
Joined: 09 Nov 2006
Posts: 210
Location: UK
|
I don't see enough information to fully understand exactly what is happening.
As you've said the Mapping node reports the SQL it will run against the database in the Broker User Trace.
I think you're saying the SQL is not using the database schema name your environment requires.
The JDBCProviders configurable service used to assocaite the Mapping with the runtime Database provides the property "databaseSchemaNames". Like it's name suggests, this "databaseSchemaNames" property gives you control over the schema name that will be sent by the Mapping node in the SQL it issues. |
|
| Back to top |
|
|
| akil |
| Posted: Fri Nov 14, 2014 8:05 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
In the trace, the following line is outputted
| Code: |
<Text>Declare external Java method calls: let $dbselect1 := jdbc:select('FINACLE', 'select CRMUSER.PHONEEMAIL.ORGKEY, CRMUSER.PHONEEMAIL.EMAIL, CRMUSER.PHONEEMAIL.PHONENOLOCALCODE, CRMUSER.PHONEEMAIL.PREFERREDFLAG from CRMUSER.PHONEEMAIL where CRMUSER.PHONEEMAIL.ORGKEY = ? AND CRMUSER.PHONEEMAIL.PREFERREDFLAG = ''Y''', ($LocalEnvironment0/Variables/io:validateRecharge/validateRechargeRequest/customerID), xs:int('0'), false(),'', xs:int('1') )</Text>
</Insert>
|
I thought this meant that the query will be run exactly as specified (which means that it will expect that the table PHONEMAIL exists in the CRMUSER schema ), and all that is required is to ensure that the user that's used to establish the connection has access to this schema & table.
However, this fails with the following error
| Code: |
<Text>java.sql.SQLException: ORA-00942: table or view does not exist
ORA-00942: table or view does not exist</Text>
|
& it works only when there is a synonym in the user that's used to establish the connection.
This means that the query that's specified at design time, is not run against the schema that is chosen at design time, and it intact runs against the user that is used to establish the connection. Is that so ?
You mentioned something about databaseSchemaNames, could you clarify what that means ? Till now I've just specified a single value there, and that's the same value I used to establish the connection ..
_________________
Regards |
|
| Back to top |
|
|
| martinb |
| Posted: Fri Nov 14, 2014 8:44 am Post subject: |
|
|
Master
Joined: 09 Nov 2006
Posts: 210
Location: UK
|
The user trace statements you should be looking at are of the form
"The Mapping Node ... SQL ..."
The JDBCProviders configurable service property "databaseSchemaNames" will determine what database schema is issued in the SQL.
Setting it to "useProvidedSchemaNames" to use the schema name provided in the map (the schema name used at design time).
Setting it to and empty string "" will cause the SQL to not include a schema name. In this case the behavior is dependent on the Database system, typically the schema will be the default for the user identity the Mapping node is configured to connect to the database with. |
|
| Back to top |
|
|
| akil |
| Posted: Fri Nov 14, 2014 9:35 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Oh all right , I will try out some tests . Thank you!
One follow up question: is it possible to override the data source name at deployment, for a mapping node ?
_________________
Regards |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Nov 14, 2014 10:20 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| akil wrote: |
Oh all right , I will try out some tests . Thank you!
One follow up question: is it possible to override the data source name at deployment, for a mapping node ? |
Isn't that why it's called a configurable service?
So no you don't override the datasource. What you do is change it's target. And the impact is broker wide not just for the application.
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| akil |
| Posted: Fri Nov 14, 2014 10:05 pm Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
There are a few differences in the configuration between compute nodes and mapping nodes, isn't it ?
Compute Node :
-- data source is configurable (overridable at deployment),
-- schema names as written in the code aren't configurable unless the the 'IN' clause is used.
Mapping Node:
-- data source is not configurable
-- schema names is configurable in the configurable service.
_________________
Regards |
|
| 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
|
|
|
|
