Fastload not handling VARCHAR field in Linux?

Tools & Utilities

Fastload not handling VARCHAR field in Linux?

I'm having problems running Fastload from a Linux client where there's a variable length field present.

I want to copy a selection of data from the production server to the test one, I've tried running the Export using both FASTLOAD & TEXT formats, and then a Fastload using both FORMATTED & TEXT formats but it objects to the variable data every time.

The error message given is -

**** 16:22:07 Bad file or data definition.
**** 16:22:07 The length of: COLUMN_A in row: 1 was greater than
defined.
Defined: 250, Received: 12336

The field is defined as VARCHAR(250) in both table definition & Fastload DEFINE statement but actually contains 14 characters in this example. I can see from a file dump that there is a valid field length identifier present -

016 000 074 112 165 154 060 071 076 060 060 060 060 061 055 040
016 \0 < J u l 0 9 > 0 0 0 0 1 -

Could it be that the program is expecting a different byte ordering? But then all our integer fields would not load properly...

Anyone else with experience of this? (Good or bad..)
11 REPLIES
Teradata Employee

Re: Fastload not handling VARCHAR field in Linux?

Where is the data coming from?
In what format is the data?
If in FastLoad format, then the record must contain the proper format (2-byte row length 'n', followed by 'n' bytes of data, followed by end-of-record marker).
In FastLoad format, all VARCHAR fields must have a 2-byte length specifier preceding the actual field data.
Might it be possible that the 2-byte field-length indicator is missing?
FastLoad will see that there is a VARCHAR, and will read the first 2 bytes of the field, expecting it to be the field length, and if it is instead the data, then the interpretation will be incorrect.

Can you show me the actual DEFINE statement and a sample of one record?
-- SteveF

Re: Fastload not handling VARCHAR field in Linux?

The data comes from a Fast Export from the production table.

In fact I have been able to get it to work by listing a subset of the columns, but it's annoying as the whole point is to avoid having to make a big list & change the field descriptors for each clause..

What I want to do is run a Fast Export of the form-

.EXPORT OUTFILE EXPORT_DATA_FILE
FORMAT FASTLOAD
MODE RECORD
;

select * from TableA {where...};

.END EXPORT

Then reload this on the Dev box by just doing-
SET RECORD FORMATTED;
DEFINE FILE={file}
BEGIN LOADING TableA;
INSERT TableA.*;
END LOADING;

In fact it has worked ok for one table but consistently fails for another one.. I can get the failing one to work if I list a subset of the columns or unload the VARCHAR column as fixed length but it's frustrating to have to fiddle about like this!
Teradata Employee

Re: Fastload not handling VARCHAR field in Linux?

Can you FastExport a couple of rows and send me the data (for the one that consistently fails), and a copy of the table schema for both tables?

-- SteveF
Junior Contributor

Re: Fastload not handling VARCHAR field in Linux?

Where do you spot that field length in the above file dump?
Could you post the actual table definition and the FastLoad output (especially the DEFINE)?
And, as Steven already asked for, a sample record?

Btw, unless there are only NOT NULL columns i'd use indicator bits...

Dieter
Teradata Employee

Re: Fastload not handling VARCHAR field in Linux?

The partial dump above shows a 2-byte length of 14 (16 octal), but I am not sure if that snippet is the record, or just the field.

So, we need more info.

It would probably help as well to see the FastExport script, just to make sure there is nothing strange going on there either.

So, to fully diagnose this, I need to see:

1. schema of source and target tables
2. FastExport and FastLoad scripts
3. sample data (at least 2 rows)

Thanks!
-- SteveF
Teradata Employee

Re: Fastload not handling VARCHAR field in Linux?

I also would like to see info that Steve request.ed

In the meantime, check your .EXPORT statement in your FastExport script and your SET and BEGIN LOADING statement in your FastLoad script. By default, FASTLOAD format are used for FastLoad and FastExport.

1) If you specifiy MODE RECORD in FastExport script such as:
.EXPORT OUTFILE TESTDAT01 FORMAT FASTLOAD MODE RECORD;
You MUST NOT specify INDICATORS in BEGIN LOADING in your fastload script. For example:
BEGIN LOADING TEST2 ERRORFILES e1, e2;

1) If you specifiy MODE INDICATOR in FastExport script such as:
.EXPORT OUTFILE TESTDAT01 FORMAT FASTLOAD MODE INDICATOR;
You MUST specify INDICATORS in BEGIN LOADING in your FastLoad script as:
BEGIN LOADING TEST2 ERRORFILES e1, e2 INDICATORS;

Thanks
-Thomas

Re: Fastload not handling VARCHAR field in Linux?

Thanks for your replies..

Some more info -

