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.

About rpillai

I am a technology enthusiasts and love to work with databases and other technology. Learning new things everyday and don't think the path ever ends ...
This entry was posted in db2 and tagged , , , , , , , , , , , , . Bookmark the permalink.