Topic
3 replies Latest Post - ‏2013-02-07T15:26:38Z by nivanov1
bmccar
bmccar
5 Posts
ACCEPTED ANSWER

Pinned topic DB2 size estimation is rocket science and NP complete

‏2013-02-06T02:26:46Z |
That is the only thing I can conclude after seeing the general confusion around this issue and lack of tool support.
Updated on 2013-02-07T15:26:38Z at 2013-02-07T15:26:38Z by nivanov1
  • SystemAdmin
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: DB2 size estimation is rocket science and NP complete

    ‏2013-02-06T09:44:20Z  in response to bmccar
    Hi,

    I cannot see what is your point. However I would like to ask want do you mean by DB size? It could be different things:

    • Allocated space in disk (how big are the tablespaces and probably the logs) - How much disk is it using.
    • Used space (how big data is stored) - How much data is stored.
    • Backup size (how big is the file that has all the data) - How big is the file to replicate the database.

    Also, the data can be compresed (indexes, tables, logs, backups), and that also change a lot the previous elements and alter the previsions for the future.

    As you can see, the DB size concept is different from many points of view, and there are other points of view and other criteriia that change the values (archive logs, diaglog, SMS tablespaces are different from DMS, temporal tablespaces create data for short periods (sort, joins, temporary tables), etc.)
    • bmccar
      bmccar
      5 Posts
      ACCEPTED ANSWER

      Re: DB2 size estimation is rocket science and NP complete

      ‏2013-02-06T23:45:57Z  in response to SystemAdmin
      So my boss comes to me and says, that's a fine looking ER diagram, but how much SAN space is that going to cost me?

      Is there one tool I can use to answer this question? Or short of that, one page of unified guidance / best practice?

      Just estimating, capacity planning.
      • nivanov1
        nivanov1
        231 Posts
        ACCEPTED ANSWER

        Re: DB2 size estimation is rocket science and NP complete

        ‏2013-02-07T15:26:38Z  in response to bmccar
        It is not rocket science, but it's not a single page problem either, so there can't be a single-page answer.

        Your ERD can give you maximum table row widths, and that's it. What it does not give you:
        • The current and projected numbers of rows in your tables.
        • Physical implementation details of the tables, such as PCTFREE settings, APPEND mode use, MDC use etc.
        • The number and physical characteristics of indexes.
        • Whether compression is used for any tables or indexes and the corresponding compression ratios, which depend on the actual data.
        • Data lifecycle: ingest and archiving intervals, rows inserted/deleted, REORG frequency.
        • The use of temporary tables in the applications.
        • Query efficiency, which determines the utilization of the system temporary spaces.
        • Typical transaction throughput, which determines the active log space.
        • Recovery requirements, which determine the archived log space used locally.