adding oracle to my vdb

[afrieden]

I already have a mongodb vdb called mydb that can query one of my collections.  I would like to add a separate oracle to the same vdb.  Do I just need to now create an "oracle-vdb.xml" with the same vdb name and model name?  I read through the documentation and it adjusting the standalone-teiid.xml datasources tag.  Will this combine with the mongo resource adapter and mongodb-vdb.xml?.  Thanks!

[rareddy]

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..

[afrieden]

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!

[rareddy]

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..

[afrieden]

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.

[rareddy]

Post your contents of module.xml that you defined for Oracle, where you copied the ojdbc6.jar

Ramesh..

[afrieden]

both are in /opt/jboss-eap-6.1/modules/com/oracle/main/

[rareddy]

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>"

[afrieden]

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!

[rareddy]

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..

[afrieden]

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!

[rareddy]

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?

[afrieden]

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!

[shawkins]

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?

[afrieden]

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!