Java Database Connectivity (JDBC) Adapter

The Java Database Connectivity (JDBC) adapter enables the Translation service to communicate with JDBC-compliant databases. The adapter enables you to update or retrieve data from a JDBC-compliant database as part of a business process within Gentran Integration Suite. The following table provides an overview of the Java Database Connectivity (JDBC) adapter:

How the JDBC Adapter Works

The queries you define in your map determine the data that is retrieved or updated. You can submit any query written in SQL, stored procedures, or stored functions to a database. The business process you create then determines how the data is used.

The following figure shows how the JDBC adapter communicates with an SQL database within a business process:

JDBC Adapter Business Process Usage

System name	JDBCAdapterType
Graphical Process Modeler (GPM) category	All Services
Description	Queries data from a remote database.
Business usage	Used to query or update data from a remote JDBC database by calling the Translator service.
Usage example	To perform any type of database query and return the results.
Preconfigured?	No
Requires third party files?	No
Platform availability	Microsoft Windows Sun Solaris HP-UX IBM-AIX United Linux RedHat AS OS/400 (iSeries) z/OS (zSeries)
Related services	No
Application requirements	To use the JDBC adapter, the config.xml, jdbc.properties, and poolManager.properties files must be edited to reference a valid data source and database. Also, the map that is going to be used by the translator must be checked in Gentran Integration Suite.
Initiates business processes?	If configured to start a new business process, the results returned from the database query will be used to start a new business process.
Invocation	Runs as part of a business process.
Business process context considerations	No
Returned status values	Success - JDBC adapter finished successfully. Warning - JDBC adapter finished but with warnings. Error - JDBC adapter finished, but with errors.
Restrictions	None

The JDBC adapter can start a business process, or it can be used in the middle or at the end of a business process. The following steps summarize how the JDBC adapter might be used in a business process:

The JDBC adapter receives a map name from the business process.

The adapter starts the Translation service and passes the name of the map to the translator.

Using the map, the translator creates the SQL commands and sends them to an SQL database.

The translator receives the results from an SQL database query, creates the output as defined in the map, and passes those results back to the JDBC adapter.

The business process advances to the next step.

Example

For example, you have customer information stored in a confidential database. Your Sales department does not have access to the database. You can use the JDBC adapter to provide the Sales department with access to customer information in the database and then write the information to disk using the File System adapter.

The adapter receives the name of the map from the business process.

The adapter starts the Translation service and passes it the map name.

The Translation service executes the map and generates SQL queries to submit to the database.

The Translation service submits the SQL queries to the database.

The Translation service receives an SQL response from the database and performs another translation to establish that the response is in a format that the adapter can process.

The Translation service passes the translated response to the adapter.

The adapter sends the customer information to the next step in the business process, the File System adapter.

The File System adapter writes the retrieved customer information to disk for the Sales department.

Gentran Integration Suite performs the next activity in the business process.

Implementing the JDBC Adapter

Activate your license for the JDBC adapter. For information, see An Overview of Implementing Services.

Create a JDBC adapter configuration. For information, see Creating a Service Configuration.

Configure the JDBC adapter. For information, see Configuring the JDBC Adapter.

Install the appropriate database drivers, if necessary, on the same computer where the Map Editor was installed.

Create an ODBC data source. For more information about ODBC, access www.msdn.microsoft.com and locate the ODBC Programming Reference documentation.

Set up a connection to an external database. For information, see Setting Up a Connection to an External Database.

Create either an input or output map using the Map Editor.

Check in the map for versioning control.

Use the JDBC adapter in a business process.

Configuring the JDBC Adapter

Gentran Integration Suite Configuration

The following table describes the fields used to configure the JDBC adapter in Gentran Integration Suite:


