Type 4 JDBC Drivers

The DB2 Driver

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 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 data source in your WebLogic Server domain.

DB2 URL

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

DB2 Connection Properties

Table 3-1 lists the JDBC connection properties supported by the DB2 driver, and describes each property. You can use these connection properties in a JDBC data source configuration in your WebLogic Server domain.

To specify a property, use the following form in the JDBC data source configuration:

   property=value

Performance Considerations

Setting the following connection properties for the DB2 driver as described in the following list can improve performance for your applications:

CatalogIncludesSynonyms

The DatabaseMetaData.getColumns method is often used to determine characteristics about a table, including the synonym, or alias, associated with a table. If your application accesses DB2 v7.1 or v7.2 for Linux/UNIX/Windows, DB2 for z/OS, or DB2 for iSeries and your application does not use database table synonyms, the driver can improve performance by ignoring this information. The driver always returns synonyms for the DatabaseMetaData.getColumns() method when accessing DB2 v8.1 and v8.2 for Linux/UNIX/Windows.

CatalogSchema

To improve performance, views of system catalog tables can be created in a catalog schema other than the default. The DB2 driver can access the views of catalog tables if this property is set to the name of the schema containing the views. The default catalog schema is SYSCAT for DB2 for Linux/UNIX/Windows, SYSIBM for DB2 for z/OS, and QSYS2 for DB2 for iSeries.

To ensure that catalog methods function correctly, views for specific catalog tables must exist in the specified schema. The views that are required depend on your DB2 database. See Using a Non-Default Schema for Catalog Methods for views for catalog tables that must exist in the specified schema.

InsensitiveResultSetBufferSize

To improve performance when using scroll-insensitive result sets, the driver can cache the result set data in memory instead of writing it to disk. By default, the driver caches 2 MB of insensitive result set data in memory and writes any remaining result set data to disk. Performance can be improved by increasing the amount of memory used by the driver before writing data to disk or by forcing the driver to never write insensitive result set data to disk. The maximum cache size setting is 2 GB.

MaxPooledStatements

To improve performance, the driver's own internal prepared statement pooling should be enabled when the driver does not run from within an application server or from within another application that does not provide its own prepared statement pooling. When the driver's internal prepared statement pooling is enabled, the driver caches a certain number of prepared statements created by an application. For example, if the MaxPooledStatements property is set to 20, the driver caches the last 20 prepared statements created by the application. If the value set for this property is greater than the number of prepared statements used by the application, all prepared statements are cached.

ResultSetMetaDataOptions

By default, the DB2 driver skips the additional processing required to return the correct table name for each column in the result set when the ResultSetMetaData.getTableName() method is called. Because of this, the getTableName() method may return an empty string for each column in the result set. If you know that your application does not require table name information, this setting provides the best performance.

See ResultSet Metadata Support for more information about returning ResultSet metadata.

SendStreamAsBlob

If the large binary objects you insert or update are stored as Blobs, performance can be improved by sending the binary stream as Blob data. In this case, this property should be set to true.

StripNewLines

If you know that the SQL statements used in your application do not contain newline characters, the driver can improve performance by omitting the parsing required to remove them. In this case, the StripNewlines property should be set to false.

UseCurrentSchema

If your application needs to access tables and views owned only by the current user, performance of your application can be improved by setting this property to true. When this property is set to true, the driver returns only tables and views owned by the current user when executing getTables() and getColumns() methods. Setting this property to true is equivalent to passing the user ID used on the connection as the schemaPattern argument to the getTables() or getColumns() call.

Setting the locationName on AS/400

When connecting to a DB2 database running on AS/400, you must set the locationName property:

The following is an excerpt of the Java client:

   ...

   Properties props = new Properties();

   props.put("user",     user);

   props.put("password", password);

   ...

   myDriver = (Driver)Class.forName("weblogic.jdbc.db2.DB2Driver").newInstance();

   conn = myDriver.connect("jdbc:bea:db2://10.1.4.1:446;LocationName=S10B757B", props);

   stmt = conn.createStatement();

   stmt.execute("select * from MYDATABASE.MYTABLE");

   rs = stmt.getResultSet();

   ...

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.

