Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    1,640
    Provided Answers: 1

    Unanswered: How to reset genereated column with one single command?

    Hi,
    my system DB2 11.1 fixpack 2. To simplify my problem I have a table:

    Code:
    CREATE TABLE ADMIN.TABLE1 (
        COL1 INT NOT NULL,
        COL2 CHAR(10)
    );
    with two rows:
    Code:
    INSERT INTO ADMIN.TABLE1 VALUES (1, 'AAA'), (2, 'BBB');
    and now I must create identity on column col1 on existing table:
    Code:
    ALTER TABLE ADMIN.TABLE1 ALTER COLUMN COL1 SET GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE);
    and after that I need to restart identity in order new inserts are possible to be executed against table:
    Code:
    SELECT MAX(COL1) + 1 FROM ADMIN.TABLE1;
    ALTER TABLE ADMIN.TABLE1 ALTER COLUMN COL1 RESTART WITH 3;
    Above select returns 3 and now value 3 has to be applied to last command. All above is working fine.

    But I have several tables I need to do this and as it looks like last two commands I need to apply manually (can't be run from simple db2 commands like file).
    Is there a way instead of last two commands to execute only single command which understands that I need to reset value to "max+1"?

    I checked official documentation https://www.ibm.com/support/knowledg.../r0000888.html and can't see an option.
    This would save me a lot of time.
    Regards
    Last edited by grofaty; 08-30-17 at 07:52.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    You could dynamically generate the 'alter table ' statement so that it had a suitable value on the 'start with ' clause , using your preferred scripting language.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,640
    Provided Answers: 1
    @db2mor, I was already thinking about scripting language, but I was just wondering if there exists some native db2 solution in order I don't invent something already available.

  4. #4
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    306
    Provided Answers: 46
    Hi,

    Try this:
    Code:
    --#SET TERMINATOR @
    BEGIN
      EXECUTE IMMEDIATE (SELECT 'ALTER TABLE ADMIN.TABLE1 ALTER COLUMN COL1 RESTART WITH '|| CHAR(MAX(COL1) + 1) FROM ADMIN.TABLE1);
    END@
    Regards,
    Mark.

  5. #5
    Join Date
    Jan 2003
    Posts
    1,640
    Provided Answers: 1
    @mark.bb, tested and working fine.
    Thanks a lot for this solution!

Posting Permissions

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