Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Posts
    19

    Unhappy Unanswered: DB2 Alter Command on existing table

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: DB2 Alter Command on existing table

    Originally posted by sks
    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

  3. #3
    Join Date
    Sep 2003
    Posts
    19

    Exclamation

    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!

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by sks
    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.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Obviously you will take a hit on performance and disk space utilization this way ...

    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.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Sep 2003
    Posts
    19
    Hello Sathyaram,

    Is there a better way?

  7. #7
    Join Date
    Feb 2004
    Posts
    24
    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

    Originally posted by sks
    Hello Sathyaram,

    Is there a better way?

  8. #8
    Join Date
    Sep 2003
    Posts
    19
    Thx! That might be the best way at this point!

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Again, don't forget to RUNSTATS and ReBIND the exisiting packages

    Good luck

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Sep 2002
    Posts
    456
    why not drop the primary key and then alter the column, re-create the primary key again?

    dollar

    Originally posted by sathyaram_s
    Again, don't forget to RUNSTATS and ReBIND the exisiting packages

    Good luck

    Sathyaram

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can't alter the column unless it is varchar.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •