1 2 Previous Next 15 Replies Latest reply on Sep 15, 2011 7:14 AM by rareddy

    Issue while connecting to oracle db from teiid

    y1452001

      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
          rareddy

          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
            y1452001

            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
              y1452001

              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
                y1452001

                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
                  rareddy

                  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
                    rareddy

                    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
                      y1452001

                      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
                        y1452001

                        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
                          rareddy

                          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
                            y1452001

                            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
                              rareddy

                              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
                                y1452001

                                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
                                  rareddy

                                  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
                                    y1452001

                                    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