Using CMP JDBC wrapper drivers

EAServer includes customized JDBC drivers for use by CMP entity beans. The wrapper drivers offer better performance by allowing updates to be deferred to the end of each transaction and sent together as a command batch. Doing so improves performance by reducing network round trips between the database server and EAServer. The Sybase wrapper driver also supports automatic creation of semi-temporary stored procedures to further improve performance.

Two wrapper drivers are supported:

Sybase This driver is a wrapper around the Sybase jConnect driver. To use the driver, specify the class name com.sybase.ejb.cmp.SybaseDriver as the driver in your connection cache. Connection cache properties for this wrapper driver are the same as are used by com.sybase.jdbc2.jdbc.SybDriver, plus those listed in Table 4-3.

Oracle This driver is a wrapper around the Oracle JDBC driver. To use the driver, specify the class name com.sybase.ejb.cmp.OracleDriver as the driver in your connection cache. Connection cache properties for this wrapper driver are the same as are used by oracle.jdbc.driver.OracleDriver, plus those listed in Table 4-3.

Oracle driver classes not included The Oracle JDBC driver classes are not included in the EAServer installation. Before using the Oracle wrapper driver, make sure these classes are deployed to EAServer and that direct Oracle connection caches can be pinged sucessfully.

Table 4-3 lists the additional properties supported by the wrapper drivers. Tune these the settings in Table 4-3 in addition to the connection cache properties described in “Connection cache settings”.

**Table 4-3: Sybase CMP JDBC wrapper driver properties**
Property	Legal Values	Default Value	Description
CMP_DRIVER. DEBUG	true/false	false	Enables debug trace output which is written to the server log file.
CMP_DRIVER. DATABASE_TYPE	(entity name)	Sybase_ASE	Type of database (should be the same as the com.sybase.jaguar.conncache.db_type property for the cache). Use Sybase_ASA when connecting to Adaptive Server Anywhere, as there are some subtle differences between Adaptive Server Enterprise and Adaptive Server Anywhere in the handling of SQL batches.
CMP_DRIVER. MAXIMUM_ BATCH_PARAMETERS	0 or positive	99 (subject to change)	Maximum number of parameters in a batch.
CMP_DRIVER. MAXIMUM_BATCH_ STATEMENTS	0 or positive	3 (subject to change)	Maximum number of statements in a batch. Any value less than 2 effectively disables batching. Larger values will give better performance as long as memory is available. Setting this too high may result in too many stored procedures being created, and the database server may run out of procedure cache.
CMP_DRIVER. PREPARE_CALL (Sybase only)	true/false	true	Set to true to enables the use of TDS-protocol RPC calls instead of language statements for more efficient communication with the database server and avoids repeated SQL statement parsing. When set to false, commands are sent as TDS-protocol language commands to execute stored procedures.
CMP_DRIVER. PRINT_WARNINGS	true/false	true	Enables all database warning messages received by wrapper driver to be printed in server log.
CMP_DRIVER. STATEMENT_CACHE_SIZE (Oracle only)	0 or positive	9 (subject to change)	When using the Oracle wrapper driver, specifies the maximum number of cached prepared statements per connection. Setting this too high may result in too many JDBC prepared statements being cached, and the DBMS may run out of procedure cache or the server may run out of memory. Larger values will give better performance as long as memory is available.
CMP_DRIVER. TRACE_COMMIT	true/false	false	Trace transaction commit, rollback and autoCommit changes.
CMP_DRIVER. TRACE_CONNECT	true/false	false	Trace connect and reconnect operations.
CMP_DRIVER. TRACE_CREATE (Sybase only)	true/false	false	When using the Sybase driver, trace the creation of semi-permanent stored procedures.
CMP_DRIVER. TRACE_EXECUTE	true/false	false	Trace the execution of stored procedures and SQL command batches.
CMP_DRIVER. TRACE_EXECUTE_MS	0 or positive	0	Trace the execution of stored procedures and SQL command batches that take longer than the specified number of milliseconds. Specify 0 to disable. This setting takes precedence over CMP_DRIVER.TRACE_EXECUTE.

Table 4-3: Sybase CMP JDBC wrapper driver properties

Property

Legal Values

Default Value

Description

CMP_DRIVER. DEBUG

true/false

false

Enables debug trace output which is written to the server log file.

CMP_DRIVER. DATABASE_TYPE

(entity name)

Sybase_ASE

Type of database (should be the same as the com.sybase.jaguar.conncache.db_type property for the cache). Use Sybase_ASA when connecting to Adaptive Server Anywhere, as there are some subtle differences between Adaptive Server Enterprise and Adaptive Server Anywhere in the handling of SQL batches.

CMP_DRIVER. MAXIMUM_ BATCH_PARAMETERS

0 or positive

99 (subject to change)

Maximum number of parameters in a batch.

CMP_DRIVER. MAXIMUM_BATCH_ STATEMENTS

0 or positive

3 (subject to change)

Maximum number of statements in a batch.

Any value less than 2 effectively disables batching.

Larger values will give better performance as long as memory is available. Setting this too high may result in too many stored procedures being created, and the database server may run out of procedure cache.

CMP_DRIVER. PREPARE_CALL (Sybase only)

true/false

true

Set to true to enables the use of TDS-protocol RPC calls instead of language statements for more efficient communication with the database server and avoids repeated SQL statement parsing.

When set to false, commands are sent as TDS-protocol language commands to execute stored procedures.

CMP_DRIVER. PRINT_WARNINGS

true/false

true

Enables all database warning messages received by wrapper driver to be printed in server log.

CMP_DRIVER. STATEMENT_CACHE_SIZE (Oracle only)

0 or positive

9 (subject to change)

When using the Oracle wrapper driver, specifies the maximum number of cached prepared statements per connection.

Setting this too high may result in too many JDBC prepared statements being cached, and the DBMS may run out of procedure cache or the server may run out of memory. Larger values will give better performance as long as memory is available.

CMP_DRIVER. TRACE_COMMIT

true/false

false

Trace transaction commit, rollback and autoCommit changes.

CMP_DRIVER. TRACE_CONNECT

true/false

false

Trace connect and reconnect operations.

CMP_DRIVER. TRACE_CREATE (Sybase only)

true/false

false

When using the Sybase driver, trace the creation of semi-permanent stored procedures.

CMP_DRIVER. TRACE_EXECUTE

true/false

false

Trace the execution of stored procedures and SQL command batches.

CMP_DRIVER. TRACE_EXECUTE_MS

0 or positive

Trace the execution of stored procedures and SQL command batches that take longer than the specified number of milliseconds. Specify 0 to disable. This setting takes precedence over CMP_DRIVER.TRACE_EXECUTE.

The wrapper does not replace the underlying JDBC driver - it merely permits CMP tuning at the level of JDBC prepared statements. All calls to the database go through the underlying JDBC driver.