Hello all
I was wondering if anyone knew of any problems with sequences in db2
v8.0 running on AIX. I ran a document 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

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:
> Hello all
> I was wondering if anyone knew of any problems with sequences in db2
> v8.0 running on AIX. I ran a document 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

--
Tibor Répási repasi@iit.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

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

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

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" <bennnybubble@hotmail.com> wrote in message news:767d7531.0310090149.da96b43@posting.google.co m...
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

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" <srielau@ca.eyebeem.com> napisa³ w wiadomo¶ci
news:bm133p$j7m$1@hanover.torolab.ibm.com...
> 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
>
>

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

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

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 InstanceB2 Node:000
PID:2316(db2syscs.exe) TID:3656 Appid:GA0A0AD9.O805.00D2C8132414
data management sqldSeqFetchUpdateGen Probe:1 DatabaseTARAN_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 InstanceB2 Node:000
PID:2316(db2syscs.exe) TID:3656 Appid:GA0A0AD9.O805.00D2C8132414
data management sqldSeqFetchUpdateGen Probe:1 DatabaseTARAN_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 InstanceB2 Node:000
PID:2316(db2syscs.exe) TID:3656 Appid:GA0A0AD9.O805.00D2C8132414
global services sqlzerdm Probe:40 DatabaseTARAN_1

0x0147A928 : 0x8704002F /..?

2003-09-18-15.25.23.187001 InstanceB2 Node:000
PID:2316(db2syscs.exe) TID:3656 Appid:GA0A0AD9.O805.00D2C8132414
relation data serv sqlrr_dump_ffdc Probe:20 DatabaseTARAN_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" <srielau@ca.eyebeem.com> napisa³ w wiadomo¶ci
news:bmgpru$b0q$1@hanover.torolab.ibm.com...
> 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
>
>

In article <bmgpru$b0q$1@hanover.torolab.ibm.com>,
srielau@ca.eyebeem.com says...
> 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
>

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