Please help!

I have a table that has 32Million records and I need to extend the lenght of the Primary Key field to 31 instead of 24.

I am not able to alter table since the it's a Primary Key and there is an index on this field.

Can you please guide me on what might be the best option to change this field without dropping the table?

Thx!

SKS

If your original PK column is a varchar then you can simply alter column definition (you may need to drop and re-create PK). If it's a char then you could do it this way:

1) add a new column with the necessary datatype (I assume it's char(31))
2) copy contents of an existing PK column to the new column
3) drop PK constraint on the original PK column
4) created a unique index on the new column
5) add PK constraint on the new column

Don't forget to do a runstats afterwards in either case

n_i

That's what we originally thought of doing, but DB2 DBA from IBM said on Version 7.2.6 DB2 UDB there is no Drop syntax for a column. Is that true? Can you please confirm!

I never said "drop column" - indeed there's no such functionality, which you could confirm by RTFM. Your old column will stay there to haunt you forever :-)

However, you can drop a PK constraint and then create it based on the new column.

Obviously you will take a hit on performance and disk space utilization this way ...

Hello Sathyaram,

Is there a better way?

Hi,

another idea is to create a new table with hte structure u need.
load the data from the old table into the table. extract all ddl's for view's etc. created on the old table. drop the old table and rename the new table the old table name and create all the other objects neede for the new table. sorry of my english but i'm german.
hope this will help.

regards

marc

Thx! That might be the best way at this point!

Again, don't forget to RUNSTATS and ReBIND the exisiting packages

Good luck

Sathyaram

why not drop the primary key and then alter the column, re-create the primary key again?

dollar

You can't alter the column unless it is varchar.