All Forums Tools
jingguo 9 posts Joined 08/16
24 Aug 2016
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.
 

dnoeth 4628 posts Joined 11/04
24 Aug 2016

Can you show the actual FastLoad script?

Dieter

jingguo 9 posts Joined 08/16
24 Aug 2016

 

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

jingguo 9 posts Joined 08/16
24 Aug 2016

 

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

feinholz 1234 posts Joined 05/08
24 Aug 2016

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

jingguo 9 posts Joined 08/16
30 Aug 2016

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,
 
 
 
 

feinholz 1234 posts Joined 05/08
01 Sep 2016

The delimiter must be a printable character.
FastLoad does not support hex values.
 

--SteveF

You must sign in to leave a comment.