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.

==================================
MORE INFORMATION ON THIS TOPIC
==================================

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


This was first published in July 2003

Back to top