Error while Executing FASTLOAD Script

Database

Error while Executing FASTLOAD Script

Hi,
I am executing a fastlaod script which is as follows:

SESSIONS 4;
ERRLIMIT 2000;
.logon tdpid/username,password;

create table DBNAME.id_unit_fload (
id VARCHAR(18),
units VARCHAR(18)
)
PRIMARY INDEX UPI_id_unit_fload ( id );

SET RECORD UNFORMATTED;

define

id (VARCHAR(18)),
units (VARCHAR(18))

file=insert.input;

SHOW;

BEGIN LOADING DBNAME.id_unit_fload errorfiles DBNAME.err_1, DBNAME.err_2;

insert into DBNAME.id_unit_fload
(
:id,
:units
);

END LOADING;

logoff;

I have inserted some records in the file "insert.input" using the following command,after connecting to db:

.set width 2000;
.set titledashes off;
export file=insert.input;
SELECT
CAST (id AS VARCHAR(18)) (TITLE ''),
CAST( units AS VARCHAR(18)) (TITLE '')
FROM DBNAME.id_units_summ
SAMPLE 2000;
.export reset

While executing the fastload script, I am getting the following error:

**** 06:09:00 Number of recs/msg: 1567
**** 06:09:00 Starting to send to RDBMS with record 1
**** 06:09:00 The length of: ID in row: 1 was greater than defined.
Defined: 18, Received: 12595

Not sure why I am encountering this error.Can anyone help please?
Tags (2)
2 REPLIES

Re: Error while Executing FASTLOAD Script

If you are generating the file with bteq you should use VARTEXT '|' in the fastload script and generate the file with something like

SELECT CAST (id AS VARCHAR(18))||'|'||CAST( units AS VARCHAR(18)) (TITLE '')
FROM DBNAME.id_units_summ
SAMPLE 2000;

The proper syntax for the INSERT is:

insert into DBNAME.id_unit_fload (id, units) values (:id, :units);

HTH.

Cheers.

Carlos.
N/A

Re: Error while Executing FASTLOAD Script

The default format for a bteq export is report format. You cannot have varchar output on a report - it is converted to char. The bteq export then becomes:

.set width 2000;
.set titledashes off;
.export report file=insert.input;
.format off
.separator 0

SELECT
CAST (id AS CHAR(18)) (TITLE ''),
CAST( units AS CHAR(18)) (TITLE '')
FROM DBNAME.id_units_summ
SAMPLE 2000;
.export reset

Your fastload is then:

------------------------------------------------------ --------------
SESSIONS 4;
ERRLIMIT 2000;
.logon tdpid/username,password;

create table DBNAME.id_unit_fload (
id VARCHAR(18),
units VARCHAR(18)
)
PRIMARY INDEX UPI_id_unit_fload ( id );

SET RECORD TEXT;
----
define

id (CHAR(18)),
----
units (CHAR(18))
----
file=insert.input;

SHOW;

BEGIN LOADING DBNAME.id_unit_fload errorfiles DBNAME.err_1, DBNAME.err_2;

insert into DBNAME.id_unit_fload
(
:id,
:units
);

END LOADING;

logoff;

------------------------------------------------------ -------

So you are importing fixed length records with a newline at the end.
.