				Wiki
	updated on 2004.06.23

ベールを脱いだアクセスパスの神秘（NEWS400編）

[ Previous ] [ HOME ] [ Upper ]

NEWS 3X/400 March 1993

AS/400 Access Path Sharing

By Richard M. Rubin (Summary)

.. omit ...

What is an access path ?

An access path is an index that refers to the data in one or more physical files. A physical file member's data is contained in a Machine Interface (MI) object called a data space. An access path is also an MI object, known as a data space index. Every data space index (except those created at runtime by the query processor) belongs to some file member. It is because the system, under the covers, regards an access path as a separate object, that an access path can be shared by two or more file members.

... omit...

A logical file access path can refer to records in one or more data spaces. Every logical file member has a scope list, which contains a list of physical members over which the logical file is built and a stipulation of how each physical file member participates in the access path's structure. Logical file also have a file-level scope list that specifies which physical files an access path can be built over.

... omit ...

The key structure

The key structure of an access path has four components : the set of fields that comprise the key , the key field order, the direction of each key field, and the collating sequence of each key field.

Key field order is simply the order in which the key fields occur.
Key field direction is the order in which values for the same field from different records are arranged. (descending, ascending.)
Collating sequence is the method by which field values are compared. (The default on the AS/400 is the EBCDIC.)

Rules for Access Path Sharing

Two file members can share an access path if they:

have the same scope list (are built over the same physical file members)

and if the access paths their file description specify have the same:

key field order
key field direction
collating sequence at both file and field level
duplicate key attribute (UNIQUE, FIFO, LIFO, FCFO (first-changed, first-out), or unspecified)

Exceptions:

A new FIFO, LIFO or FCFO can share an existing UNIQUE.
A new UNIQUE can share an existing FIFO or unspecified with *REBLD maintenance.
A new unspecified can share anything else (FIFO, LIFO, FCFO, or UNIQUE)

number of keys

Exception:

a new file member can have fewer keys than an existing file member if the new member's duplicate key attribute is unspecified (i.e., it is neither FIFO, LIFO, or FCFO)

selection criteria

Exception:

a DYNSLT access path can share or be shared by another DYNSLT access path with different selection criteria on an access path with no selection criteria.

... omit ...

Order of access path creation

The order in which access paths are created affects sharing only in the case where the number of keys is not the same, and in the exception to rule duplicate key. Suppose File A has one member and that this member could share the the access path of File B's only member, but isn't sharing it because File A was created first. To make A share B, you do not have to delete A and recreate it. Simply remove and then read its members.

... omit ...

Access Path Ownership

An access path is initially owned by the file that created it. (Although, strictly speaking, only user profile own objects, it is customary to speak of an object as "owning" its components so as to distinguish them from the components the object merely shares or refers to.) If the original file is deleted, the other logical files that share the access path are examined in the order that the members sharing the access path were created (added) to see whether the user profile owning the file has enough unused space to absorb the access path ( you can limit the amount of storage a user profile's object can take up). The first file whose owner has enough space, the access path is no longer owned by any file, because the security officer becomes its owner.

A physical file always owns its access path, because the physical file must be created before any logical files, and the access path can't be deleted without deleting the physical file itself --- and all of its logical file as well.

... omit...

Developing an eye for access path sharing is a critical part of AS/400 database management. Programmers and database designers who don't understand the rules of access path sharing or importance of minimizing the number of access paths tend to create many unnecessary access paths. Those who do understand that too many access paths can hamper update performance may be reluctant to create a new logical file for fear of creating another access path, and thus fail to take full advantage of OS/400 data management capabilities.

... omit ...

[ Previous ] [ HOME ] [ Upper ]

You are at K's tips-n-kicks of AS/400

SEO		[PR] 爆速!無料ブログ無料ホームページ開設無料ライブ放送