6 Connecting to Data Sources Through JDBC

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

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.

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.