Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2014
    Posts
    4

    Unanswered: update values in identity columns

    Hello,

    Punctually, I'm inserting identity values in tables with "generated by default as identity" columns. After this insertion process I need to update the identity generator to keep on inserting correctly by delegating the identity generator to db2. I know there is an alter commnad to restart the value, but since I have a large number of tables to alter, I would like to know if there is a faster way to do it, maybe a general way to update all tables at once.

    Regards.

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I don't think you can update all the tables at once.
    For this operation I am using a script for such that:
    - creates a cursor with all the tables that have identity columns (SYSCAT.TABLES, SYSCAT.COLUMNS)
    - for each table, gets its maximum identity, using dynamic SQL
    - alters the column, RESTART WITH max(identity) + 1
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Apr 2014
    Posts
    4
    Thanks for such a quick answer.
    That was what I was thinking but I wanted to be sure before doing anything.

    Regards

    Quote Originally Posted by aflorin27 View Post
    I don't think you can update all the tables at once.
    For this operation I am using a script for such that:
    - creates a cursor with all the tables that have identity columns (SYSCAT.TABLES, SYSCAT.COLUMNS)
    - for each table, gets its maximum identity, using dynamic SQL
    - alters the column, RESTART WITH max(identity) + 1

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I am taking a wild guess here that this is for a test system and perhaps you are copying production data into it? One of the things I have done is not restart as max +1, but instead use max +3000000. This way you don't, normally, have to worry about the numbers colliding when you put more prod data into your test environment. In fact, in a test/QA/PROD environment I have set all 3 to different numbers, just in case someone copied data from one to the next.
    As Florin stated you have to do each indiviually.
    Dave

  5. #5
    Join Date
    Apr 2014
    Posts
    4

    update values in identity columns

    Hello dav1mo,

    no, this is not for test but I still have to deal with these identity problems. However, you have given me a good way to do my own tests.
    Thanks a lot!
    Regards.

  6. #6
    Join Date
    Apr 2014
    Posts
    4

    update values in identity columns

    Thanks for your reply. That was what I figured out but I wanted to be sure before doing anything.
    Regards

    Quote Originally Posted by aflorin27 View Post
    I don't think you can update all the tables at once.
    For this operation I am using a script for such that:
    - creates a cursor with all the tables that have identity columns (SYSCAT.TABLES, SYSCAT.COLUMNS)
    - for each table, gets its maximum identity, using dynamic SQL
    - alters the column, RESTART WITH max(identity) + 1

Posting Permissions

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