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

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

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.

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..!

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!)