What is the difference between an access path and an open data path? When would an access path be created, and when would an open data path will be created? Can you provide an example of when to use each?
An access path and an open data path are used together to process the rows in a database file.
The open data path (ODP) is the path through which all input and output operations are performed on a database file. The ODP is used to connect the requesting program with the data in the file. As data is retrieved from or inserted into the file, the ODP will use an access path to navigate to a row within in the file. If the rows need to be processed in a sorted owner, then someone will need to supply a keyed access path by creating a keyed physical file, keyed logical file or SQL index.
An ODP is created and used each time that a file is opened or when an SQL statement is executed. There are ways to have an ODP shared or reused instead of creating the ODP each time.
Dig Deeper on DB2 UDB (universal databases)
Related Q&A from Kent Milligan
Create a host variable of the where in statement on the fly with dynamic SQL. Continue Reading
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ... Continue Reading
When working with DB2 files with columns that have both short and long names, there is no option choose which column names are returned via ODBC ... Continue Reading