Access path and an open data path differences

RELATED CONTENT DB2/400 and DB2 UDB When is the YES option for 'reuse deleted files' function the best choice? Monitoring members 'stuck' within a physical file on an EDI system Developing tables in a parent-child relationship in DB2 SQL server error message -321 Creating a host variable of the 'where in' statement in SQL Choose which column names are returned via ODBC when working with DB2 files Allow access to data from a stored procedure result set using COBOL or RPG Convert a numeric physical file to a character in SQL without leading zeros Inserting data from a CTE into a file in SQL Implement variables in SQL when creating an alias DB2 UDB (universal databases) Oracle boasts 11g on SPARC is faster than IBM DB2 on Power 595 When is the YES option for 'reuse deleted files' function the best choice? Monitoring members 'stuck' within a physical file on an EDI system Developing tables in a parent-child relationship in DB2 SQL server error message -321 Creating a host variable of the 'where in' statement in SQL Choose which column names are returned via ODBC when working with DB2 files Database performance comparisons on IBM i Implement variables in SQL when creating an alias Simplify the process of converting a spool file from iSeries into an Excel spreadsheet DB2 UDB (universal databases) Research

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

EXPERT RESPONSE FROM: Kent Milligan Pose a Question Other iSeries 400 Categories Meet all iSeries 400 Experts Become an Expert for this site

ANSWERED January 2009:
An access path describes the order in which the rows are retrieved from a database file. If the rows in the file are accessed in a physical sequence, that is known as an arrival sequence access path. If the rows need to be processed in an ordered manner, then a keyed access path is needed to sort the data in the specified order. With DB2 for iSeries, keyed access paths are supplied to DB2 by creating a keyed logical file, keyed physical file, or SQL index.

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.