Teradata Unique Index Question.

mallik vemugunta

Teradata Unique Index Question.
Hi ,


I want to know if I can define the column as Unique Index that have a trigger, where the value increments automatically (Running number). Appreciate your help.

Thanks
Kris.

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: Teradata Unique Index Question.
(in response to mallik vemugunta)
Hi Kris

I think the reference to "Teradata" in the subject line may have
confused us all!

Is this a question about Teradata or DB2??

If DB2, then it's easy - You can create ANY (numeric) column in a table
as AS IDENTITY - this defaults to generating the next highest value for
each row that is inserted. No trigger is needed and no index

Hope this helps

Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125752
[login to unmask email]


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of mallik vemugunta
Sent: 05 December 2006 22:31
To: [login to unmask email]
Subject: [DB2-L] Teradata Unique Index Question.


Hi ,


I want to know if I can define the column as Unique Index that have a
trigger, where the value increments automatically (Running number).
Appreciate your help.

Thanks
Kris.

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

mallik vemugunta

Re: Teradata Unique Index Question.
(in response to Phil Grainger)
Phil,

Sorry for the confuse, this is in Teradata not DB2.

"Grainger, Phil" <[login to unmask email]> wrote:
Hi Kris

I think the reference to "Teradata" in the subject line may have confused us all!

Is this a question about Teradata or DB2??

If DB2, then it's easy - You can create ANY (numeric) column in a table as AS IDENTITY - this defaults to generating the next highest value for each row that is inserted. No trigger is needed and no index

Hope this helps

Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125752
[login to unmask email]



---------------------------------
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of mallik vemugunta
Sent: 05 December 2006 22:31
To: [login to unmask email]
Subject: [DB2-L] Teradata Unique Index Question.



Hi ,


I want to know if I can define the column as Unique Index that have a trigger, where the value increments automatically (Running number). Appreciate your help.

Thanks
Kris.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: Teradata Unique Index Question.
(in response to mallik vemugunta)
Then you might not get any replies from a DB2 discussion group......

Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125752
[login to unmask email]


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of mallik vemugunta
Sent: 06 December 2006 14:43
To: [login to unmask email]
Subject: Re: [DB2-L] Teradata Unique Index Question.


Phil,

Sorry for the confuse, this is in Teradata not DB2.

"Grainger, Phil" <[login to unmask email]> wrote:

Hi Kris

I think the reference to "Teradata" in the subject line may have
confused us all!

Is this a question about Teradata or DB2??

If DB2, then it's easy - You can create ANY (numeric) column in
a table as AS IDENTITY - this defaults to generating the next highest
value for each row that is inserted. No trigger is needed and no index

Hope this helps

Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125752
[login to unmask email]


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of mallik vemugunta
Sent: 05 December 2006 22:31
To: [login to unmask email]
Subject: [DB2-L] Teradata Unique Index Question.


Hi ,


I want to know if I can define the column as Unique Index that
have a trigger, where the value increments automatically (Running
number). Appreciate your help.

Thanks
Kris.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Francis C Leblanc

Re: Teradata Unique Index Question.
(in response to Phil Grainger)
Our Teradata DBA had this to say on the topic. Hope this helps.



Fritz



Teradata has an auto increment type that will do that. I think you have
to be a little careful when copying data from one database to another if
it has that type. We tried it and backed off. We were using Informatica
to assign sequence keys externally as the data was presented to the
Teradata loader.



There are a number of options to the data type, some of which I think
might make it easier to control copies.



If you want more details on the type, let me know and I'll look it up.



One issue to consider is that if you want to join two tables in Teradata
you'll be much happier if they share primary indexes, which are a
Teradata specific notion, not to be confused with primary key. They can
be the primary key, but their first function is to define the column or
columns on which to hash the data to distribute it across the several
virtual access module processors.



In fact, we found that it was better with Teradata to use natural keys
and to stay away from sequence keys. Not an easy adjustment for someone
used to Oracle or DB2, but join performance is critical. If you have a
parent and child relationship it's common to define the primary index of
the child to match the parent, then define a secondary index as the
unique/primary key constraint that includes at least one more column.
Assuming the usual order-line item sort of relationship.



So, in summary, yes you can define a sequence key identifier, but you
probably won't want to in Teradata, unless like us you want to
experience first hand all the mistakes someone can make trying to
faithfully port an application from DB2, Oracle, Informix, etc to
Teradata. We freely experimented with all the mistakes, even after
reading the reasons for not falling into those traps.


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Grainger, Phil
Sent: Wednesday, December 06, 2006 8:49 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Teradata Unique Index Question.


Then you might not get any replies from a DB2 discussion group......

Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125752
[login to unmask email]


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of mallik vemugunta
Sent: 06 December 2006 14:43
To: [login to unmask email]
Subject: Re: [DB2-L] Teradata Unique Index Question.


Phil,

Sorry for the confuse, this is in Teradata not DB2.

"Grainger, Phil" <[login to unmask email]> wrote:

Hi Kris

I think the reference to "Teradata" in the subject line may have
confused us all!

Is this a question about Teradata or DB2??

If DB2, then it's easy - You can create ANY (numeric) column in
a table as AS IDENTITY - this defaults to generating the next highest
value for each row that is inserted. No trigger is needed and no index

Hope this helps

Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125752
[login to unmask email]


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of mallik vemugunta
Sent: 05 December 2006 22:31
To: [login to unmask email]
Subject: [DB2-L] Teradata Unique Index Question.


Hi ,


I want to know if I can define the column as Unique Index that
have a trigger, where the value increments automatically (Running
number). Appreciate your help.

Thanks
Kris.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm