Avoid numeric conversions

When a column value and a host variable (or constant value) are being compared, try to specify the same data types and attributes. Db2 for i might not use an index for the named column if the host variable or constant value has a greater precision than the precision of the column. If the two items being compared have different data types, Db2 for i needs to convert one or the other of the values, which can result in inaccuracies (because of limited machine precision).

To avoid problems for columns and constants being compared, use the following:

  • same data type
  • same scale, if applicable
  • same precision, if applicable

For example, EDUCLVL is a halfword integer value (SMALLINT). When using SQL, specify:

… WHERE EDUCLVL < 11 AND
                   EDUCLVL >= 2

instead of 

… WHERE EDUCLVL < 1.1E1 AND
                   EDUCLVL > 1.3

When using the OPNQRYF command, specify: 

... QRYSLT('EDUCLVL *LT 11 *AND ENUCLVL *GE 2')

instead of 

... QRYSLT('EDUCLVL *LT 1.1E1 *AND EDUCLVL *GT 1.3')

If an index was created over the EDUCLVL column, then the optimizer might not use the index in the second example. The constant precision is greater than the column precision. It attempts to convert the constant to the precision of the column. In the first example, the optimizer considers using the index, because the precisions are equal.