-
1. Re: Issue while connecting to oracle db from teiid
rareddy Sep 14, 2011 7:49 AM (in response to y1452001)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 Sep 14, 2011 10:54 AM (in response to rareddy)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 Sep 14, 2011 10:59 AM (in response to 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 Sep 14, 2011 11:07 AM (in response to 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 Sep 14, 2011 11:16 AM (in response to y1452001)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 Sep 14, 2011 12:11 PM (in response to y1452001)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 Sep 14, 2011 2:21 PM (in response to rareddy)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.
-
server.log.zip 15.2 KB
-
-
8. Re: Issue while connecting to oracle db from teiid
y1452001 Sep 14, 2011 2:29 PM (in response to 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 Sep 14, 2011 2:36 PM (in response to y1452001)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 Sep 14, 2011 2:56 PM (in response to rareddy)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 Sep 14, 2011 3:09 PM (in response to y1452001)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 Sep 14, 2011 3:19 PM (in response to rareddy)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.txtWow 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.
-
aa.txt.zip 1.2 KB
-
-
13. Re: Issue while connecting to oracle db from teiid
rareddy Sep 14, 2011 3:53 PM (in response to y1452001)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 Sep 15, 2011 7:10 AM (in response to rareddy)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"?