Implementing DB2 with ReportCenter › DB2 Database Sizes

DB2 Database Sizes

When you define your DB2 ReportCenter databases, you specify how much space to allocate for each separate database table. Space is physically allocated in DASD units, but database size more usefully refers to the logical number of table rows.

ReportCenter Data Model

ReportCenter uses a very simple data warehousing star schema. Star refers to data model designs where multiple dimension tables (in ReportCenter's case, Resource, Attribute, and Period) revolve around large central fact tables (NumericFact and EnumeratedFact).

Details of each different resource, report attribute, and time period are stored in the database once only-in a row in their dimension table. When a data observation arrives for a specific resource, attribute, and period, the value is stored in a row in one of the fact tables, with pointers to the associated resource, attribute, and period dimension table rows.

Table Sizes

It is the fact tables, NumericFact and EnumeratedFact, that vary most in size between ReportCenter implementations.

Typical NumericFact table sizes may range from less than one million to tens of millions of rows.

EnumeratedFact table sizes are generally 20% - 30% of the number of rows of the NumericFact table, although this varies greatly depending on the specific data collected. For the same interval, EnumeratedFact data takes more physical space than NumericFact data.

ReportCenter dimension and internal-use tables are reasonably constant in size between implementations.

Database Growth

After you have defined most of your essential data collection, ReportCenter data regions send data to the database at a more-or-less constant rate. However, the database does not grow at a constant rate, for the following reasons:

• Most dimension table entries are added the first time data arrives for them. At the beginning, when everything is new, dimension table entries need to be added quickly. After a while, few new entries are required except for new days and newly added resources.
• The ReportCenter expiry service deletes old rows from the database. You can change these retention periods; however, by default, hourly data is deleted after four days, daily data after 3-12 months, and monthly data after two years. Your database grows unabated until data is old enough to be deleted.
• With the default retention periods, after a three-month interval, when most data collection is defined and the expiry service is running regularly, your rate of database growth will decrease. After two years, the size becomes stable.

You should run the DB2 reporting utilities regularly to monitor the growth of each table.

Specify Database Size

Example database space allocations for the ReportCenter database are specified in the distributed dsnpref.NMC1.CC2DSAMP(WRDB2CRE) sample JCL member. Review the comments in this member carefully.

Member WRDB2CRE contains an example of row, cylinder, and block requirements for a database that should be adequate for most organizations. For all tables, a secondary extent is specified with 10% of the primary value.

If you have a small or medium scale CA NetMaster implementation, we recommend that you use the default primary space allocations. However, you should review and, if possible, remove the secondary allocations on the NumericFact and EnumeratedFact tables.

VSAM Extents

DB2 tables are physically stored as VSAM files. A large number of VSAM extents can badly impact SQL performance and CPU consumption required for Input/Output. Review your organization standards and, if possible, specify no secondary extents on at least the fact tables.

You may get errors sooner, when or if the primary extent fills, but you will avoid situations such as enormous tables spread over 120+ extents and multiple devices. This applies equally to index space allocations.

Estimate Numeric Fact Rows

You only need to estimate the number of rows in the NumericFact table. The EnumeratedFact table generally has one quarter to one third of the number of NumericFact rows. Most report data is numeric.

The number of rows for the remaining six tables does not vary as widely and the sample allocations should be adequate.

The number of NumericFact rows depends on how much data you collect and the length of time you keep it. In practice, it is not easy or necessary to predict exactly when a table will reach a given size. You should aim to have a primary extent large enough to hold all the NumericFact rows you are likely to need. Regular monitoring gives an indication of the growth of the fact table.

Example: Estimate Numeric Fact Rows

Use the following as a very rough guideline. If you have data feeding in from a region that is sending all possible report data from a single stack and a small number of each other resource type, you need approximately 500,000 rows after six months, after which the rate of increase should slow down. This is an indication only-individual regions can monitor more than this. This example uses the default retention periods.

Minimize the Space Required

To minimize the space required, send to ReportCenter only the data on which you want to report. Try to prevent your database filling up with data you do not want and do not know is there.

The following are ways that you can minimize the space required:

• If you are not interested in a particular device attribute, do not monitor it. Some IP devices have many default attributes, not all of which are of value to all customers.
• Send to ReportCenter settings in IP Node Monitor groups apply to all nodes in that group. If you only want to report on a small number of nodes in a group, do not send data for them all. Set up a separate group specifically for reported nodes.
• Enumerated attributes stored for long periods take large amounts of space, which is out of proportion to their probable reporting value. Do not collect enumerated attributes that you do not understand or want to report on.
• Consider revising the retention rules. For example, if you want to run only daily hourly interval reports over today and yesterday, you do not need to keep hourly data for four days. You may want to keep daily data for certain classes for only one month instead of three months, and so on.
• You may sometimes need to delete data from your database without waiting for the Expiry Service to do it. For examples of how to do this, see the WRDB2SQE member.

Data is added to and deleted from your ReportCenter live database every day. Regular database reorganizations are essential to reuse and reclaim space.