fastload cannot start with error "The request exceeds the length limit, The maximum length is 1048500."

Tools
Enthusiast

fastload cannot start with error "The request exceeds the length limit, The maximum length is 1048500."

Hi,

I am writing a fastload script to load a 13M files to a teradata table.

I received error 

     ===================================================================

     =                                                                 =

     =          FASTLOAD UTILITY     VERSION 14.10.00.07               =

     =          PLATFORM LINUX                                         =

     =                                                                 =

     ===================================================================

 

     ===================================================================

     =                                                                 =

     =          Copyright 1984-2015, Teradata Corporation.             =

     =          ALL RIGHTS RESERVED.                                   =

     =                                                                 =

     ===================================================================

 

**** 04:33:39 Processing starting at: Wed Aug 24 04:33:39 2016

 

**** 04:33:39 The request exceeds the length limit.

    The maximum length is 1048500.

     ===================================================================

     =                                                                 =

     =          Exiting                                                =

     =                                                                 =

     ===================================================================

 

**** 04:33:39 Total processor time used = '0.03 Seconds'

     .        Start : Wed Aug 24 04:33:39 2016

     .        End   : Wed Aug 24 04:33:39 2016

     .        Highest return code encountered = '12'.

**** 04:33:39 FDL4818 FastLoad Terminated

 

The data file is 13MB and has 98751 rows.

I remember fast load can handle many more rows than this.

Kindly let me know how to load this file in one piece.

 

6 REPLIES
Junior Contributor

Re: fastload cannot start with error "The request exceeds the length limit, The maximum length is 1048500."

Can you show the actual FastLoad script?

Enthusiast

Re: fastload cannot start with error "The request exceeds the length limit, The maximum length is 1048500."


SESSIONS 4;  /* optional ,total number of sessions to be allotted for the script */

ERRLIMIT 1000; /* optional */

logon td/user,pass; 

DATABASE dev_scratch;

DROP TABLE target_table_test; /* final target table */

DROP TABLE error_1; /* error table ,internal to fast load utility needed to be defined */

DROP TABLE error_2; /* error table ,internal to fast load utility needed to be defined */

CREATE MULTISET TABLE target_table_test

     (

      column1 CHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,

      column2 CHAR(60) CHARACTER SET LATIN NOT CASESPECIFIC,

      column3 TIMESTAMP(6),

      column4 TIMESTAMP(6),

      column5 FLOAT,

      column6 FLOAT,

      column7 CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,

      column8 FLOAT,

      column9 FLOAT,

      column10 CHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      column11 CHAR(60) CHARACTER SET LATIN NOT CASESPECIFIC)

NO PRIMARY INDEX ; /* because the table doesn't have too many rows, i used no primary index. anyway it doesn't seem to cause the problem here */

SET RECORD VARTEXT "\t";  /* delimiter in the source file */

DEFINE                  /* define the structure of the source file */

     column1       (VARCHAR(80)),

     column2     (VARCHAR(120)),

     column3          (VARCHAR(52)),

      column4   (VARCHAR(52)),

    column5 (VARCHAR(60)),

    column6 (VARCHAR(60)),

    column7 (VARCHAR(16)),

    column8 (VARCHAR(60)),

    column9 (VARCHAR(60)),

column10 (VARCHAR(200)),

column11 (VARCHAR(120))

File=csv_fl.dat;   /* source file location */

SHOW;

BEGIN LOADING ethoca_base_test

ERRORFILES error_1, error_2;

INSERT INTO  ethoca_base_test   /* final insert */

(

column1                                                                                 

,.../* skip the column names just for an example */

,column11              

)

VALUES

(

:column1                                                                                 

,.../* skip the column names just for an example */                                                                           

, :column11                                                                                       

)

;

END LOADING;

LOGOFF;

----------

i have to mention that if I replace the dat file with a file of 100 lines, this script works.

I use this command to run it:

fastload < test_fastload.fl

Enthusiast

Re: fastload cannot start with error "The request exceeds the length limit, The maximum length is 1048500."

SESSIONS 4;  /* optional ,total number of sessions to be allotted for the script */

ERRLIMIT 1000; /* optional */

logon td/user,pass; 

DATABASE dev_scratch;

DROP TABLE target_table_test; /* final target table */

DROP TABLE error_1; /* error table ,internal to fast load utility needed to be defined */

DROP TABLE error_2; /* error table ,internal to fast load utility needed to be defined */

CREATE MULTISET TABLE target_table_test

     (

      column1 CHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,

      column2 CHAR(60) CHARACTER SET LATIN NOT CASESPECIFIC,

      column3 TIMESTAMP(6),

      column4 TIMESTAMP(6),

      column5 FLOAT,

      column6 FLOAT,

      column7 CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,

      column8 FLOAT,

      column9 FLOAT,

      column10 CHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      column11 CHAR(60) CHARACTER SET LATIN NOT CASESPECIFIC)

NO PRIMARY INDEX ; /* because the table doesn't have too many rows, i used no primary index. anyway it doesn't seem to cause the problem here */

SET RECORD VARTEXT " ";  /* delimiter tab, i could not type tab in this post, just for an example */

DEFINE                  /* define the structure of the source file */

     column1       (VARCHAR(80)),

     column2     (VARCHAR(120)),

     column3          (VARCHAR(52)),

      column4   (VARCHAR(52)),

    column5 (VARCHAR(60)),

    column6 (VARCHAR(60)),

    column7 (VARCHAR(16)),

    column8 (VARCHAR(60)),

    column9 (VARCHAR(60)),

column10 (VARCHAR(200)),

column11 (VARCHAR(120))

File=csv_fl.dat;   /* source file location */

SHOW;

BEGIN LOADING ethoca_base_test

ERRORFILES error_1, error_2;

INSERT INTO  ethoca_base_test   /* final insert */

(

column1                                                                                 

,.../* skip the column names just for an example */

,column11              

)

VALUES

(

:column1                                                                                 

,.../* skip the column names just for an example */                                                                           

, :column11                                                                                       

)

;

END LOADING;

LOGOFF;

----------

i have to mention that if I replace the dat file with a file of 100 lines, this script works.

I use this command to run it:

fastload < test_fastload.fl

Teradata Employee

Re: fastload cannot start with error "The request exceeds the length limit, The maximum length is 1048500."

I believe the issue *might* be related to the delimiter.

The use of "\t" does not mean use the TAB character. We will look for the characters "\" followed by "t", and thus we keep reading until we get too much data.

Try this and see if it helps.:

SET RECORD VARTEXT "TAB";

-- SteveF
Enthusiast

Re: fastload cannot start with error "The request exceeds the length limit, The maximum length is 1048500."

Hi SteveF,

Thanks for your reply. It works.

Another questions: if I have the delimiter as "DEL" or ^P or 0x10, how do I write "SET RECORD VARTEXT"?

Is there a mapping somewhere between the teradata delimiter and ascii?

Thanks,

Teradata Employee

Re: fastload cannot start with error "The request exceeds the length limit, The maximum length is 1048500."

The delimiter must be a printable character.

FastLoad does not support hex values.

-- SteveF