Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2011
    Posts
    7

    Unanswered: Sequence start with value

    Hi,
    I would like to calculate a "start with" value when creating a sequence like:

    CREATE SEQUENCE objectid
    START WITH (select max(column1) from table1);

    How could I achieve this?

    Thanks,

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You cannot. You have to supply a numeric constant for the START value. You would have to build the statement dynamically.

    Andy

  3. #3
    Join Date
    Dec 2011
    Posts
    7
    Thanks for the reply.

    I am inserting records into a table. One column in the table has to have unique values.
    I thought of accomplishing this by returning the highest value in that column and assign that value to the sequence start with property before the insert statement.

    Do you see other way to provide unique value in the column when inserting a new record?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You only need to set the START once. It will auto increment from there. So just manually set it based on the current max value. From then on, it should give you a unique values when you insert. How is the column defined?

    Andy

  5. #5
    Join Date
    Dec 2011
    Posts
    7
    The column is INTEGER type.
    This is gonna be a scheduled task which would run daily and would update the table (add and delete records).

    Before inserting new data I need to know what is the highest value in that column that the inserted values would be unique.

    But still, you gave me an idea. Since I'll be sending this SQL by Python to DB2, I can retrieve the highest value separately in then dynamically create SQL code by passing in that value. Thanks

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I thought it was an identity / sequence column. If it is, then the database will generate the unique value for you. This will totally eliminate the query for the max value.

    Andy

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You cannot put a sub-select in the Alter Sequence statement to calculate the existing highest used value in a table. But you can build such a statement with dynamic SQL (in a stored procedure or scripting language) then execute the alter sequence to update the value. However, you will have to have some way to correlate the sequence name with the table and column name that it is used for (unless you want hard-code that for each sequence). This could be done with strict naming conventions of the sequences, tables, and PK column, or with a meta-data table that contains the relationships among the three components.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Tags for this Thread

Posting Permissions

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