Fast Export and Fast Load Error

Tools & Utilities
Enthusiast

Fast Export and Fast Load Error

Hello All,

Below script i exported the data using Fast Export and tried to import the data using Fast Load. While doing so. I am getting a error in FastLoad. Plz help me understand. Where i am going wrong.

FastExport Script

.LOGTABLE DB.errors;

.LOGON jugal/jbhatt,bhatt;

.BEGIN EXPORT;

.EXPORT OUTFILE sample111_R_T.txt MODE RECORD FORMAT TEXT;

Sel top 10 cast(EmpId as char(15)),cast(DeptId as char(15)) from DB.Employees;

.END EXPORT;

.LOGOFF;

Logs:

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

     =                                                                      =

     =          FastExport Utility    Release FEXP.14.00.00.05              =

     =          Platform LINUX                                              =

     =                                                                      =

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

     =                                                                      =

     =     Copyright 1990-2012 Teradata Corporation. ALL RIGHTS RESERVED.   =

     =                                                                      =

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

**** 09:58:09 UTY2411 Processing start date: WED APR 02, 2014

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

     =                                                                      =

     =          Logon/Connection                                            =

     =                                                                      =

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

0001 .LOGTABLE UI_RESULTS_DB.errors;

0002 .LOGON jugal/jbhatt,;

**** 09:58:09 UTY8400 Teradata Database Release: 14.00.05.02

**** 09:58:09 UTY8400 Teradata Database Version: 14.00.05.03

**** 09:58:09 UTY8400 Default character set: ASCII

**** 09:58:09 UTY8400 Current RDBMS has UDT support

**** 09:58:09 UTY8400 Current RDBMS has Large Decimal support

**** 09:58:09 UTY8400 Current RDBMS has FEXP w/o Spooling support

**** 09:58:09 UTY8400 Current RDBMS has TASM support

**** 09:58:09 UTY8400 Maximum supported buffer size: 1M

**** 09:58:09 UTY8400 Data Encryption supported by RDBMS server

**** 09:58:09 UTY6211 A successful connect was made to the RDBMS.

**** 09:58:09 UTY6217 Logtable 'DB.errors' has been created.

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

     =                                                                      =

     =          Processing Control Statements                               =

     =                                                                      =

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

0003 .BEGIN EXPORT;

0004 .EXPORT OUTFILE sample111_R_T.txt MODE RECORD FORMAT TEXT;

0005 Sel top 10 cast(EmpId as char(15)),cast(DeptId as char(15))

     from DB.Employees;

0006 .END EXPORT;

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

     =                                                                      =

     =          FastExport Initial Phase                                    =

     =                                                                      =

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

**** 09:58:09 UTY8700 Options in effect for this FastExport task:

     .       Sessions:    4 session(s).

     .       Mode:        RECORD

     .       Blocksize:   64330 bytes.

     .       Outlimit:    No limit in effect.

**** 09:58:09 UTY8715 FastExport is submitting the following request:

     Select NULL from DB.errors where (LogType = 220) and (Seq = 1)

     and (FExptSeq = 0);

**** 09:58:09 UTY8715 FastExport is submitting the following request:

     SET QUERY_BAND='UTILITYNAME=FASTEXP;' UPDATE FOR SESSION;

**** 09:58:09 UTY8715 FastExport is submitting the following request:

     CHECK WORKLOAD FOR BT;BEGIN FASTEXPORT;

**** 09:58:09 UTY8715 FastExport is submitting the following request:

     CHECK WORKLOAD FOR Sel top 10 cast(EmpId as

     char(15)),cast(DeptId as char(15)) from

     DB.Employees;

**** 09:58:09 UTY8715 FastExport is submitting the following request:

     CHECK WORKLOAD END;

**** 09:58:09 UTY0844 Session count 16 returned by the DBS overrides

     user-requested session count.

**** 09:58:13 UTY8705 EXPORT session(s) requested: 16.

**** 09:58:13 UTY8706 EXPORT session(s) connected: 16.

**** 09:58:13 UTY8715 FastExport is submitting the following request:

     BT;BEGIN FASTEXPORT;

**** 09:58:13 UTY8715 FastExport is submitting the following request:

     SELECT MiscInt1 (INTEGER), MiscInt2 (INTEGER), MiscInt3 (INTEGER),FExptSeq

     (INTEGER), FExptCkpt (VARBYTE(1024)) from DB.errors WHERE

     (LogType = 210) and (Seq = 1) and (FExptSeq IN (SELECT MAX(FExptSeq) from

     DB.errors where (LogType = 210) and (Seq = 1)));

**** 09:58:13 UTY8715 FastExport is submitting the following request:

     SELECT MiscInt1 (INTEGER), MiscInt2 (INTEGER),  MiscInt3 (INTEGER),FExptSeq

     (INTEGER), FExptCkpt (VARBYTE(1024)) from DB.errors WHERE

     (LogType = 212) and (Seq = 1) and (FExptSeq IN (SELECT MAX(FExptSeq) from

     DB.errors where (LogType = 212) and (Seq = 1)));

**** 09:58:13 UTY8715 FastExport is submitting the following request:

     Sel top 10 cast(EmpId as char(15)),cast(DeptId as

     char(15)) from DB.Employees;

**** 09:58:13 UTY8724 Select request submitted to the RDBMS.

**** 09:58:14 UTY8725 Select execution completed. 1 data blocks generated.

**** 09:58:14 UTY8756 Retrieval Rows statistics:

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

              CPU time:       0 Seconds

              MB/sec:         N/A

              MB/cpusec:      N/A

**** 09:58:14 UTY8715 FastExport is submitting the following request:

     INS DB.errors (LogType, Seq) VALUES (220, 1)

**** 09:58:14 UTY8715 FastExport is submitting the following request:

     END FASTEXPORT;ET;

**** 09:58:16 UTY8710 Processing complete for this FastExport task.

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

     =                                                                      =

     =          FastExport Task Complete                                    =

     =                                                                      =

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

**** 09:58:17 UTY1024 Session modal request, 'SET

     QUERY_BAND='UTILITYNAME=FASTEXP;' UPDATE FOR SESSION;', re-executed.

**** 09:58:17 UTY8722 10 total records written to output file.

0007 .LOGOFF;

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

     =                                                                      =

     =          Logoff/Disconnect                                           =

     =                                                                      =

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

**** 09:58:17 UTY6216 The restart log table has been dropped.

**** 09:58:17 UTY6212 A successful disconnect was made from the RDBMS.

**** 09:58:17 UTY2410 Total processor time used = '1.87 Seconds'

     .       Start : 09:58:09 - WED APR 02, 2014

     .       End   : 09:58:17 - WED APR 02, 2014

     .       Highest return code encountered = '0'.

Fast Export OUTPUT:

144 981

144 981

144 981

144 981

144 981

144 981

144 981

144 981

144 981

144 981

FastLoad

 .LOGON jugal/jbhatt,jugal;

drop table DB.Employees_History;

CREATE MULTISET TABLE DB.Employees_History ,FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

EmpId BIGINT NOT NULL,

DeptId INTEGER NOT NULL)

PRIMARY INDEX ( EmpId );

DROP TABLE DB.FastERR1;

DROP TABLE DB.FastERR2;

BEGIN LOADING DB.Employees_History

ERRORFILES DB.FastERR1,

    DB.FastERR2;

SET RECORD TEXT;

DEFINE EmpId (VARCHAR(20)),

       DeptId (VARCHAR(20))

FILE=/home/jbhatt/sample111_R_T.txt;

insert into DB.Employees_History values(:EmpId,:DeptId);

END LOADING;

LOGOFF;

Logs:

 fastload < FLoad_F.txt

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

     =                                                                 =

     =          FASTLOAD UTILITY     VERSION 14.00.00.07               =

     =          PLATFORM LINUX                                         =

     =                                                                 =

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

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

     =                                                                 =

     =          Copyright 1984-2012, Teradata Corporation.             =

     =          ALL RIGHTS RESERVED.                                   =

     =                                                                 =

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

**** 09:55:50 Processing starting at: Wed Apr  2 09:55:50 2014

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

     =                                                                 =

     =          Logon/Connection                                       =

     =                                                                 =

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

0001 .LOGON jugal/jbhatt,

**** 09:55:50 Teradata Database Release: 14.00.05.02

