Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2007

    Question Unanswered: DB2: Sequence number generation

    It is possible to create a stored procedure that will generate a sequence number, however, is it possible for a stored procedure to renumber the sequence?

    For example, say there are ten rows in the table. Row nbr 5 is deleted. Row nbr 6 becomes row nbr 5, row nbr 7 becomes row nbr 6, row nbr 8 becomes row nbr 7, etc.

    This can be done thru programming but if possible I would rather have the database perform the function. I am using DB2 V7.1 on an MVS os/390.

  2. #2
    Join Date
    Jan 2007
    Jena, Germany
    I don't quite understand what you want to achieve. Could you possibly explain this in an example?

    A sequence is not tied to a table. Therefore, you could get one number from the sequence for table A, another for table B, yet another as input for a UDF, etc. As a consequence, you get gaps in the sequence numbers used for table A only. Maybe IDENTITY columns are a better way to get closer to what you want to do.

    Another important aspect is that a transaction may be rolled back. The sequence numbers acquired by that transaction are not reused, of course. So you have even more gaps.

    Lastly, if you want to renumber data in a table, you are aware that this would potentially imply updating millions of rows just due to a DELETE on a single row? What should happen in case of dependent rows in other tables? Cascading updates? If the answers would by "yes", you should reconsider your design decisions.

    Usually, there is no need to have contiguous sequence numbers in the first place and any application should live with gaps. I would be very intrigued if you have a scenario where you really need that.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2005
    In case,you want to generate the row number in a table,you can use the OLAP function(ROW_NUMBER()).


Posting Permissions

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