The DB2 Driver

The BEA WebLogic Type 4 JDBC DB2 driver is available in the WebLogic Server 8.1 Service Pack 2 and later releases. It is not available with the WebLogic Server 8.1 GA and Service Pack 1 releases.

The following sections describe how to configure and use the BEA WebLogic Type 4 JDBC driver for DB2:

 

Database Version Support

The BEA WebLogic Type 4 JDBC driver for DB2 (the "DB2 driver") supports:

  • DB2 Universal Database (UDB) 7.1, 7.2, and 8.1 running on Windows NT, Windows 2000, UNIX, Linux, and Linux/s390 via DRDA
  • DB2 6.1 and DB2 UDB 7.1 running on OS/390 and z/OS via DRDA
  • DB2 UDB V4R5, V5R1, and V5R2 running on iSeries and AS/400

Note: IBM currently uses the term "UDB" in connection with DB2 mainframe platforms. This documentation uses the following terms as described:

    • DB2 UDB" refers to all versions of DB2 running on Windows, UNIX, and Linux/s390 platforms
    • DB2 OS/390" refers to all versions of DB2 on OS/390 and z/OS platforms
    • DB2 iSeries" refers to all versions of DB2 on iSeries and AS/400

 

DB2 Driver Classes

The driver class for the BEA WebLogic Type 4 JDBC DB2 driver is:

XA: weblogic.jdbcx.db2.DB2DataSource

Non-XA: weblogic.jdbc.db2.DB2Driver

Use these driver classes when configuring a JDBC connection pool in your WebLogic Server domain.

 

DB2 URL

To connect to a DB2 database, use the following URL format: 

jdbc:bea:db2://dbserver:port

The DB2 driver also supports the AlternateServers option for connection failover. See Connection Failover with AlternateServers.

 

DB2 Connection Properties

Table 2-1 lists the JDBC connection properties supported by the DB2 driver, and describes each property. You can use these connection properties in a JDBC connection pool configuration in your WebLogic Server domain. To specify a property, use the following form in the JDBC connection pool configuration: 

property=value

Note: All connection property names are case-insensitive. For example, Password is the same as password.

Property

Description

AddToCreateTable OPTIONAL A string that is automatically added to all Create Table statements. This field is primarily for users who need to add an "in database" clause.
AlternateIDOPTIONAL Sets the default DB2 schema used by unqualified SQL identifiers to the specified value. The value must be a valid DB2 schema.
BatchPerformanceWorkaroundOPTIONAL {true | false}. For DB2 UDB 8.1 and DB2 OS/390 8.1, the native DB2 batch mechanism is used. This property determines whether certain restrictions are enforced to facilitate data conversions.

  • When set to false, the methods used to set the parameter values of a batch operation performed using a PreparedStatement must match the database data type of the column the parameter is associated with. This is because DB2 servers do not perform implicit data conversions.

  • When set to true, this restriction is removed; however, parameter sets may not be executed in the order they were specified.
The default is false.See Performance Workaround for Batch Inserts and Updates for more information.

Note: For connection pools used as a JMS JDBC store that use the WebLogic Type 4 JDBC driver for DB2, the BatchPerformanceWorkaround property must be set to true.

CollectionIdOPTIONAL The collection (group of packages) to which the package is bound.This property is ignored for DB2 UDB.The default is NULLID.
CreateDefaultPackageOPTIONAL {true | false}. Determines whether the default package should be created. For DB2 OS/390 and DB2 iSeries, the package is created in the collection specified by the CollectionId property. This would be used if the package does not yet exist. For more information about creating DB2 packages, see Creating a DB2 Package.The default is false.
DynamicSectionsOPTIONAL Specifies the number of statements that the DB2 driver package can prepare for a single user. The default is 200.
DatabaseName The name of the database to which you want to connect (used with UDB).
ForceFixRowOPTIONAL {true | false}. Forces the driver to fetch rows using the Fix Row Protocol, even if the package specifies Limited Block Protocol.The default is false.
LocationName The name of the DB2 location that you want to access (used with OS/390 and iSeries).
LoginTimeoutOPTIONAL The maximum time in seconds that attempts to create a database connection will wait. A value of 0 specifies that the timeout is the default system timeout if there is one; otherwise it specifies that there is no timeout.
Password A case-sensitive password used to connect to your DB2 database. A password is required only if security is enabled on your database. If so, contact your system administrator to get your password.
PortNumberOPTIONAL The TCP port on which the database server listens for connections. The default is 50000.
ReplacePackageOPTIONAL {true | false}. Specifies whether the current bind process should replace an existing DB2 package. On DB2 UDB, this property must be used in conjunction with CreateDefaultPackage. For more information about creating DB2 packages, see Creating a DB2 Package.The default is false.
ServerName The name or IP address of the database server.
StripNewlinesOPTIONAL {true | false}. Specifies whether new-line characters in a SQL statement are sent to the DB2 server. When StripNewlines=true, the DB2 driver removes all new-line characters from SQL statements. The default is true.
User The case-sensitive user name used to connect to your DB2 database. A user name is required only if security is enabled on your database. If so, contact your system administrator to get your user name.
WithHoldCursorsOPTIONAL {true | false}. Determines whether the cursor stays open on commit and rollback - either DB2 closes all open cursors (Delete cursors) after a commit or rollback, or leaves them open (Preserve cursors). When set to true, the cursor behavior is Preserve. When set to false, the cursor behavior is Delete. The default is true.

 

