Access Metadata of the tables

[beginnerram]

Hi

I would like to know how I can fetch Metadata of the models in VDB using teiid 7.0. and also how can we update the metadata so that it is in sync with the database.For fetching metadata in teiid 6.2 i used "select * from system.elements" but that doesnt work out for teiid 7.

I want to know in case I import the complete metadata of the oracle database while creating model, Is it possible to know or update the metadata of the model if any a new table or view or procedure is created in my oracle DB.

[rareddy]

Ramya,

I would like to know how I can fetch Metadata of the models in VDB using teiid 7.0. For fetching metadata in teiid 6.2 i used "select * from system.elements" but that doesnt work out for teiid 7.

In the 7.0, the system tables have changed. So, you need to make the call as "select * from sys.columns". Prior to 7.0 we used call tables as "groups" and columns as "elements", with 7.0 release we fixed lot of semantics to match with a RDBMS. So, now you will have system tables as "schema", "tables", "columns", "procedures" etc. For complete list of system tables check  Reference Guide and/or Database Metadata object on the JDBC connection.

how can we update the metadata so that it is in sync with the database.

I want to know in case I import the complete metadata of the oracle database while creating model, Is it possible to know or update the metadata of the model if any a new table or view or procedure is created in my oracle DB.

Not in the "Dynamic VDB". In the Dynamic VDB, you can configure not to cache your metadata, then each time your server re-starts the metadata is fetched again. In future releases we will provide a Admin API method to explicitly invoke event to refresh metadata of a source model, so that you can control when you would like to refresh the metadata of a model.

If you are using Teiid Designer, it provides a way to update a model's metadata with its source. During the import there is option on the wizard to either update or replace the whole model. If you choose update it will only fetch the new metadata changes. Note that once the VDB is packaged with the changes it is read only.

Hope this helps.

Ramesh..

[beginnerram]

Thanks Ramesh. I tried it by deploying VDB for my oracle database using teiid designer  , It works perfectly fine for sys.tables, sys.keys but when i use sys.columns i dont get any data specific to my schema I only get values specific for 'SYS ' schema. Attaching the response for your refernce. Could you please lt me know why I am not getting the metadata for the tables defined in the schema.

[rareddy]

Ramya,

I tried on a VDB that I have, and I see no issues with calling "select * from system.columns" and seeing all my schema table's columns. Make sure you have columns defined on your Oracle database tables. Did you see columns in Teiid Designer when you imported the schema to create a model?

You can attach the vdb, then I can take a look and see what is wrong with it.

Ramesh..

[beginnerram]

Ramesh,

I did the same with designer 7.1 and it worked but it didnt work with 7.0. I am attaching the VDB i used for 7.0

[rareddy]

Ramya,

Yes. I checked it out with 7.0, it is a bug. Teiid was not correctly loading the index files for column information correctly thus the issue. This has been since resolved in 7.1 release. Please use 7.1 release or use 7.2-alpha release to fix the issue. The VDB from 7.0 Designer should work as is.

Thanks.

Ramesh..