FEXP,FLOAD: problems

Database
Enthusiast

FEXP,FLOAD: problems

hi to all,

how you can understand i'm new in Teradata, i have visited the forum answers but i don't understand my error. 

i did a simple try that is to make a fast export and a fast load with MODE RECORD and VARTEXT.

-FEXP:

.route messages to file file\LAB2.log with echo off;

.logtable financial.LAB2_log;

.dateform ANSIDATE;

logon 127.0.0.1/dbc,dbc;

database financial;

.begin export;

SELECT 

     trim(ID_CLNT)||','||trim(ID_ENT_SGNLN)||','||trim(CD_SPRTL_BNL_PRNCPL)||','

     from financial.LAB2;

   .export OUTFILE file\LAB2.txt FORMAT FASTLOAD MODE RECORD;

   .end export;

.logoff;

-FLOAD:

SESSIONS 4;

DATEFORM ANSIDATE;

.logon 127.0.0.1/dbc,dbc;

DATABASE Dan;

DROP TABLE Dan.LAB2_ER1;

DROP TABLE Dan.LAB2_ER2;

BEGIN LOADING Dan.LAB2

            ERRORFILES Dan.LAB2_ER1,

                 Dan.LAB2_ER2 ;

.SET RECORD VARTEXT "," NOSTOP DISPLAY_ERRORS;                

DEFINE

ID_CLNT(varchar(20)),

ID_ENT_SGNLN(varchar(20)),

CD_SPRTL_BNL_PRNCPL(varchar(20))

FILE = file\LAB2.txt;

INSERT INTO Dan.LAB2

values (

:ID_CLNT,

:ID_ENT_SGNLN,

:CD_SPRTL_BNL_PRNCPL

 )

;

END LOADING;

.LOGOFF;

-At the end when i start fast load the return message is:

0007 BEGIN LOADING Dan.LAB2

                 ERRORFILES Dan.LAB2_ER1,

                      Dan.LAB2_ER2;

**** 12:17:32 Number of AMPs available: 2

**** 12:17:32 BEGIN LOADING COMPLETE

0008 .SET RECORD VARTEXT "," NOSTOP DISPLAY_ERRORS;

**** 12:17:32 Now set to read 'Variable-Length Text' records

**** 12:17:32 Delimiter character(s) is set to ','

**** 12:17:32 Rejected rows will be sent to STDERR

**** 12:17:32 FastLoad will continue if a row is rejected

**** 12:17:32 Command completed successfully

0009 DEFINE

     ID_CLNT(varchar(20)),

     ID_ENT_SGNLN(varchar(20)),

     CD_SPRTL_BNL_PRNCPL(varchar(20))

     FILE = file\LAB2.txt;

**** 12:17:32 FDL4803 DEFINE statement processed

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

     =                                                                 =

     =          Insert Phase                                           =

     =                                                                 =

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

0010 INSERT INTO Dan.LAB2

     values (

     :ID_CLNT,

     :ID_ENT_SGNLN,

     :CD_SPRTL_BNL_PRNCPL

      )

     ;

**** 12:17:32 Number of recs/msg: 988

**** 12:17:32 Starting to send to RDBMS with record 1

**** 12:17:32 Not enough fields in vartext data record number: 1

**** 12:17:32 Not enough fields in vartext data record number: 2

**** 12:17:32 Not enough fields in vartext data record number: 3

**** 12:17:32 Finished sending rows to the RDBMS

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

     =                                                                 =

     =          End Loading Phase                                      =

     =                                                                 =

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

0011 END LOADING;

**** 12:17:32 END LOADING COMPLETE

     Total Records Read              =  3

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

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

     Total Inserts Applied           =  0

     Total Duplicate Rows            =  0

     Total Rejected Rows             =  3

     Start:   Fri Nov 25 12:17:32 2011

     End  :   Fri Nov 25 12:17:32 2011

0012 .LOGOFF;

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

     =                                                                 =

     =          Logoff/Disconnect                                      =

     =                                                                 =

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

**** 12:17:32 Logging off all sessions

Please someone can help me to make me understand where i wrong?

TNX a lot.

14 REPLIES
Enthusiast

Re: FEXP,FLOAD: problems

sorry, i omitted that i start it on the Teradata Express 13, windows version.

Senior Supporter

Re: FEXP,FLOAD: problems

I didn't check the scripts here.

Question is what do you want to achive?

Do you really want to export CSV files? You might need to take care of null handling as well etc.

On the other hand Fastexport can generate Multi Load scripts during the export session - check the manuals.

Enthusiast

Re: FEXP,FLOAD: problems

hi ulrich,

i have ceated a simple tables:

CREATE SET TABLE Dan.LAB2 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      ID_CLNT VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Identificatore Cliente' NOT NULL,

      ID_ENT_SGNLN VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Identificatore Ente Segnalante' NOT NULL,

      CD_SPRTL_BNL_PRNCPL VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Codice Sportello BNL Principale')

UNIQUE PRIMARY INDEX LAB_CLNT_PRK ( ID_CLNT ,ID_ENT_SGNLN ,CD_SPRTL_BNL_PRNCPL );

and i have inserted 3 rows in the first table like these: 'aaa','bbb','123', 'aaa','bbb','456', 'aaa','bbb','789'

both the tables are the same but when i try to run the script, fexp is ok but fload  fails, like reported.

i want to know why, where i wrong.

Enthusiast

Re: FEXP,FLOAD: problems

the exported file:

 123,456,aaa,

 123,456,ccc,

 123,456,bbb,

i tried to delete the strange symbol at the begin of the rows and the fload is ok!

for 3 rows i can delete it but not for 1 milion!

i want to know why there are these strange symbol.

some one can say me if there is something wrong in the fexp?

Senior Supporter

Re: FEXP,FLOAD: problems

The problem seems to be the record mode and your attempt to create a CSV file.

If you use fastload mode you should not concat the fields to a csv string. But in this case you need to change your fastload record mode as well.

Enthusiast

Re: FEXP,FLOAD: problems

i tried to change in the fexp the FORMAT VARCHAR in FORMAT TEXT and the exported file is now :

 123,456,aaa,

  123,456,ccc,

 123,456,bbb,

the strange symbol there is no more but now ther is a like blank char that make to fail the script.

i tried all the other FORMATs: BINARY, VARCHAR,FASTLOAD but no one is ok.

is always the same, if i delete the blank char is ok. 

Senior Supporter

Re: FEXP,FLOAD: problems

Hi, 

I did not run this but you need to harmonize the extract and load

for the extract change to


SELECT 

     ID_CLNT, ID_ENT_SGNLN,CD_SPRTL_BNL_PRNCPL


     from financial.LAB2;


   .export OUTFILE file\LAB2.txt FORMAT FASTLOAD MODE RECORD;

   .end export;

.logoff;


the load need to be changed to 


.SET RECORD FORMATED;


in the define statement change to the datatypes of the source table


DEFINE


ID_CLNT (xxx),

ID_ENT_SGNLN (yyy),

CD_SPRTL_BNL_PRNCPL (zzz)


Ulrich      

Enthusiast

Re: FEXP,FLOAD: problems

i know normally it works. i want to use the separator ",".

TNX

Enthusiast

Re: FEXP,FLOAD: problems

Hi,

Please modify your FEXP script as below:

.route messages to file file\LAB2.log with echo off;

.logtable financial.LAB2_log;

.dateform ANSIDATE;

.logon 127.0.0.1/dbc,dbc;

database financial;

.begin export;

SELECT 

CAST

(

trim(ID_CLNT)||','||

trim(ID_ENT_SGNLN) ||','||

trim(CD_SPRTL_BNL_PRNCPL)||',' AS CHAR(63)

)

     from financial.LAB2;

   .export OUTFILE file\LAB2.txt FORMAT TEXT MODE RECORD;

   .end export;

.logoff;

It should work.. :-)