10 Replies Latest reply on Aug 20, 2019 11:57 AM by Ramesh Reddy

    Dynamic Datasource

    SENTHILKUMAR P. (SENTHIL) KANDAMPALAYAM Newbie

      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?

        • 1. Re: Dynamic Datasource
          Ramesh Reddy Master

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

          • 2. Re: Dynamic Datasource
            SENTHILKUMAR P. (SENTHIL) KANDAMPALAYAM Newbie

            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?

            • 3. Re: Dynamic Datasource
              Ramesh Reddy Master

              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

              • 4. Re: Dynamic Datasource
                SENTHILKUMAR P. (SENTHIL) KANDAMPALAYAM Newbie

                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.

                • 5. Re: Dynamic Datasource
                  Ramesh Reddy Master

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

                  DDL VDB · GitBook

                  • 7. Re: Dynamic Datasource
                    SENTHILKUMAR P. (SENTHIL) KANDAMPALAYAM Newbie

                    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}

                    • 8. Re: Dynamic Datasource
                      Ramesh Reddy Master

                      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.

                      • 9. Re: Dynamic Datasource
                        SENTHILKUMAR P. (SENTHIL) KANDAMPALAYAM Newbie

                        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.

                        • 10. Re: Dynamic Datasource
                          Ramesh Reddy Master

                          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.