By default, DB2 packages created by the DB2 driver contain 200 dynamic sections and are created in the NULLID collection (or library). 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:

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 3-2 lists the connection properties you should use in your initial connection URL when you create a DB2 package:

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.

Example for DB2 for Linux/UNIX/Windows:

The following URL creates DB2 packages with 400 dynamic sections. If any DB2 packages already exist, they will be replaced by the new ones being created.

   jdbc:bea:db2://server1:50000;DatabaseName=SAMPLE;
   CreateDefaultPackage=TRUE;ReplacePackage=TRUE;DynamicSections=400

Example for DB2 for z/OS and iSeries:

The following URL creates DB2 packages with 400 dynamic sections.

   jdbc:bea:db2://server1:50000;LocationName=SAMPLE;
   CreateDefaultPackage=TRUE;DynamicSections=400

Notes About Increasing Dynamic Sections in the DB2 Package

A dynamic section is the actual executable object that contains the logic needed to satisfy a dynamic SQL request. These sections are used for handles and prepared statements and the associated result sets.

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:

Data Types

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

See GetTypeInfo for more information about data types.

Using a Non-Default Schema for Catalog Methods

To ensure that catalog methods function correctly when the CatalogSchema property is set to a schema other than the default schema, views for the catalog tables listed in Table 3-4 must exist in the specified schema. The views that are required depend on your DB2 database.

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 isolation levels listed in Table 3-5. JDBC isolation levels are mapped to the appropriate DB2 transaction isolation levels as shown. The default isolation level is Read Committed.

Using Scrollable Cursors

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

JTA Support

To use distributed transactions through JTA with the DB2 driver, DB2 v8.1 or v8.2 for Linux/UNIX/Windows is required.

Large Object (LOB) Support

Retrieving and updating Blobs is supported by the DB2 driver with the following databases:

Retrieving and updating Clobs is supported by the DB2 driver with all supported DB2 databases. The DB2 driver supports Clobs up to a maximum of 2 GB with the following DB2 databases:

The DB2 driver supports retrieving and updating Clobs up to a maximum of 32 KB with all other supported DB2 databases.

Retrieving and updating DBClobs is supported by the DB2 driver with the following databases:

Performance Workaround for Batch Inserts and Updates

For DB2 v8.1 and v8.2 for Linux/UNIX/Windows, DB2 for z/OS, and DB2 for iSeries, 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 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 32 KB, you must use the setObject() method and specify the target JDBC type as BLOB; you cannot use the setBinaryStream() or setBytes() methods.

To remove the method-type restriction, set the BatchPerformanceWorkaround property to true. 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.

Parameter Metadata Support

The DB2 driver supports returning parameter metadata as described in this section.

Insert and Update Statements

The DB2 driver supports returning parameter metadata for all types of SQL statements with the following DB2 databases:

For all other supported DB2 databases, the DB2 driver supports returning parameter metadata for the following forms of Insert and Update statements:

where operator is any of the following SQL operators: =, <, >, <=, >=, and <>.

Select Statements

The DB2 driver supports returning parameter metadata for all types of SQL statements with the following DB2 databases:

For all other supported DB2 databases, the DB2 driver supports returning parameter metadata for Select statements that contain parameters in ANSI SQL 92 entry-level predicates, for example, such as COMPARISON, BETWEEN, IN, LIKE, and EXISTS predicate constructs. Refer to the ANSI SQL reference for detailed syntax.

Parameter metadata can be returned for a Select statement if one of the following conditions is true:

The following Select statements show further examples for which parameter metadata can be returned:

   SELECT col1, col2 FROM foo WHERE col1 = ? and col2 > ?

   SELECT ... WHERE colname = (SELECT col2 FROM t2  
      WHERE col3 = ?)

   SELECT ... WHERE colname LIKE ?

   SELECT ... WHERE colname BETWEEN ? and ?

   SELECT ... WHERE colname IN (?, ?, ?)

   SELECT ... WHERE EXISTS(SELECT ... FROM T2 WHERE col1 < ?)

