All Forums Tools
sh_one 5 posts Joined 06/15
30 Jun 2015
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?

Sven
dnoeth 4628 posts Joined 11/04
30 Jun 2015

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

Change to DECIMAL(22,8).

Dieter

sh_one 5 posts Joined 06/15
01 Jul 2015

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."
 

Sven

Fred 1096 posts Joined 08/04
01 Jul 2015

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

SuSeSi 61 posts Joined 10/08
02 Jul 2015

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

-Sudhansu

sh_one 5 posts Joined 06/15
03 Jul 2015

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!

Sven

You must sign in to leave a comment.