Add Oracle JDBC Drivers to SuperCHANNEL

JDBC (Java Database Connectivity) is a programming interface that lets Java applications access a relational database.

SuperCHANNEL needs a JDBC driver so that it can access the relational database system (e.g. SQL Server, Oracle, etc) where your source data is stored.

Before you can run SuperCHANNEL, you need to install the appropriate JDBC driver and configure SuperCHANNEL so that it can access the driver.

In most cases the JDBC drivers are supplied by the database vendors. STR does not supply the drivers.

Oracle

If your source data is stored in an Oracle database, then you need to configure SuperCHANNEL to use an appropriate JDBC driver. Space-Time Research recommends version 11.1.0.6.0. This is backwards compatible with 10.2.x, 10.1.x, 9.2.x and 9.0.1.x Oracle Database releases, and is compatible with JDK 1.4.

Step 1 - Download and Install the Driver

Download an appropriate driver from http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html (you only need to download the file ojdbc6.jar).
Copy the driver file (the .jar file) to the drivers directory in your SuperCHANNEL installation. If you chose the default installation options, the location will be: C:\Program Files\STR\SuperCHANNEL\jar\drivers

Depending on your system configuration, you may need administrator permissions to copy to this directory. If you are prompted to provide administrator permissions, click Continue.

Step 2 - Edit the SuperCHANNEL Configuration

Locate the SuperCHANNEL configuration file, config.txt. If you chose the default installation options, this file will be located in C:\ProgramData\STR\SuperCHANNEL\bin

Make a backup copy of this file before making any changes.
Open config.txt in a text editor.
Locate the line that defines the Java Class Path variable (java.class.path), and add the following to the end of the line:

$(SNU_PROGRAM_HOME)\jar\drivers\ojdbc6.jar;

Notes

Replace ojdbc6.jar with the filename of the driver you installed.

$(SNU_PROGRAM_HOME) is a predefined variable that refers to the SuperCHANNEL program files directory.

Each entry in the Java Class Path definition must be separated by a semi colon, and there must also be a semi colon at the end of the line.
Locate the line that defines the jdbc.drivers and add the following to the end of the line:

:oracle.jdbc.driver.OracleDriver

Note

Each entry in the jdbc.drivers definition must be separated by a colon.

The following example shows the two additions to the configuration file:
Locate the line that defines jdbc.table.types and check that it is set to TABLE:VIEW (this enables both Oracle tables and views to be seen in SuperCHANNEL):
Locate the line that defines str.oracle.fix and check that it is set to yes:
Save config.txt.

Step 3 - Start SuperCHANNEL

Start SuperCHANNEL, and use the following connection string to connect to Oracle:

CODE

jdbc:oracle:thin:@<servername>:<port>:<databasename>

Where:

<servername> is the name of your host for Oracle.
<port> is the port to use to communicate with Oracle (the default is 1521).
<databasename> is the SID of the source database to connect to.

You will also need to provide user credentials (for Oracle authentication):

When a connection is made with Oracle, available schemas appear in the Schema window for selection.

'Oracle' will not appear in the SuperCHANNEL 'driver' list box until after the first successful connection has been made.

If you have to connect to Oracle RAC (Real Application Cluster) using JDBC with the thin driver, the classic URL (jdbc:oracle:thin:@<servername>:<port>:<databasename>) does not work.

If you see the error ORA – 12505, use the following URL format instead:

CODE

jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)
(ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=host2) (PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=service)))

Check tsnames.ora for the correct values of SERVICE_NAME and host.

Using Views

When connecting to an Oracle database, SuperCHANNEL's Source View only displays the names of synonyms contained in the selected schema. It does not display the columns of the underlying tables associated with the synonyms.

If you are using views, you can display the columns in the Source View for the Oracle Synonyms by setting the includeSynonyms property to true.

This property is an Oracle JDBC driver property. Use of this flag carries a performance penalty. If you do not need views, disable this feature by setting the flag to false.

Step 4 - Add Drivers to SNU

This step is optional. If you intend to run SuperCHANNEL from the command line or via scripting, you should also add the drivers to snu.bat. This is the SuperCHANNEL batch file, located in C:\ProgramData\STR\SuperCHANNEL\bin by default.

Make a backup copy of this file before making any changes.

Add the driver entry to the last statement in the file. This is the Java call to execute the program.

Once you have configured snu.bat, you can run SuperCHANNEL from the Windows command prompt with a command like:

CODE

snu -c -i -sp:PUMS_2009 "C:\PUMS\sxv4\pums_2009_3.xml"

In this example the password to connect to Oracle is PUMS_2009 and the SuperCHANNEL project file is C:\PUMS\sxv4\pums_2009_3.xml