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)
19 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

Enthusiast

Re: Fast Export and Fast Load Error

Dear feinholz,

"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."

This is exactly what I am attempting to do but continue to recieve errors.  The latest error is this same Fastload error in this thread.  Do you have a clear example where data is exported and loaded in the same job without all the referencing of different files as is the case in the samples offered in the /opt/teradata/client/14.10/tbuilld path?

My export/load TPT is exporting the data but attempting to load it into the userid that is running the TPT and not the database I have specified in the INSERT statement.

Any assistance would be GREATLY appreciated.

sincerely,

Mike

Highlighted
Teradata Employee

Re: Fast Export and Fast Load Error

The bottom line is, "what are you trying to do?"

 

If all you are trying to do is move data from one table to another, you do not have to go through all of the hoops of using FastExport, and CAST-ing your data to CHAR, and then have FastLoad read the data in, etc., etc., etc.

 

What is the layout of the source table?

Do you want your target table to have the same layout, or a different layout?

Do you NEED the data to be written to a file for some other reason (other than to have it read in to load into a table)?

 

You said you tried TPT but are getting the same error?

If so, I would like to see your TPT script.

 

A TPT script can be as simple as this:

 

DEFINE JOB <name>

(

   APPLY $INSERT TO OPERATOR ($LOAD)

   SELECT * FROM OPERATOR ($EXPORT);

);

 

But it is best to have all of the information as to what you are trying to do before I put together a TPT script for you.

 

-- SteveF
Enthusiast

Re: Fast Export and Fast Load Error

Thanks for your response Steve.  I had a feeling I'm doing way more than is necessary to accomplish what I want to do. When you say a TPT script can be as simple as this, I'm just not clear where the $variables are being referenced.

DEFINE JOB <name>

(

   APPLY $INSERT TO OPERATOR ($LOAD)

   SELECT * FROM OPERATOR ($EXPORT);

);

 

Below is the TPT script that I finally got to run.  I'm sure it's more than is necessary.  I resolved the issue of the script attempting to load a table in the users space itself instead of the DBA_AP database that I wanted, by not only qualifying the target table but also fully qualifying the LOG, ET, UV tables as well.

 

What I'm attempting to do here is just load a driver table of tkt ids by reading them for a table in Teradata and loading an empty table in the same system.  This driver table will be used by many other TPT jobs that archive data from the tables based on these tkt ids for that date range.  

DEFINE JOB TKT_ARCHIVE_DRIVER_LOAD
DESCRIPTION 'Loads the Driver table used by the archive/purge of TKT tables.'
(
DEFINE SCHEMA TKT_ARCHIVE_DRIVER
(
TKT_ID BIGINT
);

DEFINE OPERATOR ex_TKT_ID
TYPE EXPORT
SCHEMA TKT_ARCHIVE_DRIVER

ATTRIBUTES
(
UserName = 'tptarchive',
UserPassword = 'xxxxxxxx',
TdpId = '192.999.999.130'
,MaxSessions= 6
,MinSessions= 2
,SpoolMode='NoSpool'
,PrivateLogName = 'tpt_test_log'
,SelectStmt = '
LOCKING DW_DATA.TKT FOR ACCESS MODE
SELECT TKT_ID
FROM DW_DATA.TKT
WHERE DT_ISSUE between ''2017-09-01'' and ''2017-09-30'' ;'
);

DEFINE OPERATOR ld_TKT_ARCHIVE_DRIVER
TYPE LOAD
SCHEMA *

ATTRIBUTES
(
VARCHAR PrivateLogName = 'load_log',
VARCHAR TdpId = '192.999.999.130',
VARCHAR UserName = 'tptarchive',

VARCHAR UserPassword = 'xxxxxxxx',
VARCHAR LogTable = 'DBA_AP.TKT_ARC_DRVR_LG',
VARCHAR ErrorTable1 = 'DBA_AP.TKT_ARC_DRVR_ET',
VARCHAR ErrorTable2 = 'DBA_AP.TKT_ARC_DRVR_UV',
VARCHAR TargetTable = 'DBA_AP.TKT_ARCHIVE_DRIVER'
);


APPLY
('INSERT INTO DBA_AP.TKT_ARCHIVE_DRIVER
(TKT_ID)
VALUES
(:TKT_ID);
')
TO OPERATOR (ld_TKT_ARCHIVE_DRIVER)
SELECT * FROM OPERATOR(ex_TKT_ID);
);

 

 

Thanks for your response and your help.

-Mike

Teradata Employee

Re: Fast Export and Fast Load Error

Your script looks fine. Where we have been heading for several years now is the notion of using operator "templates".

By using templates, you do not have to specify the DEFINE OPERATOR syntax in your script, and TPT can generate the schema for you.

 

In your case, you could have a script like this:

 

DEFINE JOB TKT_ARCHIVE_DRIVER_LOAD
DESCRIPTION 'Loads the Driver table used by the archive/purge of TKT tables.'
(

   APPLY
   ('INSERT INTO ' || @LoadTargetTable || '(TKT_ID)
     VALUES(:TKT_ID);')
   TO OPERATOR ($LOAD)
   SELECT * FROM OPERATOR($EXPORT);
);

 

And then you create a job variable file that contains the metadata:

 

ExportUserName = 'tptarchive'
ExportUserPassword = 'xxxxxxxx'
ExportTdpId = '192.999.999.130'
Export
MaxSessions = 6
ExportMinSessions = 2
ExportSpoolMode = 'NoSpool'
ExportPrivateLogName = 'tpt_test_log'
ExportSelectStmt = '
LOCKING DW_DATA.TKT FOR ACCESS MODE
SELECT TKT_ID
FROM DW_DATA.TKT
WHERE DT_ISSUE between ''2017-09-01'' and ''2017-09-30'' ;'

LoadPrivateLogName = 'load_log',
LoadTdpId = '192.999.999.130',
LoadUserName = 'tptarchive',

LoadUserPassword = 'xxxxxxxx',
LoadLogTable = 'DBA_AP.TKT_ARC_DRVR_LG',
LoadErrorTable1 = 'DBA_AP.TKT_ARC_DRVR_ET',
LoadErrorTable2 = 'DBA_AP.TKT_ARC_DRVR_UV',
LoadTargetTable = 'DBA_AP.TKT_ARCHIVE_DRIVER'

 

And then you run TPT by doing this:

 

$ tbuild -f <script-name> -v <job-variable-file-name>

 

The point is, we would like for users to get out of the habit of hardcoding information in their scripts.

The $LOAD and $EXPORT syntax tells TPT to go out to the template files and bring in the operator definitions at runtime.

This is helpful if you have a lot of scripts and we introduce a new feature (which usually introduces a new attribute) you would like to take advantage of, you do not have to go back and edit your scripts; you just change the job variable file or add the new feature attribute on the command line.

 

And this syntax also tells TPT to go to the Export operator job variables in the job variable file and find the SELECT statement and use it to generate the schema for you. May not be a big deal in this specific case because you only had one column, but if the schema had a hundred columns, it saves the user from having to manually put it into the script.

 

Except for the INSERT statement, this script is now more dynamic.

The job variables for the job variable file are comprised of the attribute name preceded by the type of operator.

The template files can be seen in the "templates" directory where TPT is installed.

 

Hope this helps.

 

-- SteveF