Java applications that use DB2 Universal JDBC Driver type 4 connectivity to access DB2 UDB for z/OS(R) |servers can take advantage of its connection concentrator and Sysplex workload |balancing functions.
|These functions are similar to the connection concentrator and Sysplex |workload balancing functions of DB2 Connect.
|The DB2 Universal JDBC Driver connection concentrator can reduce the resources that DB2 |UDB for z/OS database servers require to support large numbers of client applications |by letting many connection objects use the same physical connection, which |reduces the total number of physical connections to the database server.
|DB2 Universal JDBC Driver Sysplex workload balancing can improve availability of a data-sharing |group because the driver gets frequent status information about the members |of a data-sharing group. The driver uses this information to determine the |data-sharing member to which the next transaction should be routed. With Sysplex |workload balancing, the DB2 UDB for z/OS server and Workload Manager for z/OS |(WLM) ensure that work is distributed efficiently among members of the data-sharing |group and that work is transferred to another member of a data-sharing group |if one member has a failure.
|The DB2 Universal JDBC Driver uses transport objects and a global transport objects pool |to support the connection concentrator and Sysplex workload balancing. There |is one transport object for each physical connection to the database server. |When you enable the connection concentrator and Sysplex workload balancing, |you set the maximum number of physical connections to the database server |at any point in time by setting the maximum number of transport objects.
|At the driver level, you set limits on the number of transport objects |using DB2 Universal JDBC Driver configuration properties.
|At the connection level, you enable and disable the DB2 Universal JDBC Driver connection |concentrator and Sysplex workload balancing and set limits on the number of |transport objects using DataSource properties.
|You can monitor the global transport objects pool in either of the following |ways:
|Each of the following configuration properties is used for connection concentrator |and Sysplex workload balancing
|The data type of db2.jcc.dumpPool is integer (int.). The db2.jcc.dumpPoolStatisticsOnSchedule |and db2.jcc.dumpPoolStatisticsOnScheduleFile configuration properties must |also be set for writing statistics before any statistics are written.
|You can specify one or more of the following types of statistics with the |db2.jcc.dumpPool property:
|To trace more than one type of event, add the values for the types |of events that you want to trace. For example, suppose that you want to trace |DUMP_GET_OBJECT and DUMP_CREATE_OBJECT events. The numeric equivalents of |these values are 2 and 16, so you specify 18 for the db2.jcc.dumpPool value.
|The default is 0, which means that only summary statistics for the |global transport pool are written.
|The default is -1, which means |that global transport pool statistics are not written.
|If the db2.jcc.dumpPoolStatisticsOnScheduleFile |configuration property is not specified, global transport pool statistics |are not written.
|The default value for the db2.jcc.maxTransportObjectIdleTime |configuration property is 60. Setting db2.jcc.maxTransportObjectIdleTime to |a value less than 0 causes unused transport objects to be deleted from the |pool immediately. This action is not recommended because |it can cause severe performance degradation.
|The default value for the db2.jcc.maxTransportObjectWaitTime |configuration property is -1. Any negative value means that applications wait |forever.
|The default value for the db2.jcc.maxTransportObjects |configuration property is -1, which means that there is no limit to the number |of transport objects in the global transport object pool.
|The default value for the db2.jcc.minTransportObjects |configuration property is 0. Any value less than or equal to 0 means that |the global transport object pool can become empty.
|Each of the following DB2 Universal JDBC Driver DataSource properties is used for connection |concentrator and Sysplex workload balancing
|The data type of the enableConnectionConcentrator |property is boolean. The default is false. However, if enableSysplexWLB |is set to true, the default is true.
|The data type of the enableSysplexWLB property |is boolean. The default is false. However, if enableSysplexWLB |is set to true, enableConnectionConcentrator is set to true by |default.
|The data type of this property |is integer (int.).
|If the maxTransportObjects value has not been reached |and a transport object is not available in the global transport objects pool, |the pool creates a new transport object. If the maxTransportObjects value |has been reached, the application waits for the amount of time that is specified |by the db2.jcc.maxTransportObjectWaitTime configuration property. After that |amount of time has elapsed, if there is still no available transport object |in the pool, the pool throws an SQLException.
|The maxTransportObjects |property does not override the db2.jcc.maxTransportObjects |configuration property. The maxTransportObjects property has no effect on |connections from other DataSource objects. If the maxTransportObjects |value is larger than the db2.jcc.maxTransportObjects value, maxTransportObjects |does not increase the db2.jcc.maxTransportObjects value.
|The default |value for the maxTransportObjects property is -1, which means that the number |of transport objects for the DataSource is limited only by the |db2.jcc.maxTransportObjects value for the driver..
|The following procedure is an example of enabling the DB2 Universal JDBC Driver connection |concentrator and Sysplex workload balancing functions with WebSphere(R) |Application Server.
|Server requirements:
|Client requirements:
|To enable the DB2 Universal JDBC Driver connection concentrator and Sysplex workload balancing |functions with WebSphere Application Server:
|java com.ibm.db2.jcc.DB2Jcc -versionFind a line in the output |like this: |
[ibm][db2][jcc] Driver: IBM DB2 JDBC Universal Driver Architecture n nn should be 2.7 or later.
Set the configuration properties in a DB2JccConfiguration.properties |file.
|db2.jcc.minTransportObjects=0 |db2.jcc.maxTransportObjects=1500 |db2.jcc.maxTransportObjectWaitTime=-1 |db2.jcc.dumpPool=0 |db2.jcc.dumpPoolStatisticsOnScheduleFile= | /home/WAS/logs/srv1/poolstats
In the WebSphere Application Server |administrative console, set the following properties for the data source that |your application uses to connect to the database server:
|To monitor the DB2 Universal JDBC Driver connection concentrator and Sysplex workload balancing |functions, you need to monitor the global transport objects pool. You can |monitor the global transport objects pool in either of the following ways:
|The db2.jcc.dumpPool, db2.jcc.dumpPoolStatisticsOnSchedule, and db2.jcc.dumpPoolStatisticsOnScheduleFile |configuration properties control tracing of the global transport objects pool.
|For example, the following set of configuration property settings cause |Sysplex error messages and dump pool error messages to be written every 60 |seconds to a file named /home/WAS/logs/srv1/poolstats:
|db2.jcc.dumpPool=DUMP_SYSPLEX_MSG|DUMP_POOL_ERROR |db2.jcc.dumpPoolStatisticsOnSchedule=60 |db2.jcc.dumpPoolStatisticsOnScheduleFile=/home/WAS/logs/srv1/poolstats|
An entry in the pool statistics file looks like this:
|time Scheduled PoolStatistics npr:2575 nsr:2575 lwroc:439 |hwroc:1764 coc:372 aooc:362 rmoc:362 nbr:2872 tbt:857520 tpo:10
The meanings |of the fields are:
|You can write applications to gather statistics on the global transport |objects pool. Those applications create objects in the DB2PoolMonitor class and invoke methods to retrieve information about the pool.
|For example, the following code creates an object for monitoring the global |transport objects pool:
|import com.ibm.db2.jcc.DB2PoolMonitor; |DB2PoolMonitor transportObjectPoolMonitor = | DB2PoolMonitor.getPoolMonitor (DB2PoolMonitor.TRANSPORT_OBJECT);|
After you create the DB2PoolMonitor object, you can use the following methods |to monitor the global transport objects pool.
|public int getMonitorVersion()|
Retrieves the version |of the DB2PoolMonitor class that is shipped with the DB2 Universal JDBC Driver.
|public int totalRequestsToPool()|
Retrieves the |total number of requests that the DB2 Universal JDBC Driver has made to the pool since the |pool was created.
|public int successfullRequestsFromPool()|
Retrieves |the number of successful requests that the DB2 Universal JDBC Driver has made to the pool |since the pool was created. A successful request means that the pool returned |an object.
|public int numberOfRequestsBlocked()|
Retrieves |the number of requests that the DB2 Universal JDBC Driver made to the pool that the pool blocked |because the pool had reached its maximum capacity. A blocked request might |be successful if an object is returned to the pool before the db2.jcc.maxTransportObjectWaitTime |configuration value is exceeded and an exception is thrown.
|public long totalTimeBlocked()|
Retrieves the total |time in milliseconds for requests that were blocked by the pool. This time |can be much larger than the elapsed execution time of the application if the |application uses multiple threads.
|public int lightWeightReusedObjectCount()|
Retrieves |the number of objects that were reused but were not in the pool. This can |happen if a connection object releases a transport object at a transaction |boundary. If the connection object needs a transport object later, and the |original transport object has not been used by any other connection object, |the connection object can use that transport object.
|public int heavyWeightReusedObjectCount()|
Retrieves |the number of objects that were reused from the pool.
|public int createdObjectCount()|
Retrieves the number |of objects that the DB2 Universal JDBC Driver created since the pool was created.
|public int agedOutObjectCount()|
Retrieves the number |of objects that exceeded the idle time that was specified by the db2.jcc.maxTransportObjectIdleTime |configuration property and were deleted from the pool.
|public int removedObjectCount()|
Retrieves the number |of objects that have been deleted from the pool since the pool was created.
|public int totalPoolObjects()|
The number of objects |that are currently in the pool.
|The OleDbReportIsLongForLongTypes keyword is supported by the following database servers:
OLE DB's client cursor engine and OLE DB .NET Data Provider's CommandBuilder generate update and delete statements based on column information provided by the IBM(R) DB2(R) OLE DB Provider. If the generated statement contains a LONG type in the WHERE clause, the statement will fail because LONG types cannot be used in a search with an equality operator. Setting the keyword OleDbReportIsLongForLongTypes to 1 will make the IBM DB2 OLE DB Provider report LONG types (LONG VARCHAR, LONG VARCHAR FOR BIT DATA, LONG VARGRAPHIC and LONG VARGRAPHIC FOR BIT DATA) with the DBCOLUMNFLAGS_ISLONG flag set. This will prevent the long columns from being used in the WHERE clause.
The OleDbSQLColumnsSortByOrdinal keyword is supported by the following database servers:
The Microsoft(R) OLE DB specification requires that IDBSchemaRowset::GetRowset(DBSCHEMA_COLUMNS) returns the row set sorted by the columns TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME. The IBM DB2 OLE DB Provider conforms to the specification. However, applications that use the Microsoft ODBC Bridge provider (MSDASQL) have been typically coded to get the row set sorted by ORDINAL_POSITION. Setting the OleDbSQLColumnsSortByOrdinal keyword to 1 will make the provider return a row set sorted by ORDINAL_POSITION.
The IBM DB2 OLE DB Provider has added a new property group: DB2 Data Source. The property set for DB2 Data Source is DBPROPSET_DB2DATASOURCE.
The GUID for the property set is {0x8a80412a,0x7d94,0x4fec,{0x87,0x3e,0x6c,0xd1,0xcd,0x42,0x0d,0xcd}}
DBPROPSET_DB2DATASOURCE has three properties:
#define DB2PROP_REPORTISLONGFORLONGTYPES 4 Property group: DB2 Data Source Property set: DB2PROPSET_DATASOURCE Type: VT_BOOL Typical R/W: R/W Description: Report IsLong for Long Types
OLE DB's client cursor engine and OLE DB .NET Data Provider's CommandBuilder generate update and delete statements based on column information provided by the IBM DB2 OLE DB Provider. If the generated statement contains a LONG type in the WHERE clause, the statement will fail because LONG types cannot be used in a search with an equality operator.
Values | Meaning |
---|---|
VARIANT_TRUE | Will make the IBM DB2 OLE DB Provider report LONG types (LONG VARCHAR, LONG VARCHAR FOR BIT DATA, LONG VARGRAPHIC, and LONG VARGRAPHIC FOR BIT DATA) with the DBCOLUMNFLAGS_ISLONG flag set. This will prevent the long columns from being used in the WHERE clause. |
VARIANT_FALSE | DBCOLUMNFLAGS_ISLONG is not set for LONG VARCHAR, LONG VARCHAR FOR BIT DATA, LONG VARGRAPHIC and LONG VARGRAPHIC FOR BIT DATA. This is the default. |
#define DB2PROP_RETURNCHARASWCHAR 2 Property group: DB2 Data Source Property set: DB2PROPSET_DATASOURCE Type: VT_BOOL Typical R/W: R/W Description: Return Char as WChar
Values | Meaning |
---|---|
VARIANT_TRUE | OLE DB describes columns of type CHAR, VARCHAR, LONG VARCHAR, or CLOB as DBTYPE_WSTR. The code page of data implied in ISequentialStream will be UCS-2. This is the default. |
VARIANT_FALSE | OLE DB describes columns of type CHAR, VARCHAR, LONG VARCHAR, or CLOB as DBTYPE_STR. The code page of data implied in ISequentialStream will be the local code page of the client. |
#define DB2PROP_SORTBYORDINAL 3 Property group: DB2 Data Source Property set: DB2PROPSET_DATASOURCE Type: VT_BOOL Typical R/W: R/W Description: Sort By Ordinal
The Microsoft OLE DB specification requires that IDBSchemaRowset::GetRowset(DBSCHEMA_COLUMNS) returns the row set sorted by the columns TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME. The IBM DB2 OLE DB Provider conforms to the specification. However, applications that use the Microsoft ODBC Bridge provider (MSDASQL) have been typically coded to get the row set sorted by ORDINAL_POSITION.
Values | Meaning |
---|---|
VARIANT_TRUE | Will make the provider return a row set sorted by ORDINAL_POSITION. |
VARIANT_FALSE | Will make the provider return a row set sorted by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME. This is the default. |
In the topic "Installing the DB2 Universal JDBC Driver", the DB2Binder syntax diagram incorrectly defines the URL syntax for the DB2 Universal JDBC Driver. The correct representation of the URL syntax for DB2Binder is shown in the following diagram:
The automatic client reroute feature in DB2 Universal Database(TM) (UDB) for Linux(TM), UNIX(R), and Windows(R) allows client applications to recover from a loss of communication with the server so that they can continue to work with minimal interruption.
Whenever a server locks up, each client that is connected to that server receives a communication error, which terminates the connection and results in an application error. When availability is important, you should have a redundant setup or failover support. (Failover is the ability of a server to take over operations when another server fails.) In either case, the DB2 Universal JDBC driver client attempts to reestablish the connection to a new server, or to the original server, which might be running on a failover node. When the connection is reestablished, the application receives an SQLException that informs it of the transaction failure, but the application can continue with the next transaction.
After the database administrator specifies the alternate server location on a particular database at the server instance, the primary and alternate server locations are returned back to the client at connect time. The DB2 Universal JDBC driver creates an instance of Referenceable object DB2ClientRerouteServerList and stores that instance in its transient memory. If communication is lost, the DB2 Universal JDBC driver tries to reestablish the connection using the server information that is returned from the server.
The clientRerouteServerListJNDIName DataSource property provides additional client reroute support at the client; clientRerouteServerListJNDIName has two functions:
The clientRerouteServerListJNDIName identifies a JNDI reference to a DB2ClientRerouteServerList instance in a JNDI repository for alternate server information. After a successful connection to the primary server, the alternate server information that is provided by clientRerouteServerListJNDIName is overwritten by the information from the server. The DB2 Universal JDBC driver will attempt to propagate the updated information to the JNDI store after a failover if the clientRerouteServerListJNDIName property is defined. If clientRerouteServerListJNDIName is specified, primary server information specified in DB2ClientRerouteServerList will be used for connection. If the primary server is not specified, serverName information specified on the data source will be used.
DB2ClientRerouteServerList is a serializable Java(TM) bean with four properties:
Getter and setter methods for accessing these properties are provided. The definition of the DB2ClientRerouteServerList class is as follows:
package com.ibm.db2.jcc; public class DB2ClientRerouteServerList implements java.io.Serializable, javax.naming.Referenceable { public String[] alternateServerName; public synchronized void setAlternateServerName(String[] alternateServer); public String[] getAlternateServerName(); public int[] alternatePortNumber; public synchronized void setAlternatePortNumber(int[] alternatePortNumberList); public int[] getAlternatePortNumber(); public synchronized void setPrimaryServerName (String primaryServerName); public String getPrimaryServerName (); public synchronized void setPrimaryPortNumber (int primaryPortNumber) public int getPrimaryPortNumber (); }
A newly established failover connection is configured with the original data source properties, except for the server name and port number. In addition, any DB2 UDB special registers that were modified during the original connection are reestablished in the failover connection by DB2 Universal Driver JDBC driver.
When a communication failure occurs, the DB2 Universal JDBC driver first attempts recovery to the primary server. If this fails, the driver attempts to connect to the alternate location (failover). After a connection is reestablished, the driver throws a java.sql.SQLException to the application with SQLCODE -4498, to indicate to the application that the connection has been automatically reestablished to the alternate server. The application can then retry its transaction.
To setup storage to make DB2ClientRerouteServerList persistent, follow these steps:
// Create a starting context for naming operations InitialContext registry = new InitialContext(); // Create a DB2ClientRerouteServerList object DB2ClientRerouteServerList address=new DB2ClientRerouteServerList(); // Set the port number and server name for the primary server address.setPrimaryPortNumber(50000); address.setPrimaryServerName("mvs1.sj.ibm.com"); // Set the port number and server name for the alternate server int[] port = {50002}; String[] server = {"mvs3.sj.ibm.com"}; address.setAlternatePortNumber(port); address.setAlternateServerName(server); registry.rebind("serverList", address);
datasource.setClientRerouteServerListJNDIName("serverList");
The DB2 Universal JDBC driver configuration properties let you set property values that have driver-wide scope. Those settings apply across applications and DataSource instances. You can change the settings without having to change application source code or DataSource characteristics.
Each DB2 Universal JDBC driver configuration property setting is of the following form:
property=value
If the configuration property begins with db2.jcc.override, the configuration property is applicable to all connections and overrides any Connection or DataSource property with the same property name. If the configuration property begins with db2.jcc or db2.jcc.default, the configuration property value is a default. Connection or DataSource property settings override that value.
To set configuration properties:
For standalone Java applications, you can set the configuration properties as Java system properties by specifying -Dproperty=value for each configuration property when you execute the java command.
For standalone Java applications, you can set the configuration properties by specifying the -Ddb2.jcc.propertiesFile=path option when you execute the java command.
DB2JccConfiguration.properties can be a standalone file, or it can be included in a JAR file.
If DB2JccConfiguration.properties is a standalone file, the path for DB2JccConfiguration.properties must be in the CLASSPATH concatenation.
If DB2JccConfiguration.properties is in a JAR file, the JAR file must be in the CLASSPATH concatenation.
You can set the following DB2 Universal JDBC driver configuration properties. All properties are optional.
Specify a fully qualified file name for the db2.jcc.override.traceFile property value.
The db2.jcc.override.traceFile property overrides the traceFile property for a Connection or DataSource object.
For example, specifying the following setting for db2.jcc.override.traceFile enables tracing of the DB2 Universal JDBC Driver Java code to a file named /SYSTEM/tmp/jdbctrace:
db2.jcc.override.traceFile=/SYSTEM/tmp/jdbctrace
You should set the trace properties under the direction of IBM Software Support.
The db2secFreeToken function (Free memory held by token) is no longer part of the db2secGssapiServerAuthFunctions_1 user authentication plug-in API.
The integrity of your DB2 Universal Database (UDB) installation can be compromised if the deployment of security plug-ins are not adequately coded, reviewed, and tested. DB2 UDB takes precautions against many common types of failures, but it cannot guarantee complete integrity when user-written security plug-ins are deployed.
If you are using your own customized security plug-in, you can use a user ID of up to 255 characters on a connect statement issued through the CLP or a dynamic SQL statement.
For the db2secGetGroupsForUser, db2secValidatePassword, and db2secGetAuthIDs APIs, the input parameter, dbname, can be null and its corresponding length input parameter, dbnamelen, will be set to 0.
.so is now accepted as a file name extension for user-written security plug-in libraries on all Linux and UNIX platforms.
On AIX(R), security plug-in libraries can have an extension of .a or .so. If both versions of the plug-in library exist, the .a version is used.
For HP-UX on PA-RISC, security plug-in libraries can have an extension of .sl or .so. If both versions of the plug-in library exist, the .sl version is used.
On all other Linux and UNIX platforms, .so is the only supported file name extension for security plug-in libraries.
On AIX, security plug-in libraries can have a file name extension of .a or .so. The mechanism used to load the plug-in library depends on which extension is used:
For example, to build a 32-bit archive style plug-in library:
xlc_r -qmkshrobj -o shr.o MyPlugin.c -bE:MyPlugin.exp ar rv MyPlugin.a shr.o
xlc_r -qmkshrobj -o MyPlugin.so MyPlugin.c -bE:MyPlugin.exp
On all platforms other than AIX, security plug-in libraries are always assumed to be dynamically loadable shared objects.
GSS-API authentication is limited to flowing one token from the client to the server and one token from the server to the client. These tokens are obtained from gss_init_sec_context() on the client and from gss_accept_sec_context() on the server. GSS-API plug-ins attempting additional flows will generate a security plug-in unexpected error, causing the connection to fail.
Message encryption and signing is not available in GSS-API security plug-ins.
All application terminations (normal and abnormal) implicitly roll back outstanding units of work, regardless of operating system.
In the What's new documentation for DB2 Universal Database (UDB) Version 8.2, the Distributed transaction support information for the DB2 Universal JDBC driver improvements section has incorrect information. The last sentence of this section is incorrect. The correct information is:
As of Version 8.2, DB2 UDB provides support for distributed transaction processing that conforms to the XA specification. This support implements the Java 2 Platform Enterprise Edition (J2EE) Java Transaction Service (JTS) and Java Transaction API (JTA) specifications.
[ Top of Page |Previous Page | Next Page | Contents ]