UNIONish

[jmfaerman]

I would like to "concatenate" data from two sources, a customer table in mysql such as CUSTOMER(id, firstname, lastname) and the same information from a slightly different database, for example in postgres TBL_CUSTOMER(id, full_name).

How should i configure my vdb for this?

I am just starting with Teiid, so please excuse some misconception.

Regards,
Julio

[rareddy]

Julio,

Yes, that can be using Teiid. Use Teiid Designer tool and import the metadata from both the sources and create two source models. The create a view model and create a base table in the view model with the above sql as the transformation for the table.

select id, firstname, lastname from customer

union all

select id, full_name.substring(...) as firstname, full_name.substring(...) as lastname from TBL_CUSTOMER

Ramesh..

[jmfaerman]

Thanks Ramesh, that works perfectly. One issue though...

I would like teiid to cache the results, so that if a data-source dies, clients can still live.

I have marked the view as materialized, as suggested in the caching guide, but if one of the data sources in the view goes down (a mysql table in my case) the client gets this error:

Remote org.teiid.core.TeiidProcessingException: Error Code:0 Message:Error Code:0 Message:'No operations allowed after connection closed.Connection was implicitly closed by the driver.'

I have added autoReconnect=true to the mysql jdbc url but it was no good.

Is there something i should check in configuration or materialized aren't suitable for this?

[shawkins]

Julio,

Can you get the full stacktrace from the server/profile/log/server.log file?  That would help us understand when the error is happening.  Generally you do not hit the source at all once a view is materialized.  However lob values are currently not fully materialized, so you may hit an issue if you have clob/blob/sqlxml values that are still tied to a connection.

Steve

[jmfaerman]

I could not reproduce the error after removing and redeploying the VDB, i have done something wrong.

I will now read more to understand how to control the cache one the view is materialized.

Thanks a lot, your assistance was very helpful.