Source table DDL -
CREATE SET TABLE LDB_RAS02.RAS_CRS_SUMMARY ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
TRAVEL_YEAR_NO INTEGER COMPRESS (2004 ,2005 ,2006 ,2007 ,2008 ,2009 ,2010 ),
TRAVEL_MONTH_NO INTEGER COMPRESS (1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ),
TRAVEL_MONTH_START_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL,
DOMINANT_MARKETING_AIRLINE_CD CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('9W','AA','AB','AC','AF','AI','AR','AT','AY','AZ','BA','BD','BR','CA','CI','CO','CX','DL','EI','EK','ET','EY','GF','IB','JJ','JK','JL','KE','KL','KQ','KU','LA','LH','LO','LX','LY','MA','MH','MS','MU','NW','NZ','OA','OK','OS','OZ','QF','QR','RG','RJ','SA','SK','SN','SQ','SU','SV','TG','TK','TP','UA','US','UX','VS'),
DOMINANT_OPERATING_AIRLINE_CD CHAR(2) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ('9W','AA','AB','AC','AF','AI','AR','AT','AY','AZ','BA','BD','BE','BR','CA','CI','CL','CO','CX','DL','EI','EK','EW','EY','GF','IB','JJ','JK','JL','KE','KL','KQ','LA','LH','LO','LX','LY','MA','MH','MS','MU','NW','NZ','OA','OK','OS','QF','QR','RG','SA','SK','SN','SQ','SU','SV','TG','TK','TP','UA','US','UX','VO','VS'),
SALE_COUNTRY_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('AE ','AR ','AT ','AU ','BE ','BG ','BH ','BR ','CA ','CH ','CL ','CN ','CO ','CY ','CZ ','DE ','DK ','EG ','ES ','FI ','FR ','GB ','GR ','HK ','HU ','ID ','IE ','IL ','IN ','IR ','IT ','JO ','JP ','KE ','KR ','KW ','LB ','MA ','MX ','MY ','NG ','NL ','NO ','NZ ','PE ','PH ','PK ','PL ','PT ','QA ','RO ','RU ','SA ','SE ','SG ','TH ','TR ','TW ','UA ','US ','VE ','ZA ','ZZ '),
AGENT_LOCATION_CD CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
DOMINANT_BOOKED_CABIN_CD CHAR(2) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ('C ','F ','M '),
RAS_ORIGIN_STN_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('AKL','AMS','ARN','ATH','ATL','BCN','BKK','BNE','BOM','BOS','BRU','BUD','CAI','CDG','CPH','CPT','DEL','DFW','DUS','DXB','EWR','EZE','FCO','FRA','GRU','GVA','HAM','HEL','HKG','IAD','ICN','IST','JFK','JNB','LAX','LGW','LHR','LIN','LIS','MAD','MAN','MEL','MIA','MUC','MXP','NRT','ORD','ORY','OSL','PEK','PVG','SFO','SIN','STR','SVO','SYD','TLV','TPE','TXL','VIE','YVR','YYZ','ZRH'),
ORG_STN_BA_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),
ORG_CITY_BA_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),
RAS_DESTINATION_STN_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
DEST_STN_BA_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),
DEST_CITY_BA_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),
UK_FLAG_IND BYTEINT COMPRESS (0 ,1 ,2 ),
BA_VIA_STN_CD CHAR(3) CHARACTER SET UNICODE NOT CASESPECIFIC,
CHAIN_CD CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('AU01014','AU01017','AU42305','CN90001','DE30001','DE30002','DE30004','DE42308','DE42401','DE79999','ES00003','ES00009','ES00020','ES00025','FR01300','FR02011','FR42101','FR42301','GB42102','GB42301','IN70076','IN70120','IT00099','SE69999','SG88888','US02530','US02531','US02542','US02544','US05520','US06985'),
CHAIN_SUB_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS (' ','A','B'),
RAS_AGENT_CUST_ID INTEGER COMPRESS ,
PAX_QTY INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,6 ),
LY_PAX_QTY INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,6 ),
LM_PAX_QTY INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,6 ),
FWD_PAX_QTY_TXT VARCHAR(250) CHARACTER SET LATIN NOT CASESPECIFIC,
ICW_LOAD_TM TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0))
PRIMARY INDEX ( TRAVEL_MONTH_START_DT ,AGENT_LOCATION_CD ,RAS_DESTINATION_STN_CD );

Fast Export script-
.LOGTABLE U699076.USOD_FUTUREDATA_LOG;
.BEGIN EXPORT
SESSIONS 2 ;

.EXPORT OUTFILE EXPORT_DATA_FILE
FORMAT FASTLOAD
MODE INDICATOR
OUTLIMIT 200
;

select *

