-
1. Re: adding oracle to my vdb
rareddy Nov 3, 2014 10:19 AM (in response to afrieden)You do not need a new VDB, you can add a new model to existing VDB for representing Oracle data source. You need to edit -vdb.xml, and add the model for Oracle, and also you need to create a data source for Oracle just like what you did for MongoDB. Just note that, for adding MongoDB you added a "resource-adapter", but for Oracle you need to add "data source". See example xml in "docs/teiid/datasources/oracle" directory. You also need to deploy your Oracle JDBC driver to JBoss EAP before you create the data source.
Once the the VDB is deployed, you can query, join, aggregate data from both MongoDB and Oracle as if they are in single database!
Ramesh..
-
2. Re: Re: adding oracle to my vdb
afrieden Nov 3, 2014 3:23 PM (in response to rareddy)Thanks!
I have put the oracle driver ojdbc6.jar in /opt/jboss-eap-6.1/modules/com/oracle/main/
But I am still getting errors on start up. I have added my files. Mongo stuff is working fine, only oracle stuff is broken. What am I doing wrong? Thanks!
-
standalone-teiid.xml 24.7 KB
-
server.log.zip 5.1 KB
-
mongodb-vdb.xml 975 bytes
-
-
3. Re: Re: adding oracle to my vdb
rareddy Nov 3, 2014 3:32 PM (in response to afrieden)In your "datasource" definition for Oracle in "standalone-teiid.xml" file, the driver property is defined as
<driver>ojdbc6.jar</driver>
This should be
<driver>oracle</driver>
Ramesh..
-
4. Re: Re: Re: adding oracle to my vdb
afrieden Nov 3, 2014 5:24 PM (in response to rareddy)fixed that, still getting an error that looks like my oracle jdbc jar is not specified or in the right place. I have attached most recent files.
-
standalone-teiid.xml 24.7 KB
-
server.log.zip 6.1 KB
-
mongodb-vdb.xml 975 bytes
-
-
5. Re: Re: Re: adding oracle to my vdb
rareddy Nov 3, 2014 6:13 PM (in response to afrieden)Post your contents of module.xml that you defined for Oracle, where you copied the ojdbc6.jar
Ramesh..
-
6. Re: Re: Re: Re: adding oracle to my vdb
afrieden Nov 3, 2014 7:07 PM (in response to rareddy)both are in /opt/jboss-eap-6.1/modules/com/oracle/main/
-
module.xml 290 bytes
-
-
7. Re: Re: Re: Re: Re: adding oracle to my vdb
rareddy Nov 3, 2014 9:52 PM (in response to afrieden)It needs to look like
<?xml version="1.0" encoding="UTF-8"?> <module xmlns="urn:jboss:module:1.0" name="com.oracle"> <resources> <resource-root path="ojdbc6.jar"/> </resources> <dependencies> <module name="javax.api"/> <module name="javax.resource.api"/> </dependencies> </module>
See the last line ending "</module>"
-
8. Re: Re: Re: Re: Re: Re: adding oracle to my vdb
afrieden Nov 4, 2014 12:32 PM (in response to rareddy)Fixed that, now getting a can not connect error. I made sure my server can connect to our oracle one and was able to connect without any problems. Is this error that it can't connect to the oracle database? All config files are same as before (except for the change made to module as you cited). Thanks!
-
server.log.zip 6.1 KB
-
-
9. Re: Re: Re: Re: Re: Re: Re: adding oracle to my vdb
rareddy Nov 4, 2014 1:16 PM (in response to afrieden)I see
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:359) at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:422) at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:672) at oracle.net.ns.NSProtocol.connect(NSProtocol.java:237) at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1042) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:301)
That means, the JDBC driver could NOT make connection to the Oracle database from the machine where Teiid is running. What is procedure you followed to make sure that connection to Oracle can be made outside the Teiid? Try to connect with SQL clients like SQuirreL and make sure that works!
Ramesh.. -
10. Re: Re: Re: Re: Re: Re: Re: Re: adding oracle to my vdb
afrieden Nov 4, 2014 1:34 PM (in response to rareddy)So I just ran the attached script using the ojdbc6.jar and it ran fine (on the same server as teiid). Is there any reason that would work but teiid wouldn't? Thanks!
-
QueryOracle.groovy.zip 379 bytes
-
-
11. Re: Re: Re: Re: Re: Re: Re: Re: adding oracle to my vdb
rareddy Nov 4, 2014 2:19 PM (in response to afrieden)Because the URL in groovy is jdbc:oracle:thin:@11.8.20.88:1525:ERPDR2
and in the standalone-teiid.xml is jdbc:oracle:thin:11.8.20.88:1521:ERPDR2
@ sign missing?
-
12. Re: Re: Re: Re: Re: Re: Re: Re: Re: adding oracle to my vdb
afrieden Nov 4, 2014 2:58 PM (in response to rareddy)Made the change and worked! However, I am having problems with the AUDIT_ACTIONS table and sys.AUDIT_ACTIONS table. I changed in my oracle model from
<property name="importer.useFullSchemaName" value="false"/>
to
<property name="importer.useFullSchemaName" value="true"/>
Now getting 14:52:35,462 WARN [org.teiid.RUNTIME] (teiid-async-threads - 2) TEIID50036 VDB vcf.1 model "erp" metadata failed to load. Reason:TEIID11010 java.sql.SQLException: Invalid argument(s) in call. I have attached the server.log. Any thoughts on what teiid is doing under the hood where it would need arguments? Thanks!
-
server.log.zip 5.9 KB
-
-
13. Re: Re: Re: Re: Re: Re: Re: Re: Re: adding oracle to my vdb
shawkins Nov 5, 2014 9:22 AM (in response to afrieden)More than likely useFullSchemaName is not causing an issue? What are all of you import properties? And can you run on 8.9 as that will show the full stack of the exception?
-
14. Re: Re: Re: Re: Re: Re: Re: Re: Re: adding oracle to my vdb
afrieden Nov 5, 2014 9:32 AM (in response to shawkins)I would rather not change versions unless there was a particular reason to. The trace was logged in my previous server.log. Is there a way to just exclude the AUDIT_ACTIONS table? Thanks!