Sample Connection Pool Configuration

Table 2-2 lists configuration attributes for a sample WebLogic Server connection pool that uses the non-XA version of the WebLogic Type 4 DB2 JDBC driver. Table 2-3 lists configuration attributes for a sample WebLogic Server connection pool that uses the XA version of the driver.

Attribute

Value

URL jdbc:bea:db2://host:port
Driver Class Name weblogic.jdbc.db2.DB2Driver
Properties user=username
PortNumber=port
ServerName=host
DatabaseName=dbname
batchPerformanceWorkaround=true
Password password
Target server or cluster name

Attribute

Value

URL jdbc:bea:db2://host:port
Driver Class Name weblogic.jdbcx.db2.DB2DataSource
Properties user=username
PortNumber=port
ServerName=host
DatabaseName=dbname
batchPerformanceWorkaround=true
SupportsLocalTransaction true (required only for local transactions)
Password password
Target server or cluster name

 

Creating a DB2 Package

A DB2 package is a control structure on the DB2 server produced during program preparation that is used to execute SQL statements. The DB2 driver automatically creates all DB2 packages required at connection time. If a package already exists, the driver uses the existing package to establish a connection. Note that the initial connection may take a few minutes because of the number and size of the packages that must be created for the connection. Subsequent connections do not incur this delay.

By default, DB2 packages created by the DB2 driver contain 200 dynamic sections and are created in the NULLID collection. In most cases, you do not need to create DB2 packages because the DB2 driver automatically creates them at connection time. If required, you can create DB2 packages in either of the following ways:

Note: Your user ID must have CREATE PACKAGE privileges on the database, or your database administrator must create packages for you.

 

Creating a DB2 Package Using dbping

To create a package on the DB2 server with the WebLogic Type 4 JDBC DB2 driver, you can use the WebLogic Server dbping utility. The dbping utility is used to test the connection between your client machine and a DBMS via a JDBC driver. Because the WebLogic Type 4 JDBC DB2 driver automatically creates a DB2 package if one does not already exist, running this utility creates a default DB2 package on the DB2 server.

For details about using the dbping utility to create a DB2 package, see Creating a DB2 Package with dbping.

 

Creating a DB2 Package Using Connection Properties

You can create a DB2 package automatically by specifying specific connection properties in the initial connection URL. Table 2-4 lists the connection properties you should use in your initial connection URL when you create a DB2 package:

Note: This method is not recommended for use with WebLogic Server JDBC connection pools because every connection in the connection pool uses the same URL and connection properties. When a JDBC connection pool with multiple connections is created, the package would be recreated when each database connection is created.

Property

Database

DatabaseName=database_name DB2 UDB only
LocationName=location_name DB2 OS/390 and iSeries only
CollectionId=collection_name DB2 OS/390 and iSeries only
CreateDefaultPackage=TRUE DB2 UDB, OS/390, and iSeries
ReplacePackage=TRUE DB2 UDB only

Using CreateDefaultPackage=TRUE creates a package with a default name. If you use CreateDefaultPackage=TRUE, and you do not specify a CollectionId, the NULLID CollectionId is created.

Note: On DB2 UDB, use ReplacePackage=TRUE in conjunction with CreateDefaultPackage to create a new package; however, if a package already exists, it will be replaced when using ReplacePackage=TRUE.

 

Notes About Increasing Dynamic Sections in the DB2 Package

In some cases, you may need to create DB2 packages with more than the default number of dynamic sections (200). Consider the following information if your application requires DB2 packages with a large number of dynamic sections:

  • Creating DB2 packages with a large number of dynamic sections may exhaust certain server resources. In particular, you may need to increase the database parameter PCKCACHE_SZ to allow the larger packages to be created.
  • The creation of more dynamic sections will slow down the initial creation of the DB2 package.
  • Using DB2 packages with a large number of dynamic sections may impact application performance. If a small number of sections are in use at one time, there will be no impact on the application. If a large number of sections are in use at one time, the performance of the application may decrease because the database will expend resources to check all open sections for locks.
  • As the number of open sections increases, so does the likelihood that a deadlock situation may occur.
  • If your application is mostly executing select statements, it is best to operate in the default mode of automatically committing the database. Dynamic sections are not freed in the DB2 package until the database is committed even if the statements are closed in the application. In this mode the database will commit every time a SQL statement is executed and free all of the sections that were opened. If you need to operate in a manual commit mode, then it is advisable to commit the database as often as possible to ensure that all server resources are freed in a timely manner.
  • Statements cached in the WebLogic Server prepared statement cache will keep sections in use so that the prepared statements can be reused.
  • The DB2 server has a limit on dynamic sections. It is possible to try to create more sections than the server will allow you to create.

 

