11 Replies Latest reply on Jan 23, 2013 10:34 AM by Sreekanth P

    Dynamic VDB is incomplete

    Sreekanth P Newbie

      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.

        • 1. Re: Dynamic VDB is incomplete
          Steven Hawkins Master

          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
            Sreekanth P Newbie

            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
              Steven Hawkins Master

              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
                Steven Hawkins Master

                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
                  Sreekanth P Newbie

                  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
                    Steven Hawkins Master

                    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
                      Sreekanth P Newbie

                      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
                        Steven Hawkins Master

                        > 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

                        1 of 1 people found this helpful
                        • 9. Re: Dynamic VDB is incomplete
                          Sreekanth P Newbie

                          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
                            Steven Hawkins Master

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

                            • 11. Re: Dynamic VDB is incomplete
                              Sreekanth P Newbie

                              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