12 Replies Latest reply on Jun 28, 2016 4:00 PM by fandre

    Problem with "TEIID60013 Duplicate Table <MYTABLE>"

    fandre

      Hy guys,

       

      Im having some trouble when I'm trying to import a VDB, look my .xml import file:

      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
      <vdb name="GEDIG_RIO" version="1">
          <description></description>
          
          <!-- 
            Setting to use connector supplied metadata. Can be "true" or "cached".  
            "true" will obtain metadata once for every launch of Teiid. 
            "cached" will save a file containing the metadata into 
            the deploy/<vdb name>/<vdb version/META-INF directory
          -->
          <property name="UseConnectorMetadata" value="true" />
      
      
          <!-- 
            Each model represents a access to one or more sources.
            The name of the model will be used as a top level schema name
            for all of the metadata imported from the connector.
          
            NOTE: Multiple model, with different import settings, can be bound to 
            the same connector binding and will be treated as the same source at
            runtime. 
          --> 
          <model name="GEDIG_RIO">
          
           <!-- 
                  Each source represents a translator and data source. There are 
                  pre-defined translators, or you can create one. ConnectionFactories 
                  or DataSources in JBoss AS they are typically defined using "xxx-ds.xml" files. 
              -->
        <!-- Property to let DSDataserver know of what type is the VDB model so 
        that it we can be grouped accordingly. -->
        <property name="importer.importKeys" value="true"/>
        <property name="importer.useFullSchemaName" value="false"/>
        <property name="importer.tableTypes" value="TABLE"/>
              <property name="importer.widenUnsignedTypes" value="false"/>
              <property name="importer.schemaPattern" value="%"/>
        <property name="importer.excludeTables" value="(?!ADM.*|APR.*|EEP.*|GDIGPAC.*|GEDIG_RIO.*|MAG.*|SINPBC.*|TBB.*|ADEPD1.*).*"/>
              <property name="supports-multi-source-bindings" value="false"/>
        <property name="multisource.columnName" value="TEIID_MULTI_DATA_SOURCE_COLUMN"/>
        <property name="multisource.addColumn" value="true"/>
              <source translator-name="delegate" connection-jndi-name="java:/BDPEPBR" name="BDPEPBR" />
      
        <metadata type="NATIVE,DDL"><![CDATA[
      
      
      .............My DDL HERE ........
      
      
           ]]>
              </metadata> 
      
      
          </model>
          <translator name="delegate" type="interceptor">
              <property name="delegateName" value="oracle" />
          </translator>
      </vdb>
      
      
      
      
      
      
      
      
      
      

      I saw in my db has a duplicate tables for two different owners:

      OWNERTABLETABLE SPACE NAME
      RPREVSQLN_EXPLAIN_PLANRPREV_DADOS
      ADEPD1SQLN_EXPLAIN_PLANUSERS

       

      Im try to change my excludeTable including one of this owners, like this:

      <property name="importer.excludeTables" value="(?!ADM.*|APR.*|EEP.*|GDIGPAC.*|GEDIG_RIO.*|MAG.*|SINPBC.*|TBB.*|ADEPD1.*).*"/>

       

      But without success, can anyone may help me?

       

      Thx in advance

        • 1. Re: Problem with "TEIID60013 Duplicate Table <MYTABLE>"
          rareddy

          You can define the "schemaPattern" to be the specific user to avoid importing from other, or you can set the "useFullSchemaName" to true. I suspect excludeTables is not looking at owner information.

          • 2. Re: Problem with "TEIID60013 Duplicate Table <MYTABLE>"
            fandre

            Can I define more than one schemaPattern?

            • 3. Re: Problem with "TEIID60013 Duplicate Table <MYTABLE>"
              fandre

              I changed some of properties to:

               

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

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

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

              <property name="importer.widenUnsignedTypes" value="false"/>

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

              <property name="importer.excludeTables" value="(?!ADM.*|APR.*|EEP.*|GDIGPAC.*|GEDIG_RIO.*|MAG.*|SINPBC.*|TBB.*|ADEPD1.*).*"/>

               

              Now Im receiving the follow error:

              TEIID60011 No column found with name NESTED_TABLE_ID

              • 4. Re: Problem with "TEIID60013 Duplicate Table <MYTABLE>"
                rareddy

                "excludeTables" is matching against the full table name, I checked the code. You can verify also here teiid/JDBCMetdataProcessor.java at master · teiid/teiid · GitHub

                 

                I am suspecting the column error you mention may be due to exclude tables and foreign keys to excluded tables perhaps? was there any more information like table name  etc, that you can refer back to schema and see why this is an issue?

                • 5. Re: Problem with "TEIID60013 Duplicate Table <MYTABLE>"
                  fandre

                  Ramesh I saw the code when you say "full table name" is, only tablename or owner.tablename?

                  String fullName = getFullyQualifiedName(tableCatalog, tableSchema, tableName);

                  I am suspecting the column error you mention may be due to exclude tables and foreign keys to excluded tables perhaps? was there any more information like table name  etc, that you can refer back to schema and see why this is an issue?

                  I dont know witch table throw this message. So I cant identify

                   

                  I just want to import some tables from some owners, have other way to do this?

                  • 6. Re: Problem with "TEIID60013 Duplicate Table <MYTABLE>"
                    rareddy

                    Another option is have defining multiple models pointing to the same source, but in each one define one owner and set of tables include or exclude. That way you get exactly what you want. As long as you use same translator name, source name, and JNDI name at runtime they will be planned as if they are single source.

                    • 7. Re: Problem with "TEIID60013 Duplicate Table <MYTABLE>"
                      fandre

                      May you give me an example? I'm little bit noobie

                       

                      How would it be, for example, I want all tables from owners ADM, all tables APR, EEP, GDIGPAC, GEDIG_RIO, MAG, SINPBC and TBB. I will need to create one file for each these models? This will give me a multiple vdb's? I want only one vdb with all these tables.

                      • 8. Re: Problem with "TEIID60013 Duplicate Table <MYTABLE>"
                        fandre

                        Im will test with the follow vdb.xml, like you say... This was the solution that you refer correct?

                         

                        <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
                        <vdb name="GEDIG_RIO" version="1">
                            <description></description>
                            
                            <!-- 
                              Setting to use connector supplied metadata. Can be "true" or "cached".  
                              "true" will obtain metadata once for every launch of Teiid. 
                              "cached" will save a file containing the metadata into 
                              the deploy/<vdb name>/<vdb version/META-INF directory
                            -->
                            <property name="UseConnectorMetadata" value="true" />
                        
                        
                            <!-- 
                              Each model represents a access to one or more sources.
                              The name of the model will be used as a top level schema name
                              for all of the metadata imported from the connector.
                            
                              NOTE: Multiple model, with different import settings, can be bound to 
                              the same connector binding and will be treated as the same source at
                              runtime. 
                            --> 
                            <model name="ADM">
                              <property name="importer.importKeys" value="true"/>
                              <property name="importer.useFullSchemaName" value="true"/>
                              <property name="importer.tableTypes" value="TABLE"/>
                              <property name="importer.widenUnsignedTypes" value="false"/>
                              <property name="importer.schemaPattern" value="ADM"/>
                              <property name="supports-multi-source-bindings" value="false"/>
                              <property name="multisource.columnName" value="TEIID_MULTI_DATA_SOURCE_COLUMN"/>
                              <property name="multisource.addColumn" value="true"/>
                              <source translator-name="delegate" connection-jndi-name="java:/BDPEPBR" name="BDPEPBR" />
                            </model>
                        
                        
                            <model name="APR">
                              <property name="importer.importKeys" value="true"/>
                              <property name="importer.useFullSchemaName" value="true"/>
                              <property name="importer.tableTypes" value="TABLE"/>
                              <property name="importer.widenUnsignedTypes" value="false"/>
                              <property name="importer.schemaPattern" value="APR"/>
                              <property name="supports-multi-source-bindings" value="false"/>
                              <property name="multisource.columnName" value="TEIID_MULTI_DATA_SOURCE_COLUMN"/>
                              <property name="multisource.addColumn" value="true"/>
                              <source translator-name="delegate" connection-jndi-name="java:/BDPEPBR" name="BDPEPBR" />
                            </model>
                        
                        
                            <model name="EEP">
                              <property name="importer.importKeys" value="true"/>
                              <property name="importer.useFullSchemaName" value="true"/>
                              <property name="importer.tableTypes" value="TABLE"/>
                              <property name="importer.widenUnsignedTypes" value="false"/>
                              <property name="importer.schemaPattern" value="EEP"/>
                              <property name="supports-multi-source-bindings" value="false"/>
                              <property name="multisource.columnName" value="TEIID_MULTI_DATA_SOURCE_COLUMN"/>
                              <property name="multisource.addColumn" value="true"/>
                              <source translator-name="delegate" connection-jndi-name="java:/BDPEPBR" name="BDPEPBR" />
                            </model>
                        
                        
                            <model name="GDIGPAC">
                              <property name="importer.importKeys" value="true"/>
                              <property name="importer.useFullSchemaName" value="true"/>
                              <property name="importer.tableTypes" value="TABLE"/>
                              <property name="importer.widenUnsignedTypes" value="false"/>
                              <property name="importer.schemaPattern" value="GDIGPAC"/>
                              <property name="supports-multi-source-bindings" value="false"/>
                              <property name="multisource.columnName" value="TEIID_MULTI_DATA_SOURCE_COLUMN"/>
                              <property name="multisource.addColumn" value="true"/>
                              <source translator-name="delegate" connection-jndi-name="java:/BDPEPBR" name="BDPEPBR" />
                            </model>
                        
                        
                            <model name="MAG">
                              <property name="importer.importKeys" value="true"/>
                              <property name="importer.useFullSchemaName" value="true"/>
                              <property name="importer.tableTypes" value="TABLE"/>
                              <property name="importer.widenUnsignedTypes" value="false"/>
                              <property name="importer.schemaPattern" value="MAG"/>
                              <property name="supports-multi-source-bindings" value="false"/>
                              <property name="multisource.columnName" value="TEIID_MULTI_DATA_SOURCE_COLUMN"/>
                              <property name="multisource.addColumn" value="true"/>
                              <source translator-name="delegate" connection-jndi-name="java:/BDPEPBR" name="BDPEPBR" />
                            </model> 
                        
                        
                            <model name="SINPBC">
                              <property name="importer.importKeys" value="true"/>
                              <property name="importer.useFullSchemaName" value="true"/>
                              <property name="importer.tableTypes" value="TABLE"/>
                              <property name="importer.widenUnsignedTypes" value="false"/>
                              <property name="importer.schemaPattern" value="SINPBC"/>
                              <property name="supports-multi-source-bindings" value="false"/>
                              <property name="multisource.columnName" value="TEIID_MULTI_DATA_SOURCE_COLUMN"/>
                              <property name="multisource.addColumn" value="true"/>
                              <source translator-name="delegate" connection-jndi-name="java:/BDPEPBR" name="BDPEPBR" />
                            </model> 
                        
                        
                            <model name="TBB">
                              <property name="importer.importKeys" value="true"/>
                              <property name="importer.useFullSchemaName" value="true"/>
                              <property name="importer.tableTypes" value="TABLE"/>
                              <property name="importer.widenUnsignedTypes" value="false"/>
                              <property name="importer.schemaPattern" value="TBB"/>
                              <property name="supports-multi-source-bindings" value="false"/>
                              <property name="multisource.columnName" value="TEIID_MULTI_DATA_SOURCE_COLUMN"/>
                              <property name="multisource.addColumn" value="true"/>
                              <source translator-name="delegate" connection-jndi-name="java:/BDPEPBR" name="BDPEPBR" />
                            </model> 
                        
                        
                            <model name="GEDIG_RIO">
                            
                             <!-- 
                                    Each source represents a translator and data source. There are 
                                    pre-defined translators, or you can create one. ConnectionFactories 
                                    or DataSources in JBoss AS they are typically defined using "xxx-ds.xml" files. 
                                -->
                          <!-- Property to let DSDataserver know of what type is the VDB model so 
                          that it we can be grouped accordingly. -->
                              <property name="importer.importKeys" value="true"/>
                              <property name="importer.useFullSchemaName" value="true"/>
                              <property name="importer.tableTypes" value="TABLE"/>
                              <property name="importer.widenUnsignedTypes" value="false"/>
                              <property name="importer.schemaPattern" value="GEDIG_RIO"/>
                              <property name="supports-multi-source-bindings" value="false"/>
                              <property name="multisource.columnName" value="TEIID_MULTI_DATA_SOURCE_COLUMN"/>
                              <property name="multisource.addColumn" value="true"/>
                              <source translator-name="delegate" connection-jndi-name="java:/BDPEPBR" name="BDPEPBR" />
                        
                            </model>
                            <translator name="delegate" type="interceptor">
                                <property name="delegateName" value="oracle" />
                            </translator>
                        </vdb>
                        
                        
                        
                        
                        
                        
                        
                        
                        
                        
                        
                        
                        
                        
                        
                        
                        
                        • 9. Re: Problem with "TEIID60013 Duplicate Table <MYTABLE>"
                          rareddy

                          Yes, you are in right path. But you really do NOT need

                             

                          1. <property name="supports-multi-source-bindings" value="false"/> 
                          2. <property name="multisource.columnName" value="TEIID_MULTI_DATA_SOURCE_COLUMN"/> 
                          3. <property name="multisource.addColumn" value="true"/>

                          an for translator name, you can use "oracle" and get rid of


                          1.     <translator name="delegate" type="interceptor"> 
                          2.         <property name="delegateName" value="oracle" /> 
                          3.     </translator>

                           

                          Also I think, now you can set

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


                          that will give simple table names rather than long schema scoped ones.

                          • 10. Re: Problem with "TEIID60013 Duplicate Table <MYTABLE>"
                            fandre

                            Ramesh, thx for the tips,

                             

                            Can I ask as last question? What the difference to create multiple models instead of one? What the benefits? Internally this will change the way to make queries in my teiid? What the big differences between both ways?

                            • 11. Re: Problem with "TEIID60013 Duplicate Table <MYTABLE>"
                              rareddy

                              In a Teiid VDB, multiple models are there to define different *disparate* schemas, like one model can be RDBMS, another a restful web service and another could be file etc. Now, once these are built, you can access all these sources through Teiid as if they are ONE single source. You can even define VIRTUAL model, where you can define an abstract logical schema (think like views) based on your usecase, but built using other source models to support it.

                               

                              > What the difference to create multiple models instead of one? What the benefits?

                              In your case we separated each owner into separate model, to

                              - avoid collisions in name space

                              - simplify the model definition, such that you can import the metadata correctly per owner

                              - ability to correctly represent your source. This is important. As some users may have access to all the schemas, some may not. With this separation, you can even further elevate the authorization if needs to be, individually per model.

                               

                              >Internally this will change the way to make queries in my teiid?

                              In this particular case no, as I asked to keep the source, translator and JNDI names same, to take advantage of a feature where even though they are defined as different models, for execution purposes Teiid engine will plan the query as if they are in single source.


                              >What the big differences between both ways?

                              But if each model is truly represents a different sources, then executed query will be much different for each source.


                              Teiid is data virtualization engine, it does that by letting you define your own schema for for your usecase, then allows you map that schema to your existing sources. User only sees your defined schema, and you can hide/abstract all the source details from them. I encourage you to try out a quick start Hello World Teiid Data Federation Example - Teiid Examples - Project Documentation Editor


                              • 12. Re: Problem with "TEIID60013 Duplicate Table <MYTABLE>"
                                fandre

                                Thx so much!