{"id":309,"date":"2009-05-03T11:58:17","date_gmt":"2009-05-03T05:58:17","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/?p=309"},"modified":"2016-01-07T11:31:13","modified_gmt":"2016-01-07T05:31:13","slug":"ive-heard-of-stage-1-and-stage-2-predicatehave-you-heard-of-stage-3-and-4","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=309","title":{"rendered":"I know stage 1 and stage 2 predicate&#8230;whats stage 3 and 4 ??"},"content":{"rendered":"<p>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 \u00c2\u00a0predicate 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. \u00c2\u00a0Stage 1 is a lot more efficient because if the predicate is\u00c2\u00a0nonsargable, then db2 has to bring in the data to RDS to process and eliminate the rows. \u00c2\u00a0Predicates that are stage 1 (sargable) and stage 2 vary with db2 versions and sometimes even with PTFs, so check chapter 12 of the <a href=\"http:\/\/publib.boulder.ibm.com\/infocenter\/dzichelp\/v2r2\/topic\/com.ibm.db29.doc.perf\/dsnpfk15.pdf?noframes=true\" target=\"_blank\">DB2 Performance and Tuning Guide<\/a><\/p>\n<p>So whats Stage 3 and Stage 4&#8230;&#8230;<a href=\"http:\/\/www.db2portal.com\/2009\/04\/stages-3-and-4.html\" target=\"_blank\">Craig Mullins<\/a> has written a great blog on what stage 3 and stage 4. \u00c2\u00a0 Basically its when the application programmer has coded the predicate in the application code instead of the SQL. \u00c2\u00a0In other words , the data is filtered at the application level using IF THEN ELSE \u00c2\u00a0condition. \u00c2\u00a0My take on it is, its a lot of wasted I\/O and CPU. \u00c2\u00a0I rather see the data getting filtered out in SQL rather than brining in the data to the application and evaluating. But check out <a href=\"http:\/\/www.db2portal.com\/2009\/04\/stages-3-and-4.html\" target=\"_blank\">Craig&#8217;s \u00c2\u00a0blog<\/a> for some interesting view.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 \u00c2\u00a0predicate can be processed in the data manager process, its called [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[3],"tags":[176,943,173,75,174,175,8,168,169,170,171,172,29],"class_list":["post-309","post","type-post","status-publish","format-standard","hentry","category-db2","tag-database-manager","tag-db2","tag-optimizer","tag-performance","tag-predicate","tag-rds","tag-sql","tag-sql-tuning","tag-stage-1","tag-stage-2","tag-stage-3","tag-stage-4","tag-tuning"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/309","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=309"}],"version-history":[{"count":5,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/309\/revisions"}],"predecessor-version":[{"id":1545,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/309\/revisions\/1545"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=309"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=309"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=309"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}