Problem creating NOPI table

Database

Problem creating NOPI table

I'm a Teradata newbie. I want to create tables with NO PRIMARY INDEX. I am on version 12.0. My DDL looks similar to this.

CREATE MULTISET TABLE UP_KWADA.TEST_INSERT, FALLBACK,
NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT
(
LINE_NUMBER DECIMAL(18,0),
TRANSACTION_SET_ID DECIMAL(18,0),
FILE_ID VARCHAR(20)
) NO PRIMARY INDEX ;

I get the error message
3706: Syntax error: Expected something between ')' and the 'NO' keyword.

My need for a no primary index table is due to the fact that my inserts into my "work" tables are eliminating rows that should not be (dup key probably).

Thanks for your advice.

3 REPLIES
Enthusiast

Re: Problem creating NOPI table

The No-PI table is a feature that was introduced in version 13.0. Anyway, as far as I know, there is no automatic elimination of rows on insert into a multiset table unless you are using FastLoad. That problem can be avoided by using MultiLoad instead of FastLoad.

Re: Problem creating NOPI table

Thanks Jim for responding. The fact that No-PI is a version 13.0 is one issue. The reason that I dropped rows was that whole records were being duplicated, not just the primary index.
Thanks again.
Enthusiast

Re: Problem creating NOPI table

Again, the removal of duplicate rows is a FastLoad problem. Try using a different insert method, such as MultiLoad or regular SQL.