Archives of the TeradataForum

Message Posted: Mon, 31 Dec 2001 @ 16:42:35 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Charles Perry wrote:

Charles, the responses you have received so far are good, but they haven't quite addressed the main point. In Oracle, a sequence is a database object, just like a table or a view; it automagically returns the next number in a sequence. The starting number and increment can be anything you like; thus, the sequence can be 0,1,2..., or 1,2,3..., or 0,-1,-2, or 200,205,210..., or whatever.

A previous respondent remarked that CSUM would do what you wanted, and this would probably be the best way to go. If you must have an object that mimics the behavior of a sequence, you should probably set up a table with two columns and one row and a macro (or possibly a stored procedure) to go with it. The table would be something like this:

create set table my_sequence
(prim_key integer,
next_number integer ) /* or decimal */
unique primary index(prim_key);
insert into my_sequence values( 1, starting_value );

The reason for using the "prim_key" column is to keep the row always on the same AMP. If "next_number" were the index value, the row would have to be moved to a different AMP every time you changed it -- yuk!

The macro must be written to guarantee that, if two transactions are trying to get a new sequence number at the same time, one is forced to wait for the other. The macro would go something like this (UNTESTED):

create macro my_seq_macro as
( begin transaction;
update my_sequence
set next_number = next_number + 1 /* or +5, or -1, or whatever */
where prim_key = 1; /* A one-AMP operation! */
select next_number
from my_sequence
where prim_key = 1;
end transaction;
) ;

Good luck.

--wgr

	<Prev	Next>		<<First	<Prev	Next>	Last>>