User's Guide

Database Tuning

System performance will be affected by the performance of the DRDA 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 DRDA server system performance documentation for more information.

For DB2 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.

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)
• 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 7 shows when conversion takes place.

Table 7. 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 DRDA 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 ]