FASTLOAD WITH FIXED WIDTH FILES

Database
Junior Contributor

Re: FASTLOAD WITH FIXED WIDTH FILES

When you pay for answers you might get faster response times than 2 hours.

Otherwise answers are voluntary in this forum. And the probability of getting answers will be ower when your unkind.

Did you ever read Oracle's SQL*Loader manuals? You actually considered them to be more easy to understand? Ouch.

There are hardly any books on Teradata (apart from the manuals):

- Tom Coffing's are expensive and mainly a "best of" from the manuals, good for beginners but no advanced stuff

- the books from http://www.cerulium.com/Books.aspx are less expensive, but i never read them

You finally noticed that VARTEXT expects all columns defined as VARCHAR (what they actually are) and there's an automatic typecast to the matching datatype within the target table.

In your case you define 5 fields, but according to the data it's 7 fields and 2679 indicates that 'Smith,Ginny' can't be converted to an INT.

Remove the first and last "|" from the data, because they are separators and before the first and after the last there's no data, so it's a NULL.

Dieter

Supporter

Re: FASTLOAD WITH FIXED WIDTH FILES

Dieter:

+1 (and LOL!)

Adeel:

"we have plan to shift our data from oracle to teradata but it seems to be very difficult becoz there is very fewer help on web."

If you base your choice decissions upon the 'help on the web' instead upon the performance, scalability, etc, well... there you go.

If you find it hard then you can hire a Teradata consultant (which is what I am, btw) instead of reliying on forums and 'good samaritan' advices to solve your (RTFM) problems.

Cheers.

Carlos.

Enthusiast

Re: FASTLOAD WITH FIXED WIDTH FILES

Dieter:

Thanks for help and your proposed solution was correct. and i felt shame why don't check my data source carefully, As you said  "pay for answers you might get faster response" you misunderstand me here. i meant to say that Teradata forum is less active as compare to ORacle OTN. it seems that you are the only expert here to help people and i do respect for your voluntry work. moreover, i have read oracle Sql loader manual plenty of time and there are lots of great books which make life more easy to understand the whole concept. That why i have asked about book. And thanks for your recommanded one.

one more think like to ask which i'm currently explorering is FAST LOAD Timestamp fields. i have data in such format. 10:51:32 only time. when i load it table Fast load like  2012-01-01-10:51:32. how can i load simply 10:51:32

Junior Contributor

Re: FASTLOAD WITH FIXED WIDTH FILES

Maybe the forum is less active than OTN because TD users need less advice :-)

When you load '10:51:32' into a TIMESTAMP column you should get a row in the ET error table indicating an "invalid timestamp". Could you show the actual script?

When it's a time why don't you use a TIME instead of TIMESTAMP. This is a typical Oracle problem, in TD there's DATE, TIME, TIME WITH TIMEZONE, TIMESTAMP, TIMESTAMP WITH TIMEZONE instead of a DATE (which is a timestamp).

Dieter

Enthusiast

Re: FASTLOAD WITH FIXED WIDTH FILES

Dieter,

I have a different scenario here.

I get the same error like Adeel, But for a different reason. My source CSV contains data in below format & hence my fastload is unable to process it.

"February 29, 2012","","","","","15/3/2012","22/3/2012","Packaging :: Departure Point :: LON","","15",1,1,0,0.00

"February 29, 2012","","","","","16/5/2012","20/5/2012","Packaging :: Departure Point :: LON","","77",1,1,0,0.00

"February 29, 2012","","","","","19/4/2012","22/4/2012","Packaging :: Departure Point :: LON","","50",0,1,0,0.00

I feel its cause my source data has some quotes. Can you please help me with this?

Error:

I/O Error on File Read: 35, Text: EOF encountered before

end of record





 

Highest return code encountered = '12'

 

Mallesh



Re: FASTLOAD WITH FIXED WIDTH FILES

Hi,

I am using Fastload to load some data in to Teradata database from Mainframe

I used the script mentioned below:

.SESSIONS 100;

.TENACITY 4;

.SLEEP 10;

.RUN FILE LOGON;            

.SHOW VERSIONS;

.SET RECORD VARTEXT'|';     

 DEFINE                     

 EMPLOYEE_NO (VARCHAR(5)),  

 LAST_NAME (VARCHAR(10)),   

 FIRST_NAME (VARCHAR(10)),  

 SALARY (VARCHAR(5)),       

 DEPTNO (VARCHAR(5))        

 DDNAME=INFILE;             

  BEGIN LOADING PROD_ITC.EMPLOYEE_PROFILE              

  ERRORFILES PROD_ITC.EMP_ERR1, PROD_ITC.EMP_ERR2      

  CHECKPOINT 5;                                        

  INSERT INTO PROD_ITC.EMPLOYEE_PROFILE VALUES         

  (                                                    

  :EMPLOYEE_NO                                         

  ,:LAST_NAME                                          

  ,:FIRST_NAME                                         

  ,:SALARY                                             

  ,:DEPTNO                                             

  );                                                   

 .END LOADING;                                         

 .LOGOFF;                                              

INFILE:           

10001|Patel|Sujay|30000|50001

