Identity Column Not Coming in a sequence. Need Suggestion.

Database
Enthusiast

Identity Column Not Coming in a sequence. Need Suggestion.

I have to generata a Sequence of numbers as the primary index.
But , after the insertions of some sample data, i find that it is not completely in sequence. I get numbers from 1 thru 10, then numbers from 1000001 thru 1000010, and so on ...

I understand that this is because the identity column values are generated on an amp-local basis, and hence the result. But, somehow we need the data to be essentially in sequence for more than 500 million rows.

Is there any way to do this?

Thanks.
14 REPLIES
Enthusiast

Re: Identity Column Not Coming in a sequence. Need Suggestion.

I'm a little unsure of using IDENTITY even with DECIMAL(18) because of this parallel implementation, since the 5753 error - Numbering for Identity Column columnname is over its limit - happens if the column is INTEGER when there's still room for lots of values.

In other words, if the upper bound has been reached but there are still gaps between existing values, does TD use those values (as we desire) or can that same error happen?

Other question is if the behavior varies depending if you INSERT with VALUES or INSERT SELECT.
Enthusiast

Re: Identity Column Not Coming in a sequence. Need Suggestion.

Try this:

insert into rahulj.partitbl (unique_column,mycolumn)
select
row_number() over (order by mycolumn)
,mycolumn
from
rahulj.partitbl
;
Enthusiast

Re: Identity Column Not Coming in a sequence. Need Suggestion.

This also works:

insert partitbl (k)
SELECT sum(1) over (rows unbounded preceding) As ROW_ID from dbc.databases;
Fan

Re: Identity Column Not Coming in a sequence. Need Suggestion.

hi,

In Teradata, is there an equivalent of the Sequence feature such as in Oracle? Identity is good but currently you cannot copy table with identity column, also it's less than ideal that you have gaps in the numbers assigned.

thank you

Enthusiast

Re: Identity Column Not Coming in a sequence. Need Suggestion.

No, you dont have anything thats same as Sequence of Oracle.
Fan

Re: Identity Column Not Coming in a sequence. Need Suggestion.

thanks,

now in Teradata V2R6 we cannot directly copy a table containing Identity column - is there any recommended workaround that would allow us to copy such a table in 2 or 3 steps? but still preserving its Identity column, AND still preserving the actual ID's assigned.

thank you

Re: Identity Column Not Coming in a sequence. Need Suggestion.

Hi

We too have spent weeks trying to find a sequence eqivalent in Teradata .Apparently there is none. If the requirement is not sequqnce but unique number which should not be repeated in any case then u can implement this way :

CREATE SET TABLE cfdw2_trr_tbls.davidtest_all_obligors ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
oblgr_num INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 1
INCREMENT BY 1
MAXVALUE 2147483647
NO CYCLE),
OBLGR_NUM_OLD INTEGER)
UNIQUE PRIMARY INDEX ( oblgr_num );

UPI will ensure that it is never repeated.

If u need sequence solution then u need to create a seperate table : sequence and write trigger (after insert on the table for which u wish to implement it and within this proc u need to implement the logic ). I have done it and if u want i can send u the code as well .

Rgds

Vikas

Enthusiast

Re: Identity Column Not Coming in a sequence. Need Suggestion.

FYI...

SELECT RANK(employee_number)
,employee_number
,last_name
FROM
CUSTOMER_SERVICE.employee;

Rank(Employee_Number) 1 Employee_Number 1025 Last_Name Short
Rank(Employee_Number) 2 Employee_Number 1024 Last_Name Brown
Rank(Employee_Number) 3 Employee_Number 1023 Last_Name Rabbit
Rank(Employee_Number) 4 Employee_Number 1022 Last_Name Machado
Rank(Employee_Number) 5 Employee_Number 1021 Last_Name Morrissey
Rank(Employee_Number) 6 Employee_Number 1020 Last_Name Charles
Rank(Employee_Number) 7 Employee_Number 1019 Last_Name Kubic
Rank(Employee_Number) 8 Employee_Number 1018 Last_Name Ratzlaff
Rank(Employee_Number) 9 Employee_Number 1017 Last_Name Runyon
Rank(Employee_Number) 10 Employee_Number 1016 Last_Name Rogers
Rank(Employee_Number) 11 Employee_Number 1015 Last_Name Wilson
Rank(Employee_Number) 12 Employee_Number 1014 Last_Name Crane
Rank(Employee_Number) 13 Employee_Number 1013 Last_Name Phillips
Rank(Employee_Number) 14 Employee_Number 1012 Last_Name Hopkins
Rank(Employee_Number) 15 Employee_Number 1011 Last_Name Daly
Rank(Employee_Number) 16 Employee_Number 1010 Last_Name Rogers
Rank(Employee_Number) 17 Employee_Number 1009 Last_Name Lombardo
Rank(Employee_Number) 18 Employee_Number 1008 Last_Name Kanieski
Rank(Employee_Number) 19 Employee_Number 1007 Last_Name Villegas
Rank(Employee_Number) 20 Employee_Number 1006 Last_Name Stein
Rank(Employee_Number) 21 Employee_Number 1005 Last_Name Ryan
Rank(Employee_Number) 22 Employee_Number 1004 Last_Name Johnson
Rank(Employee_Number) 23 Employee_Number 1003 Last_Name Trader
Rank(Employee_Number) 24 Employee_Number 1002 Last_Name Brown
Rank(Employee_Number) 25 Employee_Number 1001 Last_Name Hoover
Enthusiast

Re: Identity Column Not Coming in a sequence. Need Suggestion.

The Teradata RDBMS does not have a direct SQL function to implement System-Assigned Keys. With Teradata V2R5, the SQL Identity feature can be used to “generate a table-level unique primary index” which is similar to a system-assigned key.
Another technique is to use an INMOD routine that assigns a unique value (system-assigned Primary Key) when loading the data with one of the Application Utilities (e.g., FastLoad, MultiLoad, or TPump.)
An INMOD routine is a user-written program that pre-processes the data before passing it to the application utility.