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, as well as generic connection information. It contains the following topics:

Connecting to DB2 Database
Connecting to SQL Server Database

JDBC connectivity is used in conjunction 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 as well as 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

Modifying the ccastart File

If you are working on a UNIX system, modify the ccastart file to include the following to the line that invokes the CCA:

-Dapi.ext.dirs=$OWB_HOME/owb/lib/ext

The following is an example:

$JAVA_HOME/bin/java -Dapi.ext.dirs=$OWB_HOME/owb/lib/ext
-Doracle.home=$OC4J_HOME -Doracle.j2ee.home=$OWB_JRT_RUN_HOME
-Dpython.path=$OWB_JRT_RUN_HOME/applications/jrt/jython_lib.zip:$OWB_JRT_RUN_H
OME/applications/jrt/jython_owblib.jar $JRT_JVM_XOPTS $OWB_JVM_OPTS -mx128M
-jar $OC4J_HOME/j2ee/home/oc4j.jar -config
$OWB_JRT_RUN_HOME/config/server.xml >> $LOG_FILE 2>&1 &

The ccastart file is available at OWB_HOME/owb/bin/unix.

Modifying the ccastart.bat File

On a Windows system, modify the file ccastart.bat to include the following to the line that invokes the CCA:

-Dapi.ext.dirs=%OWB_HOME%\owb\lib\ext

The following is an example:

%JAVAPATH%\bin\java -Dapi.ext.dirs=%OWB_HOME%\owb\lib\ext
-Doracle.home=%OWB_HOME%\oc4j -Doracle.j2ee.home=%OWB_HOME%\owb\jrt
-Dpython.path=%OWB_HOME%\owb\jrt\applications\jrt\jython_lib.zip;%OWB_HOME%\owb\jr
t\applications\jrt\jython_owblib.jar -Xms128M -Xmx512M -XX:MaxPermSize=96M
-Xverify:none -Dlimit=384M   -jar %OWB_HOME%\oc4j\j2ee\home\oc4j.jar -config
%OWB_HOME%\owb\jrt\config\server.xml

The ccastart.bat file is available at OWB_HOME\owb\bin\win32.

The modification to the ccastart or the ccastart.bat file is required because the DB2 JDBC driver contains its own set of SQLJ library files that might conflict with the SQLJ library files provided by OC4J. By modifying the file, you are ensuring that the SQLJ libraries provided by the DB2 JDBC driver get loaded and are used when code templates are used to retrieve data from the DB2 database. See Chapter 12, "Using Code Templates to Load and Transfer Data" for more information about code templates.

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:

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

The Create Module Wizard is displayed.
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.
Click Next to open the Connection Information page.

You can select from an existing location or provide new location details.
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.
Click Test Connection to ensure that the connection is valid.
Click OK to return to the Connection Information page.
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

Importing Metadata into a DB2 Module

To import metadata into a module:

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

The Import Metadata Wizard is displayed.
In the Filter Information page, select the object types to be imported.
In the Object Selection page, select the objects to be imported. Also, specify whether dependent objects should be selected.
In the Summary page, verify the objects you selected.
Click Finish to begin the import.
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:

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

The Create Module Wizard is displayed.
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.
Click Next to open the Connection Information page.

You can select from an existing location or provide new location details.
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.
Click Test Connection to ensure that the connection is set.
Click OK to return to the Connection Information page.
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:

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

The Import Metadata Wizard is displayed.
In the Filter Information page, select the object types to be imported.
In the Object Selection page, select the objects to be imported. Also specify whether dependent objects should be selected.
In the Summary page, verify the objects you selected.
Click Finish to begin the import.
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. For these objects, you cannot perform ETL operations using regular mappings, such as PL/SQL 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 Warehouse Builder. A platform refers to a data source. See Chapter 11, "Creating New Platforms" for more information about platforms.