6 Connecting to Data Sources Through JDBC

You can connect to a wide variety of non-Oracle databases and import metadata and data from these sources using JDBC connectivity.

This chapter provides connection details for a non-Oracle data sources that can be accessed through JDBC, and generic connection information. It contains the following topics:

JDBC connectivity is used with code templates-based mappings. See Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide for more details about these mappings.

Generic Connection Using JDBC

You can connect to any data source that supports JDBC connectivity. To connect to the data source, you require the JDBC driver for that data source and the URL format to set up the connection. For any database, download the required JDBC driver into OWB_HOME/owb/lib/ext.

Connecting to DB2 Database

You can connect to a DB2 database using a JDBC connection. Ensure that the following prerequisites are met before connecting to a DB2 database.

JDBC Connection Drivers for DB2

You must download the following jar files into OWB_HOME/owb/lib/ext on your client system:

  • db2jcc.jar

  • db2jcc_license_cu.jar

See Also:

Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide for more information about using code template mappings.

Creating a DB2 Module

Before you can import metadata from a DB2 database, you must create a DB2 module to store the metadata.

To create a DB2 module:

  1. Right-click DB2 under the Databases node in the Projects Navigator and select New DB2 Module.

    The Create Module Wizard is displayed.

  2. Click Next to open the Name and Description page.

    Provide a name, description (optional), and the access method. Select the access method Native Database Connection, which implies using a JDBC driver to make the connection.

  3. Click Next to open the Connection Information page.

    You can select from an existing location or provide new location details.

  4. To provide a new location, click Edit on the Connection Information page to open the Edit DB2 Location dialog box.

    Provide the following details in the Edit DB2 Location dialog box:

    • User Name: The user name to connect to the host system.

    • Password: The password for the user name.

    • Host: The host system where the database resides.

    • Port: The port number is usually 50000 for the DB2 connection.

    • Database: The database name.

    • Schema: The schema from which objects are to be imported.

    • Version: The database version.

  5. Click Test Connection to ensure that the connection is valid.

  6. Click OK to return to the Connection Information page.

  7. Click Next, and then click Finish in the Summary page after verifying the specified details.

The newly created module is available under the DB2 node in the Projects Navigator. A DB2 module supports the following data objects:

  • Transformations

  • Tables

  • Views

  • Sequences

Note:

Temporary tables can be created in the work schema when code template mappings run. To change the work schema, edit the location and use the Advanced tab.

Importing Metadata into a DB2 Module

To import metadata into a module:

  1. Right-click the DB2 module and select Import, then Database Object.

    The Import Metadata Wizard is displayed.

  2. In the Filter Information page, select the object types to be imported.

  3. In the Object Selection page, select the objects to be imported. Also, specify whether dependent objects should be selected.

  4. In the Summary page, verify the objects you selected.

  5. Click Finish to begin the import.

  6. In the Import Results dialog box, click OK to confirm the import action. Click Undo to cancel the import.

Connecting to SQL Server Database

You can connect to a SQL Server database using a JDBC connection.

JDBC Connection Driver for SQL Server

To connect using JDBC, you must place the jar file sqljdbc.jar into OWB_HOME/owb/lib/ext on your client system.

This jar file is available for download at the Microsoft download center.

Creating a SQL Server Module

Before you can import metadata from a SQL Server database, you must create a SQL Server module to store the metadata.

To create a SQL Server Module:

  1. Right-click SQL Server under the Databases node in the Projects Navigator and select New SQL Server Module.

    The Create Module Wizard is displayed.

  2. Click Next to open the Name and Description page.

    Provide a name, description (optional), and the access method. Select the access method Native Database Connection, which implies using a JDBC driver to make the connection.

  3. Click Next to open the Connection Information page.

    You can select from an existing location or provide new location details.

  4. To provide a new location, click Edit on the Connection Information page to open the Edit SQL Server Location dialog box.

    Provide the following details in the Edit SQL Server Location dialog box:

    • User Name: The user name to connect to the host system.

    • Password: The password for the user name.

    • Host: The host system where the database resides.

    • Port: The port number is usually 1433 for the SQL Server connection.

    • Database: The database name.

    • Schema: The schema from which objects are to be imported.

    • Version: The database version.

  5. Click Test Connection to ensure that the connection is set.

  6. Click OK to return to the Connection Information page.

  7. Click Next, and then click Finish in the Summary page after verifying the specified details.

The newly created module is available under the SQL Server node in the Projects Navigator. A SQL Server module supports the following data objects:

  • Transformations

  • Tables

  • Views

  • Sequences

Importing Metadata into a SQL Server Module

To import metadata into a module:

  1. Right-click the SQL Server module and select Import, then Database Object.

    The Import Metadata Wizard is displayed.

  2. In the Filter Information page, select the object types to be imported.

  3. In the Object Selection page, select the objects to be imported. Also specify whether dependent objects should be selected.

  4. In the Summary page, verify the objects you selected.

  5. Click Finish to begin the import.

  6. In the Import Results dialog box, click OK to confirm the import action. Click Undo to cancel the import.

After you create a JDBC connection and import metadata from data objects, you can extract data from these objects and load it to target data sources by performing extraction, transformation, and loading (ETL) operations. However, to perform ETL operations on JDBC connected-data objects, you can use only code template mappings. A limited set of operators are supported for code template mappings. All operators are supported for Oracle target in code template mappings. For more information about code templates, see Chapter 12, "Using Code Templates to Load and Transfer Data". For more information about ETL operations and different types of mappings including PL/SQL mappings and code template mappings, see the Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

Importing Metadata from Other Databases Using JDBC

To import metadata definitions from other databases using JDBC drivers, you must create a new platform in Oracle Warehouse Builder. A platform refers to a data source. See Chapter 11, "Creating New Platforms" for more information about platforms.