RANDOM clause on an index is available only after you’ve upgraded your DB2 9 subsystem to New Function Mode (NFM).
Index on expression: “That’s a stage 2 predicate” is a cry that can strike fear into the heart and mind of an application programmer. Sometimes a stage 2 predicate isn’t used by choice but rather necessity. What can you do if a column contains both uppercase and lowercase characters and there’s no way of predicting which you might find? What if you really wanted only the stuff from the year 2005, yet all the data is stored with full dates? What if you have to perform a calculation on two columns, for example, as part of the predicate? Figure 1 shows examples of how an expression could be used to solve the query.
However, as we all know from the predicate types and processing table (Table 34) in the DB2 9 Performance Monitoring and Tuning Guide (SC18- 9851), or the same table (Table 124) in the DB2 V8 Administration Guide (SC18-7413), expressions used in predicates are often nothing more than a performance problem waiting to happen. The following is a list of expressions that might, but not always, result in a stage 2 predicate:
• “COL BETWEEN expression1 AND expression2”
• “T1.COL = T2 col expr”
• “T1.COL <> T2 col expr”
• “expression = value”
• “expression <> value”
• “expression op value”