Improving Performance

The Dynamic DB2/SQL Service is designed to efficiently transfer requests and data between your REXX programs and DB2. However, to obtain optimum performance, you should "tune" you application using the measures outlined in the sections that follow.

SQL Design PREPARE Avoidance

SQL Design

The single most important factor in the performance of your applications is the design of your databases and SQL statements. While this topic is beyond the scope of this manual, there are several "common sense" items you should be aware of:

Only retrieve data that is required by your application. Using "SELECT *" when all you need is one or two columns, wastes time and storage.
Declare host variables to be of the same type as the columns they are to be assigned into or compared with. For numeric types in particular, ensure that the precision of the host variable does not exceed that of the column. If you follow this rule, DB2 may be able to use an index, which cuts down on search time.
Note: The LIKE predicate forces an exception to this rule. See DB2 SQL Reference, SC26-4380, for more about the LIKE predicate. It suggests that host variables containing pattern strings should be declared VARCHAR instead of CHAR, regardless of the column's type.
List only those columns that you actually want to update in FOR UPDATE OF clauses.

There are many more SQL optimizations you can make. For more information on coding SQL efficiently, refer to DB2 Application Programming and SQL Guide for your system's release of DB2.

PREPARE Avoidance

The dynamic interface keeps track of previously PREPAREd statements. When the interface detects that a statement is the same as one it has PREPAREd before, it can skip the PREPARE. This is an important time saver, because much of the work DB2 must perform to determine how to run a statement is done during the PREPARE.

There are 3 things you can do to help ensure that PREPAREs are skipped:

When possible, code host variable expressions to make your statements variable. A statement that uses REXX expression evaluation to supply variable information may not match with a previously prepared statement. For example you should code:
```
/* A host variable expression */
"exec sql insert into mytab values(:v1,:v2);"
```
instead of:
```
/* A REXX expression (abuttal concatenation) */
"exec sql insert into mytab values("v1","v2");"
```
Try to code your SQL statements as consistently as possible. The prepared statement lookaside process will not be confused by different host variable names, differences in case, or by multiple blanks. However, it can be confused if you use some blanks in one situation but don't use any blanks in another. For example, the following statements are equivalent from DB2's perspective, but the third statement will cause the lookaside process to miss an opportunity to avoid a PREPARE:
```
"exec sql insert into mytab values(:v1,   :v2);"
"EXEC SQL Insert    Into MYTAB   VALUES(:z1, :z2);"
 
"EXEC SQL INSERt  INTO  Mytab VALUES(:q,:p);"
```
The difference is that the third statement has no blanks between ":q" and ":p".
COMMIT your work as often as required, but not too often. COMMIT statements invalidate all previously PREPAREd statements (except for prepared statements associated with "hold" cursors). As a consequence, statement PREPAREs will have to be redone.