Receiving Failure 2673 The source parcel length does not match data that was defined while importing using BTEQ

Tools
Enthusiast

Receiving Failure 2673 The source parcel length does not match data that was defined while importing using BTEQ

Hi Gurus,

Now I am able to login properly in both Batch as well as Interactive mode through BTEQ.

The problem which I am facing is with IMPORT of data to Database.

Details of the IMPORT.

.IMPORT vartext '|'  FILE=/home/dsadm/Automation/fact_data_run1.txt;
        .REPEAT *
        USING
        REPORT_NAME     (VARCHAR(50)),
        SUB_REPORT_NAME (VARCHAR(100)),
        OPTIONAL_FLAG   (VARCHAR(1)),
        DATA_DATE       (VARCHAR(20)) ,
        PROCESS_DATE    (VARCHAR(20)) ,
        Load_START      (VARCHAR(20)) ,
        Load_END        (TIMESTAMP(0)) ,
        IS_WEEKLY       (VARCHAR(20)) ,
        IS_MONTHLY      (VARCHAR(20)),
        FAILURE         (VARCHAR(20)),
        FAILURE_SR      (VARCHAR(20)),
        FAILURE_REASON  (VARCHAR(1000)),
        RAG_STATUS      (VARCHAR(20)),
        HAD_DELAY       (VARCHAR(20)),
        DELAY_REASON    (VARCHAR(20)),
        UPDATED_BY      (VARCHAR(20))

        INSERT INTO NDW_ETL_WORK.NDW_REPORT_FACT (REPORT_NAME, SUB_REPORT_NAME,OPTIONAL_FLAG,DATA_DATE,PROCESS_DATE,Load_START,Load_END,IS_WEEKLY,IS_MONTHLY,FAILURE,
        FAILURE_SR,FAILURE_REASON,RAG_STATUS,HAD_DELAY,DELAY_REASON,UPDATED_BY)
        values
        (
                :REPORT_NAME,
                :SUB_REPORT_NAME,
                :OPTIONAL_FLAG,
  CAST(:DATA_DATE AS DATE),
                CAST(:PROCESS_DATE AS DATE),
                CAST(:Load_START AS TIME(0)),
                CAST(:Load_END AS TIME(0)),
                :IS_WEEKLY,
                :IS_MONTHLY,
                :FAILURE,
                :FAILURE_SR,
                :FAILURE_REASON,
                :RAG_STATUS,
                :HAD_DELAY,
                :DELAY_REASON,
                :UPDATED_BY
        );

Sample Record: [ There is end of line in the source file, i.e. cursor will point to begining of next line]

SUBSCRIBER_REPORT|SUBSCRIBER_REPORT|N|2013-03-12|2013-03-13|12:30:00 AM|04:35:00 AM|N|N|N|N|N|GREEN|N|N|SYSTEM

Table Def :

CREATE SET TABLE NDW_ETL_WORK.NDW_REPORT_FACT ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      REPORT_NAME VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      SUB_REPORT_NAME VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
      OPTIONAL_FLAG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC CHECK ( OPTIONAL_FLAG  IN ('Y','N') ),
      DATA_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL ,
      PROCESS_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      Load_START TIMESTAMP(0),
      Load_END TIMESTAMP(0),
      IS_WEEKLY CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC CHECK ( IS_WEEKLY  IN ('Y','N') ),
      IS_MONTHLY CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC CHECK ( IS_MONTHLY  IN ('Y','N') ),
      FAILURE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC CHECK ( FAILURE  IN ('Y','N') ),
      FAILURE_SR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
      FAILURE_REASON VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      RAG_STATUS VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
      HAD_DELAY CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC CHECK ( HAD_DELAY  IN ('Y','N') ),
      DELAY_REASON VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      UPDATED_BY VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
UNIQUE PRIMARY INDEX NDW_REPORT_FACT_UPI ( REPORT_NAME ,SUB_REPORT_NAME ,
DATA_DATE );

Now while executing, the BTEQ I am receiving the following error:

 *** Failure 2673 The source parcel length does not match data that was defined.

                Statement# 1, Info =1

 *** Failure 2673 The source parcel length does not match data that was defined.

                Statement# 1, Info =1

 *** Warning: Out of data.

All the data sets are moving as rejected record.

Can you please suggest me on this. As now I am really stuck.

:(

33 REPLIES
Junior Contributor

Re: Receiving Failure 2673 The source parcel length does not match data that was defined while importing using BTEQ

Hi Sudipta,

the error is obvious, you define Load_END as a TIMESTAMP instead of VARCHAR :-)

You should also add a FORMAT to the cast to TIME due to AM/PM:

CAST(:Load_END AS TIME(0) FORMAT 'hh:mi:ssBt'),

And you should add a PACK (if there are no errors in your input data) to get a faster load.

Dieter

Enthusiast

Re: Receiving Failure 2673 The source parcel length does not match data that was defined while importing using BTEQ

Hi Dieter,

I am really puzzled with Timestamp handling.

I have re-arranged my data as :

SUBSCRIBER_REPORT|SUBSCRIBER_REPORT|N|2013-03-12|2013-03-13|2013-03-13 12:30:00|2013-03-13 04:35:00|N|N|N|N|N|GREEN|N|N|SYSTEM

In USING part of IMPORT i have made the ammendments as VARCHAR as suggested:

 Load_START      (VARCHAR(20)) ,
 Load_END        (VARCHAR(20)) ,

