1 2 Previous Next 16 Replies Latest reply on Dec 2, 2014 3:52 AM by nimishkmr

    Is VDB dependent on Source Database name ?

    madhurika_sharma

      I am creating a VDB using Teiid Designer . The VDB was earlier connecting to DB1 in Testing phase .

      Now , I have a DB2 present in PROD environment. The tables are same in both the databases.

      After changing the Source name in Teiid Designer i also changed the SQL query with DB2

      The new VDB is getting deployed too .

      When i am accessing the VDB's table  ; i am getting an error :

      Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'DB1.dbo.TABLE1'.

      Can anyone please help me understand if the VDB is dependent on the Source Database ?
      And if it is then can we change the configuration anywhere ?

        • 1. Re: Is VDB dependent on Source Database name ?
          rareddy

          The table names in both DB1 and DB2 are same, but the schema names are not. During the import of the model you can choose to uncheck "fully qualified names", or edit the VDB and fix the table names with out schema names in both name and NameInSource properties. Make sue that database supports calling without the schema name first.

           

          Ramesh..

          • 2. Re: Is VDB dependent on Source Database name ?
            madhurika_sharma

            Hi ,

            I have unchecked "fully qualified name " also edited the NameInSource properties leaving it blank.

            I am getting following error :TEIID16012 Could not produce a sucessful OData response.

            What about the SQL Query ? Do i need to change it too ?

            • 3. Re: Is VDB dependent on Source Database name ?
              rareddy

              You only showed half the error according to the code? "TEIID16012=Could not produce a sucessful OData response.  Returning status {0} with message {1}."

              • 4. Re: Is VDB dependent on Source Database name ?
                madhurika_sharma

                I understand we can change the .xmi file in the vdb itself .
                I am trying to change the database name in the db.xmi file present inside vdb .

                • 5. Re: Is VDB dependent on Source Database name ?
                  rareddy

                  Changing a XMI file outside Designer will have ZERO impact on the VDB. Teiid DOES NOT look at XMI files for metadata. So, my advise is go through advertised way to edit a VDB. If that is giving errors then we can take look at the issue.

                  • 6. Re: Re: Is VDB dependent on Source Database name ?
                    nimishkmr

                    Hi Ramesh,

                     

                    Is there any way to make VDB independent of the DB name?

                    Say, I have a VDB - vdb_acc_v1.0.vdb that is deployed in ACC, which connects to DB - DB_Acc. Now, I have to move this vdb in production and the production vdb should connect to DB - DB_Prod. Is there any way to use the vdb - vdb_acc_v1.0.vdb, that was created in ACC, to be used in PROD also or do I need to create a new VDB for production environment separately?

                    I would prefer having a generic vdb that does not store any information of databases(DB name,pwd, DB url) and provides flexibility to connect to any DB by editing any configurable property. Can we achieve this anyhow?

                     

                    Regards,

                    Nimish

                    • 7. Re: Re: Is VDB dependent on Source Database name ?
                      rareddy

                      Nimish, Madhurika,

                       

                      Yes.

                       

                      "NameInSource" means what it sounds like Name of the table in the physical source. You can give any logical name to the table in a physical model you want, however during the execution Teiid uses the "NameInSource" property if present. If one is not present then it uses the table name in physical model as the table name in the physical source.

                       

                      Given that, you need to figure out what is the non fully qualified table name that you can use both in DB1 and DB2 to access TB1, TB2.. Try those queries in SquirrL if you need to, and edit the NameInSource accordingly.

                       

                      try like "dbo.TB1", "dbo.TB2"

                       

                      Ramesh..

                      • 8. Re: Re: Is VDB dependent on Source Database name ?
                        shawkins

                        > I would prefer having a generic vdb that does not store any information of databases(DB name,pwd, DB url) and provides flexibility to connect to any DB by editing any configurable property. Can we achieve this anyhow?

                         

                        The vdb does not store the db name, password, url, etc.  That is all configured via the referenced JNDI name and the corresponding data source.  So JNDI should provide you with the level of indirection you need.  As long as the databases are the same type and have the same structure you can very what the JNDI name points to in each environment and Teiid will work just fine.

                        • 9. Re: Is VDB dependent on Source Database name ?
                          madhurika_sharma

                          Hi Ramesh,

                           

                          As you said to try with "dbo.TB1",   "dbo.TB2" and   "dbo.TB3"  
                          If i do not specify the database name in the "Name in Source" property then how the VDB will know which Database it has to go to ?
                          Is there any configurable property file which VDB refers to which database it has to connect to ?

                          • 10. Re: Is VDB dependent on Source Database name ?
                            rareddy

                            As you said to try with "dbo.TB1",   "dbo.TB2" and   "dbo.TB3"  

                            Did it work this way with DB1?

                            If i do not specify the database name in the "Name in Source" property then how the VDB will know which Database it has to go to ?

                            See Steve H comment above about JNDI names.

                            • 11. Re: Is VDB dependent on Source Database name ?
                              madhurika_sharma

                              Hi ,

                              • "dbo.TB1",   "dbo.TB2" and   "dbo.TB3"   :  didn't work .
                              • I have changed "Name in Source" property of three tables present in Source Model to "DB2"."dbo"."TB1" , "DB2"."dbo"."TB2" and "DB2"."dbo"."TB3"
                                The VDB is getting connected to new database DB2.
                              • Is there any way we can do the required change outside the Teiid Designer?
                              • 12. Re: Is VDB dependent on Source Database name ?
                                rareddy

                                See this http://technet.microsoft.com/en-us/library/ms187879%28v=sql.105%29.aspx

                                 

                                To quote from above

                                "Most object references use three-part names. The default server_name is the local server. The default database_name is the current database of the connection. The default schema_name is the default schema of the user submitting the statement. Unless otherwise configured, the default schema of new users is the dbo schema."

                                 

                                Now based on it what is the JDBC "connection-url" you have been using in your data source configuration? By default it supplies database_name like DB1, DB2 for your connection, so you do not need to define it.

                                 

                                Ramesh..

                                • 13. Re: Is VDB dependent on Source Database name ?
                                  nimishkmr

                                  Hi Ramesh,

                                  One simple question -

                                  Consider the scenario :

                                  We have 2 databases - ABC_DB and XYZ_DB. The databases have similar tables and structure. Is it possible to create a single VDB that can be used for both the databases?

                                  or we need to create 2 separat VDBs for these 2 different databases ?

                                   

                                  Regards,

                                  Nimish

                                  • 14. Re: Is VDB dependent on Source Database name ?
                                    shawkins

                                    > Is it possible to create a single VDB that can be used for both the databases?

                                    or we need to create 2 separat VDBs for these 2 different databases ?

                                     

                                    If they are the same database type, then that should be fine.  If they are different database types, then the metadata may be subtly different - such as native type names, which could cause issues.  You can deploy the same VDB twice under different names, or if you are trying to access both databases as a logical single entity, then there is a multi-source feature - Multisource Models - Teiid 8.10 (draft) - Project Documentation Editor

                                    1 2 Previous Next