Unnecessary drop & recreate of table when altering primary key sequence

Toad for IBM DB2
1

ALTER TABLE “I274785”.“O_RPS_RCP_BNFT2” DROP CONSTRAINT “P_RPS_RCP_BNFT2”;

COMMIT;

DROP INDEX “I274785”.Test_PK;

COMMIT;

ON “I274785”.“O_RPS_RCP_BNFT2”( RPS_CNTR_NMBR

CLUSTER ALLOW REVERSE SCANS COMPRESS NO;

COMMIT;

ADD CONSTRAINT “P_RPS_RCP_BNFT2” PRIMARY KEY

(“RPS_CNTR_NMBR”, “EFCTV_DT”, “RPS_PRFX_CD”, “RPS_UNT_NMBR”, “RPS_RCP_NMBR”, “RPS_RCP_BNFT_NMBR”, “RCP_BNFT_SEQ_NMBR”);

It doesn’t need dropping and recreating the table. The identical table exists in Pilot environment. I used the object compare feature (right click on the table) and compare the Test & Pilot structure. The DDL generated by TOAD to sync up Pilot requires dropping & recreating the table. I don’t believe this is the expected behavior. I was able to recreate this issue for a different table.

Is there an option I need to select to avoid the drop & recreate? Or is this a bug?

THanks!
Sharon

I made the following changes to a table in Test environment, which is to move the column EFCTV_DT from the last column of the index to the 2nd column. The index is used as the primary constraint and also the clustering index.

CREATE UNIQUE INDEX “I274785”.Test_PKASC, EFCTV_DT ASC, RPS_PRFX_CD ASC, RPS_UNT_NMBR ASC, RPS_RCP_NMBR ASC, RPS_RCP_BNFT_NMBR ASC, RCP_BNFT_SEQ_NMBR ASC)ALTER TABLE “I274785”.“O_RPS_RCP_BNFT2”

2

Hi,

If I undestood you correctly:

Your initial state was:
Table with columns A,B (for example)
Clustered unique index (A,B)
PK on columns (A, B).
So DB2 will use that index for PK.

You changed:
In alter PK constraint dialog you changed column order from B -> A
You expect to see native alter, instead of extended alter, right?

What are your TOAD and DB2 versions?

3

I am going to eloborate based on your example, as the issue wasn’t at the alter constraint diaglog box. The issue is in the compare wizard.

In Test,
Table1 with columns A,B
Clustered unique index (A,B)
PK on columns (A, B).
So DB2 will use that index for PK.

The PK & index have been altered to (B, A).

So now, Test looks like…
Table1 with columns A,B
Clustered unique index (B, A)
PK on columns (B, A).

In Pilot, same table exists and it looks like…
Table1 with columns A,B
Clustered unique index (A,B)
PK on columns (A, B).

I perform an object compare for this Table1 between Test and Pilot. The DDL statements generated from the object compare wizard are drop & recreate the table.

I expect the compare object wizard to generate alter statements for changing the PK constraint & index order from B -> A.

I hope this makes more sense.

I am using TOAD 4.7.0.556, and DB2 is 9.7.2.

Thanks!
SHaron