Update Strategy with Teradata

UDA

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
4 REPLIES
gg
Enthusiast

Re: Update Strategy with Teradata

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 );

Re: Update Strategy with Teradata

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.

Re: Update Strategy with Teradata



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.
Teradata Employee

Re: Update Strategy with Teradata

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