BTEQ Import/Export of decimals does not work

Tools
Enthusiast

BTEQ Import/Export of decimals does not work

Hello experts,

I have a problem using import and export with bteq. I have the following simple table in DB1 and DB2:

CREATE SET TABLE DB1.MY_TABLE
(
ID INTEGER NOT NULL,
NAME VARCHAR(50),
DEC_VALUE DECIMAL(22,8) NOT NULL)
UNIQUE PRIMARY INDEX ( ID );

insert into DB1.my_table values (1, 'TEST 1', 0.11);
insert into DB1.my_table values (2, 'TEST 2', 0.22);
insert into DB1.my_table values (3, null, 0.33);

I have create a bteq script that exports the data of this table in DB1: 

.set width 500
.set titledashes off
.EXPORT INDICDATA DDNAME=./work/tmp/my_table.dat
select * from DB1.my_table;
.EXPORT RESET
.logoff
.quit

Now I want to import the data into DB2 using the following bteq script:

DATABASE DB2;
.IMPORT INDICDATA DDNAME=./work/tmp/my_table.dat
.REPEAT *
USING (
ID INTEGER
, NAME VARCHAR (50)
, DEC_VALUE DECIMAL
)
INSERT INTO MY_TABLE
(
ID
, NAME
, DEC_VALUE
)
VALUES
(
:ID
,:NAME
,:DEC_VALUE
);
.logoff
.quit

But I get the error:

 *** Warning: Out of data.
*** Failure 2683 Numeric overflow occurred during computation.
Statement# 1, Info =3

*** Finished at input row 3 at Tue Jun 30 12:13:30 2015
*** Total number of statements: 3, Accepted : 0, Rejected : 3

When I change the import script to DEC_VALUE DECIMAL(22,8) in the USING section I get a different error:

 *** Warning: Out of data.
*** Failure 2673 The source parcel length does not match data that was defi
ned.
Statement# 1, Info =3

*** Finished at input row 3 at Tue Jun 30 12:14:13 2015
*** Total number of statements: 3, Accepted : 0, Rejected : 3

I have no clue how to import the decimals specified like this back to my database. Any suggestions? Is it a language or character set problem?

5 REPLIES
Junior Contributor

Re: BTEQ Import/Export of decimals does not work

For your import you define DEC_VALUE as DECIMAL which defaults to DECIMAL(5,0).

Change to DECIMAL(22,8).

Enthusiast

Re: BTEQ Import/Export of decimals does not work

Hi Dieter,

that's what I wrote in my last paragraph. If I change it to DECIMAL(22,8) then I get the error message "Failure 2673 The source parcel length does not match data that was defined."

Teradata Employee

Re: BTEQ Import/Export of decimals does not work

What is MAXDECIMAL set to? Maybe your exported data is being limited to DECIMAL(15,8) or DECIMAL(18,8).

Teradata Employee

Re: BTEQ Import/Export of decimals does not work

Use following command for exporting the data

.set width 500
.set titledashes off
.decimaldigits 38;
.EXPORT INDICDATA DDNAME=my_table.dat
select * from sud.my_table;
.EXPORT RESET
.logoff
.quit

Note ".decimaldigits 38;" command. Also use "DECIMAL(22,8)" as suggested by Dieter

Enthusiast

Re: BTEQ Import/Export of decimals does not work

Thanks to all of you. I have used "DECIMAL(22,8)" and add ".decimaldigits 38;" both in the export AND the import. That finally solved the problem.

Many thx!