All Forums UDA
roy_soumya 3 posts Joined 04/09
20 Apr 2009
Update Strategy with Teradata

Hi,In Informatica, if we use update strategy we need Primary key in Target table.But in teradata we can't set primary key I think.Update strategy fails if target table does not have any Primary Key.How to solve this issue?Thanks.Roy

gg 12 posts Joined 10/04
21 Apr 2009

Teradata stores a primary key as unique primary index (UPI).Probably Informatica can deal with that.ct test_db.test1 (col1 integer not null,col2 char(1) not null,col3 varchar(255) not null)unique primary key (col1,col2);show table test_db.test1;CREATE SET TABLE testdb.test1 ,NO FALLBACK ,NO BEFORE JOURNAL,NO AFTER JOURNAL,CHECKSUM = DEFAULT(col1 INTEGER NOT NULL,col2 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,col3 VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)UNIQUE PRIMARY INDEX ( col1 ,col2 );Would be interesting to find out what happens in Informatica if you have an alternate key:create unique index ak1 (col3) on test_db.test1;CREATE SET TABLE test_db.test1 ,NO FALLBACK ,NO BEFORE JOURNAL,NO AFTER JOURNAL,CHECKSUM = DEFAULT(col1 INTEGER NOT NULL,col2 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,col3 VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)UNIQUE PRIMARY INDEX ( col1 ,col2 )UNIQUE INDEX ak1 ( col3 );

shri_infa 3 posts Joined 05/09
06 May 2009

Hi Roy,In Infa you can designate the columns which u want to update against as PK's in the target definiton, update strategy wil work then.

michaldlouhy 4 posts Joined 06/09
24 Jun 2009

I also think, that it does not matter if target table in Teradata has UPI or NUPI, what matters is how you set PK in target definition in Informatica.

Adeel Chaudhry 773 posts Joined 04/08
24 Jun 2009

Hello,The concepts of INDEX and KEY, though share some common attributes, but are pretty different. INDEX is the physical concept where as KEY is the logical concept.All you need to do is to mark the respective column(s) as PRIMARY KEY in Informatica (Target tab), as per the logical model.HTH!Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.