Handle records in Mload ET table in TEXT mode - A column or character expression is larger than the max size

Tools
Enthusiast

Handle records in Mload ET table in TEXT mode - A column or character expression is larger than the max size

Greetings experts,

I have fastexported a table in text mode and loaded the data in to target table using Mload in text mode (I am using 13.00 Demo version on windows 7).  

source/target table structure:

      L_ORDERKEY INTEGER,

      L_PARTKEY INTEGER,

      L_QUANTITY DECIMAL(15,2),

      L_LINESTATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC

PRIMARY INDEX ( L_ORDERKEY )

Now I have manually edited some records in the fastexported file to have numeric overflow thereby some records end up in ET table.  I was trying to handle the records in ET table.

Following is the BTEQ script that I tried to use to export the records in REPORT mode from ET table which is failing.

.logon localtd/tduser,tduser;
.set format on;

.export report file="G:\Users\cheeli\Desktop\bteq_op\et_itemppi_text.txt";

select hostdata from samples.et_itemppi_wodate;

.export reset;

Error message is:

select hostdata from samples.et_itemppi_wodate;

$

 *** Failure 3798 A column or character expression is larger than the max size.

                Statement# 1, Info =0 

 *** Total elapsed time was 1 second.

However, when I tried the same with fast export it worked and I successfully loaded the records into the target table.

Fastexport script:

.logtable LT_itemppi_;
.logon localtd/tduser,tduser;
.begin export sessions 12;
.export outfile "G:\Users\cheeli\Desktop\fexp_out\et_fexp_itemppi_text.txt" format text mode record;
select hostdata from samples.et_itemppi_wodate;
.end export;

Mload script:

.LOGTABLE SAMPLES.ML_ITEMPPI_wodate;
.logon localtd/tduser,tduser;

.begin import mload tables samples.itemppi_wodate
checkpoint 70
errlimit 3;

.LAYOUT DATA_LAYOUT;
.filler abc * char(2);
.field L_ORDERKEY * char(12);
.filler l_partkey_filler * char(7);
.field L_PARTKEY * char(5);
.field L_QUANTITY * char(20);
.field L_LINESTATUS * CHAR(2);

.dml label insert_itemppi;
insert into samples.itemppi_wodate values (:L_ORDERKEY, :L_PARTKEY, :L_QUANTITY, :L_LINESTATUS);

.import infile "G:\Users\cheeli\Desktop\fexp_out\et_fexp_itemppi_text.txt"
format text
layout data_layout
apply insert_itemppi;
.end mload;

.logoff;

Can you please let me know how to export the records from BTEQ!

I have tried to cast the hostdata to char(1000) and it has failed as cast is not allowed on VARBYTE.

2 REPLIES
Teradata Employee

Re: Handle records in Mload ET table in TEXT mode - A column or character expression is larger than the max size

Try this:

.logon localtd/tduser,tduser;
.export data file="G:\Users\cheeli\Desktop\bteq_op\ et_itemppi_text.txt";
select hostdata from samples.et_itemppi_wodate;
.export reset;
Enthusiast

Re: Handle records in Mload ET table in TEXT mode - A column or character expression is larger than the max size

Hi Sudhansu,

That worked, thank you.  I was expecting the data to be in unreadable format due to DATA mode.  But I could see that the content is readable.  Any thoughts on this?  

Also, why did the above one fail with REPORT mode.  Can you please elucidate on this.