This document suggests some possible solutions to share common elements across different VDP databases.
The following solutions only apply to Denodo versions older than the 6.0 version. Since version 6.0 and later versions it is possible to access views from other databases in a direct way. To do so, when running a query or referencing a view from a different database the name of the view must be preceded with the name of the database the view belongs to: <database_name>.<view_name>.
When different data sources are shared among different virtual databases, there are two ways of dealing with it:
- Create a common database that contains the common data sources and views to be shared. This common database will be imported as a normal JDBC data source in the different databases that require the shared elements. To use this approach, the following steps can be executed:
- Create a new common virtual database.
- Create all the data sources and views required to be shared.
- For any database that needs the shared resources:
- Create a new JDBC data source using Denodo Virtual DataPort x.y as Database adapter.
- Import the shared views.
- Create the data sources and views in one of the different databases which requires them, export all the information from this database and import it in the other ones.
- A way to easily export/import all this information is to create a Folder containing the shared elements and export/import all the content of the folder.
The first approach has the following drawbacks:
- An overhead will be added to the common data sources due to the extra connection needed to implement the solution.
- It may prevent certain queries from being delegated to the target data source. Only the queries that can be pushed down to both VDP and the original data source will be delegated.
- When monitoring the VDP server every final consumer request will generate two requests on the VDP server adding noise to the monitoring that will make harder to monitor and diagnose a problem on the server.
If the second approach is implemented it will be necessary to repeat the export/import process for all the databases after a change in the shared elements.
Given the above, the second approach is recommended over the first approach.