Results 1 to 9 of 9

Thread: unique_rows

  1. #1
    Join Date
    Sep 2008
    Posts
    4

    Unanswered: unique_rows

    Hi @ all,
    i have a problem with a db2 trigger hopefully someone can help me.
    I will create a insert in a row, sometimes I have at one time two inserts, but
    i need to create a unique row column with a unique nr.
    To create a trigger which inserts this unique nr. is not really a problem, but
    take care that the other rows which I create near at the same time became
    the same number is for me a challenge :-)
    Now to the trigger:

    Code:
    create trigger unique 
    before insert on db2inst1.header
    referencing new as n
    for each row mode db2sql
      set n.row_is =
      case
      when safets != n.safets and
              id != n.id 
              then generate_unique()
      when safets = n.safets and
              id = n.id
              then ............
    Maybe I have to insert "set" with a "select" statement ???
    But I am not sure.
    Maybe I am also on the complete wrong way??
    Hope someone can help me!!

    thanks in advance
    mfgjens

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    what about trying out something like a sequence or an identity column.... I hear those work well.

  3. #3
    Join Date
    Sep 2008
    Posts
    4
    But than I have also only one unique row. but sometimes I need the unique ID on two different rows.
    And than????

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    No, you wouldn't have that. Your trigger is executed for each row, and it would request NEXTVAL from the sequence for each row. Sequences are synchronized, so you would never get the same sequence value for different rows being inserted.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Sep 2008
    Posts
    4
    Ok sounds not good for me
    But can someone give me a hint for a way arround??

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Why is this "not good"? Just use sequences and be done with it. It will be much easier than trying to use something home-grown.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Sep 2008
    Posts
    4
    Ok, maybe I have now a wrong understanding.
    Right now I use a trigger like this.

    Code:
    create trigger unique no cascade
    before insert on db2inst1.table
    referencing new as n
    for each row mode db2sql
    set n.row_id = generate_unique()
    I am right that this are a sequence or?
    This make every row unique, but my problem is
    that I sometimes have two rows from the same measurement
    and in this case, this two rows needs to have the same "unique" ID.
    The reason behind is, I will use this as a reference for a different table with detail information from this measurement.
    I will have a clear relation between this two different tables.

    Sorry if I have a wrong understanding

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by mfgjens
    I am right that this are a sequence or?
    This make every row unique,
    1. No, this is not a sequence.
    2. I think you have confused everyone here by using the words "unique ID"; what you are looking for is the way of generating "just IDs"
    3. A sequence may still help: when you need a new ID, fetch NEXTVAL() from the sequence, otherwise fetch PREVVAL(). You just need to make sure that no other process uses that sequence at the same time to guarantee that PREVVAL() does not change between the calls. If you cannot do that, create a function that will generate an ID according to your requirements.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by mfgjens
    To create a trigger which inserts this unique nr. is not really a problem, but take care that the other rows which I create near at the same time became the same number is for me a challenge
    Do I understand you correctly that you want to give the (say) two rows inserted with the same INSERT statement the same ID nr.?
    All earlier suggestions (sequence object or identity column) will give those two rows different ID nrs.

    Replace for each row in your trigger by for each statement; that trigger will be called just once per INSERT statement instead of once per inserted row, i.e., generate a common value n.row_id, e.g. retrieved from a sequence object.
    Doing it this way, there is even no need to guarantee that others wouldn't use that sequence object.
    Using generate_unique() is also fine, of course. Main difference being that the latter generates a CHAR(13), while a sequence object returns an integer.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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