All Forums Database
zhiyanshao 10 posts Joined 10/13
19 Feb 2014
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 );

Adeel Chaudhry 773 posts Joined 04/08
19 Feb 2014

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

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

zhiyanshao 10 posts Joined 10/13
19 Feb 2014

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

M.Saeed Khurram 544 posts Joined 09/12
19 Feb 2014

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

Adeel Chaudhry 773 posts Joined 04/08
19 Feb 2014

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

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

zhiyanshao 10 posts Joined 10/13
20 Feb 2014

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.

M.Saeed Khurram 544 posts Joined 09/12
20 Feb 2014

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

jinli 10 posts Joined 11/12
20 Feb 2014

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.

Adeel Chaudhry 773 posts Joined 04/08
20 Feb 2014

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.

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

dnoeth 4628 posts Joined 11/04
20 Feb 2014

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?

Dieter

zhiyanshao 10 posts Joined 10/13
24 Feb 2014

My client is using jdbc driver 14.00.00.13 and TD is 14.0.3.02

Adeel Chaudhry 773 posts Joined 04/08
24 Feb 2014

I think there is some missing information .... can you share the whole script you use for creating-populating-deleting-recreating/repopulating data?
 
IDENTITY column never generates duplicates. Hence it has to be some other problem.

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

zhiyanshao 10 posts Joined 10/13
26 Feb 2014

There is definitely an issue that happens randomly. Here is the stored procedure that is used to insert.
 

(

  ImportBatchID        INTEGER,

  InvoiceDate          DATE,

  OUT OutErrorCode     INTEGER,

  OUT OutErrorMsg      VARCHAR(2000)

)

BEGIN

  DECLARE provider INTEGER DEFAULT 1; -- provider is settlement source no. 1

  DECLARE InvoiceDateInt INTEGER;

  DECLARE DatabaseName  VARCHAR(128) DEFAULT 'SANDBOX';

  DECLARE ProcedureName VARCHAR(128) DEFAULT 'provider_INVOICE_IMPORT';

  DECLARE ValidationFailed      CONDITION;

  DECLARE StatusBit             INTEGER;

  DECLARE CreditDebit           VARCHAR(8);

  DECLARE StatusInvoiceReceived INTEGER;

  DECLARE StatusInvoiceCancel   INTEGER;

  DECLARE ErrCode               INTEGER DEFAULT 0;

  DECLARE ErrMsg                VARCHAR(2000);

 

 

  CALL SANDBOX.DATE_TO_INT(InvoiceDate, /*OUT*/ InvoiceDateInt);

 

  FOR r AS inv_cursor CURSOR FOR 

    SELECT

           bus_partnr_spec_bkg_info1txt as booking_num,

           :provider as settlement_source,

           book_cancel as settlement_type,

           :InvoiceDate as settlement_doc_date,

           invoice_number as settlement_doc_id, 

           excel_row_number as settlement_doc_line, 

           transaction_id as settlement_unique_id,

           CAST(confirmation_number AS VARCHAR(50)) as confirmation_number,

           CAST(itinerary_id AS VARCHAR(50)) as id_for_merchant,

           CAST(NULL AS VARCHAR(200)) as Description,

           amount as supplier_amount,

           currency_code as supplier_currency,

           amount_us as system_amount, 

           'USD' as system_currency, 

           CASE system_amount WHEN 0 THEN 0 ELSE supplier_amount/system_amount END as supp_to_sys_ex_rate,

           create_date, 

           cancel_date,

           :ImportBatchID as import_batch_id

      FROM

           SANDBOX.STUFF_provider_INVOICE

     WHERE

           invoice_date = :InvoiceDateInt

  UNION ALL

    SELECT

           bus_partnr_spec_bkg_info1txt as booking_num,

           :provider as settlement_source,

           'Refund' as settlement_type,

           :InvoiceDate as settlement_doc_date,

           invoice_number as settlement_doc_id, 

           excel_row_number as settlement_doc_line, 

           transaction_id as settlement_unique_id,union

           CAST(NULL AS VARCHAR(50)) as confirmation_number,

           CAST(itinerary_id AS VARCHAR(50)) as id_for_merchant,

           (booking_payment_desc || '. ' || booking_payment_reason) as description,

           generic_payment_amount_local as supplier_amount,

           currency_code as supplier_currency,

           generic_payment_amount_usd as system_amount, 

           'USD' as system_currency, 

           CASE system_amount WHEN 0 THEN 0 ELSE supplier_amount/system_amount END as supp_to_sys_ex_rate,

           book_date, 

           generic_payment_date, 

           :ImportBatchID as import_batch_id

      FROM

           SANDBOX.STUFF_provider_REFUND_INVOICE

     WHERE

           invoice_date = :InvoiceDateInt

  DO

 

    -- Bring the data from import file to our MERCHANT_SETTLEMENT file

    INSERT INTO SANDBOX.MERCHANT_SETTLEMENT (

           booking_num, settlement_source, settlement_type, 

           settlement_doc_date, settlement_doc_id, settlement_doc_line,

           settlement_unique_id, confirmation_number, id_for_merchant, description,

           supplier_amount, supplier_currency, system_amount, system_currency, supp_to_sys_ex_rate, 

           create_date, cancel_date, import_batch_id)

    VALUES (

           r.booking_num, r.settlement_source, r.settlement_type, 

           r.settlement_doc_date, r.settlement_doc_id, r.settlement_doc_line,

           r.settlement_unique_id, r.confirmation_number, r.id_for_merchant, r.description,

           r.supplier_amount, r.supplier_currency, r.system_amount, r.system_currency, r.supp_to_sys_ex_rate, 

           r.create_date, r.cancel_date, r.import_batch_id);

 

  END FOR;

 

  SET OutErrorCode = ErrCode;

  SET OutErrorMsg = ErrMsg;

END;

zhiyanshao 10 posts Joined 10/13
05 Mar 2014

To follow up on this issue, I finally identified that the error came from a logging call I didn't include here. The logging call will insert a record to the following table inside FOR loop. The insert call didn't have entry_time as parameter so each time it uses the default value. The problem is that if I run my tests locally, often the timestamp is the same for multiple records. Whenever this happens, it will throw the dup error. The table recreation didn't help when I ran the test locally.
Thanks for everyone's help.
 
CREATE SET TABLE SANDBOX.TRAVEL_FIN_LOG ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      import_batch_id INTEGER NOT NULL,
      database_name VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      procedure_name VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      entry_type VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      sqlcode_ INTEGER,
      sqlstate_ CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
      activity_count_ INTEGER,
      error_code INTEGER,
      message VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      entry_time TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6))
PRIMARY INDEX ( database_name ,procedure_name );
 
 

You must sign in to leave a comment.