multi-source dynamic vdb using first source multiple times

[dstannard]

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

[dstannard]

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

[rareddy]

Dave,

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

Thanks

Ramesh..

[dstannard]

Ramesh,

Many thanks, TEIID-2251 raised.

Regards

Dave

[shawkins]

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 

[rareddy]

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

[dstannard]

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

[shawkins]

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.

[dstannard]

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}

[shawkins]

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

[dstannard]

Many thanks.