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

[02CSE33]

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.

:(

[dnoeth]

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

[02CSE33]

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.

[dnoeth]

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

[Manjunath_2012u]

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????

[dnoeth]

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

[Manjunath_2012u]

Dieter,

Thanks a lot , this worked :) 

[malli111]

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

[dnoeth]

Hi Malli,

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

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

[malli111]

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