DB2 Load Utility Primary Key vs. Unique Index

db2db2-luwindex

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.

Best Answer

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 = '...'

Disclaimer

This is experimental and only tested rudimentarily. Proceed at your own risk. I would not use it myself and just drop / recreate constraints with standard DDL commands. If you break entries in the catalog tables you could easily mess up your database.

For all I know, there are only two differences between a PRIMARY KEY and a UNIQUE constraint in the catalog tables (the index itself is identical):

pg_index.indisprimary:
For PRIMARY KEY constraint ... TRUE
For UNIQUE constraint ... FALSE

pg_constraint.contype:
PRIMARY KEY constraint ... 'p'
UNIQUE constraint ... 'u'

You could convert constraint and index in place, from PRIMARY KEY constraint to UNIQUE constraint, my_idx being the (optionally schema-qualified) index name:

UPDATE pg_index SET indisprimary = FALSE WHERE indexrelid = 'my_idx'::regclass
UPDATE pg_constraint SET contype = 'u' WHERE conindid = 'my_idx'::regclass;

Or upgrade from UNIQUE to PRIMARY KEY:

UPDATE pg_index SET indisprimary = TRUE WHERE indexrelid = 'my_idx'::regclass;
UPDATE pg_constraint SET contype = 'p' WHERE conindid = 'my_idx'::regclass;

Change a partitioned global non-unique index that is the primary key to a non-partitioned unique index

You can do this with DBMS_REDEFINITION, but you'll need double the space occupied by the table.

If I read your question correctly, this should do it:

[oracle@node1 ~]$ sqlplus phil/phil

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jan 6 18:47:12 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set echo on
SQL> @part
SQL>
SQL> drop table PARTITIONED_INTERIM;

Table dropped.

SQL>
SQL> drop table partitioned;

Table dropped.

SQL>
SQL> create table partitioned
  2  (
  3  pk number,
  4  blah  varchar(40),
  5  blah2 varchar(40)
  6  )
  7  partition by range (pk)
  8  (partition p1 values less than (1000),
  9   partition p2 values less than (10000),
 10   partition p3 values less than (30000),
 11   partition p4 values less than (MAXVALUE)
 12   );

Table created.

SQL>
SQL> create index pk_idx
  2  on partitioned (pk)
  3  global partition by range (pk)
  4  (partition p1 values less than (1000),
  5   partition p2 values less than (10000),
  6   partition p3 values less than (30000),
  7   partition p4 values less than (MAXVALUE)
  8   )
  9  ;

Index created.

SQL>
SQL> ALTER TABLE partitioned ADD CONSTRAINT ppk PRIMARY KEY (pk) USING INDEX pk_idx;

Table altered.

SQL>
SQL> select index_name, uniqueness , table_name, partitioned
  2  from user_indexes
  3  where table_name = 'PARTITIONED';

INDEX_NAME                     UNIQUENES TABLE_NAME                     PAR
------------------------------ --------- ------------------------------ ---
PK_IDX                         NONUNIQUE PARTITIONED                    YES

SQL>
SQL> EXEC dbms_redefinition.can_redef_table('PHIL', 'PARTITIONED');

PL/SQL procedure successfully completed.

SQL>
SQL> create table PARTITIONED_INTERIM as ( select * from PARTITIONED where 1=0 );

Table created.

SQL>
SQL> exec DBMS_REDEFINITION.start_redef_table(uname =>     'PHIL',orig_table => 'PARTITIONED',int_table => 'PARTITIONED_INTERIM');

PL/SQL procedure successfully completed.

SQL>
SQL> create unique index partitioned_interim_pk on partitioned_interim (pk) global online;

Index created.

SQL>
SQL> exec dbms_redefinition.sync_interim_table(uname => 'PHIL',orig_table => 'PARTITIONED',int_table => 'PARTITIONED_INTERIM');

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_redefinition.finish_redef_table(uname => 'PHIL',orig_table => 'PARTITIONED',int_table => 'PARTITIONED_INTERIM');

PL/SQL procedure successfully completed.

SQL>
SQL> select index_name, uniqueness , table_name, partitioned
  2  from user_indexes
  3  where table_name = 'PARTITIONED';

INDEX_NAME                     UNIQUENES TABLE_NAME                     PAR
------------------------------ --------- ------------------------------ ---
PARTITIONED_INTERIM_PK         UNIQUE    PARTITIONED                    NO

SQL>
SQL>
SQL>

Note that you'll have to deal with other indexes/objects etc too, using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS() and DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT().

Best Answer

Related Solutions

Postgresql – What happens to the index of a primary key after a DROP CONSTRAINT

Disclaimer

Change a partitioned global non-unique index that is the primary key to a non-partitioned unique index

Related Question