In the sections that follow, some performance techniques about the database and SQL language environment are described. To learn about DB2 performance considerations, visit the web at: http://review.ibm.com/software/data/db2/performance
The following summary outlines some of the most simple database techniques that can improve database access:
For example, EDUCLVL is a halfword integer value (SMALLINT). Specify:
... WHERE EDUCLVL < 11 AND EDUCLVL >= 2
Instead of:
... WHERE EDUCLVL < 1.1E1 AND EDUCLVL > 1.3
For example, EMPNO is CHAR(6) and DEPTNO is CHAR(3). Specify:
... WHERE EMPNO > '000300' AND DEPTNO < 'E20'
Instead of:
... WHERE EMPNO > '000300 ' AND DEPTNO < 'E20 '
... WHERE LASTNAME LIKE 'J%SON%'
However, when used at the beginning of a character string, LIKE patterns can prevent DB2 from using any indexes that might be defined on the LASTNAME column to limit the number of rows scanned. For example:
... WHERE LASTNAME LIKE '%SON'
Avoid using these symbols at the beginning of character strings, especially if you are accessing a particularly large table.
Note that Net.Data reissues the query for every page because the cursor position is not maintained across requests.
Because Net.Data uses variable substitution to optimize the query, take care to handle single quotes correctly in your SQL statements. For example, if the column returns a string, use the DTW_ADDQUOTE() function to escape the string's single quotes.