Ask the Expert

Difference between Access path and Open data path

What is the difference between Access path and Open data path?
Open Data Paths (ODPs) provide a way for more than one program in the same job to share the same file status information (I/O feedback areas), file pointer positions, and storage area. ODP's are quite useful as they can improve performance, reduce the amount of main storage needed by the job, and reduces file opens/closes.

For native I/O access you can set up ODP's by specifying SHARE(*YES) on the CRTPF, CHGPF, or OVRDBF commands. However, SQL based I/O access is not influenced by the SHARE (*YES) setting. The DB2 SQL engine is solely responsible for creating and reusing ODP's for I/O performed from SQL-based interfaces (and does so automatically).

An access path (also known as a keyed logical file or an index in SQL terms) is data structure that represents the order in which data will be retrieved from a file. It provides a quick way of locating data. Without an access path, a program would be forced to read every row in the table to find the particular rows of interest. This technique (known as a full table scan), can be a lengthy process, depending on the size of the table and how many rows are being retrieved. A scan using an access path tends to be more efficient than a full table scan when a small percentage of rows are selected since the length of the access path key value is usually shorter than the length of the table row.


Visit the ITKnowledge Exchange and get answers to your DB2 questions fast.

Check out this Featured Topic: Expert advice on DB2's targeted search engine: Get relevant information on DB2/400.

The Best Web Links: Tips, tutorials and more.

This was first published in March 2005

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: