String vs Numeric Type

[Victor Mehta]

would it matter if I decided to make a primary key a string of numbers
vs actual numbers ? would it make any difference to DB2 in terms of
efficiency ? why would you choose numeric over a string for a key that
does not need to be in numeric format ?

thanks
--
reply to newsgroupsurferATyahooDOTcom

[Serge Rielau]

kenfar wrote:

Serge,

Couple of comments:
1. How do you enforce the functional dependency?

Hmm, I've never seen anyone attempt to enforce functional dependency
between an artificial & natural key. Is there a method that you had in
mind for this? Note, that I haven't found this to be a serious source
of data quality problems either tho...

It's not enforcable. That's the point. Maybe you haven't found serious
problem because you have no means detecting them... ;-)
Not trying to scaremonger.. just to make you (and no doubt others) think
about the consequences of design choices.

2. How do you index? I presume your app wants to see the natural key,
yet your joins operate on the abstract key. That means that you end up
with ISCAN-FETCH combinations instead of pure ISCAN on covering indexes.
You have traded lazy fingers for performance.

Is it really that bad? Many of the queries that I have that use
artificial ids also refer to non-key attributes in the query anyway.
These wouldn't benefit from the natural key, but the index io certainly
must benefit from use of a 4-byte smallint vs composit key with
multiple large varchars.

SMALLINT is 2 byte.... ;-)
Anyway. Check out the INCLUDE clause on unique indexes.
As you say.. disk is cheap who cares for one extra level in the index.
Won't bother your bufferpool much either.
When creating a table you can create a unique index on the PK columns
with INCLUDE columns first. THEN add teh primary key attribute. DB2 will
pick up the unique index (raisinng a warning to tell you).
This way you can get covering index access for your common queries.
Plus, these days labor costs so much more than hardware, and business
rules change so fast, that the future-proofing you get from artificial
keys can be worth any theoretical performance problem. I know one
system that has business rule changes every month - as new customers
with slightly different needs are added, new source systems are fed
into it, etc. Assumptions made about natural keys for one set of data
often fails when the next set is added. This is a data warehouse,
which is much more susceptible to this kind of a problem than a small
OTLP database though.

OK. There are "good" designed warehouses out there with substitute keys.
I buy it for a warehouse much more willingly than for OLTP.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[kenfar]

> It's not enforcable. That's the point. Maybe you haven't found serious

problem because you have no means detecting them... ;-)
Not trying to scaremonger.. just to make you (and no doubt others) think
about the consequences of design choices.
Sure, great point. The one place I've seen this actually happen is in
OLTP systems in which a user adds a new entry by updating non-key
valuesth new values (rather than deleting the first and adding the
second). This can cause quite a mess with historical data, security,
etc as you can imagine. This can happen with natural keys as well,
just less likely I suppose.
SMALLINT is 2 byte.... ;-)
dang, i had changed that after i wrote it thinking that smallint was an
exaggeration, but only changed the length not the name: i've used
smallint often, but sure enough I've now got a database with 40 tables
that need a migration from smallint to integer. The table was never
intended to be this large, but requirement change. And the conversion
will be a total and unnecessary pain in the butt.
When creating a table you can create a unique index on the PK columns
with INCLUDE columns first. THEN add teh primary key attribute. DB2 will
pick up the unique index (raisinng a warning to tell you).
This way you can get covering index access for your common queries.

will keep that in mind, thanks

Ken

[Gregor Kovaè]

kenfar wrote:

dang, i had changed that after i wrote it thinking that smallint was an
exaggeration, but only changed the length not the name: i've used
smallint often, but sure enough I've now got a database with 40 tables
that need a migration from smallint to integer. The table was never
intended to be this large, but requirement change. And the conversion
will be a total and unnecessary pain in the butt.

You can imagine how big of a pain is in my case. We have a database with
over 1500 tables. And I would have to change about 400 tables and go from
DECIMAL(18, 2) to DECIMAL(30, 15) for about 1500 columns. :) :) :) :)

I hope new version of DB2 on LUW will allow me to do something like:
ALTER TABLE A CHANGE COLUMN F SET DATA TYPE DECIMAL (31, 15).
Serge ? :) :) :)

When creating a table you can create a unique index on the PK columns
with INCLUDE columns first. THEN add teh primary key attribute. DB2 will
pick up the unique index (raisinng a warning to tell you).
This way you can get covering index access for your common queries.

will keep that in mind, thanks

Yes, this is worth keeping in mind :)

Ken

Best regards,
Kovi

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mikropis.si
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

[Serge Rielau]

Gregor Kova� wrote:

kenfar wrote:

dang, i had changed that after i wrote it thinking that smallint was an
exaggeration, but only changed the length not the name: i've used
smallint often, but sure enough I've now got a database with 40 tables
that need a migration from smallint to integer. The table was never
intended to be this large, but requirement change. And the conversion
will be a total and unnecessary pain in the butt.
You can imagine how big of a pain is in my case. We have a database with
over 1500 tables. And I would have to change about 400 tables and go from
DECIMAL(18, 2) to DECIMAL(30, 15) for about 1500 columns. :) :) :) :)

And of course you ahev been using USER DEFINED DISTINCT DATATYPES to at
least script thsi so much easier.... ;-)
Sorry petpeeve - trying to teach DBAs what app developers have found
out about years ago: typedef.
I hope new version of DB2 on LUW will allow me to do something like:
ALTER TABLE A CHANGE COLUMN F SET DATA TYPE DECIMAL (31, 15).
Serge ? :) :) :)

Well, have you been good this year? If not Viper just might bring a lump
of coal.
(date for DB2 x-mas is not included)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Gregor Kovaè]

Serge Rielau wrote:

Gregor Kovaè wrote:

kenfar wrote:

dang, i had changed that after i wrote it thinking that smallint was an
exaggeration, but only changed the length not the name: i've used
smallint often, but sure enough I've now got a database with 40 tables
that need a migration from smallint to integer. The table was never
intended to be this large, but requirement change. And the conversion
will be a total and unnecessary pain in the butt.
You can imagine how big of a pain is in my case. We have a database with
over 1500 tables. And I would have to change about 400 tables and go from
DECIMAL(18, 2) to DECIMAL(30, 15) for about 1500 columns. :) :) :) :)

And of course you ahev been using USER DEFINED DISTINCT DATATYPES to at
least script thsi so much easier.... ;-)
Sorry petpeeve - trying to teach DBAs what app developers have found
out about years ago: typedef.

Hmm.. We do have our data types defined in our logical database model
(PowerDesigner), but they are not propageted into our phisical model. Don't
ask me why.

I hope new version of DB2 on LUW will allow me to do something like:
ALTER TABLE A CHANGE COLUMN F SET DATA TYPE DECIMAL (31, 15).
Serge ? :) :) :)

Well, have you been good this year? If not Viper just might bring a lump
of coal.
(date for DB2 x-mas is not included)

Yes, I have been a very, very good boy (take my word, don't ask my
mother :) )
Cheers
Serge

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mikropis.si
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~