Data Virtualization Using SourceBuilder
Prev Chapter 3. Using SourceBuilder - Tutorial on Multiple Database Querying Next

Data Virtualization Using SourceBuilder

To build your own data virtualization of multiple data sources, create a new sources file by selecting File->New Source Group and providing a file name (the default is sources.xml).

Creating a New Source Group with SourceBuilder

Prompt for Name of Sources File

Then, you will add each one of your sources by providing its JDBC connection information. To add a source you need the following information:

  1. The JDBC Driver class name (e.g. com.mysql.jdbc.Driver).

  2. The JDBC URL to connect to the source (e.g. jdbc:mysql://localhost/mydb).

  3. User and password information if not specified in the JDBC URL.

  4. A unique database name (does not have to be the same as the system database name) to refer to the data source in your data virtualization.

An example of adding a MySQL source to the virtual database is below.

Adding a MySQL Data Source to the Data Virtualization

There are also several additional features that can be used to control the extraction.

  1. Schema - Specify a schema name or pattern (JDBC API) to only retrieve tables in the given schema. This is especially important for Oracle as by default tables from all schemas will be added to the virtualization.

  2. Tables included - Specify a JDBC API pattern (use % for wild card character match) to indicate which tables should be added to the extraction. For example, a pattern of R% will only add tables that begin with R.

  3. Tables excluded - Specify a Java string pattern (use .* for matching any sequence of characters) to indicate what tables to exclude from extraction. Each database has certain default exclusion patterns to avoid extracting system tables. It is recommended to modify the exclusion pattern if there are any issues extracting too many tables, especially system tables for your data source.

  4. Statistics - Statistics collection helps the UnityJDBC optimizer perform more efficient data virtualization queries. The default statistics collection is Row Counts which will calculate the number of rows for each extracted table. The All statistics setting collects rows counts as well as value distribution information for each field. Some sources do not support collecting field distribution information in which case the Row Counts setting should be used. A setting of None will collect no statistics and is the fastest when performing extraction. UnityJDBC will execute queries with no statistics perfectly fine, although statistics do help in query planning for complex queries involving many tables.

Below is an example of extracting only the tables that end in 'ER' from an Oracle database. Note how the schema is also supplied as RLAWRENC.

Adding an Oracle Data Source to the Data Virtualization (note use of schema name)

Multiple Database Virtualization and Querying for HSQLDB

Using UnityJDBC SourceBuilder, it is possible to build a data virtualization of one or more HSQLDB databases with any other database system. To add a HSQLDB data source, the following information is used:

  1. JDBC Driver class name: org.hsqldb.jdbcDriver.

  2. JDBC URL: jdbc:hsqldb:hsql://<host address>/<database name>.

An example of adding a HSQLDB source to a data virtualzation is below.

Adding a HSQLDB Data Source to the Data Virtualization

Multiple Database Virtualization and Querying for IBM DB2

Using UnityJDBC SourceBuilder, it is possible to build a data virtualization of one or more IBM DB2 databases with any other database system. To add an IBM DB2 data source, the following information is used:

  1. JDBC Driver class name: com.ibm.db2.jcc.DB2Driver.

  2. JDBC URL: jdbc:db2://<host name>/<database name>.

An example of adding an IBM DB2 source to a data virtualzation is below.

Adding an IBM DB2 Data Source to the Data Virtualization

Multiple Database Virtualization and Querying for Microsoft SQL Server

Using UnityJDBC SourceBuilder, it is possible to build a data virtualization of one or more Microsoft SQL Server databases with any other database system. To add a Microsoft SQL Server data source, the following information is used:

  1. JDBC Driver class name: com.microsoft.sqlserver.jdbc.SQLServerDriver.

  2. JDBC URL: jdbc:sqlserver://<host>;DatabaseName=<database name>.

An example of adding a Microsoft SQL Server data source to a data virtualzation is below.

Adding a Microsoft SQL Server Data Source to the Data Virtualization

Multiple Database Virtualization and Querying for MySQL

Using UnityJDBC SourceBuilder, it is possible to build a data virtualization of one or more MySQL databases with any other database system. To add a MySQL data source, the following information is used:

  1. JDBC Driver class name: com.mysql.jdbc.Driver.

  2. JDBC URL: jdbc:mysql://<host address>/<database name>.

An example of adding a MySQL source to a data virtualzation is below.

Adding a MySQL Data Source to the Data Virtualization

Multiple Database Virtualization and Querying for Oracle

Using UnityJDBC SourceBuilder, it is possible to build a data virtualization of one or more Oracle databases with any other database system. To add an Oracle data source, the following information is used:

  1. JDBC Driver class name: oracle.jdbc.driver.OracleDriver.

  2. JDBC URL: jdbc:oracle:thin:<user>/<password>@<server>:1521/<service>.

An example of adding an Oracle data source to a data virtualzation is below.

Adding an Oracle Data Source to the Data Virtualization

Multiple Database Virtualization and Querying for PostgreSQL

Using UnityJDBC SourceBuilder, it is possible to build a data virtualization of one or more PostgreSQL databases with any other database system. To add a PostgreSQL data source, the following information is used:

  1. JDBC Driver class name: org.postgresql.Driver.

  2. JDBC URL: jdbc:postgresql://<server>/<database>?user=<userId>&password=<password>.

An example of adding a PostgreSQL source to a data virtualzation is below.

Adding a PostgreSQL Data Source to the Data Virtualization

Multiple Database Virtualization and Querying for Sybase

Using UnityJDBC SourceBuilder, it is possible to build a data virtualization of one or more Sybase databases with any other database system. To add a Sybase data source, the following information is used:

  1. JDBC Driver class name: com.sybase.jdbc4.jdbc.SybDriver.

  2. JDBC URL: jdbc:sybase:Tds:<server>:<port>/<database>?user=<userid>&password=<password>.

An example of adding a Sybase source to a data virtualzation is below.

Adding a Sybase Data Source to the Data Virtualization

Multiple Database Virtualization and Querying for MongoDB

Using UnityJDBC SourceBuilder, it is possible to build a data virtualization of one or more MongoDB databases (collections) with any other database system. Note that a MongoDB JDBC driver is built into UnityJDBC and can be used separately from UnityJDBC directly. For more information go to http://www.unityjdbc.com/mongojdbc To add a MongoDB data source, the following information is used:

  1. JDBC Driver class name: mongodb.jdbc.MongoDriver.

  2. JDBC URL: jdbc:mongo://<server>:1521/<database>.

An example of adding a MongoDB data source to a data virtualzation is below.

Adding a MongoDB Data Source to the Data Virtualization

Multiple Database Virtualization and Querying for Other JDBC/ODBC Sources

Using UnityJDBC SourceBuilder, it is possible to build a data virtualization for any data source that supports JDBC or ODBC including Microsoft Access databases, Excel files, text files, and any data source that has a JDBC driver.

Prev Up Next
Chapter 3. Using SourceBuilder - Tutorial on Multiple Database Querying Home Chapter 4. Multiple Database Programming with UnityJDBC