DB2: Oracle equivalent of Autonomus transaction

[Prince Kumar]

I have a requirement where I have to generate sequnce like numbers
within another key.

My table is like,

custrec
-------
cust# seq#
10 1
10 2
10 4
10 5

20 1
20 2
20 3
20 4

...
...

For various reasons, I can not use sequnces. In Oracle I have a table
with as the following, which keeps the last sequnce number for each
cust# and autonomous transaction updates the maxseq#.

mastseq:
--------
cust# maxseq#
------ -------
10 5
20 4

Is there any way, I can get the same thing done in DB2.

Note, I do not want to select the maxseq# with "for update of", as
this will hold the lock until the transaction is complete. Also, I can
not commit the record immediately after updating the maxseq# within
the cust#, as this means inconsistent transaction.

Any Alternatives are welcome.

thanks,

[Mark A]

"Prince Kumar" <gs**@yahoo.com> wrote in message
news:62**************************@posting.google.c om...

I have a requirement where I have to generate sequnce like numbers
within another key.

My table is like,

custrec
-------
cust# seq#
10 1
10 2
10 4
10 5

20 1
20 2
20 3
20 4

..
..

For various reasons, I can not use sequnces. In Oracle I have a table
with as the following, which keeps the last sequnce number for each
cust# and autonomous transaction updates the maxseq#.

mastseq:
--------
cust# maxseq#
------ -------
10 5
20 4

Is there any way, I can get the same thing done in DB2.

Note, I do not want to select the maxseq# with "for update of", as
this will hold the lock until the transaction is complete. Also, I can
not commit the record immediately after updating the maxseq# within
the cust#, as this means inconsistent transaction.

Any Alternatives are welcome.

thanks,

The lock held (SIX) with "for update of" will not lock out others who are
only doing selects. It will only lock others who are doing "for update of"
on the same row (and of course for direct updates of the data).

The actual exclusive lock (X) takes place when the "update where current of
cursor..." is issued. As long as you commit soon after that, you should be
OK.

[Blair Adamache]

Can you use generate_unique() (note - this will not generate keys in
sequence) or IDENTITY?

Prince Kumar wrote:

I have a requirement where I have to generate sequnce like numbers
within another key.

My table is like,

custrec
-------
cust# seq#
10 1
10 2
10 4
10 5

20 1
20 2
20 3
20 4

..
..

For various reasons, I can not use sequnces. In Oracle I have a table
with as the following, which keeps the last sequnce number for each
cust# and autonomous transaction updates the maxseq#.

mastseq:
--------
cust# maxseq#
------ -------
10 5
20 4

Is there any way, I can get the same thing done in DB2.

Note, I do not want to select the maxseq# with "for update of", as
this will hold the lock until the transaction is complete. Also, I can
not commit the record immediately after updating the maxseq# within
the cust#, as this means inconsistent transaction.

Any Alternatives are welcome.

thanks,

[Fan Ruo Xin]

Can you post the pseudo-code of the whole parent transaction (include the
automonus transaction) part?
BTW, is there any hole in the sequence number for one customer? Do you
care this?
Such as:
custrec
-------
cust# seq#
10 1
10 2
10 5 <==
10 6
Regards,
FRX

Prince Kumar wrote:

I have a requirement where I have to generate sequnce like numbers
within another key.

My table is like,

custrec
-------
cust# seq#
10 1
10 2
10 4
10 5

20 1
20 2
20 3
20 4

..
..

For various reasons, I can not use sequnces. In Oracle I have a table
with as the following, which keeps the last sequnce number for each
cust# and autonomous transaction updates the maxseq#.

mastseq:
--------
cust# maxseq#
------ -------
10 5
20 4

Is there any way, I can get the same thing done in DB2.

Note, I do not want to select the maxseq# with "for update of", as
this will hold the lock until the transaction is complete. Also, I can
not commit the record immediately after updating the maxseq# within
the cust#, as this means inconsistent transaction.

Any Alternatives are welcome.

thanks,

[Prince Kumar]

Of course, everyone one will use "for update of", because the
application has the same SQL for everyone.

The suggested solution will not work for me, as I can not commit
immediately after getting the next record. In this case, I may be in
the middle of the transaction.

Also, Somewhat related to this. Is there anything similar to
sequences/identifiers in the partitioned DB? I believe sequences are
not allowed on partitioned db. What substitute DB2 has it here?

Thanks,

"Mark A" <[e-mail]> wrote in message news:<%7*****************@news.uswest.net>...

"Prince Kumar" <gs**@yahoo.com> wrote in message
news:62**************************@posting.google.c om...

I have a requirement where I have to generate sequnce like numbers
within another key.

My table is like,

custrec
-------
cust# seq#
10 1
10 2
10 4
10 5

20 1
20 2
20 3
20 4

..
..

For various reasons, I can not use sequnces. In Oracle I have a table
with as the following, which keeps the last sequnce number for each
cust# and autonomous transaction updates the maxseq#.

mastseq:
--------
cust# maxseq#
------ -------
10 5
20 4

Is there any way, I can get the same thing done in DB2.

Note, I do not want to select the maxseq# with "for update of", as
this will hold the lock until the transaction is complete. Also, I can
not commit the record immediately after updating the maxseq# within
the cust#, as this means inconsistent transaction.

Any Alternatives are welcome.

thanks,

The lock held (SIX) with "for update of" will not lock out others who are
only doing selects. It will only lock others who are doing "for update of"
on the same row (and of course for direct updates of the data).

The actual exclusive lock (X) takes place when the "update where current of
cursor..." is issued. As long as you commit soon after that, you should be
OK.

[Serge Rielau]

SEQUENCE and IDENTITY are supported in DPF starting with V8 FP2

Cheers
Serge

[Prince Kumar]

Thanks Serge for the information.

"Serge Rielau" <sr*****@ca.eyebeem.com> wrote in message news:<bo**********@hanover.torolab.ibm.com>...

SEQUENCE and IDENTITY are supported in DPF starting with V8 FP2

Cheers
Serge