Estimating storage when using the LOAD utility

You must complete several calculations to estimate the storage that is required for a table to be loaded by the LOAD utility.

About this task

For a table to be loaded by the LOAD utility, assume the following values:
  • Let FLOOR be the operation of discarding the decimal portion of a real number.
  • Let CEILING be the operation of rounding a real number up to the next highest integer.
  • Let number of records be the total number of records to be loaded.
  • Let average record size be the sum of the lengths of the fields in each record, using an average value for varying-length fields, and including the following amounts for overhead:
    • 8 bytes for the total record
    • 1 byte for each field that allows nulls
    • 2 bytes for each varying-length field
  • Let percsave be the percentage of kilobytes saved by compression (as reported by the DSN1COMP utility in message DSN1940I)
  • Let compression ratio be percsave/100

Procedure

To calculate the storage required when using the LOAD utility, complete the following steps:

  1. Calculate the usable page size.

    Usable page size is the page size minus a number of bytes of overhead (that is, 4 KB - 40 for 4 KB pages, 8 KB - 54 for 8 KB pages, 16 KB - 54 for 16 KB pages, or 32 KB - 54 for 32 KB pages) multiplied by (100-p) / 100, where p is the value of PCTFREE.

    If your average record size is less than 16, then usable page size is 255 (maximum records per page) multiplied by average record size multiplied by (100-p) / 100.

  2. Calculate the records per page.

    Records per page is MIN(MAXROWS, FLOOR(usable page size / average record size)), but cannot exceed 255 and cannot exceed the value you specify for MAXROWS.

  3. Calculate the pages used.

    Pages used is 2+CEILING(number of records / records per page).

  4. Calculate the total pages used.

    Total pages is FLOOR(pages used× (1+fp ) / fp ), where fp is the (nonzero) value of FREEPAGE. If FREEPAGE is 0, then total pages is equal to pages used.

    If you are using data compression, you need additional pages to store the dictionary.

  5. Estimate the number of kilobytes required for a table.
    • If you do not use data compression, the estimated number of kilobytes is total pages× page size (4 KB, 8 KB, 16 KB, or 32 KB).
    • If you use data compression, the estimated number of kilobytes is total pages× page size (4 KB, 8 KB, 16 KB, or 32 KB) × (1 - compression ratio).

Example

For example, consider a table space containing a single table with the following characteristics:
  • Number of records = 100000
  • Average record size = 80 bytes
  • Page size = 4 KB
  • PCTFREE = 5 (5% of space is left free on each page)
  • FREEPAGE = 20 (one page is left free for each 20 pages used)
  • MAXROWS = 255
If the data is not compressed, you get the following results:
  • Usable page size = 4056 × 0.95 = 3853 bytes
  • Records per page = MIN(MAXROWS, FLOOR(3853 / 80)) = 48
  • Pages used = 2 + CEILING(100000 / 48) = 2085
  • Total pages = FLOOR(2085 × 21 / 20) = 2189
  • Estimated number of kilobytes = 2189 × 4 = 8756

If the data is compressed, multiply the estimated number of kilobytes for an uncompressed table by (1 - compression ratio) for the estimated number of kilobytes required for the compressed table.