10 Replies Latest reply on Oct 11, 2012 7:33 AM by Dave Stannard

    multi-source dynamic vdb using first source multiple times

    Dave Stannard Newbie

      I am attempting to create a simple, multi-source, dynamic vdb over a couple of IBM System-i datasources. 

       

      I am finding that Teiid is only sourcing data from the first schema specified, multiplied by the number of sources declared.

       

      Here's my -vdb.xml

       

      {code:xml}

      <vdb name="multisourceiseries" version="1">

       

          <property name="UseConnectorMetadata" value="true"/>

       

          <model name="multisourceiseriesmodel" visible="true" type="PHYSICAL">

       

              <property name="supports-multi-source-bindings" value="true"/>

       

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

       

              <source name="multisourceiseries1" translator-name="db2" connection-jndi-name="iseriesConnection1"/>

              <source name="multisourceiseries2" translator-name="db2" connection-jndi-name="iseriesConnection2"/>

       

          </model>

      </vdb>

      {code}

       

      So, if multisourceiseries1 is first (as above) then when I perform a simple select on Teiid I get only the data from iseriesConnection1, twice.

       

      If I swap the sources around, so that multisourceiseries2 is first, then I get the data from iseriesConnection2, twice.

       

      Note I have included the useCatalogName property, above, because I had the exact same problem with MySQL connections and that resolved the issue.  Admittedly, here it makes no difference.

       

      If it helps, the I'm using:

      • JBoss AS 7.1.1
      • Teiid 8.1 Final (and 8.2 alpha 2)
      • jtOpen 6.5.1 (and 7.8)
      • Squirrel 3.0.2
        • 1. Re: multi-source dynamic vdb using first source multiple times
          Dave Stannard Newbie

          More information.

           

          I have tried debugging Teiid while commenting out and re-ordering the two sources - I see the following statements in the log:

           

          Source 1 enabled

          12:12:32,535 DEBUG [org.teiid.CONNECTOR] (Worker7_QueryProcessorQueue51) Source-specific command: SELECT g_0."COL1" FROM "SCHEMA1"."TEST" AS g_0

           

          Source 2 enabled

          12:15:44,897 DEBUG [org.teiid.CONNECTOR] (Worker8_QueryProcessorQueue86) Source-specific command: SELECT g_0."COL1" FROM "SCHEMA2"."TEST" AS g_0

           

          both sources enabled - source 1 first

          12:17:38,476 DEBUG [org.teiid.CONNECTOR] (Worker10_QueryProcessorQueue92) Source-specific command: SELECT g_0."COL1" FROM "SCHEMA1"."TEST" AS g_0

          12:17:38,476 DEBUG [org.teiid.CONNECTOR] (Worker7_QueryProcessorQueue91)  Source-specific command: SELECT g_0."COL1" FROM "SCHEMA1"."TEST" AS g_0

           

          both sources enabled - source 2 first

          12:22:01,777 DEBUG [org.teiid.CONNECTOR] (Worker14_QueryProcessorQueue101) Source-specific command: SELECT g_0."COL1" FROM "SCHEMA2"."TEST" AS g_0

          12:22:01,777 DEBUG [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue100) Source-specific command: SELECT g_0."COL1" FROM "SCHEMA2"."TEST" AS g_0

          • 2. Re: multi-source dynamic vdb using first source multiple times
            Ramesh Reddy Master

            Dave,

             

            That looks like regression, can you log a JIRA? We will fix in 8.2 cycle.

             

            Thanks


            Ramesh..

            • 4. Re: multi-source dynamic vdb using first source multiple times
              Steven Hawkins Master

              This is not a regression, but a known limitation of the multi-source design.  With the current logic the metadata import is performed from only the first available source (or in Designer there is only a single model that represents all of the source).  So depending on the order that the sources are listed, you will only get iseriesConnection1 or iseriesConnection2 metadata and thus the duplicated results.

               

              The only possible workaround for multi-source is if you can refer to the tables in a non-schema qualified manner with the importer property useFullSchemaName=false.  In non-multisource mode you can manually remodel as a union all (see also partitioned unions) of the two sources, which would appropriately have their respective source metadata.

               

              Steve 

              • 5. Re: multi-source dynamic vdb using first source multiple times
                Ramesh Reddy Master

                For some reason I kept thinking about the query results rather than the metadata load thus my confusion and comment. Please ignore my previous comment.

                 

                Thanks

                 

                Ramesh..

                • 6. Re: multi-source dynamic vdb using first source multiple times
                  Dave Stannard Newbie

                  Hi, thanks for looking at this.

                   

                  My connections are set up so that they do not need catalog or schema prefixes to find their tables. 

                   

                  I have added useFullSchemaName=false and useCatalogName=false but unfortunately Teiid is still adding the schema to the query.

                   

                  In method org.teiid.translator.jdbc.JDBCMetdataProcessor.getTables (near line 244) it looks like those two switches will have caused the new table definition to be created with...

                  • name=tableName
                  • nameInSource=schemaName.tableName

                   

                  Since nameInSource is used to build the query later on, it will always have the unwanted schemaName.

                   

                  Shouldn't useFullSchemaName=false cause nameInSource to drop the schema?

                   

                  Regards

                   

                  Dave

                  • 7. Re: multi-source dynamic vdb using first source multiple times
                    Steven Hawkins Master

                    I was confusing myself on that property.  Yes, useCatalogName refers to both the Teiid table name and the name in source whereas useFullSchemaName refers only to the Teiid name and does not affect the name in source.  It was written that way since users typically did not want Teiid to use a needlessly long table name, but still wanted or had no ill affect from leaving the name in source qualified.

                     

                    To not affect backwards compatibility, and to simplify this scenario, I'd propose adding a property to the affect of useQualifiedName (which supercedes useCatalogName and useFullSchemaName) that affects both the Teiid names and name in source.

                    • 8. Re: multi-source dynamic vdb using first source multiple times
                      Dave Stannard Newbie

                      Something like this?

                       

                      org.teiid.translator.jdbc.JDBCMetdataProcessor.getFullyQualifiedName()

                       

                      {noformat}

                      {code:java}

                      private String getFullyQualifiedName(String catalogName, String schemaName, String objectName, boolean quoted) {

                          String fullName = (quoted?quoteName(objectName):objectName);

                          if (useQualifiedName)

                          {

                              if (schemaName != null && schemaName.length() > 0) {

                                  fullName = (quoted?quoteName(schemaName):schemaName) + AbstractMetadataRecord.NAME_DELIM_CHAR + fullName;

                              }

                              if (useCatalogName && catalogName != null && catalogName.length() > 0) {

                                  fullName = (quoted?quoteName(catalogName):catalogName) + AbstractMetadataRecord.NAME_DELIM_CHAR + fullName;

                              }

                          }

                          return fullName;

                      }

                      {code}

                      • 9. Re: multi-source dynamic vdb using first source multiple times
                        Steven Hawkins Master

                        Yes, that is what I had in mind.  Logged and worked as https://issues.jboss.org/browse/TEIID-2252

                         

                        As for the original multi-source issue, this should mitigate it for your case.  I'll create a general multi-source issue and add it as a subtask to be worked later.

                         

                        Thanks,

                        Steve