Java Database Connectivity (JDBC) Adapter (V5.2.0 - 5.2.2)

The Java Database Connectivity (JDBC) adapter enables the Translation service to communicate with JDBC-compliant databases. The adapter updates or retrieves data from a JDBC-compliant database as part of a business process within the application.

The following table provides an overview of the JDBC adapter:

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 All supported application platforms
Related services No
Application requirements To use the JDBC adapter, the jdbc_customer.properties file 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 to the application.
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 Returned status values:
  • Success – JDBC adapter finished successfully.
  • Warning – JDBC adapter finished but with warnings.
  • Error – JDBC adapter finished, but with errors.
Restrictions None

How the JDBC Adapter Works

The queries you define in your map determine the data that to be 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

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:
  1. The JDBC adapter receives a map name from the business process.
  2. The adapter starts the Translation service and passes the name of the map to the translator.
  3. Using the map, the translator creates the SQL commands and sends them to an SQL database.
  4. 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.
  5. 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 following steps summarize the JDBC adapter data flow for this example:
  1. The adapter receives the name of the map from the business process.
  2. The adapter starts the Translation service and passes it the map name.
  3. The Translation service executes the map and generates SQL queries to submit to the database.
  4. The Translation service submits the SQL queries to the database.
  5. 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.
  6. The Translation service passes the translated response to the adapter.
  7. The adapter sends the customer information to the next step in the business process, the File System adapter.
  8. The File System adapter writes the retrieved customer information to disk for the Sales department.
  9. The application performs the next activity in the business process.

Implementing the JDBC Adapter

To implement the JDBC adapter, complete the following tasks:
  1. Create a JDBC adapter configuration. For information, see Managing Services and Adapters.
  2. Configure the JDBC adapter. For information, see Configuring the JDBC Adapter.
  3. Install the appropriate database drivers, if necessary, on the same computer where the Map Editor was installed.
  4. Create an ODBC data source. For more information about ODBC, access www.msdn.microsoft.com and locate the ODBC Programming Reference documentation.
  5. Set up a connection to an external database. For information, see Setting Up a Connection to an External Database.
  6. Create either an input or output map using the Map Editor.
  7. Check in the map for versioning control.
  8. Use the JDBC adapter in a business process.

Configuring the JDBC Adapter

Application Configuration

The following table describes the fields used to configure the JDBC adapter in your application:

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 Managing Services and Adapters.
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.
Business Process Select the business process that this adapter will start. Only applicable when Start a new business process is set to Yes.
  • EDI Output Tag Delimiter (edi_output_tag_delimiter)
  • 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)
 Values to change delimiters specified in the map, if the output side of the map is in EDI format. Optional.
Map Name(map_name) Map name used by the translator.
Output Report To Process Data (output_report_to_process_data) Whether to output the report to process data. Valid values are:
  • Yes – Output the report to process data.
  • No – Do no output the report to process data.
Sender Identity ID (SenderIdentityID) Used by map to access trading partner codelists.
Receiver Identity ID (ReceiverIdentityID) Used by map to access trading partner codelists.
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 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 That Must Be Added in BPML

The following parameters can be used with the JDBC adapter by editing the BPML.

Parameter Description
FromSchema Used to enable manipulation of a database schema prefix within the SQL Table/View or SQL Statement of a map. This parameter is required when overriding schema names within one or more SQL Statement fields. If the FromSchema and ToSchema parameters are not supplied, then no schema name substitution is performed.
Note: The schema search/replace is case-sensitive.
ToSchema Used to enable manipulation of a database schema prefix within the SQL Table/View or SQL Statement of a map.
Note: The schema search/replace is case-sensitive.
If the FromSchema and ToSchema parameters are not supplied, then no schema name substitution is performed. If the ToSchema parameter is supplied and contains a non-empty value, then any matching schema names are changed at translation time to use the supplied ToSchema schema value as follows:
  • For an SQL Statement, only schema names that match the FromSchema value will be substituted. The FromSchema parameter is required—otherwise, no schema values are substituted. To match and substitute more than one value pair, the FromSchema and ToSchema parameter strings can be delimited with an @ sign. For example:
    FromSchema=”from1@from2”
ToSchema=”to1@to2”

    In this example, any schema names matching “from1” are changed to “to1,” and any schema names matching “from2” are changed to “to2.”

