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