Ask the Expert

What are internal indexes?

I've recently read a quote from "Database Design and Programming for DB2/400" that has me quite confused. It says, "For each primary, unique or foreign key constraint, DB2/400 creates an internal index in the physical file object. Unlike the DDS key specifications for a physical file, however, neither a primary key nor a unique key defines any particular ordering for a table's rows".

I've been coding DDS for years and my idea of an index is some kind of table that maps key values to RRN's, this is what I call an index or access path. So what is this "internal indexes" that the system maintains when you define a constraint over an SQL table? I can't imagine what they look like, especially for foreign key constraints.

The "internal index" just means that if DB2 creates an index for the enforcement of a constraint, the index is hidden from the user/administrator. Actually before it creates an internal index, DB2 first tries to share any existing index or keyed LF that might already exist over the primary key or foreign key columns. So the "internal index" is just like the indexes you have been using via DDS.

=================================='s targeted search engine: Get relevant information on DB2/400.

The Best Web Links: tips, tutorials and more.

Check out this Featured Topic: Database issues resolved

This was first published in July 2003

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: