Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Posts
    33

    Question 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. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote 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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote 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.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    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..!
    Vinay,

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote 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!)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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