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:

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.

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

1277

65471

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

709

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

1943

2490417

One row for each data source used.

SMCOLUMNS

1500000

572

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

124

8477

1-n rows per procedure, where n is the number of 72 character segments in the procedure statement.

SMRPCWF

3000

2109

10813

One row per stored procedure from WebFOCUS.

SMAUDIT

3000000

580

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 * 1277)         1277
1 SMQUERY (1 * 2273)           2273
5 SMREQUESTS (4 * 124)         496
2 SMFROMS (2 * 1943)           3886
5 SMCOLUMNS (5 * 572)          2860
2 SMRELATIONS (2 * 2282)       4564
1 SMRPCS (1 * 1335)            1335
1 SMRPCREQUESTS (1 * 124)      124
1 SMRPCWF (1 * 2109)           2109
                              ------
                               18942 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:

*The Formula for KB:

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.


WebFOCUS