10003|Pathak|prajyan|60000|50002

10002|Agarwal|Sunil|70000|50003

10005|Dsouza|Ishaan|80000|50004

10004|Singh|Rajesh|90000|50005

But I am getting the below error:

        .SET RECORD VARTEXT;                       

**** 04:05:48 FDL4800 Invalid FastLoad statement 

04:05:49 Number of recs/msg: 756                              

04:05:49 Starting to send to RDBMS with record 1              

04:05:49 Bad file or data definition.                         

04:05:49 The length of: EMPLOYEE_NO in row: 1 was greater than

Please provide me some solution to this.

Supporter

Re: FASTLOAD WITH FIXED WIDTH FILES

"I am using Fastload to load some data in to Teradata database from Mainframe"

RTFM:

"The SET RECORD command:

• Applies only to network-attached systems."

Cheers.

Carlos.

Not applicable

Re: FASTLOAD WITH FIXED WIDTH FILES

I am using Fastload to load some data in to Teradata but it returns 

 No row found for this job in log table SYSADMIN.FASTLOG.

              BEGIN LOADING was successful, but there is no restart info.

              This job cannot be continued or restarted.

I used the script mentioned below:

.sessions 2;

.errlimit 50;

.LOGON dbc/dbc,dbc;

.set record unformatted

DATABASE squid_data;

DEFINE

timestamps (varchar(200)), 

responsetime (varchar(10)), 

IP (varchar(15)), 

tcp_code (varchar(255)),

http_code (varchar(50)), 

bytesreceived (varchar(10)), 

method1 (varchar(10)), 

URL (varchar(1000)), 

username (varchar(150)), 

peerhost (varchar (200)), 

peerstatus (varchar (100)),

contenttype (varchar (50)) 

FILE=access_log.txt;

BEGIN LOADING

squid_data.access

ERRORFILES

Event_ERROR1,Event_ERROR2

CHECKPOINT 10000;

INSERT INTO squid_data.access VALUES

(

:timestamps ,

:responsetime ,

:IP ,

:tcp_code ,

:http_code ,

:bytesreceived ,

:method1 ,

:URL ,

:username ,

:peerhost ,

:peerstatus ,

:contenttype

);

END LOADING;

LOGOFF;

kindly anyone can help? 

Enthusiast

Re: FASTLOAD WITH FIXED WIDTH FILES

I am having a similar problem in loading fixed width records. Data is simpley not loaded with returned copde = '0' Need some help please...

.logmech LDAP;   

.logon 10.61.59.93/796207,XXXXXX; 

drop table DATAMDL_SNDBX.QA_FL_PD;

drop table DATAMDL_SNDBX.ERROR_TABLE_ucv;

drop table DATAMDL_SNDBX.ERROR_TABLE_TV;

CREATE SET TABLE DATAMDL_SNDBX.QA_FL_PD ,NO FALLBACK,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      NAME VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,

      Age integer

     )

PRIMARY INDEX CRO_FLIGHT_LEG_DEP_NUPI (NAME);

SET RECORD unformatted;         

RECORD 2 thru 3;            

DEFINE

NAME (CHAR(4)),

Age (CHAR(3))

FILE = C:\Users\Scarlet\Desktop\FL_Data.TXT;

.BEGIN LOADING DATAMDL_SNDBX.QA_FL_PD ERRORFILES DATAMDL_SNDBX.ERROR_TABLE_UCV, DATAMDL_SNDBX.ERROR_TABLE_TV;

INSERT INTO DATAMDL_SNDBX.QA_FL_PD

       (

         NAME,

         Age 

       )

VALUES (:NAME,

        :Age

       );

.END LOADING;

.LOGOFF;

File content:

NAMEAGE

AAAA322

BBBB221

Error message in Fast Load Utility:

*** 12:24:52 END LOADING COMPLETE

    Total Records Read              =  3

     - skipped by RECORD command    =  1

     - sent to the RDBMS            =  2

    Total Error Table 1             =  2

    Total Error Table 2             =  0  ---- Table has been dropped

    Total Inserts Applied           =  0

    Total Duplicate Rows            =  0

    Start:   Fri Mar 11 12:24:52 2016

    End  :   Fri Mar 11 12:24:52 2016

*** 12:24:52 Application Phase statistics:

             Elapsed time: 00:00:00 (in hh:mm:ss)

013 .LOGOFF;

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

    =                                                                 =

    =          Logoff/Disconnect                                      =

    =                                                                 =

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

*** 12:24:52 Logging off all sessions

*** 12:24:56 Total processor time used = '1.96561 Seconds'

    .        Start : Fri Mar 11 12:24:37 2016

    .        End   : Fri Mar 11 12:24:56 2016

    .        Highest return code encountered = '0'.

*** 12:24:56 FDL4818 FastLoad Terminated


Thanks

Prashant

Teradata Employee

Re: FASTLOAD WITH FIXED WIDTH FILES

Please start a new thread for a new question.

With UNFORMATTED you need to declare any end-of-record termination as a field in your DEFINE (which you would not include in the INSERT).

Or since your fixed fields are all character data, use TEXT instead.