Data Types

Table 2-5 lists the data types supported by the DB2 driver and how they are mapped to JDBC data types.

DB2 Data Type

JDBC Data Type

Bigint BIGINT1
Blob BLOB 2
Char CHAR
Char for Bit Data BINARY
Clob CLOB
Date DATE
Decimal DECIMAL
Double DOUBLE
Float FLOAT
Integer INTEGER
Long Varchar LONGVARCHAR
Long Varchar for Bit Data LONGVARBINARY
Numeric NUMERIC
Real REAL
Rowid3 VARBINARY
Smallint SMALLINT
Time TIME
Timestamp TIMESTAMP
Varchar VARCHAR
Varchar for Bit Data VARBINARY

1BIGINT is supported only for DB2 UDB 8.1.2BLOB is supported only for DB2 UDB 8.1, DB2 OS/390, and DB2 iSeries V5R2.3Rowid is supported only for DB2 OS/390 and DB2 iSeries V5R2.



 

SQL Escape Sequences

See SQL Escape Sequences for JDBC for information about SQL escape sequences supported by the DB2 driver.

 

Isolation Levels

The DB2 driver supports the following isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. The default is Read Committed.

The DB2 driver maps JDBC transaction isolation levels to the appropriate DB2 transaction isolation levels as listed in Table 2-6.

JDBC Isolation Level

DB2 Isolation Level

Read Committed Cursor Stability
Read Uncommitted Uncommitted Read
Repeatable Read Read Stability
Serializable Repeatable Read

 

Using Scrollable Cursors

The DB2 driver supports scroll-insensitive result sets and updatable result sets.

Note: When the DB2 driver cannot support the requested result set type or concurrency, it automatically downgrades the cursor and generates one or more SQLWarnings with detailed information.

 

JTA Support

To use JDBC distributed transactions through JTA with the DB2 driver, DB2 UDB 8.1 for Windows, UNIX, or Linux is required.

 

Large Object (LOB) Support

Retrieving and updating Blobs is supported by the DB2 driver only with DB2 UDB 8.1, DB2 OS/390, and DB2 iSeries V5R2.

Retrieving and updating Clobs is supported by the DB2 driver. The DB2 driver supports Clobs up to a maximum of 2 GB with DB2 UDB 8.1, DB2 OS/390, and DB2 iSeries V5R2; it supports Clobs up to a maximum of 32 KB with all other DB2 databases.

 

Performance Workaround for Batch Inserts and Updates

For DB2 UDB 8.1 and DB2 OS/390, the DB2 driver uses the native DB2 batch mechanism. By default, the methods used to set the parameter values of a batch performed using a PreparedStatement must match the database data type of the column with which the parameter is associated.

DB2 servers do not perform any implicit data conversions, so specifying parameter values that do not match the column data type causes the DB2 server to generate an error. For example, to set the value of a Blob parameter using a stream or byte array when the length of the stream or array is less than 32K, use the setObject method and specify the target SQL type as BLOB; you cannot use setBinaryStream or setBytes methods.

To remove this restriction, set the BatchPeformanceWorkaround connection property to true (see DB2 Connection Properties). When set to true, the method-type restriction is not enforced. For example, you can use the setBinaryStream or setBytes methods to set the value of a Blob parameter regardless of the length of the stream or array; however, the parameter sets may not be executed in the order they were specified.

Note: When you create a connection pool in the Administration Console, the Administration Console sets the BatchPeformanceWorkaround connection property to true by default.

Note: For connection pools used as a JMS JDBC store that use the WebLogic Type 4 JDBC driver for DB2, the BatchPerformanceWorkaround property must be set to true.

 

Auto-Generated Keys Support

The DB2 driver supports retrieving the values of auto-generated keys. How you return those values depends on whether you are using an Insert statement that contains parameters:

When using an Insert statement that does not contains any parameters, the DB2 driver supports the following form of the Statement.execute and Statement.executeUpdate methods to inform the driver to return the values of auto-generated keys: 

Statement.execute (String sql, int autoGeneratedKeys) 

Statement.executeUpdate (String sql, int autoGeneratedKeys)

When using an Insert statement that contains parameters, the DB2 driver supports the following form of the Connection.prepareStatement method to inform the driver to return the values of auto-generated keys: 

Connection.prepareStatement (String sql, int autoGeneratedKeys)

The application fetches the values of generated keys from the driver using the Statement.getGeneratedKeys() method.

Skip navigation bar  Back to Top Previous Next