DB2 Connect User's Guide

Database Tuning

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 journaling. Please note that 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.

Tuning DB2 for OS/390

You will find the best performance in OS/390 V2R5 and later versions. TCP/IP support was introduced in OS/390 R3, so this is the base release level if you wish to use TCP/IP.

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. In order 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 instance as follows:

DDF LOCATION=LOC1,LUNAME=LU1,PORT=8000,RESPORT=8001

For best performance, you should use the recommended DDF address space prioritization (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 CMSTAT, CONDBAT and MAXDBATM affect thread processing. For best performance, set CMSTAT 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 on-line Connectivity Supplement.

Data Conversion

When data is transferred from one environment to another, it may need to be converted. This conversion can affect performance.

Consider the following platforms:

• Intel (OS/2 or Windows NT)

• IEEE (UNIX-based systems)

• System/370 and System/390 (MVS, OS/390, VM, and VSE)

• OS/400.

and the following types of numeric data:

• Packed decimal

• Zoned decimal

• Integer

• Floating point.

Table 8 shows when conversion takes place.

Table 8. Data Conversion

	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.

Data Types for Character Data

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:

• If the size of actual data varies significantly, VARCHAR is more efficient because CHAR adds extra blank characters to fill the field. These blank characters must be transmitted across the network like any other characters.

• If the size of actual data does not vary much, CHAR is more efficient because each VARCHAR field has a few bytes of length information which must be transmitted.

[ DB2 List of Books | Search the DB2 Books ]