I know stage 1 and stage 2 predicate…whats stage 3 and 4 ??

When tuning a SQL, one of the things a DBA looks at is to see whether the predicate in the SQL is stage 1 or stage 2. So what is stage 1 and stage 2. Basically when DB2 optimizer evaluates the predicate, if the  predicate can be processed in the data manager process, its called a stage 1 predicate. If DB2 has to pass it to the RDS, then its called stage 2 predicate.  Stage 1 is a lot more efficient because if the predicate is nonsargable, then db2 has to bring in the data to RDS to process and eliminate the rows.  Predicates that are stage 1 (sargable) and stage 2 vary with db2 versions and sometimes even with PTFs, so check chapter 12 of the DB2 Performance and Tuning Guide

So whats Stage 3 and Stage 4……Craig Mullins has written a great blog on what stage 3 and stage 4.   Basically its when the application programmer has coded the predicate in the application code instead of the SQL.  In other words , the data is filtered at the application level using IF THEN ELSE  condition.  My take on it is, its a lot of wasted I/O and CPU.  I rather see the data getting filtered out in SQL rather than brining in the data to the application and evaluating. But check out Craig’s  blog for some interesting view.