Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Primary key constraint vs. a unique index

Usually, if creating PF's and LF's the primary key should not be defined on the PF, but on the LF instead -- this should improve performance. Using DDL and SQL one can define tables as well as indexes. Taken performance into account, what's the best way of defining tables and indexes if one is to use embedded SQL within a HLL program? Should one define indexes? Where should the primary key be placed? Is it better to use the table instead of the index in the embedded SQL?

There's really no performance difference between a primary key constraint and a unique index, so the choice is really yours. In my opinion, if the columns (or fields) are the primary key, then a primary key constraint should be defined. SQL constraints and indexes are created with a larger logical page size that keyed LF's, so it would be beneficial to create the index and constraint objects with SQL.

SQL standards do not allow an SQL index to be referenced on a SELECT statement, so the embedded SQL has to reference the table. Even if SQL did allow index references it should not change the performance.


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

The Best Web Links: tips, tutorials and more.

Check out this online event, Getting the Most out of SQL & DB2 UDB for the iSeries.

Dig Deeper on DB2 UDB (universal databases)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.