-
1. Re: Dynamic VDB is incomplete
shawkins Jan 21, 2013 10:13 AM (in response to sreekanthatl)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
-
2. Re: Dynamic VDB is incomplete
sreekanthatl Jan 22, 2013 4:26 AM (in response to shawkins)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
-
3. Re: Dynamic VDB is incomplete
shawkins Jan 22, 2013 9:46 AM (in response to sreekanthatl)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
-
4. Re: Dynamic VDB is incomplete
shawkins Jan 22, 2013 1:41 PM (in response to 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
-
5. Re: Dynamic VDB is incomplete
sreekanthatl Jan 23, 2013 8:45 AM (in response to shawkins)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
-
6. Re: Dynamic VDB is incomplete
shawkins Jan 23, 2013 8:58 AM (in response to sreekanthatl)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
-
7. Re: Dynamic VDB is incomplete
sreekanthatl Jan 23, 2013 9:18 AM (in response to shawkins)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
-
8. Re: Dynamic VDB is incomplete
shawkins Jan 23, 2013 9:48 AM (in response to sreekanthatl)1 of 1 people found this helpful> 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
-
9. Re: Dynamic VDB is incomplete
sreekanthatl Jan 23, 2013 10:13 AM (in response to shawkins)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
-
10. Re: Dynamic VDB is incomplete
shawkins Jan 23, 2013 10:18 AM (in response to sreekanthatl)No, you have to be on the AS 7.1.1+ series to use Teiid 8+
-
11. Re: Dynamic VDB is incomplete
sreekanthatl Jan 23, 2013 10:34 AM (in response to shawkins)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