1 2 Previous Next 15 Replies Latest reply: Sep 15, 2011 7:14 AM by Ramesh Reddy RSS

Issue while connecting to oracle db from teiid

yogi goli Newbie

Please help,

 

Oracle connection problem with dynamic vdb. I am attaching herewith 2 files 1 vdb file and other is datasource. Please tell me what i did wrong.

The Dynamic portfolio example working fine.

 

1) run localhost 31000 dynamicportfolio "select * from product"  -> working fine

2) run localhost 31000 oracledb "select * from product" -> problems

  • 1. Re: Issue while connecting to oracle db from teiid
    Ramesh Reddy Master

    Yogi,

     

    In "oracledb-vdb.xml" on the HUMIS01 model you have the translator name set wrong. it should be "oracle". However that will not generate any errors, it is just wrong translator being used. what is the error that you are seeing?

     

    Ramesh..

  • 2. Re: Issue while connecting to oracle db from teiid
    yogi goli Newbie

    SEVERE: Could not create connection

    org.teiid.jdbc.TeiidSQLException: Remote org.teiid.client.security.LogonExceptio

    n: VDB "oracledb" version "1" is not in the "active" status.

            at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:113)

            at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:70)

            at org.teiid.jdbc.SocketProfile.connect(SocketProfile.java:56)

            at org.teiid.jdbc.TeiidDriver.connect(TeiidDriver.java:107)

            at java.sql.DriverManager.getConnection(Unknown Source)

            at java.sql.DriverManager.getConnection(Unknown Source)

            at JDBCClient.getDriverConnection(JDBCClient.java:54)

            at JDBCClient.main(JDBCClient.java:42)

    Caused by: [ConnectionException]Remote org.teiid.client.security.LogonException:

    VDB "oracledb" version "1" is not in the "active" status.

    1 [LogonException]Remote org.teiid.client.security.LogonException: VDB "oracledb

    " version "1" is not in the "active" status.

    2 [TeiidSecurityException]Remote org.teiid.dqp.service.SessionServiceException:

    VDB "oracledb" version "1" is not in the "active" status.

            at org.teiid.net.socket.SocketServerConnection.selectServerInstance(Sock

    etServerConnection.java:141)

            at org.teiid.net.socket.SocketServerConnection.<init>(SocketServerConnec

    tion.java:94)

            at org.teiid.net.socket.SocketServerConnectionFactory.getConnection(Sock

    etServerConnectionFactory.java:320)

            at org.teiid.jdbc.SocketProfile.connect(SocketProfile.java:54)

            ... 5 more

    Caused by: [LogonException]Remote org.teiid.client.security.LogonException: VDB

    "oracledb" version "1" is not in the "active" status.

    1 [TeiidSecurityException]Remote org.teiid.dqp.service.SessionServiceException:

    VDB "oracledb" version "1" is not in the "active" status.

            at org.teiid.transport.LogonImpl.logon(LogonImpl.java:91)

  • 3. Re: Issue while connecting to oracle db from teiid
    yogi goli Newbie

    C:\jboss-5.1.0.GA\server\default\teiid-examples\simpleclient>run localhost 31000

    oracledb "select * from SC.NAME"

    Sep 14, 2011 6:38:40 PM org.teiid.jdbc.TeiidDriver connect

    SEVERE: Could not create connection

    org.teiid.jdbc.TeiidSQLException: Remote org.teiid.client.security.LogonExceptio

    n: VDB "oracledb" version "1" is not in the "active" status.

            at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:113)

            at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:70)

            at org.teiid.jdbc.SocketProfile.connect(SocketProfile.java:56)

            at org.teiid.jdbc.TeiidDriver.connect(TeiidDriver.java:107)

            at java.sql.DriverManager.getConnection(Unknown Source)

            at java.sql.DriverManager.getConnection(Unknown Source)

            at JDBCClient.getDriverConnection(JDBCClient.java:54)

            at JDBCClient.main(JDBCClient.java:42)

    Caused by: [ConnectionException]Remote org.teiid.client.security.LogonException:

    VDB "oracledb" version "1" is not in the "active" status.

    1 [LogonException]Remote org.teiid.client.security.LogonException: VDB "oracledb

    " version "1" is not in the "active" status.

    2 [TeiidSecurityException]Remote org.teiid.dqp.service.SessionServiceException:

    VDB "oracledb" version "1" is not in the "active" status.

            at org.teiid.net.socket.SocketServerConnection.selectServerInstance(Sock

    etServerConnection.java:141)

            at org.teiid.net.socket.SocketServerConnection.<init>(SocketServerConnec

    tion.java:94)

            at org.teiid.net.socket.SocketServerConnectionFactory.getConnection(Sock

    etServerConnectionFactory.java:320)

            at org.teiid.jdbc.SocketProfile.connect(SocketProfile.java:54)

            ... 5 more

    Caused by: [LogonException]Remote org.teiid.client.security.LogonException: VDB

    "oracledb" version "1" is not in the "active" status.

    1 [TeiidSecurityException]Remote org.teiid.dqp.service.SessionServiceException:

    VDB "oracledb" version "1" is not in the "active" status.

            at org.teiid.transport.LogonImpl.logon(LogonImpl.java:91)

            at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

            at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

            at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

            at java.lang.reflect.Method.invoke(Unknown Source)

            at org.teiid.transport.ServerWorkItem.run(ServerWorkItem.java:81)

            at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkCon

    text.java:194)

            at org.teiid.transport.SocketClientInstance.processMessagePacket(SocketC

    lientInstance.java:160)

            at org.teiid.transport.SocketClientInstance.receivedMessage(SocketClient

    Instance.java:149)

            at org.teiid.transport.SSLAwareChannelHandler.messageReceived(SSLAwareCh

    annelHandler.java:210)

            at org.jboss.netty.channel.SimpleChannelHandler.handleUpstream(SimpleCha

    nnelHandler.java:100)

            at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultCh

    annelPipeline.java:545)

            at org.jboss.netty.channel.DefaultChannelPipeline$DefaultChannelHandlerC

    ontext.sendUpstream(DefaultChannelPipeline.java:754)

            at org.jboss.netty.handler.stream.ChunkedWriteHandler.handleUpstream(Chu

    nkedWriteHandler.java:144)

            at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultCh

    annelPipeline.java:545)

            at org.jboss.netty.channel.DefaultChannelPipeline$DefaultChannelHandlerC

    ontext.sendUpstream(DefaultChannelPipeline.java:754)

            at org.jboss.netty.channel.Channels.fireMessageReceived(Channels.java:30

    2)

            at org.jboss.netty.handler.codec.frame.FrameDecoder.unfoldAndFireMessage

    Received(FrameDecoder.java:317)

            at org.jboss.netty.handler.codec.frame.FrameDecoder.callDecode(FrameDeco

    der.java:299)

            at org.jboss.netty.handler.codec.frame.FrameDecoder.messageReceived(Fram

    eDecoder.java:216)

            at org.jboss.netty.channel.SimpleChannelUpstreamHandler.handleUpstream(S

    impleChannelUpstreamHandler.java:80)

            at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultCh

    annelPipeline.java:545)

            at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultCh

    annelPipeline.java:540)

            at org.jboss.netty.channel.Channels.fireMessageReceived(Channels.java:27

    4)

            at org.jboss.netty.channel.Channels.fireMessageReceived(Channels.java:26

    1)

            at org.jboss.netty.channel.socket.nio.NioWorker.read(NioWorker.java:349)

  • 4. Re: Issue while connecting to oracle db from teiid
    yogi goli Newbie

    Also the Dynamicportfolio is working perfectly fine. I changed the traslator to oracle

     

    directly C:\jboss-5.1.0.GA\server\default\lib contains following jar files. please let me know what is missing.

    classes12_g.jar

    ojdbc14_g.jar

    teiid-7.5.0.Final-client.jar

    teiid-hibernate-dialect-7.5.0.Final.jar

  • 5. Re: Issue while connecting to oracle db from teiid
    Ramesh Reddy Master

    When you deployed the "oracledb-vdb.xml", there should be some messages printed out to the console and/or to log file as to why the VDB is deployed in the "inactive" state. Or you can look at the admin-console and go to the VDB panel under data services and it should also list the same error towards the bottom of the page.

     

    Ramesh..

  • 6. Re: Issue while connecting to oracle db from teiid
    Ramesh Reddy Master

    Also for Oracle model try using these properties in the -vdb.xml

     



    <property name="importer.tableTypes" value="TABLE,VIEW"/>


    <property name="importer.schemaPattern" value="my-schema"/>


    <property name="importer.useFullSchemaName" value="true"/>

     

    otherwise the import process will be very slow and vdb will be inactive until the metadata is loaded.

     

    Ramesh..

  • 7. Re: Issue while connecting to oracle db from teiid
    yogi goli Newbie

    Ramesh - Thanks for ur gr8 help.

    oracledb-ds.xml

    -----------------------

    <?xml version="1.0" encoding="UTF-8"?>

    <datasources>

    <xa-datasource>

            <jndi-name>oracleds</jndi-name>

            <track-connection-by-tx>true</track-connection-by-tx>

            <isSameRM-override-value>false</isSameRM-override-value>

            <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>

            <xa-datasource-property name="URL">jdbc:oracle:thin:@170.0.12.18:1521:IS01</xa-datasource-property>

            <xa-datasource-property name="User">SC</xa-datasource-property>

            <xa-datasource-property name="Password">eu</xa-datasource-property>

            <exception-sorter-class-name>

                org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter

            </exception-sorter-class-name>

            <no-tx-separate-pools/>

     

          <metadata>

             <type-mapping>Oracle9i</type-mapping>

          </metadata>    

      </xa-datasource>

    </datasources>

     

    This datasouce causing issue i think. Also see attached teh log file.

  • 8. Re: Issue while connecting to oracle db from teiid
    yogi goli Newbie

    From Admin console VDB is showing available whereas datasource with red mark and unavailable. No errors.

  • 9. Re: Issue while connecting to oracle db from teiid
    Ramesh Reddy Master

    I see

     

    2011-09-14 23:36:29,422 INFO  [org.teiid.RUNTIME] (JBoss System Threads(1)-5) VDB oracledb.1 model HUMIS01 metadata is currently being loaded.

    2011-09-14 23:36:29,422 INFO  [org.teiid.RUNTIME] (JBoss System Threads(1)-5) oracledb.1 status has been changed to active

     

    in th log, that indicates that VDB is active, did you try connecting again?

  • 10. Re: Issue while connecting to oracle db from teiid
    yogi goli Newbie

    From Admin console VDB is showing available whereas datasource with red mark and unavailable. No errors.

     

    COMMAND PROMPT

    -------------------------------

    C:\jboss-5.1.0.GA\server\default\teiid-examples\simpleclient>run localhost 31000
    oracledb "select * from IS01.SC.NAME"
    org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryReso
    lverException: Group does not exist: HUMIS01.SC_BASE.NAME
            at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:113)
            at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:70)
            at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:61
    1)
            at org.teiid.jdbc.StatementImpl.access$000(StatementImpl.java:70)
            at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:530)
            at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:130)
            at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:37)

            at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.ja
    va:75)
            at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketS
    erverInstanceImpl.java:218)

  • 11. Re: Issue while connecting to oracle db from teiid
    Ramesh Reddy Master

    Well, this different error messagew than before. So, separate issue

     

    From the error above it is saying that it does not have table you are trying to execute. This may be due to

     

    1) Table does not exist in source (unlikely)

    2) Table metadata is not imported

    3)  some name resolution conflicts. (likely)

     

    So, can issue

     

    select * from sys.tables where schemaname='HUMIS01'
    

     

    to see what tables are imported from the source.

     

    Ramesh..

  • 12. Re: Issue while connecting to oracle db from teiid
    yogi goli Newbie

    i ran the command

    C:\jboss-5.1.0.GA\server\default\teiid-examples\simpleclient>run localhost 31000
    oracledb "select * from sys.tables where schemaname='HUMIS01'" >aa.txt

     

    Wow some output is there however i do not see the tables which i can see from SQL plus under this schema.

    see attached teh aa.txt output file.

     

  • 13. Re: Issue while connecting to oracle db from teiid
    Ramesh Reddy Master

    Well, there were no tables, that indicates that the metadata import you did for the 'HUMIS01' is bad. This may be due to the import properties you supplied. Did you supply the correct *schema*  name for the property?

     

    <property name="importer.schemaPattern" value="****my-schema*****"/>

  • 14. Re: Issue while connecting to oracle db from teiid
    yogi goli Newbie

    That's Great!!! You are great!

     

    That worked perfectly fine and i got the results from both of the models (HUMIS1, ACCOUNTS).

     

    However there is 1 model we have created using flat file and that is <model name="MarketData">.

     

    What SQL statement is required to populate the data from this model "MarketData"?

1 2 Previous Next