--- title: "DB2 Plan Table Columns Explained" description: "Here's a way to verify PLAN Table columns after you run the EXPLAIN query." url: https://srinimf.com/2014/03/14/db2-plan_table/ date: 2014-03-14 modified: 2026-10-10 author: "Srini" image: https://i0.wp.com/srinimf.com/wp-content/uploads/2014/03/pexels-photo-4508751.jpeg?fit=1880%2C1253&ssl=1 categories: ["DB2"] tags: ["DB2 Explain", "DB2 Plan_Table", "Db2 Tips"] type: post lang: en --- # DB2 Plan Table Columns Explained You need to verify the following columns in the PLAN_TABLE after you run EXPLAIN query. Related[Mastering EXPLAIN PLAN: Improve SQL Performance](https://srinimf.com/2016/03/11/10-impressive-db2-explain-plan-validations-you-need-for-sql-performance/) ## PLAN Table in DB2 1) **QUERYNO:** A number identifies the statement being explained. 2) **METHOD:** 0 – First table accessed, a continuation of the previous table accessed or not used Related[3 DB2 Optimizer Interview Questions](https://srinimf.com/2015/05/08/db2-optimizer-interview-questions/) 1 – Nested Loop Join 2 – Merge Scan Join 3 – Sorts required by ORDER BY, GROUP BY, SELECT DISTINCT, UNION 4 – Hybrid Join 3) **ACCESSTYPE:** I – By an Index I1 – One fetch Index Scan M – Multiple index scan MX – By Index mentioned in ACCESSNAME MI – Intersection of Multiple indexes MU – Union of multiple indexes N – Index scan when matching predicated in IN keyword R – Tablespace scan RW – Work file scan of a materialized user-defined table function T – By a spare index – Star join work files V – By buffers for an INSERT statement within a SELECT Blank – NA 4) **INDEXONLY:** Whether access to an Index alone is enough to carry out the step 5) **TSLOCKMODE:** IS – Intent Share Lock IX – Intent Exclusive lock S – Share Lock U – Update Lock X – Exclusive Lock SIX – Share with Intent Exclusive lock N – UR Isolation: No Lock NS – For CS, RS, RR an S Lock NIS – For CS, RS, RR an IS Lock NSS – For CS, RS an IS Lock and for RR an S Lock SS – For UR, CS, and RS an IS Lock and for RR an S Lock 6) **PREFETCH:** S – Pure Sequential L – thru a Page List D – Optimizer expects dynamic prefetch Blank – Unknown at bind time or NA 7)**ACCESS_DEGREE:** Number of Parallel tasks or operations activated by a Query 0 – if t[here is](https://srinimf.com/2014/09/01/maximum-limits-on-db2/) a host variable 8) **PARALLELISM_MODE:** I – Query I/O parallelism C – Query CP parallelism X – Sysplex query parallelism 9) **PAGE_RANGE:** Whether the table qualifies for page range (scan only the partitions that are needed) Y – Yes Blank – No 10) **JOIN_TYPE:** F – Full Outer Join L – Left Outer Join S – Star Join Blank – Inner Join or No join Note: Right Outer Join always converted to Left Outer Join 11) **WHEN_OPTIMIZE:** Blank: At bind time, using a default filter factor for any host variables, parameter markers, or special registers B – Above facts + Bind option REOPT (ALWAYS) or REOPT (ONCE) must be specified. R – At Runtime, using input variables, parameter markers, or special registers. Bind option REOPT (ALWAYS) or REOPT (ONCE) must be specified. 12) **QBLOCK_TYPE:** SELECT – Select INSERT – Insert UPDATE – Update DELETE – Delete SELUPD – Select FOR UPDATE OF DELCUR – DELETE WHERE CURRENT OF CURSOR UPDCUR – UPDATE WHERE CURRENT OF CURSOR CORSUB – Correlated Subquery NCOSUB – NonCorrelated Subquery TABLEX – Table Expression TRIGGER – WHEN clause on CREATE TRIGGER UNION – Union UNIONA – Union All 13) **PRIMRY-ACCESSTYPE:** D – Direct Row access Blank – No Direct Row access 14) **TABLE_TYPE:** B – Buffers for an INSERT statement within a SELECT C – Common Table Expression F – Table Function M – Materialized Query Table Q – Temp intermediate table(Not Materialized), name of the viewer nested table expression, Contains a UNION ALL where materialization was virtual not actual. RB – Recursive Common Table Expression T – Table W – Work file (Materialized) 15) **TABLE_ENCODE:** A – ASCII E – EBCDIC U – Unicode M – when multiple CCSID is in one table Apart from the above DBA has to verify Accounting Reports [to get CPU](https://srinimf.com/2021/11/06/how-to-get-cpu-time-for-sql-query/) time or elapsed time. Related - [Mastering EXPLAIN PLAN: Improve SQL Performance](https://srinimf.com/2016/03/11/10-impressive-db2-explain-plan-validations-you-need-for-sql-performance/)DateMarch 11, 2016 - [DB2 in simple way: what is index only access](https://srinimf.com/2016/09/17/db2-in-simple-way-what-is-index-only-access/)DateSeptember 17, 2016 - [DB2: Here is All About Explain Plan](https://srinimf.com/2014/09/23/db2-performance-tuning-explain-plan/)DateSeptember 23, 2014 ### Share this: - - [Tweet](https://twitter.com/share) - [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2014%2F03%2F14%2Fdb2-plan_table%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2014%2F03%2Fpexels-photo-4508751.jpeg%3Ffit%3D1200%252C800%26ssl%3D1&description=DB2%20Plan%20Table%20Columns%20Explained) - - [Share on Tumblr](https://www.tumblr.com/share) - [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2014/03/14/db2-plan_table/?share=jetpack-whatsapp) - [More](#) - - [Print (Opens in new window) Print](https://srinimf.com/2014/03/14/db2-plan_table/?share=print) - [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2014/03/14/db2-plan_table/?share=reddit) - [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2014/03/14/db2-plan_table/?share=telegram) - [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%20Plan%20Table%20Columns%20Explained&body=https%3A%2F%2Fsrinimf.com%2F2014%2F03%2F14%2Fdb2-plan_table%2F&share=email) - ### Like this: Like Loading…