The recommended size of the relational 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.
Relational Database Table Name |
Number of Rows |
Row Size in Characters |
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 sub servers 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
A suggested method on how to determine the average storage needed would be to take the average of the least complex monitored request and the most complex, and multiply that by the number of requests that could be monitored in a month or year.
The size to 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 3 bytes to represent a character. All table sizes have been rounded up as necessary.
|
WebFOCUS |