Interview Questions   Tutorials   Discussions   Programs   

DB2 - What is a DB2 access path?

asked mar August 25, 2014 11:45 AM  

What is a DB2 access path?


1 Answers

answered By Mswami   0  
BIND command reads the SQL statements from the DBRM and produces a mechanism to access data(access path), in an efficient manner, as directed by the SQL statements being bound.

The access path is determined by DB2 during the bind process. This determines what indexes will be used.

DB2 Access path is (in most cases) the path which results in the most optimized execution of an SQL query.
Whenever any Static SQL (Embedded SQL) is bound to a package/plan, the DB2 optimizer creates the most efficient (optimized) way to access the data from the Query using the Predicates Used in the QUERY (AND,OR, GROUP BY, ORDER BY, >=, IN, IS NULL,etc.) and also the information from DB2 Catalog of available indexes on the tables used in the SQL and also using the table statistics (no. of rows in tablespace, no. of distinct values of a column,etc.) . The result is an access path which decides which predicates to filter first (indexable, stage1 or stage2). The Access Path is stored in the Package/Plan in the DB2 Directory which is not Queryable. The DB2 Catalog merely stores information about Access Path created during BIND in the OWNER.PLAN_TABLE Catalog table.
   add comment

Your answer

Join with account you already have



 Write A Tutorials
Online-Classroom Classes

  1 person following this question

  1 person following this tag

  Question tags

Asked 1 year and 1 month ago ago
Number of Views -186
Number of Answers -1
Last updated
2 years and 1 month ago ago

Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!