For convenience, you can supply fewer ToSchema fragments than FromSchema fragments, and when there is no corresponding ToSchema fragment, the last fragment in the ToSchema string is used. For example:
FromSchema=”from1@from2@from3”ToSchema=”to”
In this example, any schema names matching “from1,” “from2,” or “from3” will be changed to “to.”
  • For an SQL Table/View, the FromSchema parameter is optional. If it is not supplied, all schema names are changed to the supplied ToSchema value. If it is supplied, the substitution occurs in the same way as it does for an SQL Statement. If the translator property sql.driver.useIdentifierQuoteString is set to True within customer_overrides.properties, then matching and substitution occurs with quoted schema names.
  • If the ToSchema parameter is supplied but is empty (equal to ““ (two double quotation marks) or ‘' (two single quotation marks)), then any matching schema names contained in the map are removed at translation time.

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 the application for internal use (see the System Requirements documentation), or other JDBC-compliant databases, such as Sybase.

Adding New Database Pools

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 application's /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.

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 “packaged” properties file. It is extremely important to ensure that you add the records to the template file, jdbc_customer.properties.in, not to the packaged file. Each time you run the setupfiles command in the application, all the packaged files are updated with the information contained in their template (.in) files. This means that if you make changes to the packaged file, jdbc_customer.properties, they are lost each time setupfiles runs. Always make changes to the template file, jdbc_customer.properties.in, and your changes will be maintained.

If the database you want to connect to reside on a database server type that is not the same as the application database server type, you must also install a JDBC driver using the install3rdparty.sh or install3rdparty.cmd utility.

Select a table and column in your database to use in the test on reserve function. This function causes the application 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.

Note: To remove a database pool, verify that all the pool properties are removed for the pool you want to delete, including, jdbc.properties_*_ext, jdbc_customer.properties, customer_overrides.properties files and system_overrides.properties.

Connecting to an External Database

To connect to an external database:
  1. Add the necessary records to the jdbc_customer.properties.in file found in the /install_dir/properties directory.
    Note: If invalid data (like ABC or 13.45) is entered in a pool setting, the setting uses its default value.

    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 http://www.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.GenericStoredProcQuery
    • Oracle 8i/9i –
      com.sterlingcommerce.woodstock.util.frame.jdbc.OracleNoAppStoredProcQuery
    Note: The JDBC adapter does not support stored procedures for DB2/ iSeries, DB2/zOS, and MySQL.
    databasePool.varDataClassName Each database that the application 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 (default)
    databasePool.testOnReserve Whether to test the connection. Valid values:
    • true
    • false
    Note: This function causes the application 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.testOnReserveInterval The minimum number of milliseconds between running testOnReserve on the same connection. The default value is 60000. Valid values:
    • 0 – No interval and current interval is used.
    • <= 0 – No interval.
    • > 0 – The minimum number of milliseconds between running testOnReserve on the same connection.
    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.lifespan The number of milliseconds a connection will live in a given pool before it needs to be removed.
    • 0 – (Default) No timeout.
    • <= 0 – No timeout.
    • > 0 – Number of milliseconds that a connection stay in pool.
    databasePool.idletimeout The number of milliseconds a connection can stay idle in a given pool before it needs to be removed. The default value is 86400000. Valid values:
    • 0 – No timeout.
    • <= 0 – No timeout.
    • > 0 – Number of milliseconds that a connection stay in pool.
    databasePool.housekeepinginterval The minimum number of milliseconds between running the housekeeping task to clean out idle connections. Valid values are any positive number. The default value is 3600000 milliseconds (1 hour). Any number less than 3600000 will cause the default of 3600000 milliseconds to be used.
    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.
  2. Run the setupfiles.sh (UNIX) or setupfiles.cmd (Windows) utility located in the /install_dir/bin directory of the application installation directory. This updates the “packaged” properties file, jdbc_customer.properties, with the changes from the “template” properties file, jdbc_customer.properties.in.
  3. If the vendor for the connection database is not the same vendor as the database vendor used for the application 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 application's 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 DB2, install the driver using the following command:

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

    • To install the jConnect driver for Sybase, refer to Installing a Sybase Driver.
  4. Stop and restart the application to use the changed files.

Installing a Sybase Driver

Install the jConnect driver for Sybase using the following procedure:
  1. Download jConnect-5_5.zip from the Sybase web site.
  2. Run the following command:

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

    • If this command succeeds, you are finished with this procedure.
    • If the application reports in the system log that the driver could not be registered because the driver class cannot be found, continue with the procedure. Use the following steps to remove existing references to jConnect.
  3. Stop the application.
  4. Change your directory to install_dir/jar.
  5. Delete any existing folders referencing jConnect.
  6. Change your directory to install_dir/properties.
  7. Open the files dynamicclasspath.cfg and dynamicclasspath.cfg.in. Delete any lines referencing jConnect, and save the files.
  8. Create the following temporary directory:

    install_dir/bin/jconnect

  9. Extract only the jar files from jConnect-5_5.zip to this directory.
  10. Run the following command:

    install3rdparty jconnect 5_5 -d install_dir/bin/jconnect/*.jar

  11. Check install_dir/jar/jconnect/5_5/your_platform to make sure that six jar files have been copied successfully.
  12. Open the dynamicclasspath.cfg file in install_dir/properties and check that the following entries are included:
    VENDOR_JAR=/install_dir/jar/jconnect/5_5/your_platform/jconn2.jar
VENDOR_JAR=/install_dir/jar/jconnect/5_5/your_platform/jconn2d.jar
VENDOR_JAR=/install_dir/jar/jconnect/5_5/your_platform/jisql.jar
VENDOR_JAR=/install_dir/jar/jconnect/5_5/your_platform/jTDS2.jar
VENDOR_JAR=/install_dir/jar/jconnect/5_5/your_platform/jTDS2d.jar
VENDOR_JAR=/install_dir/jar/jconnect/5_5/your_platform/ribo.jar
  13. Open the customer.jdbc.properties.in file in install_dir/properties and check that the following entries are included:
    
jconnectPool.driver=com.sybase.jdbc2.jdbc.SybDriver
jconnectPool.url=jdbc:sybase:Tds:your
Hostname:4100/your DB
jconnectPool.user=your user name
jconnectPool.password=your password
jconnectPool.catalog=your  database
jconnectPool.type=local
jconnectPool.testOnReserve=true 
jconnectPool.testOnReserveQuery=your Test On Reserve Query
jconnectPool.dbvendor=Sybase
jconnectPool.buffersize=500
jconnectPool.maxsize=28 
jconnectPool.initsize=1
jconnectPool.behaviour=2
jconnectPool.waittime=1000
jconnectPool.storedProcClassName= com.sterlingcommerce.woodstock.
      util.frame.jdbc.SybaseStoredProcQuery
jconnectPool.varDataClassName=com.sterlingcommerce.woodstock.util.
      frame.jdbc.JConnectVarData
jconnectPool.factory=com.sterlingcommerce.woodstock.util.frame.jdbc.
       ConnectionFactory
  14. Save the customer.jdbc.properties.in file and run the following command:
    install_dir/bin/setupfiles.sh

This procedure should result in a successful connection to your Sybase database. However, if the database has been configured as character set ROMAN8, it is likely that you will see the following message in the application system log, because of a limitation in the Sybase driver:

java.sql.SQLWarning: JZ0IB: The server's
default charset of roman8 does not map to an encoding that is available
in the client Java environment. Because jConnect will not be able
to do client-side conversion, the connection is unusable and is
being closed. Try using a later Java version or try including your
Java installation's i18n.jar or charsets.jar file in the classpath

One resolution of this issue is to configure the primary Adaptive Server with a default character set that maps to one of the character sets supported by jConnect for JDBC (for example, UTF-8). For more details, refer to the Sybase documentation.

Another resolution of this issue is to use the open source jTDS driver from Sourceforge (sourceforge.net). To install this driver, follow these instructions:
  1. Stop the application.
  2. Remove references to jConnect as described previously.
  3. Copy the jtds-1.2.jar file to an accessible directory on the application machine.
  4. Run the following command:

    install_dir/bin/Install3rdparty.sh jTDS 1_2 - jar absolutePath/jtds-1.2.jar

  5. Check that the dynamicclasspath.cfg file has picked up this change. For example, /install_dir/jar/jTDS/1_2/your_platform/jtds-1.2.jar.
  6. Edit the jdbc_customer.properties.in file. The definition of the pool should be similar to the following example:
    
jTDSPool.driver=net.sourceforge.jtds.jdbc.Driver
jTDSPool.url=jdbc:jtds:sybase://your Hostname:4100/answer
jTDSPool.user=your user name
jTDSPool.password=your password
jTDSPool.catalog=your database
jTDSPool.type=local
jTDSPool.testOnReserve=false
#jTDSPool.testOnReserveQuery=your Test On Reserve Query
jTDSPool.dbvendor=jtds
jTDSPool.buffersize=50
jTDSPool.maxsize=20
jTDSPool.initsize=5
jTDSPool.behaviour=2
jTDSPool.storedProcClassName=com.sterlingcommerce.woodstock.util.
       frame.jdbc.SybaseStoredProcQuery
jTDSPool.varDataClassName=com.sterlingcommerce.woodstock.util.
       frame.jdbc.GenericVarData
jTDSPool.factory=com.sterlingcommerce.woodstock.util.frame.jdbc.
       ConnectionFactory
  7. Restart the application.

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:

databasePool.driver=oracle.jdbc.OracleDriver 
databasePool.url=jdbc:oracle:thin:@servername:0000:servername 
databasePool.user=username 
databasePool.password=password 
databasePool.catalog=catalogname 
databasePool.type=local 
databasePool.testOnReserve=true 
databasePool.testOnReserveQuery=SELECT TestConnection from 
      Connection_tb WHERE TestConnection = ? 
databasePool.testOnReserveInterval=60000 
databasePool.max8177RetryCount=n 
databasePool.dbvendor=oracle 
databasePool.buffersize=n 
databasePool.maxsize=n 
databasePool.initsize=n 
databasePool.behaviour=n 
databasePool.lifespan=0 
databasePool.idletimeout=86400000 
databasePool.housekeepinginterval=3600000 
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

DB2

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:

Note: The JDBC adapter does not support stored procedures for DB2/iSeries and DB2/zOS.
databasePool.driver=com.ibm.as400.access.AS400JDBCDriver 
databasePool.url=jdbc:as400://serverName/DB2Database;translate 
      binary=true;transaction isolation=none; 
databasePool.catalog=DB2Database 
databasePool.varDataClassName=com.sterlingcommerce.woodstock.util.
      frame.jdbc.DB2ISeriesVarData 
databasePool.user=username 
databasePool.password=password 
databasePool.maxconn=20 
databasePool.testOnReserve=true 
databasePool.testOnReserveQuery=SELECT SI_VERSION from SI_VERSION 
     where SI_VERSION = ? 
databasePool.testOnReserveInterval=60000 
databasePool.blobPageSize=1024000 
databasePool.compressBlob=true 
databasePool.cacheps=true 
databasePool.dbvendor=db2iSeries 
databasePool.buffersize=500 
databasePool.maxsize=20 
databasePool.initsize=0 
databasePool.factory=com.sterlingcommerce.woodstock.util.frame.
     jdbc.ConnectionFactory 
databasePool.behaviour=2 
databasePool.lifespan=0 
databasePool.idletimeout=86400000 
databasePool.housekeepinginterval=3600000 
databasePool.waittime=1000

MS SQL 2000

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:

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 
databasePool.testOnReserve=true 
databasePool.testOnReserveQuery=SELECT TestConnection from 
     Connection_tb WHERE TestConnection = ? 
databasePool.testOnReserveInterval=60000 
databasePool.dbvendor=mssql 
databasePool.buffersize=n 
databasePool.maxsize=n 
databasePool.initsize=n 
databasePool.behaviour=n 
databasePool.lifespan=0 
databasePool.idletimeout=86400000 
databasePool.housekeepinginterval=3600000 
databasePool.waittime=n  
databasePool.storedProcClassName=com.sterlingcommerce.woodstock.util.
     frame.jdbc.GenericStoredProcQuery 
databasePool.varDataClassName=com.sterlingcommerce.woodstock.util.
      frame.jdbc.MSSQLVarData 
databasePool.factory=com.sterlingcommerce.woodstock.util.frame.jdbc.
      ConnectionFactory

Sybase

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:

databasePool.driver=com.sybase.jdbc2.jdbc.SybDriver 
databasePool.url=jdbc:sybase:Tds:servername:0000/SybaseDB 
databasePool.user=username 
databasePool.password=password 
databasePool.catalog=catalogname 
databasePool.type=local 
databasePool.testOnReserve=true 
databasePool.testOnReserveQuery=SELECT TestConnection from 
      Connection_tb WHERE TestConnection = ? 
databasePool.testOnReserveInterval=60000 
databasePool.dbvendor=Sybase 
databasePool.buffersize=n 
databasePool.maxsize=n 
databasePool.initsize=n 
databasePool.behaviour=n 
databasePool.lifespan=0 
databasePool.idletimeout=86400000 
databasePool.housekeepinginterval=3600000 
databasePool.waittime=n  
databasePool.storedProcClassName=com.sterlingcommerce.woodstock.util.
      frame.jdbc.SybaseStoredProcQuery 
databasePool.varDataClassName=com.sterlingcommerce.woodstock.util.
     frame.jdbc.JConnectVarData 
databasePool.factory=com.sterlingcommerce.woodstock.util.frame.
     jdbc.ConnectionFactory

Encrypting Your Database Password

To encrypt your database password:
  1. Use encrypt_string.sh (in Windows, encrypt_string.cmd).
  2. When prompted, enter your external database password.

    The script returns the encrypted value for your password.

  3. Place the encrypted password in your jdbc.properties.in file entry (see step 2 in the previous procedure), prefixing the encrypted password with ENCRYPTED.

    For example, myDSN.password=ENCRYPTED:rO0ABXQABkRFU2VkZXVy.