from ras_crs_summary
where DOMINANT_OPERATING_AIRLINE_CD = 'SQ'
and agent_location_cd = '1234567'
and ras_destination_stn_cd = 'SIN'
and dominant_booked_cabin_cd in ('F', 'C')
and travel_month_start_dt in (1090701,1090801)
and FWD_PAX_QTY_TXT <> ' '
;

.end export ;

.logoff ;

Fastload script-
DROP TABLE u699076.RAS_CRS_SUMMARY;
DROP TABLE u699076.ERROR1;
DROP TABLE u699076.ERROR2;

CREATE SET TABLE U699076.RAS_CRS_SUMMARY ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
TRAVEL_YEAR_NO INTEGER,
TRAVEL_MONTH_NO INTEGER,
TRAVEL_MONTH_START_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL,
DOMINANT_MARKETING_AIRLINE_CD CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
DOMINANT_OPERATING_AIRLINE_CD CHAR(2) CHARACTER SET UNICODE NOT CASESPECIFIC,
SALE_COUNTRY_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
AGENT_LOCATION_CD CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
DOMINANT_BOOKED_CABIN_CD CHAR(2) CHARACTER SET UNICODE NOT CASESPECIFIC,
RAS_ORIGIN_STN_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
ORG_STN_BA_CD CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC,
ORG_CITY_BA_CD CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC,
RAS_DESTINATION_STN_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
DEST_STN_BA_CD CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC,
DEST_CITY_BA_CD CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC,
UK_FLAG_IND BYTEINT,
BA_VIA_STN_CD CHAR(3) CHARACTER SET UNICODE NOT CASESPECIFIC,
CHAIN_CD CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC,
CHAIN_SUB_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
RAS_AGENT_CUST_ID INTEGER,
PAX_QTY INTEGER,
LY_PAX_QTY INTEGER,
LM_PAX_QTY INTEGER,
FWD_PAX_QTY_TXT VARCHAR(250) CHARACTER SET LATIN NOT CASESPECIFIC,
ICW_LOAD_TM TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0))
PRIMARY INDEX ( TRAVEL_MONTH_START_DT ,AGENT_LOCATION_CD ,RAS_DESTINATION_STN_CD );

SET RECORD FORMATTED;

DEFINE FILE =/vw/home/u699076/EXPORT_DATA_FILE;

BEGIN LOADING u699076.RAS_CRS_SUMMARY ERRORFILES u699076.ERROR1, u699076.ERROR2 INDICATORS;

INSERT u699076.RAS_CRS_SUMMARY.* ;

END LOADING;
LOGOFF;

I can't provide any sample data records as they include binary data - unless there's a way of attaching a file to the forum? But you are right that I just provided the individual field snippet above showing a field descriptor of 016 octal (14 decimal). Anything more would be even less readable!

I've just tried it again including all the Compress statements on the target table but that didn't help.

0011 INSERT u699076.RAS_CRS_SUMMARY.* ;

**** 14:23:43 Number of recs/msg: 183
**** 14:23:43 Starting to send to RDBMS with record 1
**** 14:23:43 Bad file or data definition.
**** 14:23:43 The length of: FWD_PAX_QTY_TXT in row: 1 was greater than
defined.
Defined: 250, Received: 15929
Teradata Employee

Re: Fastload not handling VARCHAR field in Linux?

First of all, let me add that the wilcard INSERT syntax does not always work on tables that have Unicode columns.
I am not sure if this is documented, but we have seen problems in this area.

Having said that, please read on in order to determine if there are ways to narrow this down.

Okay, I know this might be a pain, but maybe there are a few other things that can be done to figure this out.

1. You indicated that other jobs did not have this problem.
- if so, then what is different between the other jobs and this one?
- did the other jobs have VARCHARs?
- did the other jobs have target tables that combined Unicode columns with non-Unicode columns?
2. Are you sure you are providing the correct amount of data for the Unicode columns?
3. Since the offending column of this job is towards the end, it is a shame, but one other thing to try is this:
- try SELECT-ing (in the FastExport script) all columns up to (but excluding) the FWD_PAX_QTY_TEXT column
- load that data with FastLoad (specifying the columns individually)
- verify that all data in all columns is correct

For #3, if some of the columns do not have correct data, then it points to a problem earlier in the record than
the FWD_PAX_QTY_TEXT.

In other words, it is possible that the field length indicator is correct for that column, but if prior columns are
off, then when FastLoad gets to the FWD_PAX_QTY_TXT column, the pointer in the record is not really pointing
to the 2-byte length. It is pointing to some other byte position in the record.

Due to the use of the wildcard insert on the target table, I suspect that FastLoad is not processing the row
correctly on the data for the Unicode columns.
-- SteveF
Teradata Employee

Re: Fastload not handling VARCHAR field in Linux?

One more thing to provide to us please.

I would like to see the entire output of the FastLoad job. I want to see the column/byte layout of all columns.

-- SteveF