Keep getting StatusCode="2802",ErrorMessage="SQLState =23505" after delete and reinsert

Database
Enthusiast

Keep getting StatusCode="2802",ErrorMessage="SQLState =23505" after delete and reinsert

Hi, I was wondering if there is a cach or anything. After I clear the table using "Delete from" and reinsert the same set of rows, I often get the error on totally unique record. I was wondering if there was any cache or anything.

CREATE MULTISET TABLE SANDBOX.MERCHANT_SETTLEMENT ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      settlement_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY

           (START WITH 1 

            INCREMENT BY 1 

            MINVALUE -2147483647 

            MAXVALUE 2147483647 

            NO CYCLE),

      booking_num INTEGER NOT NULL,

      settlement_source INTEGER NOT NULL,

....

      CONSTRAINT MRCHSET_SOURCE_ENUM CHECK ( settlement_source =  1  ), 

CONSTRAINT MERCH_SETTLEMENT_PK PRIMARY KEY ( settlement_id ))

PRIMARY INDEX ( booking_num );

13 REPLIES
Teradata Employee

Re: Keep getting StatusCode="2802",ErrorMessage="SQLState =23505" after delete and reinsert

No there is no cache .... have you checked if there are actually full-row duplicates coming from source?

Enthusiast

Re: Keep getting StatusCode="2802",ErrorMessage="SQLState =23505" after delete and reinsert

No, there is no duplicates. Actually, this happened on different records and same time they all succeeded without any issue. The latter usually happens after waiting for a few hours. BTW, I am using the vm 14 on VMWare running on my mac. The settlement_id jumps around like the following:

10093

10092

10091

10090

10089

10088

266

265

264

263

262

261

260

Enthusiast

Re: Keep getting StatusCode="2802",ErrorMessage="SQLState =23505" after delete and reinsert

Hi,

AFAIK, The identity value is cached in PE, and also saved in some table, So it will start from 1 only when you recreate the the table, other wise the number might be repeated. you have defined PK on Settlement_id, PK is implemented as USI, so it will check for dups. I think you should add drop and create to you script in order to avoid this error.

Khurram
Teradata Employee

Re: Keep getting StatusCode="2802",ErrorMessage="SQLState =23505" after delete and reinsert

Jumps around .... ? Can you share complete DDLs and the SQL you are using?

Enthusiast

Re: Keep getting StatusCode="2802",ErrorMessage="SQLState =23505" after delete and reinsert

Thanks Khurram, seems you are right. My test never fails if the table is dropped and recreated. I am really surprised that Teradata has this kind of behavior.

Enthusiast

Re: Keep getting StatusCode="2802",ErrorMessage="SQLState =23505" after delete and reinsert

This jump around is normal, AFAIk TD generates id with gaps. So dont worry about that, but as long as the table is being deleted and reinserted the issue can occure. 

Khurram
Enthusiast

Re: Keep getting StatusCode="2802",ErrorMessage="SQLState =23505" after delete and reinsert

with your ddl for "identity" usage, you will always get totally unique records(as long as [min, max] range), regardless of that you delete or not or keep inserting your same data, because "identity" will always generate you a unique value within the range.

Teradata Employee

Re: Keep getting StatusCode="2802",ErrorMessage="SQLState =23505" after delete and reinsert

This is due to the fact that sequence number is generated on AMP-local basis.

It is not called a sequence for a reason, but it will surely not repeat the values.

Junior Contributor

Re: Keep getting StatusCode="2802",ErrorMessage="SQLState =23505" after delete and reinsert

A sequence GENERATED ALWAYS AS IDENTITY with NO CYCLE should never return a duplicate value (based on that you don't need to define a PK on settlement_id).

What's your client and your TD release?