Field	Description
Name	Unique and meaningful name for the service configuration. Required.
Description	Meaningful description for the service configuration, for reference purposes. Required.
Select a Group	Select one of the options: None - You do not want to include this configuration in a group at this time. Create New Group - You can enter a name for a new group in this field, which will then be created along with this configuration. Select Group - If you have already created one or more groups for this service type, they are displayed in the list. Select a group from the list. Note: For more information about groups, see Using Service Groups.
Start a new business process (StartNewWorkFlow)	Whether to start a new business process. Valid values are: Yes: This JDBC adapter starts a new business process. No: This JDBC adapter does not start a new business process.
EDI Output Tag Delimiter (edi_output_tag_delimiter)	Values to change delimiters specified in the map, if the output side of the map is in EDI format. Optional. Available only if you specified Yes for Start a New Business Process.
EDI Output Segment Terminator (edi_output_segment_delimiter)
EDI Output Data Element Separator (edi_output_element_delimiter)
EDI Output Component Element Separator edi_output_sub-element_delimiter)
EDI Output Release Character (edi_output_release_character)
EDI Output Repeating Element Separator (edi_output_repeating_element_delimiter)
EDI Output Decimal Character (edi_output_decimal_separator)
Map Name (map_name)	Map name used by the translator.
Run as User	Enter (or select from the list) the user ID to be associated with business process instances of this service.
Use 24 Hour Clock Display	Select to specify times for this schedule using the 24 hour clock. Leave blank to use 12 hour clock and AM and PM.
Schedule	Information about scheduling the JDBC adapter configuration to run and to start the specified business process. Valid values: Do not use schedule If this field is selected, this service does not start a business process and does not run on a schedule. Run based on timer Valid values are the hour and minutes at which to run the service. Indicate whether you want the service to run at startup. Run daily Valid values are the hour and minutes at which to run the service, daily. You can also specify a time interval. Indicate whether you want the service to run at startup. Run based on day(s) of the week Valid values are the day of the week, the hour, and the minutes at which to run the service. You can also specify a time interval. Indicate whether you want the service to run at startup. Run based on day(s) of the month Valid values are the day of the month (including the last day of the month (LDOM)), hour, and the minutes at which to run the service. You can also specify a time interval. Indicate whether you want the service to run at startup. Note: The Schedule field only displays as an option if you set the Start a new business process parameter to "This JDBC Adapter will start a new business process".

Parameters Passed Through BPML Only

The following parameters can be passed through BPML using an Assign statement. Note that these parameters are not available through the GPM.

Setting Up a Connection to an External Database

You must set up a connection to an external database for the JDBC adapter. You can use any of the databases supported by Gentran Integration Suite for internal use (see the System Requirements documentation on Help on the Web), or other JDBC-compliant databases, such as Sybase.

Adding New Database Pools

Parameter	Description
SenderIdentityID	Used by map to access trading partner codelists.
ReceiverIdentityID	Used by map to access trading partner codelists.

To define a new database pool for use by the JDBC adapter, you must add settings for the pool to the jdbc_customer.properties.in file, which is located in the Gentran Integration Suite /install_dir/properties directory.

In jdbc_customer.properties.in, specify the database server name, port number, database/catalog name, user ID and password. To encrypt your database password, use the encrypt_string.sh or encrypt_string.cmd utility in the bin directory. Then place the encrypted password, prefixed by an encryption indicator, in your properties file.

If the database you want to connect to resides on a database server type that is not the same as the Gentran Integration Suite database server type, you also need to install a JDBC driver using the install3rdparty.sh or install3rdparty.cmd utility.

Caution: There are two jdbc_customer.properties files: jdbc_customer.properties.in, which is the "template" properties file; and jdbc_customer.properties, which is the "live" properties file. It is extremely important to ensure that you add the records to the template file, jdbc_customer.properties.in, not to the live file. Each time you run the setupfiles command in Gentran Integration Suite, all live files are updated with the information contained in their template (.in) files. This means that if you make changes to the live file, jdbc_customer.properties, they are lost each time setupfiles is run. Always make changes to the template file, jdbc_customer.properties.in, and your changes will be maintained.

Select a table and column in your database to use in the test on reserve function. This function causes Gentran Integration Suite to test the database connection using a quickly run query before attempting to use it. This function ensures that idle connections are revived. The column referenced in the query should be of the type varchar and should be at least five characters in length.

Connecting to an External Database

Add the necessary records to the jdbc_customer.properties.in file found in the /install_dir/properties directory.

See the examples that follow this procedure for Oracle 8i/9i, DB2, MS SQL 2000, and Sybase.

The following table contains the parameters needed to add a new database pool to the jdbc_customer.properties.in file:


