TPT DataConnector Error : Delimited Data Parsing error: Input record exceeds allocated storage

Tools & Utilities
Enthusiast

TPT DataConnector Error : Delimited Data Parsing error: Input record exceeds allocated storage

TPT DataConnector producer error : "Delimited Data Parsing error: Input record exceeds allocated storage"

Hello,

I am using TPT Data connector producer and sql inserter operator to load clob data using tab delimited file. Max length of text I am loading into CLOB is 90000 approx.

I am unable to load the files as it is failing with Below error.

FILE_READER: TPT19350 I/O error on file 'C:\redlogs\sample_data.fmt'.

FILE_READER: TPT19134 !ERROR! Fatal data error processing file 'C:\redlogs\sample_data.fmt'. Delimited Data Parsing error: Input record exceeds allocated storage on row 16.

FILE_READER: TPT19003 TPT Exit code set to 8.

TPT File :

USING CHARACTER SET ASCII

DEFINE JOB L_2_txtTABLE_FROM_FILE

DESCRIPTION 'LOAD L_2_txt TABLE FROM A FILE'

(

  DEFINE SCHEMA L_2_txt_SCHEMA

  DESCRIPTION 'TABLE L_2_txt SCHEMA'

  (

      "COL1"                                     varchar(39)

    , "Col2"                                     varchar(39)

    , "Col3"                                     CLOB(6213140) AS DEFERRED BY NAME

  );

  DEFINE OPERATOR DDL_OPERATOR()

  DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DDL OPERATOR'

  TYPE DDL

  ATTRIBUTES

  (

    VARCHAR ARRAY ErrorList = ['3706','3803','3807'],

    VARCHAR DateForm,

    VARCHAR PrivateLogName = 'L_2_txt_DDL',

    VARCHAR TdpId = 'xxx.xxx.xx.xxx',

    VARCHAR UserName = 'USR',

    VARCHAR UserPassword = @TDPassword,

    VARCHAR AccountID

   );

  DEFINE OPERATOR FILE_READER()

  DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'

  TYPE DATACONNECTOR PRODUCER

  SCHEMA L_2_txt_SCHEMA

  ATTRIBUTES

  (

    VARCHAR Format = 'Delimited',

    TextDelimiter = 'TAB',    

    VARCHAR IndicatorMode = 'N',

    VARCHAR OpenMode = 'Read',

    VARCHAR FileName = 'C:\redlogs\sample_data.fmt'

  );

    DEFINE OPERATOR SQL_INSERTER ()

    DESCRIPTION 'TERADATA INSERTER UTILITY'

    TYPE INSERTER

    INPUT SCHEMA *

    ATTRIBUTES

    (

    VARCHAR TraceLevel = 'None',

    VARCHAR PrivateLogName = 'ins_log',

    VARCHAR Tdpid = 'xxx.xxx.xx.xxx',

    VARCHAR UserName = 'USR',

    VARCHAR UserPassword = @TDPassword

    );

  STEP setup_tables

  (

    APPLY

    ('DROP TABLE DLS.L_2_txt_E1;'),

    ('DROP TABLE DLS.L_2_txt_E2;'),

    ('DROP TABLE DLS.L_2_txt_WT;'),

    ('DROP TABLE DLS.L_2_txt_LT;')

    TO OPERATOR (DDL_OPERATOR () );

  );

    STEP CREATE_SOURCE_TABLE

    (

    APPLY

    ('drop table DLS.L_2_txt ;'),

    ('create table DLS.L_2_txt , NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL

   (

        col1 VARCHAR(39),

        col2 VARCHAR(39),

        col3 clob(6213140)

    ) primary index L_2_txt_idx_PR (COL1);')

    TO OPERATOR ( DDL_OPERATOR () );

    );

    STEP LOADING_DATA_TO_SOURCE_TABLE

    (

    APPLY

    (

        'INSERT INTO DLS.L_2_txt

         values (:COL1, :COL2, :COL3);'

    )

   TO OPERATOR (SQL_INSERTER [1])

   SELECT * FROM OPERATOR (FILE_READER ());

);

);

3 REPLIES
Teradata Employee

Re: TPT DataConnector Error : Delimited Data Parsing error: Input record exceeds allocated storage

Delimited data processing reads in a record made up of VARCHAR data.

Thus, the max column size is 64000 bytes.

Plus, you specified the CLOB column as "deferred by name" which means your input record has (or should have) a file name in the 3rd field, and the actual LOB data is located in an external file.

-- SteveF
Enthusiast

Re: TPT DataConnector Error : Delimited Data Parsing error: Input record exceeds allocated storage

Yes. Loaded the data successfully. May be I missunderstood the concept of deferred by name earlier. But creating such files from SQL Server is still concern though.

Thanks Steven for your prompt replies to all the post.

~ Ashish

Teradata Employee

Re: TPT DataConnector Error : Delimited Data Parsing error: Input record exceeds allocated storage

Please read:

 http://developer.teradata.com/tools/articles/large-object-loading-with-teradata-parallel-transporter

It describes how to load LOBS into Teradata using TPT.

-- SteveF