Handling Money Via UDT / UDF (i.e. homegrown "extender")

[Philip Nelson]

Folks,

I've been exercising my mind recently about the complexities of implementing
a "currency" data type within DB2 to cope with multiple currencies.

A monetary value is often simply represented as a DECIMAL column : for
example many times I've seen DECIMAL(12,2) used. The issue with this is
that there is nothing to interpret what currency this relates to (US
dollars, Canadian dollars, Euros, British pounds or whatever), and a
mixture of currencies and the SUM() function could be lethal.

So I thought that it would be a good idea to build an "extender" to handle
the situation (by "extender" I mean a set of complex user defined types and
related user defined functions to go with them).

I'm wondering if anyone else had done such a thing, and knows of any code
which is freely available ?

My initial thoughts were that the "currency" type consists of at least the
following elements -

MONETARY_VALUE DECIMAL(12,2)
CURRENCY_TYPE CHAR(3) : there is an ISO standard for this I believe

We'd need homegrown versions of the SUM() and other similar functions.

We'd also need functions to do conversions from one currency to another (and
potentially some tables to store conversion rates : perhaps we need a date
in the "type" as any conversion will be at a specific exchange rate for
that date ?

All thoughts welcome.

Sorry if this is a bit unusual topic, but it interests me ... sad I know.

Phil

[Matt Emmerton]

"Philip Nelson" <te*****@scotdb.com> wrote in message
news:Mh********************@fe06.news.easynews.com ...

Folks,

I've been exercising my mind recently about the complexities of implementing a "currency" data type within DB2 to cope with multiple currencies.

A monetary value is often simply represented as a DECIMAL column : for
example many times I've seen DECIMAL(12,2) used. The issue with this is
that there is nothing to interpret what currency this relates to (US
dollars, Canadian dollars, Euros, British pounds or whatever), and a
mixture of currencies and the SUM() function could be lethal.

So I thought that it would be a good idea to build an "extender" to handle
the situation (by "extender" I mean a set of complex user defined types and related user defined functions to go with them).

I'm wondering if anyone else had done such a thing, and knows of any code
which is freely available ?

My initial thoughts were that the "currency" type consists of at least the
following elements -

MONETARY_VALUE DECIMAL(12,2)
CURRENCY_TYPE CHAR(3) : there is an ISO standard for this I believe

We'd need homegrown versions of the SUM() and other similar functions.

We'd also need functions to do conversions from one currency to another (and potentially some tables to store conversion rates : perhaps we need a date
in the "type" as any conversion will be at a specific exchange rate for
that date ?

This is a good thought and I think a feasible implementation.

--
Matt Emmerton

[Serge Rielau]

Philip Nelson wrote:

Folks,

I've been exercising my mind recently about the complexities of implementing
a "currency" data type within DB2 to cope with multiple currencies.

A monetary value is often simply represented as a DECIMAL column : for
example many times I've seen DECIMAL(12,2) used. The issue with this is
that there is nothing to interpret what currency this relates to (US
dollars, Canadian dollars, Euros, British pounds or whatever), and a
mixture of currencies and the SUM() function could be lethal.

So I thought that it would be a good idea to build an "extender" to handle
the situation (by "extender" I mean a set of complex user defined types and
related user defined functions to go with them).

I'm wondering if anyone else had done such a thing, and knows of any code
which is freely available ?

My initial thoughts were that the "currency" type consists of at least the
following elements -

MONETARY_VALUE DECIMAL(12,2)
CURRENCY_TYPE CHAR(3) : there is an ISO standard for this I believe

We'd need homegrown versions of the SUM() and other similar functions.

We'd also need functions to do conversions from one currency to another (and
potentially some tables to store conversion rates : perhaps we need a date
in the "type" as any conversion will be at a specific exchange rate for
that date ?

All thoughts welcome.

Sorry if this is a bit unusual topic, but it interests me ... sad I know.

Phil

I would NOT use this approach for a few reasons:
1. Performance will suck
2. There are limits on LOAD, EXPORT, IMPORT of structured datatypes
3. Did I mention that performance will suck?

With performance I don't mean raw performnec of structured types. No
problem there, but what happens when you try to compare two values?
You need to compare through some funny functions (normalize to a
predefine(yet floating) currency)?
Selfdescribing datatypes spell death for a system geared towards
optimizing based on static information....

The standard solution is to define one distinct type (UDT) per currency.
I.e. you can have a EURO based on DEC(12, 2) and a USD on DEC(12, 2).
Then when you compare EUROs to EURO's you can do native compares,
indexing, hash-joins, .. all the good stuff.
You can also add some comparison functions between the currencies,
perhaps using a webservice to look up the latest exchange rates.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark Yudkin]

Decimal(12,.2) may be adequate for a piggy bank, but is way too small for
the currencies and sums dealt with in the real world. In fact, it's not even
adequate to deal with simple things like the balance of the bank I work in
(expressed in USD).

Another problem you face is that there's no such thing as AN exchange rate,
so that a function as simple as adding two values in different currencies
has no single answer. Exchange rates are variables within a range, where the
range of values tends towards one side or the other, depending on whether
you're buying or selling.

I fear that your view of the world is too simplistic. That's why there's no
"free code" available.

"Philip Nelson" <te*****@scotdb.com> wrote in message
news:Mh********************@fe06.news.easynews.com ...

Folks,

I've been exercising my mind recently about the complexities of
implementing
a "currency" data type within DB2 to cope with multiple currencies.

A monetary value is often simply represented as a DECIMAL column : for
example many times I've seen DECIMAL(12,2) used. The issue with this is
that there is nothing to interpret what currency this relates to (US
dollars, Canadian dollars, Euros, British pounds or whatever), and a
mixture of currencies and the SUM() function could be lethal.

So I thought that it would be a good idea to build an "extender" to handle
the situation (by "extender" I mean a set of complex user defined types
and
related user defined functions to go with them).

I'm wondering if anyone else had done such a thing, and knows of any code
which is freely available ?

My initial thoughts were that the "currency" type consists of at least the
following elements -

MONETARY_VALUE DECIMAL(12,2)
CURRENCY_TYPE CHAR(3) : there is an ISO standard for this I believe

We'd need homegrown versions of the SUM() and other similar functions.

We'd also need functions to do conversions from one currency to another
(and
potentially some tables to store conversion rates : perhaps we need a date
in the "type" as any conversion will be at a specific exchange rate for
that date ?

All thoughts welcome.

Sorry if this is a bit unusual topic, but it interests me ... sad I know.

Phil

[AK]

Serge,

"The standard solution is to define one distinct type (UDT) per
currency."

that leads to tons of tables like

PAYMENT_USD,
PAYMENT_CAD,
PAYMENT_EURO,

whatever, and all these tables having almost identical structure / RI /
Check constraints etc. Looks like an anomaly to me - storing data
(currency code) in metadata.

Maybe it would be simpler to:

1. have one single table PAYMENT
....
MONETARY_VALUE DECIMAL(12,2)
CURRENCY_TYPE CHAR(3)
....

2. create a set of UDDTs for all the currecncies you need
3. Create views WITH CHECK OPTION on top of single table PAYMENT and
expose your data via those view only. Have these views convert
MONETARY_VALUE to the UDDTs

Does it make sense?

[Serge Rielau]

AK wrote:

Serge,

"The standard solution is to define one distinct type (UDT) per
currency."

that leads to tons of tables like

PAYMENT_USD,
PAYMENT_CAD,
PAYMENT_EURO,

whatever, and all these tables having almost identical structure / RI /
Check constraints etc. Looks like an anomaly to me - storing data
(currency code) in metadata.

Maybe it would be simpler to:

1. have one single table PAYMENT
...
MONETARY_VALUE DECIMAL(12,2)
CURRENCY_TYPE CHAR(3)
...

2. create a set of UDDTs for all the currecncies you need
3. Create views WITH CHECK OPTION on top of single table PAYMENT and
expose your data via those view only. Have these views convert
MONETARY_VALUE to the UDDTs

Does it make sense?

Yes, if that is the need then I agree.
But how many cases are there where arbitrary currency values are held in
a table?
At my bank I can have an account in CAND and USD, only...

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Darin McBride]

Serge Rielau wrote:

But how many cases are there where arbitrary currency values are held in
a table?
At my bank I can have an account in CAND and USD, only...

I have to think that a company such as IBM itself would deal in
arbitrary currencies. I would imagine each national "branch" would
deal in its own currency for most transactions (plus maybe USD), but
then when producing the quarterly results, everything needs to be tied
back to USD for SEC reporting.

Banks, too, will deal with even more "currencies": most banks allow you
to buy/sell other currencies at some given rate loosely based on the
market.

And then do other currencies count? Bonds, mutuals, stocks, handled by
most trading companies - are these currencies? One could really
abstract the heck out of this, I would think.

Just because I can only get CDN at my local ATM, or I can only open CDN
and USD accounts at my local branch, doesn't mean that's all the
currencies a bank deals with ;-)

[Serge Rielau]

Darin McBride wrote:

Serge Rielau wrote:

But how many cases are there where arbitrary currency values are held in
a table?
At my bank I can have an account in CAND and USD, only...

I have to think that a company such as IBM itself would deal in
arbitrary currencies. I would imagine each national "branch" would
deal in its own currency for most transactions (plus maybe USD), but
then when producing the quarterly results, everything needs to be tied
back to USD for SEC reporting.

Are you telling me you would track yoru revenue centrally, but not
convert to your "master" currency directly.
I think I would take serious offense with such a design...
Also the question whether conversion rates are those of the booking date
at the subsidiary or the report date plays in here. Im CS and not MBA..
so I shut up on that one....
Banks, too, will deal with even more "currencies": most banks allow you
to buy/sell other currencies at some given rate loosely based on the
market. Yes, OK.. but these are point in time transactions. Your account will
NOT show that you received 100 EUR. It shows you received a bucket of
CAND with a nice annotation on that this was originally 100 EUR
converted with a rate of 1/x bucket.
And then do other currencies count? Bonds, mutuals, stocks, handled by
most trading companies - are these currencies? One could really
abstract the heck out of this, I would think. And it's not a new problem, and it has been solved quite satisfactory
long before structured types came around. Banking and OO.. never seen
that combination.
Just because I can only get CDN at my local ATM, or I can only open CDN
and USD accounts at my local branch, doesn't mean that's all the
currencies a bank deals with ;-)

That wasn't the question. The question is what do I need to store
persistently and in general. No question there are cases where I want to
store holdings with a qualifying currency columns, but that sure won't
be the common case.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab