Home Page for the TeradataForum

Archives of the TeradataForum

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

  <Prev Next>   <<First <Prev

Subj:   Re: Sequences in Teradata?
From:   Geoffrey Rommel

Charles Perry wrote:

  I'm moving a web based application using an Oracle DB that relies heavily on sequences to work with a ,to be built, Teradata DB. I can't seem to find an equivalence to the sequences in Teradata or am I missing something?  

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.


  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023