출처: http://www-1.ibm.com/support/techdocs/atsmastr.nsf/PubAllNum/FQ101012

Abstract:

When you are running DB2 Connect, the database processing occurs at the DRDA host. Therefore most of the things you can do to make DB2 Connect itself perform better will have to do with making the "pipeline" of data faster and smoother, with fewer interruptions and in larger chunks of data, or reducing the amount of data returned, or shortening the data path.

TUNING THE CLIENT

The client machine plays a role in how well the application will perform. The following software factors should be considered when planning for performance:

RQRIOBLK setting in the DBM configuration

The RQRIOBLK value for the DB2 Client Application Enabler should be set to 32,767. This is most important when retrieving large numbers of answer set rows. Lesser values can be set with some performance improvement if you are sure the byte count of all answer sets will not exceed the RQRIOBLK value. If you exceed the RQRIOBLK size, the performance penalty is greater than the performance gain by attempting to minimize the RQRIOBLK size.

Maximum Transmission Unit (MTU) setting in TCP/IP

MTU should be set to the maximum allowed by the installed TCP/IP. For Ethernet LANs, this is 1500 and for Token Ring LANs it is 4400.

DB2CLI.INI file

If the application is an ODBC/CLI application, settings in the DB2CLI.INI can affect performance. These settings are especially important in a "chatty" application such as PeopleSoft that sends many messages back and forth across the network. Here are the recommended settings, although some of these might need to be set differently based on your application:

DeferredPrepare=1

CursorHold=0

KeepConnect=1

DB2Degree=any

OptimizeForNRows=x

TXNISOLATION Reduces network traffic by combining line flows and can have a major impact on performance.

Closes non-held cursors between transactions, moderate performance gain.

Caches connection data for applications that connect and disconnect often, minor performance gain.

Lets the database manager determine what level of parallelism should be used for queries.

Where x is the number of rows you expect to be returned. The number of rows that are sent to the client as a single block are bounded by this value. Set to 5000 if you do not know the number of rows that will be returned.

Sets isolation level to cursor stability.

Hardware factors should be considered at the client machine also:

Processor power

The faster the processor, the faster the client machine will process application code as well as send and receive data.

Memory

32 MB minimum, 64 MB recommended. Avoid paging due to lack of memory.

LAN Adapter card

Getting the data on and off the LAN quickly is the goal. Look for fast LAN adapter cards and make sure you set up the buffers in that card to handle a large amount of data. Consider setting these buffers up to the maximum to avoid data retransmits due to discarded data.

Video card

The speed with which your application can paint your monitor screen is also a factor.

TUNING AT THE DB2 CONNECT MACHINE

The following software settings are factors in performance:

RQRIOBLK setting in the DBM configuration

The RQRIOBLK value for DB2 Connect should be set to 32,767. The communication buffer will be initially set to this size until a connection is established from a client and the DB2 Connect gateway can determine the value of RQRIOBLK at the client. Once the gateway know this value, it will reallocate its communication buffer if the client's buffer is not 32,767. Keep them the same on client and gateway to avoid this reallocation.

Maximum Transmission Unit (MTU) setting in TCP/IP

If TCP/IP is being used for communication to clients and/or communication to the DRDA host, MTU should be set to the maximum allowed by the installed TCP/IP. For Ethernet LANs, this is 1500 and for Token Ring LANs it is 4400.

SNA Communications settings (if APPC is being used for communication between gateway and host)

Set the send and receive RUSIZE in the mode table settings to match the RUSIZE at the host. Recommended value is 4096.

Set the send and receive pacing values in the mode table settings to 16.

In the SNA DLC Settings, the Send Frame value should be set at 7. The Receive Frame value should be set at 2 for 3172s and at 4 for NCP and OSA.

The Max BTU value should be set at 16393.