**** 09:55:50 Teradata Database Version: 14.00.05.03

**** 09:55:50 Number of AMPs available: 96

**** 09:55:50 Current CLI or RDBMS allows maximum row size: 64K

**** 09:55:50 Character set for this job: ASCII

0002 drop table DB.Employees;

**** 09:55:51 Command completed successfully

0003 CREATE MULTISET TABLE Employees_History ,FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

     EmpId BIGINT NOT NULL,

     Deptid INTEGER NOT NULL)

     PRIMARY INDEX ( EmpId );

**** 09:55:51 Command completed successfully

0004 DROP TABLE DB.FastERR1;

**** 09:55:51 Command completed successfully

0005 DROP TABLE DB.FastERR2;

**** 09:55:51 Command completed successfully

0006 BEGIN LOADING Employees_History

     ERRORFILES DB.FastERR1,

           DB.FastERR2;

**** 09:55:51 Session count 16 returned by the DBS overrides

              user-requested session count

**** 09:55:54 Number of FastLoad sessions connected = 16

**** 09:55:54 FDL4808 LOGON successful

**** 09:55:55 Number of AMPs available: 96

**** 09:55:55 BEGIN LOADING COMPLETE

0007 SET RECORD TEXT;

**** 09:55:55 Now set to read 'TEXT' records

**** 09:55:55 Command completed successfully

0008 DEFINE EmpId (VARCHAR(20)),

            DeptId (VARCHAR(20))

     FILE=/home/jbhatt/sample111_R_T.txt;

**** 09:55:55 FDL4803 DEFINE statement processed

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

     =                                                                 =

     =          Insert Phase                                           =

     =                                                                 =

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

0009 insert into Employees_History values(:EmpId,:DeptId);

**** 09:55:55 Number of recs/msg: 1428

**** 09:55:55 Starting to send to RDBMS with record 1

**** 09:55:55 Bad file or data definition.

**** 09:55:55 The length of: EmpId in row: 1 was greater

              than defined.

              Defined: 20, Received: 14135

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

     =                                                                 =

     =          Logoff/Disconnect                                      =

     =                                                                 =

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

**** 09:55:55 Logging off all sessions

**** 09:55:56 Total processor time used = '1.75 Seconds'

     .        Start : Wed Apr  2 09:55:50 2014

     .        End   : Wed Apr  2 09:55:56 2014

     .        Highest return code encountered = '12'.

**** 09:55:56 FastLoad Paused

Tags (2)
5 REPLIES
Enthusiast

Re: Fast Export and Fast Load Error

Please create the table within your fastload script with Varchar columns and then try loading, else you can try using TPT Load operator.

Thanking You

Santanu

Enthusiast

Re: Fast Export and Fast Load Error

Hi Santanu,

I have tried the sccript with TPT. i got a result.

The above script ran when i changed the Empid and DeptId to Char(10).

Because when we use the SET RECORD TEXT. we sud define a fixed length of char.

Teradata Employee

Re: Fast Export and Fast Load Error

When dealing with text data, you need to have FastLoad written to read the data in the exact same format as you have FastExport exporting the data.

In the FastExport script, you define the output as FORMAT TEXT. This format will create fixed-width columns.

You then CAST both fields to CHAR(15).

In the FastLoad script, you define your schema as 2 columns, each being VARCHAR(20).

Even though you did SET RECORD TEXT, that format is expecting fixed-width column data.

VARCHAR is a format that expects each field to be preceded by a 2-byte field length identifier.

Your data will not have that. Textual data cannot have a 2-byte (binary) field length identifier.

Thus, you need to have your FastLoad script define the input layout (through the DEFINE statement) as a series of fixed length CHAR fields.

And they should be CHAR(15), since that is how you asked FastExport to write out the data to the file.

Of course, you can always use TPT with an Export operator and a Load operator to move the data from one Teradata table to another without having to write out the data to a flat file.

-- SteveF
Enthusiast

Re: Fast Export and Fast Load Error

Thanks feinholz.

Enthusiast

Re: Fast Export and Fast Load Error

That is right Jugalkis, it should be fixed length data type. I had SET RECORD VARTEXT in my mind while typing my response. My bad. :-)

Thanking You

Santanu