You can optimize application performance by adopting guidelines described in this section.

CatalogOptions: To improve performance, the driver can emulate getColumns() calls using the ResultSetMetaData object instead of querying database catalogs for the column information. Using emulation can improve performance because the SQL statement formulated by the emulation is less complex than the SQL statement formulated using getColumns(). The argument to getColumns() must evaluate to a single table. If it does not, because of a wildcard or null value, for example, the driver reverts to the default behavior for getColumns() calls.

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 i.

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 "Non-default schemas for catalog methods" for views for catalog tables that must exist in the specified schema.

EncryptionMethod: Data encryption may adversely affect performance because of the additional overhead (mainly CPU usage) required to encrypt and decrypt data.

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.

LobStreamingProtocol: In most cases, streaming provides better performance; however, when updating LOB data using Clob and Blob objects, the driver can materialize (cache) the data on the client, which can reduce network round trips to the database server and improve performance.

LongDataCacheSize: To improve performance when your application retrieves images, pictures, long text, binary data, or XML data, you can disable caching for long data on the client if your application retrieves long data column values in the order they are defined in the result set. If your application retrieves long data column values out or order, long data values must be cached.

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.

OptimizationProfile: An optimization profile contains optimization guidelines that are passed to Db2 to influence the generation of query plans for specific SQL statements. If you have tried other performance-tuning options, but you think that you are still getting less than optimal performance for a specific SQL statement, you can specify an optimization profile to provide explicit optimization guidelines. Refer to your Db2 documentation for details on creating an optimization profile.

RandomGenerator: By default, RandomGenerator is set to secureRandom. While secureRandom offers more secure seeding of random numbers, operations generally require additional processing with this configuration. Therefore, if your environment does not require more secure seeding, you should consider setting RandomGenerator to random to increase response times for your applications.

ResultSetMetaDataOptions: The driver’s performance may be adversely affected if you set this option to 1. If set to 1 and the ResultSetMetaData.getTableName method is called, the driver performs emulations which take additional processing.

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.

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.

Refer to Designing JDBC applications for performance optimization in the Progress DataDirect for JDBC Drivers Reference for more information about using prepared statement pooling to optimize performance.