Coding for effective indexes: Be aware of the instances where DB2 UDB for AS/400 does not use an index

DB2 UDB for AS/400 does not use indexes in the following instances:

• For a column that is expected to be updated; for example, when using SQL, your program might include the following:

  EXEC SQL
   DECLARE DEPTEMP CURSOR FOR
      SELECT EMPNO, LASTNAME, WORKDEPT
        FROM CORPDATA.EMPLOYEE
        WHERE (WORKDEPT = 'D11' OR
                 WORKDEPT = 'D21') AND
                EMPNO = '000190'
        FOR UPDATE OF EMPNO, WORKDEPT
  END-EXEC.
  

  When using the OPNQRYF command, for example:

  OPNQRYF FILE((CORPDATA/EMPLOYEE)) OPTION(*ALL)
     QRYSLT('(WORKDEPT *EQ ''D11'' *OR WORKDEPT *EQ ''D21'')
     *AND EMPNO *EQ ''000190''')
  

  Even if you do not intend to update the employee's department, DB2 UDB for AS/400 cannot use an index with a key of WORKDEPT.

  DB2 UDB for AS/400 can use an index if all of the updateable columns used within the index are also used within the query as an isolatable selection predicate with an equal operator. In the previous example DB2 UDB for AS/400 would use an index with a key of EMPNO.

  DB2 UDB for AS/400 can operate more efficiently if the FOR UPDATE OF column list only names the column you intend to update: WORKDEPT. Therefore, do not specify a column in the FOR UPDATE OF column list unless you intend to update the column.

  If you have an updateable cursor because of dynamic SQL or the FOR UPDATE clause was not specified and the program contains an UPDATE statement then all columns can be updated.

• For a column being compared with another column from the same row. For example, when using SQL, your program might include the following::

  EXEC SQL
   DECLARE DEPTDATA CURSOR FOR
      SELECT WORKDEPT, DEPTNAME
        FROM CORPDATA.EMPLOYEE
        WHERE WORKDEPT = ADMRDEPT
  END-EXEC.
  

  When using the OPNQRYF command, for example:

  OPNQRYF FILE (EMPLOYEE) FORMAT(FORMAT1)
     QRYSLT('WORKDEPT *EQ ADMRDEPT')
  

  Even though there is an index for WORKDEPT and another index for ADMRDEPT, DB2 UDB for AS/400 will not use either index. The index has no added benefit because every row of the table needs to be looked at.