Professional Web Applications Themes

sequences not in sequence - IBM DB2

Hello all I was wondering if anyone knew of any problems with sequences in db2 v8.0 running on AIX. I ran a doent insert on a well used table via a stored procedure that uses a sequence to generate a primary key, but the key produced was not unique (it was 50 or 60 below the current max id in the table). I recreated the sequence to fix the problem but am concerned that there may be a problem in this area. Any help would be greatly appreciated thanks Ben...

  1. #1

    Default sequences not in sequence

    Hello all
    I was wondering if anyone knew of any problems with sequences in db2
    v8.0 running on AIX. I ran a doent insert on a well used table via
    a stored procedure that uses a sequence to generate a primary key, but
    the key produced was not unique (it was 50 or 60 below the current max
    id in the table). I recreated the sequence to fix the problem but am
    concerned that there may be a problem in this area.
    Any help would be greatly appreciated
    thanks
    Ben
    ben Guest

  2. #2

    Default Re: sequences not in sequence

    Hy,

    a sequence itself does not provide uniqueness of a column in a table.
    There is no connection between a table and a sequence, they are
    independent objects.

    If You have a table with some data and You deside to use a sequence to
    create primary keys of new records then You should start the sequence
    from the max value of the PK +1. If You insert a record with the next PK
    value into the table without using the sequence, the sequence will stay
    at the value before. Next time You use the sequence it will give a value
    wich is the next value of the sequence, but not the next PK value of the
    table.

    ben wrote: 

    --
    Tibor Répási uni-miskolc.hu
    Ph.D. Student
    M.Sc. in Information Engineering

    Univ. of Miskolc, Dept. of Information Technology
    Tel.: +36 46 565-111 / 21-08



    Tibor Guest

  3. #3

    Default Re: sequences not in sequence

    Ben,

    Are you using a SEQUENCE or an IDENTITY column?
    The max-id of a table has nothing to do with the values of generated by a
    sequence or identity.
    Sequences are orthogonal objects to tables. Identies are associated with a
    column, BUT identities can be altered, restarted, cycled as well as LOADs
    can add values not using identity.
    Of course if the identity is generaded BY DEFAULT you can overide the
    generation and, if you don't associate ranges get duplicates later on in all
    those cases.

    I'm curious: Is your background from Informix by any chance. The SERIAL
    property in Informix seems to match more your expectations (i.e.
    automagically synching up with the highwatermark).

    I have a hard time imagining a genuine bug in the generation of sequences
    and identity, but I'm open to be proven wrong.

    Cheers
    Serge


    Serge Guest

  4. #4

    Default Re: sequences not in sequence

    Hi Serge, thanks for the reply
    I realise that sequences are not related directly to tables, but the
    primary key of the table in question is always taken from the same
    sequence on an insert. We now have the situation where the sequence
    appears to have lost some value so my question was whether or not
    there is any possibility that a sequence is not 100% reliable, perhaps
    if under certain conditions it could lose its value. If this is not
    possible, then there must be some other problem, such as a developer
    here resetting the sequence. I suppose this is more likely!
    Ben
    ben Guest

  5. #5

    Default Re: sequences not in sequence

    PMJI ... I am not sure if you are stating that sequence numbers appear out of order numerically or if a range is missing? i.e you have 1-10 then 15-20 etc. If the latter, could it be related to the cache or sequence numbers not being all used up and the system being recycled so that those values in the cache are lost?

    --

    Bob
    IBM Toronto Lab
    IBM Software Services for Data Management
    "ben" <com> wrote in message news:google.com...
    Hi Serge, thanks for the reply
    I realise that sequences are not related directly to tables, but the primary key of the table in question is always taken from the same
    sequence on an insert. We now have the situation where the sequence appears to have lost some value so my question was whether or not
    there is any possibility that a sequence is not 100% reliable, perhaps if under certain conditions it could lose its value. If this is not
    possible, then there must be some other problem, such as a developer here resetting the sequence. I suppose this is more likely!
    Ben
    Bob Guest

  6. #6

    Default Re: sequences not in sequence

    Hi
    BTW. After using "alter sequence ... restart with " any call "nextval for
    seq" hangs db.
    There is one solution: drop altered sequence and recrete it. W2003 Server. I
    did not check on other platforms.

    Regards
    Adrian Kalicki

    Użytkownik "Serge Rielau" <eyebeem.com> napisał w wiadomości
    news:bm133p$j7m$torolab.ibm.com... 
    all 


    Adrian Guest

  7. #7

    Default Re: sequences not in sequence

    Hi Bob thanks for your reply
    The problem was the the sequence generated some numbers, say
    10000-10030, but then started with 10000 again. I think we have an
    answer here, the dbas downstairs were doing some work changing
    tablespaces etc and must have recreated the sequences from scripts
    with the STARTS WITH clause out of date
    thanks anyway
    Ben
    ben Guest

  8. #8

    Default Re: sequences not in sequence

    Which release are you on? I know there were soem early hic-ups in V7.2, but
    I'm not awar eof any recent problems with sequences.

    Cheers
    Serge


    Serge Guest

  9. #9

    Default Re: sequences not in sequence

    Hi

    DB2 WSE 8.1 fp3

    db2level:
    DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08013" with
    level identifier "02040106".
    Informational tokens are "DB2 v8.1.3.132", "s030728", "WR21324", and FixPak
    "3".


    Contents of db2diag.log
    (....)
    2003-09-18-15.25.23.156002 Instance:DB2 Node:000
    PID:2316(db2syscs.exe) TID:3656 Appid:GA0A0AD9.O805.00D2C8132414
    data management sqldSeqFetchUpdateGen Probe:1 Database:DTARAN_1

    DTS mismatch: Sequence Request:

    0x0147A958 : 0000 0000 0000 0000 1100 0000 E300 0000 ............ă...
    0x0147A968 : 0100 0000 0000 0000 0000 0000 0100 0000 ................
    0x0147A978 : 0000 0000 0000 0000 0000 2003 0902 1845 .......... . ..E
    0x0147A988 : 5465 6005 0000 0000 0000 0000 0000 0000 Te`.............
    0x0147A998 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
    0x0147A9A8 : 0000 0000 0000 0000 0200 1F00 0100 0000 ................
    0x0147A9B8 : ECA9 4701 0000 0000 0000 0000 0000 0000 ěCG.............
    0x0147A9C8 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
    0x0147A9D8 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
    0x0147A9E8 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
    0x0147A9F8 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
    0x0147AA08 : 0000 0000 0000 0000 0000 0000 0000 0000 ................

    2003-09-18-15.25.23.156003 Instance:DB2 Node:000
    PID:2316(db2syscs.exe) TID:3656 Appid:GA0A0AD9.O805.00D2C8132414
    data management sqldSeqFetchUpdateGen Probe:1 Database:DTARAN_1

    DTS mismatch: Sequence Cache:

    0x16F5EC00 : 6100 0000 1000 0000 0000 0000 E300 0000 a...........ă...
    0x16F5EC10 : 0100 0000 0000 0000 0100 0000 0000 0000 ................
    0x16F5EC20 : 0E66 1D00 0000 000C 4E50 2003 0918 1434 .f......NP . ..4
    0x16F5EC30 : 5015 6000 0200 1F00 0200 0000 64EC F516 P.`.........děő.
    0x16F5EC40 : 0200 1F00 0200 0000 74EC F516 0200 1F00 ........těő.....
    0x16F5EC50 : 0200 0000 84EC F516 0200 1F00 0000 0000 ...."ěő.........
    0x16F5EC60 : 94EC F516 0000 0000 0000 0000 0000 0000 "ěő.............
    0x16F5EC70 : 0008 554C 0000 0000 0000 0000 0000 0214 ..UL............
    0x16F5EC80 : 7483 647C 0000 0000 0000 0000 0000 0000 t.d|............
    0x16F5EC90 : 0000 001C 0000 0000 0000 0000 0000 0000 ................
    0x16F5ECA0 : 0008 554C 0000 0000 0000 0000 0000 0000 ..UL............
    0x16F5ECB0 : 0000 001C 8000 0000 0000 0000 0000 0000 ................

    2003-09-18-15.25.23.156004 Instance:DB2 Node:000
    PID:2316(db2syscs.exe) TID:3656 Appid:GA0A0AD9.O805.00D2C8132414
    global services sqlzerdm Probe:40 Database:DTARAN_1

    0x0147A928 : 0x8704002F /..?

    2003-09-18-15.25.23.187001 Instance:DB2 Node:000
    PID:2316(db2syscs.exe) TID:3656 Appid:GA0A0AD9.O805.00D2C8132414
    relation data serv sqlrr_dump_ffdc Probe:20 Database:DTARAN_1

    DIA8544C An invalid data type was encountered, the value was "".
    ZRC=0x8704002F

    PID:2316 TID:3656 Node:000 Title: SQLCA
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -902 sqlerrml: 2
    sqlerrmc: 47
    sqlerrp : sqlrisnv/
    sqlerrd : (1) 0x8704002F (2) 0x0000002F (3) 0x00000000
    (4) 0x00000000 (5) 0x00000000 (6) 0x00000000
    sqlwarn : (1) (2) (3) (4) (5) (6)
    (7) (8) (9) (10) (11)
    sqlstate:

    PID:2316 TID:3656 Node:000 Title: SQLCA
    Dump File:C:\PROGRA~1\IBM\SQLLIB\DB2\23163656.000
    (... and much more lines for PID:2316 with dump files )

    Droping and recreating sequences are not rewarding solution, since there are
    any triggers which uses the sequences (the triggers also must be dropped
    before dropping sequence).


    Regards
    Adrian Kalicki


    Użytkownik "Serge Rielau" <eyebeem.com> napisał w wiadomości
    news:bmgpru$b0q$torolab.ibm.com... 
    but 


    Adrian Guest

  10. #10

    Default Re: sequences not in sequence

    In article <bmgpru$b0q$torolab.ibm.com>,
    eyebeem.com says... 

    Hi Serge,
    Our production servers are running with DB2 V7 fixpack 6. Could we
    run into troubles when using sequences with this level?

    TIA, Gert
    Gert Guest

Similar Threads

  1. Storing sequences
    By turnitup in forum MySQL
    Replies: 2
    Last Post: June 5th, 10:39 PM
  2. Shared Sequences?
    By C. Duncan Hudson in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: January 2nd, 10:30 PM
  3. New Sequences for Batch Processing
    By Matt_Torbin@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 9
    Last Post: July 29th, 01:56 PM
  4. SOAP4r sequences and authentication
    By Martin Stannard in forum Ruby
    Replies: 1
    Last Post: November 11th, 02:16 AM
  5. reordering column sequences
    By BK Kim in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: September 8th, 11:24 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139