Explicitly bind DDCSMVS.LST with blocking all for all client and database systems. If you are using a WIN/OS/2 environment then you need to run the DDCSMVS.LST job twice, for OS/2 from the \SQLLIB\BND subdirectory, for WIN/OS2 from the \SQLLIB\WIN\BND subdirectory.

Use the CLI/ODBC Administrator tool to pare down tables examined by the application. This is done by specifying a Database Name and a Schema List when configuring an ODBC data source. The first thing an ODBC application does is fetch system catalog information. By specifying the above information you can dramatically reduce the amount of information returned to the client PC, thus speeding up processing.

Run ODBC trace (initiated from Client Configuration Assistant) to check for the presence of SQLGETDATA's. ODBC applications should be written with SQLBINDCOL's instead. End-user will experience severe performance degradation when large result sets are returned to ad-hoc query tools when those tools issue SQLGETDATA's.

Hardware plays a large role in how well DB2 Connect will work. The following hardware factors are important on the DB2 Connect machine:

Processor Power and SpecInt

Processor speed with high SpecInt values is very important for DB2 Connect performance. Consider an SMP system for the best performance. The greater the number of processors, the greater the capacity.

Memory

Sufficient memory on the DB2 Connect machine is vital to performance. Paging must be avoided. Allow for about 200 KB of RAM per client. Consider 64 MB of RAM as a minimum value for DB2 Connect on NT or OS/2. Increase the amount of RAM as the number of clients increases, or if paging occurs.

LAN Adapter card

Getting the data on and off the LAN quickly is the goal. Look for fast LAN adapter cards and make sure you set up the buffers in that card to handle a large amount of data. Consider setting these buffers up to the maximum to avoid data retransmits due to discarded data.

TUNING THE NETWORK

Outside of the transaction time at the DRDA server, network time is where the most time is spent in a transaction, both in the LAN and the WAN, and so has great potential for performance impact.

LAN

The LAN portion of your network connectivity is generally the faster portion as opposed to the WAN portion. Overutilization of the LAN and the devices (Bridges/Routers) will have an effect. Retransmissions due to LAN congestion or other causes are common. Retransmissions are very expensive in terms of response time.

Ethernet

Ethernet tends to have congestion problems when approaching 50 percent LAN utilization.

Token Ring

Token Ring can operate up to 75 to 100 percent LAN utilization, depending on the type of workload. Bulk transfer types of transactions utilize the LAN the most. For best performance, enable early token release.

Bridges and Routers

Bridges generally operate at media speed and therefore create little latency unless the bridge is over-utilized. An over-utilized bridge will result in delays. Routers provide more logic, with the result of increasing the latency. A busy router can add .1 to .3 of a second to the message traffic time. If a client needs to cross many routers to access the database, the latency will add up and the potential for retransmit conditions increases. Eliminate routers if possible. Using the PING command can help to determine the impact of routers.

WAN

The WAN portion of the network is generally where the majority of time is spent. Performance can be affected by the type of devices as well as how those devices are configured. The following are some typical connectivity solutions:

3745

LAN attached (TIC) provides medium to high throughput, dependent on the TIC type and 3745 model. 3745s are also used for connections over long distance by connecting between two 3745s. The line speed of the connection between the 3745s should be as fast as possible to provide the best response time.

OSA

The mainframe is directly connected to the LAN which provides very high throughput.

3172

This can provide LAN/channel attached capabilities as well as "3172 to 3172" attached capabilities for long distance. The LAN/channel attached provides very high throughput and the 3172 to 3172 configuration is depending on the line speed connecting the two.

ESCON

This is a channel attached solution where DB2 Connect is directly channel attached to the mainframe. It does not require DB2 Connect traffic to traverse the LAN like an OSA, 3172, or 3745 solution. This solution provides very high throughput with Multi-Path Channel (MPC) support providing the best throughput.

The fastest gateway to host connections: ESCON, 3172, 2216, and OSA. The slowest... 3174.

The configuration parameters for these devices, VTAM and TCP/IP have large to small effects on response time, dependent on the parameter. Listed below are some of these parameters. They are not all valid in all configurations. Treat this list as parameters to look at depending on your configuration.