Parameter	Description
databasePool.driver	JDBC driver class file for the database application.
databasePool.url	Database location (full URL as defined by the Java JDBC standards). Note: For Oracle systems, the last segment in the URL is the Oracle SID (not the System Reference or Tnsnames entry). Note: You can locate the Java JDBC standards on the java.sun.com Web site.
databasePool.user	Username for logging into the database.
databasePool.password	Password for logging into the database.
databasePool.maxconn	Maximum number of database connections for the connection pool.
databasePool.storedProcClassName	Specifies the class that handles stored procedure calls for the JDBC adapter. The following classes are used for the database types: MSSQL, Sybase, and DB2 - com.sterlingcommerce.woodstock.util.frame.jdbc.SybaseStoredProcQuery Oracle 8i/9i - com.sterlingcommerce.woodstock.util.frame.jdbc.OracleNoAppStoredProcQuery Note: The JDBC adapter does not support stored procedures for MySQL.
databasePool.varDataClassName	Each database that Gentran Integration Suite supports handles binary objects differently. This parameter specifies the class used to handle binary data for the database. Enter the correct class for your database: com.sterlingcommerce.woodstock.util.frame.jdbc.DB2ISeriesVarData com.sterlingcommerce.woodstock.util.frame.jdbc.DB2VarData com.sterlingcommerce.woodstock.util.frame.jdbc.DB2ZOSVarData com.sterlingcommerce.woodstock.util.frame.jdbc.JConnectVarData com.sterlingcommerce.woodstock.util.frame.jdbc.MSSQLVarData com.sterlingcommerce.woodstock.util.frame.jdbc.MySQLVarData com.sterlingcommerce.woodstock.util.frame.jdbc.OracleBlobVarData com.sterlingcommerce.woodstock.util.frame.jdbc.OracleVarData
databasePool.catalog	Database name (usually the same as the last segment of the URL)
databasePool.type	Valid values: local remote Important: For Gentran Integration Suite release 4.0, always enter local as the value for this parameter.
databasePool.testOnReserve	Whether to test the connection. Valid values: true false Note: This function causes Gentran Integration Suite to test the database connection before attempting to use it and revives idle connections.
databasePool.testOnReserveQuery	SQL query to use when testing the connection. Select a table and column in your database to use in the test on reserve function. The column referenced in the query should be of the type varchar and should be at least five characters in length. This query needs to be executable by the databasePool.username account and must be a valid SQL query. For example: `SELECT table_name FROM user_tables WHERE table_name=?` where ? must accept a string value. The query does not have to return a value to operate. If the query fails, the Database Pool is not activated.
databasePool.max8177RetryCount	Only used for an Oracle database, this tells the software how many times to retry if it receives an ORA-8177 error in certain situations.
databasePool.dbvendor	Enter the database name: sybase, oracle, mysql, mssql, db2, db2zos, db2iseries, or other vendor name.
databasePool.maxsize	Maximum size of the database pool. This property was previously contained in the poolManager.properties file.This value must not exceed the value specified for the databasePool.maxconn parameter in the jdbc.properties file.
databasePool.initsize	Initial size of the database pool. This property was previously contained in the poolManager.properties file.
databasePool.factory	Always enter the following: com.sterlingcommerce.woodstock.util.frame.jdbc.ConnectionFactory
databasePool.behaviour	Behavior a connection pool exhibits when it runs out of connections. This property replaces the databasePool.onEmpty property in the former poolManager.properties file. Valid values: 0 - The pool simply returns indicating to the software to abort its current action and try again later. This value corresponds to the value return in the databasePool.onEmpty property. 1 - The pool waits the number of milliseconds specified in databasePool.waittime for a connection to be returned before indicating to the software to abort and try again. This value corresponds to the value wait in the databasePool.onEmpty property. 2 - The pool creates a buffered connection (a connection above the size specified in databasePool.maxsize). When using a setting of 2, the maximum number of connections for the pool is the value specified for databasePool.maxsize plus the value specified for databasePool.buffersize connections. This allows connections to be created under heavy demand. This value corresponds to the value new in the databasePool.onEmpty property.
databasePool.buffersize	Number of extra connections that the connection pool can create above the value specified for databasePool.maxsize to improve handling of unanticipated loads on the system. This property is only used if databasePool.behavior is set to 2.
databasePool.waittime	Amount of time (in milliseconds) to wait for a connection to become available before indicating to the software to abort the current action and try again later. This property is only used if databasePool.behavior is set to 1.

Run the setupfiles.sh (UNIX) or setupfiles.cmd (Windows) utility located in the /install_dir/bin directory of the Gentran Integration Suite installation directory. This updates the "live" properties file, jdbc_customer.properties, with the changes from the "template" properties file, jdbc_customer.properties.in.

If the database you want to connect to is not the same vendor as the Gentran Integration Suite database, install the appropriate JDBC driver to access the database server. Use the install3rdParty.sh (UNIX) or install3rdparty.cmd (Windows) utility located in the /install_dir/bin directory of the Gentran Integration Suite installation directory to add the JDBC driver jar file(s). Type install3rdParty on the command line to get a description of the parameters you can specify.

The following examples are for a UNIX environment. The vendor name and version are the first two parameters, along with the location of the zip file containing the JDBC driver files.

For Oracle 9i, install the driver using the following command:


