leading zeros when exporting from decimal

Kermit Lowry

unread,

Mar 17, 2004, 2:54:44 PM3/17/04

to

Hello! The issue I am having is that when I export a decimal column
out of DB2 UDB 8.1.5 I get a "+" and leading zeros to round out the
field (see example below). I have a quick and dirty perl one-liner to
fix this, but even after scouring the export documentation and
searching the web, I cannot find a parameter to turn this off (except
to get rid of the +). I did see a "db2 values cast" command but it is
not very well documented either.

Has anyone run into this issue before and knows how to deal with it?

Thank you in advance,

Kermit Lowry
kermit dot lowry [AT] kp dot org

create table txn(cust_id integer not null, value decimal(19,2) not
null)
in userspace1;

db2 "select * from txn"

CUST_ID VALUE
----------- ---------------------
1 10.20

db2 export to k.del of del "select * from txn"

cat k.del
1,+00000000000000010.20

perl -pe 's/\+0+([1-9]\d*\.\d*)/$1/g;' k.del

1,10.20

Ian

unread,

Mar 18, 2004, 7:15:06 AM3/18/04

to

Kermit Lowry wrote:

> Hello! The issue I am having is that when I export a decimal column
> out of DB2 UDB 8.1.5 I get a "+" and leading zeros to round out the
> field (see example below). I have a quick and dirty perl one-liner to
> fix this, but even after scouring the export documentation and
> searching the web, I cannot find a parameter to turn this off (except
> to get rid of the +). I did see a "db2 values cast" command but it is
> not very well documented either.

The number format in the file is still valid; are you having a problem
moving the data to another DBMS? EXPORT is a data movement utility,
not a reporting utility. If you want/need your data in a specific format,
consider using a different tool like QMF or some other reporting tool.
(Or use your perl script to reformat).

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Kermit Lowry

unread,

Mar 18, 2004, 1:42:55 PM3/18/04

to

Yes, I will be passing this to another rdbms and opening in an excel
spreadsheet. I guess I should have said that I wanted to be able to
use in Sybase bcp or Ora SQL*Loader. I am going to check out bringing
it out of the database using the perl DBD::DB2 module.

Thanks,

Kermit

Ian <ian...@mobileaudio.com> wrote in message news:<405992cb$1...@corp.newsgroups.com>...

Stefan M. Mihokovic

unread,

Mar 19, 2004, 5:19:49 AM3/19/04

to

Hello,

use the 'modified by decplusblank' filetype-mod option to remove the "+"
sign.
For the leading zeros haven't i a solution.

Regards / Gruß

----------------------------------
Stefan M. Mihokovic
email: st...@stemi.de
----------------------------------

Kermit Lowry

unread,

Mar 22, 2004, 1:14:17 PM3/22/04

to

Thanks everyone. This is a strange way for a text file creator to act
(IMHO) but it seems to be the way IBM wants it to work. For my perl
one-liner to work, it needs the (+) to remain in, but I will see if
the output without the + is automatically understandable to Sybase and
Excel loads.

Again thanks,

Kermit

"Stefan M. Mihokovic" <ne...@stemi.de> wrote in message news:<c3ehgc$pir$00$1...@news.t-online.com>...

PM (pm3iinc-nospam)

unread,

Mar 23, 2004, 12:35:11 AM3/23/04

to

In the upcoming version, Stinger, there MAY be a new export file type
modifier (DEL)
striplzeros

PM

Tim.D

unread,

Mar 23, 2004, 5:05:30 PM3/23/04

to

What about if you actually want leading zeros ? something like:

001
002
004
006
012

representing say months but must be three digits long ? I tried using
decimal, digits etc but couldnt get rid of the decimal character or I
ended up with a text string "001" etc.

Any ideas on how this could be exported as a numeric value with
leading zeros ? The value in the table to be exported is an integer.

Stefan M. Mihokovic

unread,

Mar 26, 2004, 2:05:24 AM3/26/04

to

Hello,

use the 'modified by decplusblank striplzeros' filetype-mod option to remove
the "+" sign and the leading zeros.