Aqua Data Studio can query multiple databases with different SQL queries. However, you cannot query more than one database at the same time in one SQL query. UnityJDBC allows a user to write one query to join, aggregate, and summarize data across any number of databases. It also performs translation of SQL dialects and functions.
Benefits:
-
UnityJDBC allows Aqua Data Studio to support multiple source queries.
-
No data source or server changes are required.
-
UnityJDBC supports standard SQL including joins, group by, aggregation, LIMIT, and ordering where tables may come from one or more sources.
-
UnityJDBC will perform function translation where a user requests a function that is not supported on a certain source.
This example assumes that the installation of UnityJDBC has already been completed. The following is an example of creating sources and executing multiple database queries with Aqua Data Studio.
-
Create a New Source Group - Under File Menu, Select New Source Group.
Aqua Example: Creating a New Source Group Data Virtualization
-
Select Sources File Name and Location - Use the default sources.xml or select a file location.
Aqua Example: Specifying a Source File Location
-
Select New Source - Under File Menu, Select New Source.
Aqua Example: Creating a New Source
-
Add a Microsoft Database - Enter connection information for Microsoft SQL Server to add it to the data virtualization.
Aqua Example: Adding a Microsoft SQL Server Database to the Data Virtualization
-
Add a MySQL Database - Enter connection information for MySQL database to add it to the data virtualization.
Aqua Example: Adding a MySQL Database to the Data Virtualization
-
Viewing Data Virtualization of Sources - Select Exit to return to the main screen and see data virtualization of MySQL and Microsoft SQL Server sources.
Aqua Example: Resulting Data Virtualization of Two Database Sources
-
Multiple Database Query and Cross-Database Join Example - Type in a cross-database query and execute it to view results.
Aqua Example: Executing a Cross-Database Join of Two Databases
-
Finding Sources and Schema Files - Now that the data virtualization is complete, find the sources.xml file and schema files for your sources. The image below shows the default location which would be inside the UnityJDBC installation directory.
Aqua Example: Finding Source and Schema Files
-
Moving Sources and Schema Files - Optionally, move the sources and schema files to a permanent location. In this example, they are moved to
C:/unityjdbc.
Aqua Example: Moving Source and Schema Files to Another Location
-
Adding UnityJDBC data source to Aqua Data Studio - Copy
unityjdbc.jarand all JDBC drivers for databases intojre/lib/extin Aqua Installation directory.
Aqua Example: Installing unityjdbc.jar in Aqua Data Studio
-
Start Aqua and Register a Generic JDBC Source - The configuration information is:
-
Driver:
unity.jdbc.UnityDriver -
URL:
jdbc:unity://<path to sources.xml file>
Aqua Example: Adding a UnityJDBC Virtual Data Source in Aqua Data Studio
-
-
Multiple Database Query in Aqua Data Studio - Create a query like usual except it can contain multiple databases.
Aqua Example: Executing a Multiple Database Join Query and Displaying Results
-
Function and SQL Dialect Translation with Aqua Data Studio - Forget what functions you can use on each database? No problem – UnityJDBC will translate automatically. This translation is supported for common databases and can be freely extended by user-defined functions and translations for each database dialect.
Aqua Example: Performing SQL Dialect and Function Translation