Coattailing values (DELAY)

Most devices have one or more DELAY parameters that can be set. For 3745 connectivity, there are two definitions that contain DELAY parameters, the PCCU and the LNCTL=CA. The PCCU is most important and controls outbound coattailing (outbound with reference to the host). The DELAY value determines how long a PIU is held in the queue (NCP or VTAM) before it is transmitted. The purpose of this wait is to increase the possibility that other PIUs will arrive in the interim and all of these can be transmitted on a single channel program. Note also that in some configurations, the PCCU macro is replaced by a PU definition.

For the lowest latency, the DELAY value should be set to 0. Changing the value of the outbound coattailing delay value to 0 should have no noticeable effect on the host except for improved performance for outbound traffic. Some improvement in inbound traffic performance will also be realized. Changing the DELAY on the NCP to 0 should be done with a little more care. The value can be set to 0 if the NCP is not overloaded and the inbound traffic does not consist of a significant percentage of small frames. Setting the values of DELAY to 0 may improve response time significantly.

VTAMB7 PCCU CUADDR=CAF,

AUTODMP=NO,

AUTOIPL=NO,

AUTOSYN=YES,

BACKUP=YES,

DELAY=0, <---------------------- DELAY

VFYLM=YES, /

CHANCON=UNCOND, /

MAXDATA=32768, /

DUMPDS=NCPDUMP, /

OWNER=HOSTB7, /

SUBAREA=17 /

/

/

LNCTLS GROUP LNCTL=CA,CA=TYPE6,DELAY=0.0,TIMEOUT=500.0

CA0 LINE ADDRESS=00

PUCHAN0 PU PUTYPE=5,TGN=1

CA1 LINE ADDRESS=01

PUCHAN1 PU PUTYPE=5,TGN=1

If your DB2 Connect traffic does not go through a FEP (eg. 3745) but instead goes through a 3174 or 3172 then in bound coattailing delays are controlled by configuration parameters on the 3174 (ie. question 223) and 3172 (see GUI setup tool). Outbound coattailing delays can be controlled by class of service parameters in the IBMRDB logmode definitions on the gateway and the host or by the following VTAM definitions:

For a 3174 ... it defined as a LOCAL SNA MAJOR NODE. Under this definition there is VBUILD macro. The DELAY=0 must be coded under the PU macro coded under the VBUILD macro. (MAXBFRU defaults to one for a 3174 ... this should be explicitly coded as 3.)

For a 3172 ... is defined as an XCA Major Node. DELAY=0 should be coded under the PORT macro.

For an ESCON connected RS/6000 ... again it is defined as VTAM Major Node. The DELAY=0 is coded under the PU macro which is under the VBUILD macro. (ESCON RS/6000 connections require additional memory to be allocated under the 'wall' due to DB2 Connect usage.)

Delay parameters have severe performance implications since the network message can be held up in route to and from the client. They have a direct effect on response time, and should be set to zero if at all possible. Watch out for defaults, as the default values tend to be non-zero.

MAXOUT (VTAM PU)

MAXOUT should be set to 7.

PASSLIM (VTAM PU)

PASSLIM should be set to 7.

MAXDATA (VTAM definition)

This value should be set large enough to contain the largest PIU. If set to 33,100, it would be large enough to handle RUs up to 32K.

VPACING (VTAM APPL)

VPACING should be set to 16 or greater (max of 63).

MAXTSL (NCP LINE)

If connecting through a 3745 TIC, set MAXTSL to 16,732.

MAXBFRU (VTAM definition)

Make sure this is large enough to avoid slowdown conditions. It should be set to a value larger (by a factor of 2 0r 3 times) the largest PIU size.

IN ADDITION...

Take advantage of application development techniques that will reduce network traffic and improve performance. These include:

Compound SQL

Stored procedures

Record blocking

Static SQL