Usage Monitoring RDBMS Table Sizing

The recommended size of the relational database tables will vary from one implementation to another. The following are the major factors to consider:

Type of configuration: Standard or Many-to-One.
How many requests are anticipated from all configured servers?
The level of monitoring that will be used on each server:
- There are two levels of detail in Stored Procedure monitoring.
- There are seven levels of detail in Command monitoring.

For more information on what levels of detail are available, see Configuring and Administering Resource Management. The sizing example below assumes that all levels are being monitored.

The storage values shown in the table below are estimates. Resource Management uses VARCHAR for character fields that are greater than or equal to 120 bytes in length, except for the SMREQUESTS and SMRPCREQUESTS tables, which use 72 byte VARCHAR fields. All VARCHAR fields are the ACTUAL size plus 2 bytes. This size difference is used in the Table Size chart but not in the tables used to describe each field.

Note: The table with a $ is used by Resource Governor. It must be allocated, but the size can be reduced if you are not using Resource Governor.

Table Name	Number of Rows	Row Size	Table Size in KB*	Notes
SMSERVERS	1	220	1	1-n rows. Where n is the number of configured servers in the repository.
SMSESSIONS	30000	1111	56960	One row per session.
SMQUERY	300000	2273	1165356	One row per data request.
SMREQUESTS	3000000	124	635724	1-n rows per data request, where n is the number of 72 character segments in the data request.
SMGOVERN$	15000	710	18175	One row per governed request.
SMREMOTES	20000	251	8579	1-n rows per suffix=EDA data request, where n is the number of subservers used in the request.
SMFROMS	750000	2522	3232544	One row for each data source used.
SMCOLUMNS	1500000	573	1466309	One row per column returned or held.
SMFUNCTIONS	50000	1188	101514	One row per function used.
SMRELATIONS	150000	2282	584985	One row per relational test.
SMRPCS	35000	1335	79852	One row per stored procedure.
SMRPCREQUESTS	40000	1076	73555	One row per parameter pass to the procedure. For example, EX MYCAR PARM1=A, PARM2=B will create two rows. If the procedure is executed without any parameters, then no rows are created.
SMRPCWF	35000	2624	156953	One row per stored procedure from WebFOCUS.
SMAUDIT	3000000	581	2973633	1-n rows per translated request or message issued in a session.

Example of a WebFOCUS procedure:

EX MYWFFEX PAY=50000

Where MYWFFEX contains:

SELECT E.NAME, E.ADDRESS, E.JOBTITLE, J.JOBDESCRIPTION, J.PAY
FROM EMPLOYEE E, JOBS J
WHERE E.JOBTITLE = J.JOBTITLE AND J.PAY < &PAY
ORDER BY J.JOBTITLE, J.PAY;

Stores:

1 SMSESSION (1 * 1111)         1111
1 SMQUERY (1 * 2273)           2273
5 SMREQUESTS (4 * 124)          496
2 SMFROMS (2 * 2522)           5044
5 SMCOLUMNS (5 * 573)          2865
2 SMRELATIONS (2 * 2282)       4564
1 SMRPCS (1 * 1335)            1335
1 SMRPCREQUESTS (1 * 1076)     1076
1 SMRPCWF (1 * 2624)           2624
                              ------
                              21388 bytes of storage

To determine the average storage needed, take the average of the least complex monitored request and the most complex, and multiply that value by the number of requests that could be monitored in a month or year.

The size of the table is calculated using a CP size, or character size, of 1. If the server is using a UNICODE code page (for example, CP65001), the size of the rows should be recalculated using a CP size of 3 to obtain the correct table size. The formula for calculating the table size is defined as:

(Rows * (Size * CP size) * 1.75) / 1024 = KB.

When creating a Db2 TABLESPACE, this value should be used for PRIQTY.

If the RDBMS being used for the repository uses a different byte size for nvarchar fields, use the value used by the RDBMS. A general rule is that nvarchar fields will use three bytes to represent a character. All table sizes have been rounded up as necessary.