-
1. Re: Oracle Empty Model
rareddy Apr 17, 2013 11:33 AM (in response to pinkstondevin)Is there a schema called "my-schema" in your source? When you define the "importer.*" properties, the translator filters the metadata to match to what is specified. If none matched that is the issue you are seeing.
Ramesh..
-
2. Re: Oracle Empty Model
pinkstondevin Apr 17, 2013 12:09 PM (in response to rareddy)Ramesh,
No there is not a "my-schema". I have a sample oracle database created for testing called "baseball", with just one table "master".
If I remove the importer.schemaPattern, Teiid gives a long list of invalid tables that "has no columns defined". Here is just a snippet of the list, it is very long:
12:02:27,385 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.WWV_RENDER_CHART2 has no columns defined
12:02:27,385 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.WWV_SEQ has no columns defined
12:02:27,386 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.WWV_SPELING has no columns defined
12:02:27,386 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XDB$STRING_LIST_T has no columns defined
12:02:27,387 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XDB_PVTRIG_PKG has no columns defined
12:02:27,387 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XDB_RVTRIG_PKG has no columns defined
12:02:27,388 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XDBURITYPE has no columns defined
12:02:27,388 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XDS_ACE has no columns defined
12:02:27,389 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XDS_ACL has no columns defined
12:02:27,389 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XIMETADATA_PKG has no columns defined
12:02:27,389 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLAGG has no columns defined
12:02:27,390 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLBINARYINPUTSTREAM has no columns defined
12:02:27,390 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLBINARYOUTPUTSTREAM has no columns defined
12:02:27,391 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLCHARACTERINPUTSTREAM has no columns defined
12:02:27,391 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLCHARACTEROUTPUTSTREAM has no columns defined
12:02:27,392 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLDOM has no columns defined
12:02:27,392 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLFORMAT has no columns defined
12:02:27,393 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLPARSER has no columns defined
12:02:27,393 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLSEQUENCE has no columns defined
12:02:27,394 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLSEQUENCETYPE has no columns defined
12:02:27,394 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLTYPE has no columns defined
12:02:27,395 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XQSEQUENCE has no columns defined
12:02:27,395 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XQWINDOWSEQUENCE has no columns defined
12:02:27,396 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XS$CACHE_ACTIONS has no columns defined
12:02:27,396 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XS$CACHE_DELETE has no columns defined
12:02:27,397 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XS_SESSION_ROLES has no columns defined
12:02:27,397 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XSLPROCESSOR has no columns defined
12:02:27,398 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.Z has no columns defined
12:02:27,398 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.SYS.HS_PARTITION_COL_NAME has no columns defined
12:02:27,399 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.SYS.HS_PARTITION_COL_TYPE has no columns defined
12:02:27,402 INFO [org.teiid.RUNTIME] (teiid-async-threads - 1) TEIID40073 The metadata for the VDB Portfolio.1 is loaded, however it is not valid. Check models for errors. Correct the metadata and re-deploy.
12:02:27,403 INFO [org.teiid.RUNTIME] (teiid-async-threads - 1) TEIID40003 VDB Portfolio.1 is set to FAILED
My VDB when i do this:
<?xml version="1.0" ?><vdb name="Portfolio" version="1">
<description>The Portfolio Dynamic VDB</description>
<!--
<model name="weather">
<source name="textWeather-connector" translator-name="file" connection-jndi-name="java:/weather-file"/>
</model>
<model visible = "true" type = "VIRTUAL" name = "weatherstats">
<metadata type = "DDL"><![CDATA[
CREATE VIEW edinburgh (
theIndex integer,
theYear integer,
theDay integer,
theTime integer,
ap integer,
rainfall double,
windspeed double,
temp double
) OPTIONS (UPDATABLE TRUE) AS
select edinburgh.* from (call weather.getTextFiles('*.txt')) f, TEXTTABLE(f.file COLUMNS theIndex integer, theYear integer, theDay integer, theTime integer, ap integer, rainfall double, windspeed double, temp double HEADER) edinburgh;
]]>
</metadata>
</model>
<model name="fieldingPostgres">
<source name="fielding-connector" translator-name="postgresql" connection-jndi-name="java:/postgres-ds"/>
</model>
-->
<property name="UseConnectorMetadata" value="true" />
<model name="oracleBaseball">
<property name="importer.useFullSchemaName" value="true"/>
<source name="oracle-connector" translator-name="oracle" connection-jndi-name="java:/Oracle-DS"/>
</model>
</vdb>
I have my two other sources commented out so i can focus on Oracle. When I used value of "false" for useFullSchemaName I received duplicated tables error, so i changed it to true for now.
Thanks Ramesh!
-
3. Re: Oracle Empty Model
shawkins Apr 17, 2013 12:15 PM (in response to pinkstondevin)If these tables are synonyms, you should set the oracle connection property includeSynonyms=true - http://docs.oracle.com/cd/E18283_01/appdev.112/e13995/oracle/jdbc/OracleConnection.html#CONNECTION_PROPERTY_INCLUDE_SYNONYMS
-
4. Re: Oracle Empty Model
pinkstondevin Apr 17, 2013 12:27 PM (in response to shawkins)Steven,
Thanks for your quick response. I do not have synonyms. I did read a couple posts on the community here about that just in case.
My Oracle DB is on a windows server, I can query it and see the contents of the table using the user given to Teiid. i can also create a connection to the data source through Teiid's web console and it connects successfully. However I am just having trouble with the Virtual Database.
Thanks!
-
5. Re: Oracle Empty Model
shawkins Apr 17, 2013 12:46 PM (in response to pinkstondevin)I think you are importing too many object types. Try narrowing down, for example:
<property name="importer.tableTypes" value="TABLE,VIEW"/>
-
6. Re: Oracle Empty Model
pinkstondevin Apr 17, 2013 12:58 PM (in response to shawkins)Steven,
Thanks for the tip. If i use the property mentioned like below, I receive a different error I received before when trying the tabletypes property.
What do you think?
<?xml version="1.0" ?><vdb name="Portfolio" version="1">
<description>The Portfolio Dynamic VDB</description>
<property name="UseConnectorMetadata" value="true" />
<model name="oracleBaseball">
<property name="importer.useFullSchemaName" value="true"/>
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<source name="oracle-connector" translator-name="oracle" connection-jndi-name="java:/Oracle-DS"/>
</model>
</vdb>
Error:
12:48:38,615 INFO [org.teiid.RUNTIME] (teiid-async-threads - 1) TEIID50030 VDB Portfolio.1 model "oracleBaseball" metadata loaded. End Time: 4/17/13 12:48 PM
12:48:38,626 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.SYS.HS_PARTITION_COL_NAME has no columns defined
12:48:38,627 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31071 Invalid table; Table oracleBaseball.SYS.HS_PARTITION_COL_TYPE has no columns defined
12:48:38,630 INFO [org.teiid.RUNTIME] (teiid-async-threads - 1) TEIID40073 The metadata for the VDB Portfolio.1 is loaded, however it is not valid. Check models for errors. Correct the metadata and re-deploy.
12:48:38,631 INFO [org.teiid.RUNTIME] (teiid-async-threads - 1) TEIID40003 VDB Portfolio.1 is set to FAILED
Thanks again!
-
7. Re: Oracle Empty Model
shawkins Apr 17, 2013 1:05 PM (in response to pinkstondevin)Notice how you have exluded things like oracleBaseball.PUBLIC.XMLAGG, which is an aggregate function definition and not a table. However you are still pulling things from the SYS schema, so more than likely you should limit yourself to a specific schema using <property name="importer.schemaPattern" value="my-schema"/>
If you do need to go across several schemas, then you would probably want to use the excludeTables regex to filter anything that is not needed and is causing a problem.
Steve
-
8. Re: Oracle Empty Model
pinkstondevin Apr 18, 2013 6:15 AM (in response to shawkins)Thanks Steven!