1

In short, does the Primary Key double as a Unique Index?

Some background: I am trying to load a bunch of data into two tables using the DB2 Load utility loading in IXF file types. The data is separated out by table, and by month per table, so I need to run the load command repetitively. The actual load of the data doesn't appear to take to long, however, the utility goes and rebuilds the indexes after each load. This is progressively making my execution time longer and longer with each run as the tables grow. In the DB2 documentation for the load utility, it shows a indexing mode of deferred option that sounds like it skips that step (recommending that you do a rebuild on the very last load). When I set this option, I get the following error on execution for both tables...

SQL3214N  The load operation failed because the table is incompatible with the DEFERRED index mode. Reason code: "1".

Now I have gone through and looked at the syscat.indexes table and neither table has a "Unique Index". Both have primary keys and only one has a unique key, which has prompted my question above.

0

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0061097.html

It is important to understand that there is no significant difference between a primary key or unique key constraint and a unique index. To implement the concept of primary and unique key constraints, the database manager uses a combination of a unique index and the NOT NULL constraint

0

How did you determine that the table(s) has no unique indexes? Primary keys and unique constraints are implemented via unique indexes (unless the are informational constraints, i.e. NOT ENFORCED). Try:

SELECT INDSCHEMA, INDNAME
FROM SYSCAT.INDEXES
WHERE UNIQUERULE IN ('U','P')
  AND TABSCHEMA = '...'
  AND TABNAME = '...'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.