# Thread: How to calculate table size

1. Registered User
Join Date
Aug 2006
Posts
33

## Unanswered: How to calculate table size

Hello,

I want to save a temp table in DB2 8.2 QMF. The table contains 15 million rows, and 4 columns: integer, char(10), TimeStamp, varchar(75).

I wonder is there any formula that can calculate how many bytes I need?

Thanks,
Jinse

2. Registered User
Join Date
Jan 2003
Posts
4,310
Add up the maximum row length:

Integer = 4
Char(10) = 10
timestamp = 10
varchar(75) = 75
total = 99

If any of these are nullable that you will need to add 1 for each column that is nullable.

determine what pagesize you want (minus overhead) and devide by max length to get rows per page (round down):

4096 = 4005 / 99 = 40
8192 = 8101 / 99 = 81
16384 = 16,293 / 99 = 164
32768 = 32677 / 99 = 330

Divide 15 million by records per page (round up) = number of pages needed

4096 = 375,000
8192 = 185,186
16384 = 91,464
32768 = 45,456

Multiply number of pages need by page size = number of byes needed

4096 * 375,000 = 1,536,000,000
8192 * 185,186 = 1,517,043,712
16384 * 91,464 = 1,498,546,176
32768 * 45,456 = 1,489,403,904

Andy

3. Registered User
Join Date
May 2003
Location
USA
Posts
5,737
Originally Posted by jinsezh
Hello,

I want to save a temp table in DB2 8.2 QMF. The table contains 15 million rows, and 4 columns: integer, char(10), TimeStamp, varchar(75).

I wonder is there any formula that can calculate how many bytes I need?

Thanks,
Jinse
QMF save data tables are created in a database and tablespace defined in you QMF profile. Check with your DBA to find out if there is sufficient space available.

Edit: I am assuming QMF for z/OS (OS/390), but not sure if that is the QMF being used.
Last edited by Marcus_A; 03-08-07 at 16:55.

4. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by ARWinner
varchar(75) = 75
Actually, varchar(75) = 77.
(The first two bytes store the actual length of the varchar, as a smallint.)

5. Registered User
Join Date
May 2006
Posts
82
Actually, varchar(75) = 77.
(The first two bytes store the actual length of the varchar, as a smallint.)
But still those 2 bytes accounts for calcualtion of space required right..?
Please correct me if am wrong..!

6. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by vini_srcna
But still those 2 bytes accounts for calcualtion of space required, right..?
That's right, be it that this would be the "maximally required size" in case all fields actually use up all 75 bytes.
When some entries are shorter, the effectively used space is of course less.
To get a good (but rough) estimate of the required space, you need a representative sample of data from which you just calculate the average string length. Use that number instead of "75" (but don't forget to add the 2 bytes!)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•