JDBC URL Samples

Oracle Data Integrator does not include any licenses for JDBC Drivers. Some drivers are provided free if you have already purchased certain server products.

A list of all available JDBC drivers is maintained at the following URL: http://java.sun.com/products/jdbc/jdbc.drivers.html

Not all drivers have been validated for use with Oracle Data Integrator. Below is a list of drivers that have been tested and are recommended for use with Oracle Data Integrator.

Driver

Driver and URL parameters, comments

Oracle Data Integrator Driver for File (new version)

driver: com.sunopsis.jdbc.driver.file.FileDriver

URL: jdbc:snps:dbfile?<option1>&<option2>&...

Options:

  • ENCODING=<encoding_code>: File encoding. The list of supported encodings is available at http://java.sun.com/j2se/1.4.2/docs/guide/intl/encoding.doc.html. The default encoding value is ISO8859_1.

  • TRUNC_FIXED_STRINGS=TRUE|FALSE: Truncates strings from fixed-column-width files to the field size. Default value is FALSE.

  • TRUNC_DEL_STRINGS=TRUE|FALSE: Truncates strings from delimited files to the field size. Default value is FALSE.

  • OPT=TRUE|FALSE: Optimizes file access on multiprocessor machines for better performance. Using this option on single processor machines may actually decrease performance. Default value is FALSE.

Warning: Although this driver is not fully JDBC compliant, it supports certain methods used by the agent

Oracle Data Integrator Driver for File (old version - deprecated)

Warning: This driver version is deprecated.

driver: com.sunopsis.jdbc.driver.FileDriver

URL: jdbc:snps:file

Other URL:

  • jdbc.snps.file?CHARSET_ENCODING=<encoding>&RAW_HEADERS=true|false
    - CHARSET_ENCODING
    enables you to indicate the encoding of the target file. The list of supported encodings is available at the URL: http://java.sun.com/j2se/1.3/docs/guide/intl/encoding.doc.html
    - RAW_HEADERS enables you to read the header line as raw data. This lets you retrieve special characters in this line to generate column names during a reverse-engineering.

Warning: This driver is not fully JDBC compliant, it supports certain methods used by the agent

Sun ODBC/JDBC Bridge (JDBC 2 Type 1)

driver: sun.jdbc.odbc.JdbcOdbcDriver

URL: jdbc:odbc:<odbc datasource name>

Note: You must have installed an ODBC Driver and created an ODBC datasource with the ODBC Administrator before using the type 1 JDBC Driver.

Oracle JDBC Driver (JDBC 1 type 4)

driver: oracle.jdbc.driver.OracleDriver

URL: jdbc:oracle:thin:@<IP address or name>:<listener port>:<SID>

Note: The SQL*Net client is not required on the client machine.

Inet Software JDBC Driver for Microsoft SQL/Server (Type 4)

driver: com.inet.tds.TdsDriver

URL: jdbc:inetdae:<IP adress or name>:<listener port>

Note: Open Client and ODBC are not required on the client machine.
This driver loads each result set into the client's virtual memory before fetching rows.

Other sub-protocols:

  • jdbc:inetdae: .... classic

  • jdbc:inetdae6: .... SQL Server 6.5 compatible mode (on Sprinta Driver)

  • jdbc:inetdae7: .... supporting of the SQL Server 7.0 (or higher) (On Sprinta Driver)

Other URLs:

  • jdbc:inetdae:hostname:portnumber

  • jdbc:inetdae:hostname -> with default port 1433

  • jdbc:inetdae:hostname:portnumber?database=MyDb&language=deutsch

-> with properties

  • jdbc:inetdae://servername/pipe/pipename -> with named pipes

Sybase JConnect (Type 4)

driver: com.sybase.jdbc2.jdbc.SybDriver

URL: jdbc:sybase:Tds:<IP address or name>:<listener port>/<database>

Note: DB-LIB, CT_LIB and ODBC are not required on the client machine.

Warning: Array Fetch is not available if you do not have a Unique Index on Table (leave the Array Fetch empty in Oracle Data Integrator). Batch Update is available.

Warning: You must run the correct SQL script sql_server.sql (provided with the driver distribution) on sql/server before use.

Hit JDBC Driver for AS/400 via Client/Access (Type 4)

driver: hit.as400.As400Driver

URL: jdbc:as400://<IP adress or name>

Notes: No extra middleware is required on the client machine. ALL Client/Access services must be started on AS/400 (STRHOSTSVR *ALL)

Other options:=

    [;user=<user>]

    [;password=<password>]

    [;options=[secure,][http][<Compressorclass>,][Cryptographerclass>,]

    [;license=<serial number>]

    [;libraries=<lib1,lib2,...,libn>]

    [;ccsid=<number>]

    [;use_packages=<YES|NO>]

    [;use_package_library=<package library>]

    [;use_package_name=<package name>]

    [;allow_package_update=<YES|NO>]

    [;convert_ccsid_65535=<YES|NO>]

    [;fetch_block_size=<Ksize>]

    [;naming=<sql|system]

IBM TOOLBOX JDBC Driver for AS/400 (Type 4)

driver: com.ibm.as400.access.AS400JDBCDriver

URL: jdbc:as400://<IP adress or name>

Note: No extra middleware is required on the client machine.

Oracle Data Integrator AS/400 Driver Wrapper

driver: com.sunopsis.jdbc.driver.wrapper.SnpsDriverWrapper

URL: jdbc:snps400:<machine_name/IP_address>[;param1=valeur1[;param2=valeur2...]]

This driver wrapper enables the use of the Native of JT/400 driver depending on the situation. This driver wrapper connects using the Native driver when a connection to the local AS/400 machine (the one where the agent is runs) is requested. Otherwise it uses the JT/400 driver. This wrapper uses 3 Java properties:

  • HOST_NAME: Comma-separated list of names for the local AS/400 machine.

  • HOST_IP: IP address of the local AS/400 machine. It is used if the connection is requested with the IP address.

  • SNPS_VERBOSE: Driver trace level. Values: 0 (no trace)  to 5 (full trace).

For more information, see Creating a DB2/400 Data Server.