ANSI SQL 92 entry-level predicates in a WHERE clause containing GROUP BY, HAVING, or ORDER BY statements are supported. For example:

   SELECT * FROM t1 WHERE col = ? ORDER BY 1

Joins are supported. For example:

   SELECT * FROM t1,t2 WHERE t1.col1 = ?

Fully qualified names and aliases are supported. For example:

   SELECT a, b, c, d FROM T1 AS A, T2 AS B WHERE A.a = ? 
      and B.b = ?"

ResultSet Metadata Support

If your application requires table name information, the DB2 driver can return table name information in ResultSet metadata for Select statements. By setting the ResultSetMetaDataOptions property to 1, the DB2 driver performs additional processing to determine the correct table name for each column in the result set when the ResultSetMetaData.getTableName() method is called. Otherwise, the getTableName() method may return an empty string for each column in the result set.

The table name information that is returned by the DB2 driver depends on whether the column in a result set maps to a column in a table in the database. For each column in a result set that maps to a column in a table in the database, the DB2 driver returns the table name associated with that column. For columns in a result set that do not map to a column in a table (for example, aggregates and literals), the DB2 driver returns an empty string.

The Select statements for which ResultSet metadata is returned may contain aliases, joins, and fully qualified names. The following queries are examples of Select statements for which the ResultSetMetaData.getTableName() method returns the correct table name for columns in the Select list:

   SELECT id, name FROM Employee

   SELECT E.id, E.name FROM Employee E 

   SELECT E.id, E.name AS EmployeeName FROM Employee E

   SELECT E.id, E.name, I.location, I.phone FROM Employee E, 

      EmployeeInfo I WHERE E.id = I.id

   SELECT id, name, location, phone FROM Employee,

      EmployeeInfo WHERE id = empId

   SELECT Employee.id, Employee.name, EmployeeInfo.location, 

      EmployeeInfo.phone FROM Employee, EmployeeInfo 

      WHERE Employee.id = EmployeeInfo.id

The table name returned by the driver for generated columns is an empty string. The following query is an example of a Select statement that returns a result set that contains a generated column (the column named "upper").

   SELECT E.id, E.name as EmployeeName, {fn UCASE(E.name)} 

      AS upper FROM Employee E

The DB2 driver also can return schema name and catalog name information when the ResultSetMetaData.getSchemaName() and ResultSetMetaData.getCatalogName() methods are called if the driver can determine that information. For example, for the following statement, the DB2 driver returns "test" for the catalog name, "test1" for the schema name, and "foo" for the table name:

   SELECT * FROM test.test1.foo 

The additional processing required to return table name, schema name, and catalog name information is only performed if the ResultSetMetaData.getTableName(), ResultSetMetaData.getSchemaName(), or ResultSetMetaData.getCatalogName() methods are called.

Rowset Support

The DB2 driver supports any JSR 114 implementation of the RowSet interface, including:

See http://www.jcp.org/en/jsr/detail?id=114 for more information about JSR 114.

Auto-Generated Keys Support

The DB2 driver supports retrieving the values of auto-generated keys. An auto-generated key returned by the DB2 driver is the value of an auto-increment column.

How you return those values depends on whether you are using an Insert statement that contains parameters:

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

Known Issues

Due to a defect in DB2 servers, problems may occur when using a driver patch that is equal to, or greater than the patch levels above, if connecting to DB2 UDB on UNIX, Windows, or Linux where the server version is earlier than DB2 v8 FixPak 11. If you have connecton problems, you may need to include the following driver property in your configuration:

   ExtendedOptions=ServerReleaseLevel=SQL08020

Use the following table to provide guidance on when to use ServerReleaseLevel:

For more information, see IBM's support and download site at DB2 UDB Version 8.1 FixPak 11 (also known as Version 8.2 FixPak 4).