Stephane
Using DB2 UDB 7.1/AIX the answer would be NO, don't use decimal except where you need it (use integer types where possible)!
I'd be surprised if this has changed in UDB 8.1
Extract from IBM Redbook "DB2 UDB v7.1 Performance Tuning Guide" given below (see http://publib-b.boulder.ibm.com/Redbooks.nsf/Portals/UNIXRedbooks)
7.1.7.1 Other considerations for data types
DB2 UDB allows you to use various data types. You can use SMALLINT,
INTEGER, BIGINT, DECIMAL, REAL, and DOUBLE for numeric data; CHAR, VARCHAR, LONG
VARCHAR, CLOB for character data; GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC, and
DBCLOB for the double byte character data, and so on. As the amount of
database storage and the cost to process varies depending on the data type,
you should choose the appropriate data type.
The following are guidelines when choosing a data type:
* Use character (CHAR) rather than varying-length character (VARCHAR)for
short columns. The varying-length character data type can save database
storage when the length of data values varies, but there is a cost to check
the length of each data value.
* Use VARCHAR or VARGRAPHIC rather than LONG VARCHAR or LONG VARGRAPHIC.The
maximum length for VARCHAR and LONG VARCHAR columns, VARGRAPHIC and
LONG VARGRAPHIC are almost same (32,672 bytes for VARCHAR, 32,700 bytes
for LONG VARCHAR, 16,336 characters for VARGRAPHIC, and 16,350 characters
for LONG VARGRAPHIC), while LONG VARCHAR and LONG VARGRAPHIC columns have
several restrictions. For example, data stored in LONG VARCHAR or LONG
VARGRAPHIC columns is not buffered in the database buffer pool. See
4.1.1.4, "Use VARCHAR or LONG VARCHAR" on page 89 for further
description.
* Use integer (SMALLINT,INTEGER, BIGINT) rather than floating-point number
(REAL or DOUBLE) or decimal (DECIMAL) if you do not need to have the fraction
part. Processing cost for integers is much more inexpensive.
* Use date-time (DATE, TIME, TIMESTAMP) rather than character (CHAR).
Date-time data types consume less database storage, and you can use
some built-in functions for date-time data types such as YEAR and MONTH.
* Use numeric data types rather than character.
-----Original Message-----
From: Stephane Paquette [mailto:stephane.[Email Address Removed] 04 December 2003 14:58
To: DB2 UDB LazyDBA
Subject: Number datataype
Hi,
DB2 UDB 8.1/AIX
In Oracle, numeric datatype is number only. In DB2 there are multiple choices : smallint, int, float, ... and decimal.
In DB2, is there a better way ?
Can I use decimal for everything ? For example: decimal (5,0) instead of int , do I loose something ?
Stephane Paquette
Administrateur de bases de données
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 poste 7470
stephane.[Email Address Removed] <mailto:stephane.[Email Address Removed]
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
Get today's cartoon: http://www.LazyDBA.com
To unsubscribe, e-mail: db2udbdba-[Email Address Removed] additional commands, e-mail: db2udbdba-[Email Address Removed] e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________
“This email and any attachments are confidential and may contain privileged information intended for the named addressee(s) only
If you are not the intended recipient, please notify us immediately and take note that you do not have the legal authority to open, use, retain, disclose or distribute this email or any part of it
The content of this email or any of its attachments may contain data that falls within the scope of the Data Protection Act 1998, the unauthorised opening, use, recording, disclosure, copying or alteration of this message is strictly forbidden under the Data Protection Act 1998
You must take full responsibility for virus checking this email and any attachments. This email and any attachments have been scanned for viruses prior to leaving our Group network. LTF Holdings (UK) Ltd or any of its subsidiaries will not be liable for direct, special, indirect or consequential damages arising from alteration of the contents of this message by a third party or as a result of any virus being passed on.
Unless expressly stated, opinions in this email are those of the individual sender and not LTF Holdings (UK) Ltd or any of its subsidiaries
LTF Holdings (UK) Ltd and any of its subsidiaries reserves the right to monitor and record email messages sent to and from this address for the purposes of investigating or detecting any unauthorised use of its system and ensuring its effective operation”
DB2 & UDB email list listserv db2-l LazyDBA home page