Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2007
    Posts
    38

    Unanswered: Add column with default value

    Hi all,

    I am trying to understand how does it work when you add a new not null column with default value to a large transaction table. Does it go and update each and every record of the entire table

    ALTER TABLE TXN_TABLE ADD COLUMN DESC VARCHAR(10) SET NOT NULL WITH DEFAULT 'XYZ';

    Once you add the column will it go and update all the records in the table?

    Thank you all in advance.

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    110
    Provided Answers: 13
    Hi,

    No, it will not update every row in this case.

    The internal format of some particular row is changed when you update this column of this row only. The default value specified at the time of the 'alter table ... add column ...' command is stored in the system catalog and used, when some particular row has an old format (doesn't contain this column).
    Regards,
    Mark.

  3. #3
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Hi,

    It will update each and every record in the table with default value specified for the new column.

    If you try to add a new column by specifying not null without specifying default value, you will get error. See the following link for more details.

    http://www.ibm.com/support/knowledge.../r0000888.html

    Satya..

  4. #4
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    110
    Provided Answers: 13
    Quote Originally Posted by stiruvee View Post
    It will update each and every record in the table with default value specified for the new column.
    Hi,

    Can you explain the following result in this case?
    Code:
    create table test_default (i int, a char(254)) in userspace1;
    
    insert into test_default
    with t(i) as (
    values 1
      union all
    select i+1
    from t
    where i<10000
    )
    select i, repeat('*', 254)
    from t;
    
    call admin_cmd('runstats on table TEST_DEFAULT');
    
    select DATA_OBJECT_P_SIZE from table(admin_get_tab_info(USER, 'TEST_DEFAULT'));
    DATA_OBJECT_P_SIZE: 3072
    
    alter table test_default add b char(254) not null default '*';
    call admin_cmd('runstats on table TEST_DEFAULT');
    
    select OVERFLOW from syscat.tables where tabschema=user and tabname='TEST_DEFAULT';
    OVERFLOW: 0
    
    select DATA_OBJECT_P_SIZE from table(admin_get_tab_info(USER, 'TEST_DEFAULT'));
    DATA_OBJECT_P_SIZE: 3072
    
    update test_default set b = b||'' where i<1000;
    
    call admin_cmd('runstats on table TEST_DEFAULT');
    
    select OVERFLOW from syscat.tables where tabschema=user and tabname='TEST_DEFAULT';
    OVERFLOW: 500
    
    SELECT DATA_OBJECT_P_SIZE FROM TABLE(ADMIN_GET_TAB_INFO(USER, 'TEST_DEFAULT'));
    DATA_OBJECT_P_SIZE: 3328
    Regards,
    Mark.

  5. #5
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    @Mark,

    Your answer (not updating every row) is correct. I did not realize that DB2 is keeping track of row format versions and returning default value from catalog when select is done immediately after alter. After running your script I understood what is happening. Thanks for correcting and providing detailed example for clarifying things.

    Regards..

    Satya..

  6. #6
    Join Date
    Oct 2007
    Posts
    52
    Provided Answers: 3
    Once you perform a reorg on the table, it will populate the default on all rows that are marked as using the default value.

  7. #7
    Join Date
    Nov 2007
    Posts
    38
    Thank you all very much.

    Looks like the value is stored in implicitvalue column in syscat.columns.

Posting Permissions

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