5 Replies Latest reply on Sep 15, 2010 12:04 PM by Ramesh Reddy

    Access Metadata of the tables

    ramya r Newbie

      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.

        • 1. Re: Access Metadata of the tables
          Ramesh Reddy Master

          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..

          1 of 1 people found this helpful
          • 2. Re: Access Metadata of the tables
            ramya r Newbie

            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.

            • 3. Re: Access Metadata of the tables
              Ramesh Reddy Master

              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..

              • 4. Re: Access Metadata of the tables
                ramya r Newbie

                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

                • 5. Re: Access Metadata of the tables
                  Ramesh Reddy Master

                  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..