Dynamic VDB is incomplete

[sreekanthatl]

Hi all,

I am new to Teiid. I am trying to connect oracle DB .

I have created VDB and deployed it is working fine with the following VDB configuration.

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

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

<property name="importer.schemaPattern" value="WEB"/>

If I changed the configuration for synonyms VDB is not loading.

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

<property name="importer.tableTypes" value="SYNONYM"/>

<property name="importer.schemaPattern" value="WEB"/>

showing the message "ibisDevVdb.1 is now "incomplete".

I am using the following jar version.

Teiid - 7.7.1

ojdbc14.jar

jboss soa- 5.3

Here I am attaching my configuratin files.

please give me a solution.

[shawkins]

It looks like Oracle wants to have the includeSynonyms connection property set to true to return columns for SYNONYMS.  Try chainging your -ds.xml connection url to something like: "dbc:oracle:thin:@<host>:1522:dev;includeSynonyms=true"

Steve

[sreekanthatl]

Hi steven,

Thanks for the quick reply.

I tried with includeSynonyms=true connection property, but I am getting the following error.

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

The Connection descriptor used by the client was:

If I am removing the property (includeSynonyms=true) data bases connection is OK. But

metadata import is not possible

Thanks

sreekanth P

[shawkins]

It looks like url properties are not supported for the Oracle driver.  See https://community.jboss.org/wiki/ConfigDataSources - use <connection-property name="includeSynonyms">true</connection-property>

I can see synonym columns with that setting in SQurrieL, but there are lots of synonyms that have no columns which the Teiid importer will not handle correctly.

Steve

[shawkins]

So this looks like it will work fine as long as you just import table synonyms.  That may require narrowing the tableNamePattern or usage of the exclusion regular expression.  Other than that an enhancement could be logged to cover filtering synonyms (and other table entries) that do not report columns.

Steve

[sreekanthatl]

Hi steve

Thanks for the help. I have added

<property name="importer.tableNamePattern" value="SCHOOL"/>

in VDB file. Now I am able to import synonym. It is only import the synonym "SCHOOL".

I want to import all synonym starting the string "SCHOOL" (^SCHOOL.*$).

I tried with the following, but it is not working. I am using Teiid 7.7

<property name="importer.tableNamePattern" value="SCHOOL%"/>

and

<property name="importer.tableNamePattern" value="(SCHOOL).*"/>

<property name="importer.tableNamePattern" value="^(SCHOOL|SCHOOL_CENSUS)$"/>

<property name="importer.tableNamePattern" value="(?SCHOOL(_CENSUS)).*"/>

I tried with the following query

"select * from SCHOOL s"

Group does not exist: SCHOOL As s

can u help me.

Thanks

Sreekanth P

[shawkins]

tableNamePattern is a LIKE pattern (as directed by the JDBC DatabaseMetadata getTables method).  You can use "SCHOOL%" to import all tables that begin with the SCHOOL prefix.  The use of REGEX and SIMILAR patterns should not work.

Only one value is allowed for each importer property, you if you are trying to simultanuously set multiple tableNamePattern properties, that won't work. 

The approach is to use the most generally applicable pattern as the importer.tableNamePattern.  Starting with Teiid 8.0 (https://issues.jboss.org/browse/TEIID-1959) you can then use the excludeTables regex to further exclude or specifically include only a subset of the tables applicable to the tableNamePattern.  See also the Reference for the general definitions of these properties: https://docs.jboss.org/author/display/TEIID/JDBC+Translator

Steve

[sreekanthatl]

Hi steve,

Thanks for the help

I am using Teiid 7.7

My configuration is as follows

<property name="importer.useFullSchemaName" value="true"/>
<property name="importer.tableTypes" value="SYNONYM"/>
<property name="importer.tableNamePattern" value="SCHOOL%"/>

But I am getting the following error

[RUNTIME] ibisDevVdb.1 is now "incomplete", because model "ibisSchool" can not retrieve metadata. Please fix any errors and re-deploy relevant DataSources and/or the VDB.

Without "%" I am able to fetch only "SCHOOL".

Thanks

Sreekanth P

[shawkins]

> Without "%" I am able to fetch only "SCHOOL".

That would be expected as the pattern is specific to a single name.

> But I am getting the following error

Do you see any other errors in the logs?  The error would either be an unrelated source issue (such as the source being down) or there is a downstream error due to importing a non-table SYNONYM with no columns.  The latter issue, as I indicated before, will take an enhancement to address automatically.  Can you check in SQurrieL or another tool if the DatabaseMetadata is showing SCHOOL prefix synonyms with no columns (with includeSynonyms set to true)?

> I am using Teiid 7.7

Yes, however I would encourage you to upgrade if possible as you can take advantage of the exclusion (or use it as an inclusion) regex.  You can also just manually mix-in any of the needed source metadata as DDL rather than solely relying on the automatic import.

Steve

[sreekanthatl]

Hi steve,

Thaks for the help,

I am using Jboss soa 5.3. In jbos soa 5.3 default support is Teiid 7.7.

Is it possible to upgrade to Teiid 8.0 to using the new features?.

Thanks

Sreekanth P

Message was edited by: Sreekanth P

[shawkins]

No, you have to be on the AS 7.1.1+ series to use Teiid 8+

[sreekanthatl]

Hi steve,

Thank you very much for the help.

We find out some invalid synonyms are there in the data base. Your guidence was very much helpful.

Thanks

Sreekanth P