This topic has been locked.
3 replies Latest Post - 2013-02-07T15:26:38Z by nivanov1
Pinned topic DB2 size estimation is rocket science and NP complete
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
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 110000D4XK17917 PostsACCEPTED ANSWER
Re: DB2 size estimation is rocket science and NP complete2013-02-06T09:44:20Z in response to bmccarHi,
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 270004NHQQ5 PostsACCEPTED ANSWER
Re: DB2 size estimation is rocket science and NP complete2013-02-06T23:45:57Z in response to SystemAdminSo 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 100000H9H7226 PostsACCEPTED ANSWER
Re: DB2 size estimation is rocket science and NP complete2013-02-07T15:26:38Z in response to bmccarIt 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.