Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    hyderabad
    Posts
    10

    Unanswered: set default value to a column

    hi

    how to set a default value to a column of an existing table.

    the column name is acctdate this column should take a default value of current timestamp. what is the syntax to do it


    bye
    ramakrishna

  2. #2
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Drop and Recreate

    Hi,

    To the best of my knowledge you cannot change the datatype of a column that has data in it.

    What you can do is go ahead exoport the data to a file. Drop the table and re-create the table and use the default parameter for the column and then import the data back again.

    It's a tedious job however that's the only solution I know.

    The other option is using a Tool like DBArtisan which will do the necessary however at the end it also follows the same procedure.

    HTH

    Nitin
    HTH

    Nitin

    Ask the experienced rather than the learned

  3. #3
    Join Date
    Jan 2004
    Location
    hyderabad
    Posts
    10

    Re: Drop and Recreate

    hi

    thnx for the quick response. But it is difficult with db2 if we dont have this facility there may be live data. which may not permit me to always drop the table and recreate just to add a default value for a column on the fly.


    But any way thanx for the response

    Regards,
    ramakrishna

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Drop and Recreate

    If you want to have a minimum downtime, you can create a 'shadow' table with the new defn , populate the data from the original table , then drop the table and rename the shadow table ..

    Whether this will be a good approach depends on the nature of your data, other db structures dependent on it etc ...

    HTH

    Sathyaram

    Originally posted by ramakrishna
    hi

    thnx for the quick response. But it is difficult with db2 if we dont have this facility there may be live data. which may not permit me to always drop the table and recreate just to add a default value for a column on the fly.


    But any way thanx for the response

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

Posting Permissions

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