DB2 Connect Enterprise Edition servers often provide database connections for thousands of simultaneous client requests. Establishing and severing connections to the database server can be a very resource intensive process that adversely affects both database server and DB2 Connect server performance. This is especially evident in web environments where each visit to a web page can require building a new connection to the database server, performing a query and terminating a connection. To reduce this overhead, DB2 Connect Enterprise Edition uses connection pooling to maintain open connections to the database in a readily accessible pool.
Connection pooling is transparent to applications connecting to the host through DB2 Connect. When an application requests disconnection from the host, DB2 Connect drops the inbound connection with the application, but keeps the outbound connection to the host in a pool. When a new application requests a connection, the DB2 Connect uses one from the existing pool. Using the already-present connection reduces the overall connection time, as well as the high CPU connect cost on the host.
To use connection pooling, the following APAR must be applied to DB2 for OS/390 Version 6.1:
APAR PQ33473
DB2 Connect agents can be in one two states: idle or active. An agent is active when it is executing work for an application. Once this work is completed the agent goes into an idle state awaiting further work from the same or a different application. All idle agents are kept together in what is known as the idle agent pool. You can configure the size of this pool using the NUM_POOLAGENTS configuration parameter. This parameter equals the maximum number of idle agents you wish the system to maintain. Setting this parameter to zero is equivalent to turning off the connection pooling feature.
DB2 Connect does not establish connections to the database before receiving its first client request. If you wish, however, you may fill the pool of idle agents before any clients make a request. The pool can be filled on start-up using the NUM_INITAGENTS configuration parameter. This parameter determines how many idle agents should be created at start up time. These idle agents will not initially have connections to the host database server.
When a client requests a connection to the host, DB2 Connect will attempt to get an agent from among those in the pool that have a connection to the host database server. If that fails, it will try to find an available agent in the idle pool. If the pool is empty, DB2 Connect will create a new agent.
You can control the maximum number of agents that can be concurrently active using the MAX_COORDAGENTS configuration parameter. Once this number is exceeded, new connections will fail with error sqlcode SQL1226. (This code means that the maximum number of concurrent outbound connections has been exceeded.)
The db2 registry variable DB2CONNECT_IN_APP_PROCESS allows applications running on the same machine as DB2 Connect EE to either have DB2 Connect run within the applications process, default behavior, or to have the application connect to the DB2 Connect EE Server and then have the host connection run within an agent. In order for an application to use connection pooling the connections to the host must be made from within the DB2 Connect EE Server agents and thus DB2CONNECT_IN_APP_PROCESS must be set to NO.
DB2 Connect's connection concentrator technology allows DB2 Connect Enterprise Edition servers to provide support to thousands of users simultaneously executing business transactions, while drastically reducing resources required on the S/390 host or AS/400 database servers. It accomplishes this goal by concentrating the workload from all applications in a much smaller number of S/390 host or AS/400 database server connections. While this may seem similar to the connection pooling function described above it is in fact a more sophisticated approach to reducing resource consumption for very high volume OLTP (On-line Transaction Processing) applications.
Connection pooling saves the cost of establishing a connection when one is no longer needed by a terminating application. In other words, one application has to disconnect before another one can reuse a pooled connection.
Connection concentrator, on the other hand, allows DB2 Connect to make a connection available to an application as soon as another application has finished a transaction and does not require that other application to disconnect. In essence, a database server connection and its associated host and DB2 Connect resources are used by an application only while it has an active transaction. As soon as the transaction completes, the connection and associated resources are available for use by any other application that needs to have a transaction executed.
In previous versions of DB2 Connect, every active application had an Engine Dispatchable Unit (EDU) which managed the database connection as well as any application requests. This EDU was typically referred to as the coordinator agent. Each coordinator agent tracked the state, or context of the application and EDU. Each EDU takes a significant amount of memory when the number of connections increase, and context switching between agents results in additional overhead.
In the above architecture, there is a one-to-one relationship between connections and EDUs. The connection concentrator, however, permits a many-to-one relationship between connections and EDUs. That is, the relationship of connections (X) to EDUs (Y) is now X >= Y.
The connection concentrator splits the agent into two entities, a logical agent and a worker agent. Logical agents represent an application, but without reference to a particular EDU. The logical agent contains all the information and control blocks required by an application. If there are n applications connected to the server, there will be n logical agents on the server. Worker agents are physical EDUs that execute application requests, but which have no permanent attachment to any given application. Worker agents associate with logical agents to perform transactions, and at transaction boundary end the association and return to the available pool.
An entity known as the logical agent scheduler assigns worker agents to logical agents. Limitations in the number of open file handles on certain computing platforms may result in more than one scheduler instance when the number of logical agents exceeds the file handle limit.
To use the connection concentrator, the following APAR must be applied to DB2 for OS/390 Version 6.1:
APAR PQ33473
The database manager configuration parameter MAX_LOGICAGENTS sets the maximum number of logical agents. You can activate the concentrator feature by setting the value of MAX_LOGICAGENTS to any number greater than the default. The default value for MAX_LOGICAGENTS is equivalent to the value of MAX_COORDAGENTS. Because each application will have one logical agent, MAX_LOGICAGENTS actually controls the number of applications that can be connected to the database instance, while MAX_COORDAGENTS controls the number of inbound connections that can be active at any time. MAX_LOGICAGENTS will take a numeric range from MAX_COORDAGENTS up to 64,000. The default number of logical agents is equal to MAX_COORDAGENTS.
Several existing configuration parameters are used to configure agents. These parameters are as follows:
The architecture of the connection concentrator allows DB2 Connect to provide tightly coupled XA transaction support to DB2 for OS/390 and DB2 for AS/400. The concentrator will associate a worker agent with a particular XA transaction (single XID) as it would for any other transaction. However, if the XA transaction is ended by xa_end() (branch boundary), the worker agent will not release itself into the general pool. Instead, the worker remains associated with that particular XA transaction. When another application joins the same XA transaction, the worker agent will be attached to that application.
Any transaction boundary call will return the agent to the pool. For instance, xa_prepare() with read only, xa_rollback(), xa_recover(), xa_forget(), xa_commit(), or any XA error that causes rollback will return the agent to the normal pool. Xa_end() itself only ends the transaction branch, and this is not sufficient to end its association with the XID.
The DB2 Connect server system may not be able to maintain 4,000 simultaneous open connections to the database machine. In most cases, the number of transactions occurring at any given moment will be considerably less than the number of concurrent connections. The system administrator could then maximize the efficiency of the system by setting the database configuration parameters as follows:
MAX_LOGICAGENTS = 4,000
MAX_AGENTS = 1,000
MAX_COORDAGENTS = 1,000
NUM_POOLAGENTS = 1,000
The concentrator will keep open up to 4,000 concurrent sessions, even though the gateway is only managing 1,000 transactions at a time.
The case of XA transactions is somewhat different. For this example, we may assume that a TP Monitor is being used with a DB2 Connect gateway and an OS/390 or AS/400 database. When an application requests a connection, the concentrator will either turn an inactive agent over to serve that request, or create a new worker agent. Let us assume that the application requests an XA transaction. An XID is created for this transaction and the worker agent is associated with it.
When the application's request has been serviced, it issues an xa_end() and detaches from the worker agent. The worker agent remains associated with the XID of the transaction. It can now only service requests for transactions with its associated XID.
At this time, another application may make a request for a non-XA transaction. Even if there are no other available worker agents, the agent associated with the XID will not be made available to the second application. It is considered active. The second application will have a new worker agent created for it. When that second application completes its transaction, its worker agent is released into the available pool.
Meanwhile, other applications requesting the transaction associated with the first agent's XID may attach and detach from that agent, which executes its dedicated XA transaction for them. Any application requesting that particular transaction will be sent to this worker agent if it is free.
The worker agent will not be released back into the general pool until an application issues a transaction boundary call (not xa_end()). For instance, an application might end the transaction with xa_commit(), at which point the worker agent drops its association with the XID and returns to the available pool. At this point any requesting application can use it for either another XA, or a non-XA, transaction.
There are a number of important restrictions to the use of the gateway concentrator. Review the following information in its entirety before attempting to use the connection concentrator on your system.
System performance will be affected by the performance of the host or AS/400 database server database.
Different database management systems have different performance features. SQL optimizers of different systems, for example, could behave differently with the same application. Check your host or AS/400 database server system performance documentation for more information.
For DB2 Universal Database for AS/400, you may be able to improve performance by using the uncommitted read (UR) or no commit (NC) bind options to avoid journalling.
| Note: | When using UR, unjournalled data can only be read, not updated, and then only if blocking is set to ALL. |
Depending on the application server and the lock granularity it provides, the isolation level used for a query or application may have a significant effect on performance.
The database should have the appropriate level of normalization, effective use of indexes, and suitable allocation of database space. Performance can also be affected by the data types that you use, as described in the following sections.
OS/390 V1R3 is the minimum requirement for TCP/IP support. OS/390 V2R5 or later is highly recommended.
The Distributed Data Facility (DDF) is responsible for connecting distributed applications to DB2 for OS/390. The DDF should be set up as an application server. To do this, you can either insert the LU name of the remote system into the SYSIBM.LUNAMES table, or insert the LUNAME, SYSMODENAME, USERSECURITY, ENCRYPTPSWDS, MODESELECT, and USERNAMES values into the SYSIBM.SYSLUNAME table. Then perform a DDF update to the Boot Strap Data Set (BSDS). For example:
DDF LOCATION=LOC1,LUNAME=LU1,PORT=8000,RESPORT=8001
For best performance, you should use the recommended DDF address space prioritisation (slightly lower or equal to DBM1 if you are in COMPAT mode). Use RACF caching of authorizations in VLF, and use V5 package authorizations caching if you can. A value of CACHEPAC=32768 is sufficient for most operations.
Since DDF will try to connect to VTAM, VTAM must be active when DDF starts. A sample VTAM APPL definition is included below:
SYD51TC* APPL AUTH=(ACQ), X
PARSESS=YES, X
HAVAIL=YES, X
EAS=1600, X
APPC=YES, X
DSESLIM=1024, X
DMINWNL=512, X
DMINWNR=512, X
AUTOSES=1, X
SECACPT=ALREADYV, X
SRBEXIT=YES, X
SYNCLVL=SYNCPT, X
MODETAB=DB2MODET, X
VPACING=63 X
You can optimize inactive thread processing in OS/390. In V3, you are allowed up to 10,000 concurrently connected clients, and up to 25,000 in V4 and V5. In all cases, the maximum number that can be concurrently active, however, is 1999. Each workstation client can stay connected when it is inactive; its thread is placed on an inactive chain at each commit.
The DSNZPARM parameters CMTSTAT, CONDBAT and MAXDBAT affect thread processing. For best performance, set CMTSTAT to INACTIVE, adjust CONDBAT to the maximum number of connected DBATs that provide good performance, and MAXDBAT to the maximum acceptable number of active DBATs.
For a complete discussion on connecting DB2 for OS/390 in a DRDA network, including VTAM configuration, refer to the Connectivity Supplement.
When data is transferred from one environment to another, it may need to be converted. This conversion can affect performance.
Consider the following platforms:
and the following types of numeric data:
Table 8 shows when conversion takes place.
|
|
Intel |
IEEE |
S/370 & S/390 |
OS/400 |
|---|---|---|---|---|
|
Packed decimal data | ||||
|
Intel IEEE S/370/390 OS/400 |
No No No No |
No No No No |
No No No No |
No No No No |
|
Zoned decimal data | ||||
|
Intel IEEE S/370/390 OS/400 |
No No Yes Yes |
No No Yes Yes |
Yes Yes No No |
Yes Yes No No |
|
Integer data | ||||
|
Intel IEEE S/370/390 OS/400 |
No Yes Yes Yes |
Yes No No No |
Yes No No No |
Yes No No No |
|
Floating point data | ||||
|
Intel IEEE S/370/390 OS/400 |
No Yes Yes Yes |
Yes No Yes No |
Yes Yes No Yes |
Yes No Yes No |
The CPU cost of single-byte character data conversion is generally less than that of numeric data conversion (where data conversion is required).
The data conversion cost of DATE/TIME/TIMESTAMP is almost the same as that of single-byte CHAR. FLOATING point data conversion costs the most. The application designer may want to take advantage of these facts when designing an application based on DB2 Connect.
If a database table has a column defined 'FOR BIT DATA', the character data being transferred between the application and the database does not require any data conversion. This can be used when you are archiving data on the host or AS/400 database server.
Character data can have either the CHAR or VARCHAR data type. Which data type is more efficient depends on the typical length of data in the field:
The best way to improve overall performance in a distributed database environment is to eliminate delays from the network. It is common for network administrators to consider a network to be more efficient if it collects as much data as possible between transmissions. This approach doesn't work for applications such as distributed databases because it builds delays into the network. The end-user doesn't see the efficiency of the network, only the delays.
Most network devices have delay parameters, and most of them default to values that are very bad for distributed databases. To improve performance you should locate these parameters and if possible, set them to zero. In addition you should ensure that the buffer size on the device is large enough to prevent retransmits due to lost data. For instance, UNIX systems typically have a Transmit or Receive queue depth default of 32. For better results, set the queue depth to 150. A corresponding parameter on DLC settings is the Receive Depth, which should also be 150.
The IOBUF parameter is set too low at most sites. It is usually set at 500, but experience has shown that a value of 3992 works best if you are moving large amounts of data, especially for channel connections such as ESCON or 3172.
For SNA connections, you should set the Mode Profile of any workstation software to 63. In general, receive pacing values throughout the network should be set to their highest value, so the VPACING and PACING parameters on the DB2 APPL statement, and the PU/LU for the workstation in a switched major mode should also be set to 63. What this will do is allow the amount of message flows before the sender must wait for a response to increase progressively.
On a LAN system the DLC or LLC transmit and receive window sizes can have a dramatic effect on performance. The send value should be set to seven or more, and for most configurations a receive value of four or less works best.
If you are running Ethernet, you should set the TCP segment size to 1500 bytes. On a token ring or FDDI network this value should be 4400 bytes, and if you are using an ESCON adapter with TCP/IP, the segment size should always be 4096.
Finally, for TCP/IP networks, the TCP Send and Receive buffer sizes should be set higher than 32768. A value of 65536 is generally best.
| Note: | Establishing a connection from the gateway to the server (outbound
connection) is much more expensive than establishing a connection from a
client to the gateway (inbound connection). In an environment where
thousands of clients frequently connect to and disconnect from the server
through the gateway, a substantial amount of processing time is spent
establishing outbound connections. DB2 Connect provides connection
pooling over TCP/IP. When a client requests disconnection from the
server, the gateway drops the inbound connection with the client, but keeps
the outbound connection to the server in a pool. When a new client
comes into the gateway to request a connection, the gateway provides an
existing one from the pool thus reducing the overall connection time and
saving the high CPU connect cost on the server.
For more information about connection pooling under DB2, refer to the Administration Guide. |
A summary of network performance tuning methods is provided in the
following table.
| What to Look For | Example | Setting | Notes |
|---|---|---|---|
| Deliberate Delays | Delay parameters on network devices | Set to 0. | Defaults are usually higher. |
| Buffers | IOBUF parameter | Set up to 3992. | Particularly useful for ESCON or other channel adapter. |
| RUSIZE | Optimum size is 4096. | Setting RUSIZE and RQRIOBLK to same size may give best performance. | |
| Pacing | VPACING, PACING, and Mode Profiles should be set to 63. | Use adaptive pacing where applicable. | |
| Adapter Settings | Transmit/Receive queue depth | Recommended value is 150. | Default is usually 32. |
| DLC Windowing on SNA | Set transmit window size high (>7). Set receive window size low (for example, at 1), test and increment repeatedly to find ideal value. | Every logical device adds delays. Simply network topology as much as possible. | |
| TCP Settings | Segment Sizes | 1500 on Ethernet, 4400 on token ring and FDDI. | ESCON adapters used for TCP/IP should always be set to 4096. |
| Send/Receive Space Sizes | Should be 64K for both. | Default is only 8192 for Windows. Can be set in the Windows registry. |
The following considerations relate to the hardware:
Performance improves with a faster transmission medium. For example, the following are some typical raw data transfer rates:
The data transfer rate is limited by the slowest transmission medium in the path to the host or AS/400 database server.
You should carefully plan the memory usage of the network adapter and communication controller. In addition, you should work with a network specialist to ensure that the controller has the capability to handle the extra traffic generated by DB2 Connect.
If data crosses from LAN to LAN, and from one SNA Network to another SNA Network, consider the travel time. Bridges, routers, and gateways will add to the elapsed time. For example, reducing the number of bridges that are crossed reduces the number of hops required for each request.
The physical distance between nodes should also be considered. Even if a message is transferred by satellite, the transfer time is limited by the speed of light (3 * 10**8 m/s) and the round-trip distance between the sender and receiver.
If the bandwidth of the network has been fully utilized, both the response time and the data transfer rate for a single application will decrease.
Congestion can occur in the network when data accumulates at a particular part of the network; for example, at an old NCP with a very small buffer size.
If the error rate of the network is high, the throughput of the network will decrease and this will cause poor performance because of data re-transmission.
Performance could be degraded if many tasks in the system are contending for system resources. Consider the following questions:
If DB2 Connect users are experiencing long response times during large queries from host or AS/400 servers, the following areas should be examined for the possible cause of the performance problem:
db2 update database manager configuration using RQRIOBLK 32767