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.