In the INSERT query i have used the following:

 CAST(:Load_START AS TIME(0)FORMAT 'yyyy-mm-dd hh:mi:ss'),
 CAST(:Load_END AS TIME(0)FORMAT 'yyyy-mm-dd hh:mi:ss'),

And now the PARCEL LENGHT error is fixed but receiving invalid time error:

 *** Warning: Out of data.
 *** Failure 3530 Invalid FORMAT string 'yyyy-mm-ddhh:mi:ss'.
Statement# 1, Info =0

Can you please suggest on this.

Also if you have any posts/articles for Date Time arithmetic in Teradata, it will be really helpfull for me.

Junior Contributor

Re: Receiving Failure 2673 The source parcel length does not match data that was defined while importing using BTEQ

Hi Sudipta,

there are different datatypes in Teradata for DATE, TIME and TIMESTAMP.

When you cast a string to a time you can't use a date part, when you cast to a date you can't use a time part.

In your case either keep data as is and use the cast i showed or use the rearranged data and cast to a timestamp.

And because the FORMAT is based on Cobol you must use a 'B' instead of a blank in the format string: yyyy-mm-ddBhh:mi:ss

You'll find a lot about working with DateTime datatypes scattered throughout the manuals and there's an old article written by Geoffrey Rommel on the TeradataForum website covering the basics: http://www.teradataforum.com/l081007a.htm

It's a bit outdatet, as there are more advanced datatypes/functions, but there are two good articles on PERIODs by Rob Garrison:

http://developer.teradata.com/database/articles/exploring-teradata-13s-period-functions

Dieter

Re: Receiving Failure 2673 The source parcel length does not match data that was defined while importing using BTEQ

hi guys,

im the below export script to export data,

.logon xxxxxxxx/students,learning13

.export data file= c:/au/data33_4

sel

trim(cast(cast(account_number as format 'z(i)') as varchar(10))),

trim(cast(cast(number as format 'z(i)') as varchar(10))),

trim(cast(street as varchar(30))),

trim(cast(city as varchar(30)) ),

trim(cast(state as char(2))), 

trim(cast(zip_code as varchar(30))),

trim(cast(cast(balance_forward as format 'z(i)') as varchar(20))),

trim(cast(cast(balance_current as format 'z(i)') as varchar(20))) 

from ap.accounts;

.export reset

.quit

and using the below script to import it,

.logon xxxxxxxxxx/students,learning13

DELETE FROM STUDENTS.ACCOUNTS1 ALL;

.import data file= c:/au/data33_4

.quite on

.repeat * pack 1000

using(

in_account_number   varchar(10),

in_number           varchar(10),

in_street           varchar(30),

in_city             varchar(30),

in_state            char(2),

in_zip_code         varchar(30),

in_balance_forward  varchar(20),

in_balance_current  varchar(20) )

INSERT INTO STUDENTS.ACCOUNTS1 

VALUES ( 

cast(:in_account_number as integer),

cast(:in_number as integer), 

cast(:in_street as char(25)),

cast(:in_city as char(20)),

:in_state,

cast(:in_zip_code as integer),

cast(:in_balance_forward as decimal(10,2)),

cast(:in_balance_current as decimal(10,2))

);

.quite off

.quit 

im gettin the error,

the source parcel lenght does not match the data that was defined.

can anyone help me here????

Junior Contributor

Re: Receiving Failure 2673 The source parcel length does not match data that was defined while importing using BTEQ

Hi Manjunath.M,

when you TRIM a CHAR(2) i changes to a VARCHAR(2).

You can either

  • remove the trim
  • export using CAST(TRIM(state) AS CHAR(2)) or
  • import using in_state            varchar(2),
Btw, it's "quiet on/off" , not "quite on/off" :-)

Dieter

Re: Receiving Failure 2673 The source parcel length does not match data that was defined while importing using BTEQ

Dieter,

Thanks a lot , this worked :) 

Enthusiast

Re: Receiving Failure 2673 The source parcel length does not match data that was defined while importing using BTEQ

Hi All,

I am new to teradata. I am importing data from text file into table.

Text file data:

1,a,3

2,b,4

Logic:

logon username/password;

database dbname;

.IMPORT VARTEXT ','  FILE='/users/mbukkala/e2.txt'

.QUIET ON  

.repeat *

using

dno(INTEGER),

dname(VARCHAR(200)),

eno(INTEGER)

insert into supportdb.tdp(dno,dname,eno) values(:dno,:dname,:eno);

.logoff;

.quit;

i am getting below error

Receiving Failure 2673 The source parcel length does not match data that was defined while importing using BTEQ.

Please help me

Thans in advance

Malli

Junior Contributor

Re: Receiving Failure 2673 The source parcel length does not match data that was defined while importing using BTEQ

Hi Malli,

when you define VARTEXT all fields in USING must be VARCHARs:

using
dno (VARCHAR(11)),
dname(VARCHAR(200)),
eno(VARCHAR(11))
Enthusiast

Re: Receiving Failure 2673 The source parcel length does not match data that was defined while importing using BTEQ

HI Dieter,

Thanks you so much its worked.

One more doubt:  i am exporting data in all modes.But incase of .EXPORT DIF mode i am unable to get column names in the result set.

Please help me on this

Thanks in advance,

Malli