Hello ,

I would like to know the size in bytes required internally for each datatype db2 supports.

For eg.
I think for BIGINT its 8 bytes

Would like to know the size in bytes for
SMALLINT, BIGINT, DOUBLE, VARCHAR, BLOB, TIMESTAMP, DECIMAL, INTEGER

Thanks..

SQL Refernece should have this info ...

Cheers
Sathyaram

Thanks Sathyaram,

Yes SQL reference doc do have this info.
But its little bit confusing.

For eg.
For TimeStamp it talks about internal representation as a String of 10 bytes.
And it also talks about
length of timeStamp column , as described in SQLDA , is 26 bytes.

In this case what is the actual bytes used for storing timestamp ?

Also for Decimal how much is the byte size ? its not given in doc .

Decimal is stored like mainframe packed-decimal (comp-3). I know that does not help most people here. Basically 2 numbers fit in one byte, except that the last half byte must contain the sign (+ or -). But it is always rounded up to the next highest byte if only a half byte is used. Decimal places are not stored in the data itself, they are known to the column definition, so they take up no space.

Consider the following examples of decimal:

4 digits - 3 bytes of storage (last half byte in byte 3 is not used)
5 digits - 3 bytes of storage (3rd byte has the 5th digit plus the sign)
6 digits - 4 bytes of storage
7 digits - 4 bytes of storage

Date, Time, and Timestamp are stored internally somewhat like decimals (two digits per byte), except there is no need to store a sign in the last half byte. Also, the separator characters such as "-", "/", ".", etc are not stored internally in DB2 in the actual table.

Therefore, since Date has 8 digits in it (YYYYMMDD), it only uses 4 bytes internally stored on the row. When Date is returned to an application program in a string or the SQLDA, one byte is used for each digit and the separator characters are put in, so Date has 10 bytes (8 digits plus 2 separator characters).

Similar concept with Time (3 bytes internally) and Timestamp (10 bytes internally).

Just in case anyone read my post above, I made some errors in the decimal length that I have corrected.

I am glad I found your post after looking around for size documentation on Decimals. Will your information also be true for decimals and their sizes on DB2 v9.7 for Windows (Linux and unix)?

The actual question I am debating is smallint vs decimal for smaller numeric values ... (from 1 to 32767). It seems that the only time you would want to select a Decimal over a smallint would be if it is single digits- A packed decimal would consume only one byte (half byte for the number, half byte for the sign), vs. the smallint consuming two bytes.

Do Decimals have any other advantages from performance standpoint? (less I/O time or conversion times?)

There has been no change in the information I provided above.

In most cases, a smallint/int/bigint works faster with mathematical calcuations than decimal, but the difference is probably not even measureable unless you are doing a lot of math. The main advantage of decimal is that it handles numbers to the right of the decimal point. Also, some developers insist that some numeric columns only can be a certain number of digits in length without them having to validate the value to make sure the max length is not exceeded and that it will fit on a form/report/webpage correctly.

You may also want to consider comparisons (as in integer vs. decimal index key values) and hash calculations in a partitioned database. In all such cases integers are more efficient than decimals.