Import Bteq Error

General

Import Bteq Error

Good morning to all,
sorry but i do not speak English very well.
i have an error loading a file in teradata with bteq.
my file is an extraction from sql server.
The data inside my file looks like follows;

1;000013;0002899;T15;LA1;OLB;OLA;FLA;FN1;ACG;AFE;LAV;12690;10080;7614;7614;776;776;337;2;7614;20100101;20100131

2;000013;0002899;EXO;FES;APP;NAT;ROR;FEM;T07;HL2;T06;226;2;7614;6;7614;776;5;10080;2600;20100101;20100131

3;000013;0002899;T05;T04;T03;T31;HLV;OLO;HLT; ; ;2600;16800;16800;16800;10080;9304;10080;0;0;20100101;20100131

1;000013;0002899;T15;LA1;OLB;OLA;ACG;LAV;REC;ML1;EXO;8993;9692;5396;5396;369;5396;124;480;252;20100201;20100228

my script to load this file is:

.LOGON db/user,password,;
SET session dateform=ansidate;

DATABASE BB_PROD_005_001;

SEL date;–to select current date

SEL time;–to select current time

CREATE VOLATILE TABLE (
col1 CHAR(1),
col2 CHAR(6),
col3 CHAR(7),
col4 CHAR(3),
col5 CHAR(3),
col6 CHAR(3),
col7 CHAR(3),
col8 CHAR(3),
col9 CHAR(3),
col10 CHAR(3),
col11 CHAR(3),
col12 CHAR(3),
col13 INTEGER,
col14 INTEGER,
col15 INTEGER,
col16 INTEGER,
col17 INTEGER,
col18 INTEGER,
col19 INTEGER,
col20 INTEGER,
col21 INTEGER,
col22 CHAR(8),
col23 CHAR(8)
)

select count(*) ;

.import report file=C:\extraction.txt

.quiet on

.repeat*

ON COMMIT PRESERVE ROWS;

.import report file=estrazione_completa.txt;
.QUIET ON
.REPEAT *
USING

(
col1 CHAR(1),
col2 CHAR(6),
col3 CHAR(7),
col4 CHAR(3),
col5 CHAR(3),
col6 CHAR(3),
col7 CHAR(3),
col8 CHAR(3),
col9 CHAR(3),
col10 CHAR(3),
col11 CHAR(3),
col12 CHAR(3),
col13 INTEGER,
col14 INTEGER,
col15 INTEGER,
col16 INTEGER,
col17 INTEGER,
col18 INTEGER,
col19 INTEGER,
col20 INTEGER,
col21 INTEGER,
col22 CHAR(8),
col23 CHAR(8)
)

INSERT INTO (
col1 ,
col2 ,
col3 ,
col4 ,
col5 ,
col6 ,
col7 ,
col8 ,
col9 ,
col10 ,
col11 ,
col12 ,
col13 ,
col14 ,
col15 ,
col16 ,
col17 ,
col18 ,
col19 ,
col20 ,
col21 ,
col22 ,
col23
)
VALUES
(
:col1 ,
:col2 ,
:col3 ,
:col4 ,
:col5 ,
:col6 ,
:col7 ,
:col8 ,
:col9 ,
:col10 ,
:col11 ,
:col12 ,
:col13 ,
:col14 ,
:col15 ,
:col16 ,
:col17 ,
:col18 ,
:col19 ,
:col20 ,
:col21 ,
:col22 ,
:col23
);
.quiet off
select count(*)from .QUIT ERRORCODE

i call the column in the script with the same name in the database table.

the script execute without a problem but when i check the table i find the ; in my field and the record was moved to the right of one character

:(

Tags (1)
4 REPLIES
Senior Apprentice

Re: Import Bteq Error

You defined the wrong import mode, you need VARTEXT, because it's a delimited text file:
.import vartext ';' file=C:\extraction.txt

You have to specify VARCHARs for all your input columns (btw, REPORT format also needs CHAR/VARCHAR).
USING
(
col1 VARCHAR(1),
col2 VARCHAR(6),
...
col13 VARCHAR(11) -- instead of INTEGER
...

The last two columns look like dates, you might define as DATE in your target table and add a FORMAT for the typecast:
:col22 (DATE, FORMAT 'YYYYMMDD'),
:col23 (DATE, FORMAT 'YYYYMMDD')

And finally add a PACK to load faster:
.REPEAT * PACK 100

Of course when you load a large number of records you'd better use FastLoad/MultiLoad instead of BTEQ.

Dieter

Re: Import Bteq Error

thanks very much for the reply you have been very useful.

now the prompt show this warning:

Failure 2631 Transaction ABORTed due to deadlock.
Statement# 1, Info =0

it's normal?
Senior Apprentice

Re: Import Bteq Error

Strahge, there can't be a deadlock on a volatile table, because there are no locks on it.

Could you show your actual BTEQ script and/or it's output?

And i noted another problem, you didn't specify a Primary Index, thus it defaults to the first columns, which happens to be a CHAR(1). This table will have a totally skewed distribution, think about a better PI.

Dieter

Re: Import Bteq Error

i retry to load with the script and the prompt now don't show the error (Failure 2631 Transaction ABORTed due to deadlock.Statement# 1, Info =0)
strange but the table are loaded completly
thanks a lot for the help