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.
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 online event, Getting the Most out of SQL & DB2 UDB for the iSeries.
Dig Deeper on DB2 UDB (universal databases)
Related Q&A from Kent Milligan
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
When working with DB2 files with columns that have both short and long names, there is no option choose which column names are returned via ODBC ... Continue Reading
When developing tables in a parent-child relationship, use a primary key and a foreign key along with a unique ID to make your database easier to ... 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.