Dynamic Datasource

[thezionview]

Hi,

I have a requirement to add datasource that needs to keep updating if any new table is creating or table modified in source database.

Basically they want this VDB to be a gateway for multiple datasource each of which can have new tables added or modified any time and it needs to show up when queried through this VDB.

I know i can created datasource in Teiid Designer and add existing tables and make a VDB out of it, but this doesnt serve my purpose where new table wont be updated automatically.

Can someone please point out how to do this or documentation showing how to get this accomplished?

[rareddy]

You could, however not using Designer at all. There are a couple of options

1) You could run the Teiid in WildFly server, and using the AdminAPI you can create data sources and deploy a Dynamic VDB that refers to those sources programmatically. Then each time the source changes, you can update that source model which will update the vdb with the new table or remove the table. See this example and this which has various tests to create data sources, vdb deployments etc.

2) You can use Teiid Embedded or Teiid Spring Boot to write an application, that is done in (1) but in-process where client and server in one JVM. Even here you would use Admin API, but since the process is not remote you would need to define how to create a data source.

The bigger question is, how you will get notified about the schema changes in the source databases? If you are inclined, you can pull in the technologies like Debezium and read their events on to when the schema is changed but goes beyond what we can help with this forum here.

Ramesh..

[thezionview]

So there isnt a straight forward way where i mention just the database and schema name in datasource for VDB to pick all the tables under it everytime?

In my case the schemas are fixed , i only need to account for new tables or modification of existing tables.

I thought Dynamic Datasource is the achieve this purpose. Did i understand it wrong?

[rareddy]

I see that you comments are contradicting for me, you say your schema is fixed, at the same time you say I need to account for new tables or modifications.

if # of data sources you are working with is fixed, then you define a Dynamic VDB where the source schema is imported only on deployment, then each time the vdb is deployed/redeployed it will fetch the new state of metadata from those sources and make up the VDB. This may be what you are looking for

[thezionview]

Sorry for my confusion. That is what i am looking for exactly. Can you point to me to resource or documentation which explains this Dynamic VDB creation.

[rareddy]

Define your VDB in DDL, then use below command to import metadata from source.

DDL VDB · GitBook

[thezionview]

Thanks Ramesh. I will check it out

[thezionview]

Hi rareddy

I created a sample VDB defining a external datasource from DB2 to show up in a VDB, there are no views or anything in here just a vanilla vdb which has a external datasource.i providing the content of the file named Enterprise-vdb.ddl. The java:/OLTP_DS  datasource is already present in the JDV and being used in other non Dynamic VDB.

When i run the below file through the Web portal Deployment tool i am getting below error. I am not able to identify what i am doing wrong here. Can you let me know where am i going wrong in this one

<code>

CREATE DATABASE Enterprise_Sandbox VERSION '1.0.0';

CREATE FOREIGN DATA WRAPPER db2;

CREATE SERVER pgdb2 TYPE 'db2jcc4.jar'

    VERSION 'one' FOREIGN DATA WRAPPER db2

    OPTIONS (

        "jndi-name" 'java:/OLTP_DS'

    );

CREATE SCHEMA PRODAPP1 SERVER pgdb2;

IMPORT FOREIGN SCHEMA DEVAPP1 FROM SERVER pgdb2 INTO PRODAPP1

    OPTIONS(

        importer.useFullSchemaName false,

        importer.tableTypes 'TABLE,VIEW'

);

</code>

ERROR:

Deployment failed: Enterprise-vdb.ddl: {"outcome" : "failed", "failure-description" : {"JBAS014671: Failed services" : {"jboss.deployment.unit.\"Enterprise-vdb.ddl\".STRUCTURE" : "org.jboss.msc.service.StartException in service jboss.deployment.unit.\"Enterprise-vdb.ddl\".STRUCTURE: JBAS018733: Failed to process phase STRUCTURE of deployment \"Enterprise-vdb.ddl\"\n Caused by: org.jboss.as.server.deployment.DeploymentUnitProcessingException: JBAS018740: Failed to mount deployment content\n Caused by: java.util.zip.ZipException: error in opening zip file"}}, "rolled-back" : true}

[rareddy]

Are you using the latest Teiid release? I forget when we started supporting the DDL based VDBs but older versions may not have full support. Use latest 12.2.

[thezionview]

rareddy I can see the JBOSS version we are using is EAP-6.4.0 but i am not sure how to check the Teiid release we are using , how to get that information.

[rareddy]

We do not support Teiid Community with EAP 6.4.0, only WildFly server. You must be using the Red Hat supported version of DV, which does not support the DDL based VDBs. There is XML based VDB that is supported in that version which basically works same. Using XML & DDL · GitBook  However this version is quite old I believe 8.12, if possible I suggest upgrading to the latest.