Problem solve Get help with specific problems with your technologies, process and projects.

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.


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

The Best Web Links: tips, tutorials and more.

Check out this Search400.com Featured Topic: Database issues resolved

Dig Deeper on Oracle on iSeries

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.