./install3rdParty.sh Oracle 9_2_0_5 -d /usr/local/directory/oracle/9_2_0_5/classes12.zip

For Sybase, install the driver using the following command:


./install3rdParty.sh jconnect 5_5 -d /usr/local/directory/jconnect/5_5/jConnect-5_5.jar

For DB2, install the driver using the following command:


./install3rdParty.sh db2java 7_2 -d /usr/local/directory/db2java.zip

Stop and restart Gentran Integration Suite to use the changed files.

Properties File Examples for Specific Databases

Oracle 8i/9i

For Oracle 8i/9i, enter the following parameters in the jdbc_customer.properties.in file, where databasePool is the name of the pool you are adding. Sample values are italicized; enter the correct value for your environment instead of the sample value. Values that are not italicized are the actual values that you should enter for the parameter:

DB2

databasePool.driver=oracle.jdbc.driver.OracleDriver databasePool.url=jdbc:oracle:thin:@servername:0000:servername databasePool.user=username databasePool.password=password databasePool.catalog=catalogname databasePool.type=local (NOTE: For Gentran Integration Suite release 4.0, this should always be local) databasePool.testOnReserve=true databasePool.testOnReserveQuery=SELECT TestConnection from Connection_tb WHERE TestConnection = ? databasePool.max8177RetryCount=n databasePool.dbvendor=oracle databasePool.buffersize=n databasePool.maxsize=n databasePool.initsize=n databasePool.behaviour=n databasePool.waittime=n databasePool.storedProcClassName= com.sterlingcommerce.woodstock.util.frame.jdbc.OracleNoAppStoredProcQuery databasePool.varDataClassname=com.sterlingcommerce.woodstock.util.frame.jdbc.OracleVarData databasePool.factory=com.sterlingcommerce.woodstock.util.frame.jdbc.ConnectionFactory

For DB2, enter the following parameters in the jdbc_customer.properties.in file, where databasePool is the name of the pool you are adding. Sample values are italicized; enter the correct value for your environment instead of the sample value. Values that are not italicized are the actual values that you should enter for the parameter:

MS SQL 2000

databasePool.driver=COM.ibm.db2.jdbc.net.DB2Driver databasePool.url=jdbc:db2://servername:0000/DB2Database databasePool.user=username databasePool.password=password databasePool.catalog=catalogname databasePool.type=local (NOTE: For Gentran Integration Suite release 4.0, this should always be local) databasePool.testOnReserve=true databasePool.testOnReserveQuery=SELECT TestConnection from Connection_tb WHERE TestConnection = ? databasePool.dbvendor=db2 databasePool.buffersize=n databasePool.maxsize=n databasePool.initsize=n databasePool.behaviour=n databasePool.waittime=n databasePool.storedProcClassName= com.sterlingcommerce.woodstock.util.frame.jdbc.SybaseStoredProcQuery databasePool.varDataClassname=com.sterlingcommerce.woodstock.util.frame.jdbc.DB2VarData databasePool.factory=com.sterlingcommerce.woodstock.util.frame.jdbc.ConnectionFactory

For MS SQL 2000, enter the following parameters in the jdbc_customer.properties.in file, where databasePool is the name of the pool you are adding. Sample values are italicized; enter the correct value for your environment instead of the sample value. Values that are not italicized are the actual values that you should enter for the parameter:

Sybase

databasePool.driver=com.microsoft.jdbc.sqlserver.SQLServerDriver databasePool.url=jdbc:microsoft:sqlserver://servername:0000;DatabaseName=SQLdatabase;SelectMethod=cursor databasePool.user=username databasePool.password=password databasePool.catalog=catalogname databasePool.type=local (NOTE: For Gentran Integration Suite release 4.0, this should always be local) databasePool.testOnReserve=true databasePool.testOnReserveQuery=SELECT TestConnection from Connection_tb WHERE TestConnection = ? databasePool.dbvendor=mssql databasePool.buffersize=n databasePool.maxsize=n databasePool.initsize=n databasePool.behaviour=n databasePool.waittime=n databasePool.storedProcClassName= com.sterlingcommerce.woodstock.util.frame.jdbc.SybaseStoredProcQuery databasePool.varDataClassname=com.sterlingcommerce.woodstock.util.frame.jdbc.MSSQLVarData databasePool.factory=com.sterlingcommerce.woodstock.util.frame.jdbc.ConnectionFactory

For Sybase, enter the following parameters in the jdbc_customer.properties.in file, where databasePool is the name of the pool you are adding. Sample values are italicized; enter the correct value for your environment instead of the sample value. Values that are not italicized are the actual values that you should enter for the parameter: