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
Dig deeper on Oracle on iSeries
Related Q&A from Kent Milligan
To monitor members stuck within a physical file on AS/400, you can periodically use the display file description (DSPFD) command to create an output ...continue reading
Create a host variable of the where in statement on the fly with dynamic SQL.continue reading
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.