File statistics and tuning: Difference between revisions
No edit summary |
|||
Line 234: | Line 234: | ||
LANGFILE US LANGUAGE NAME FOR FILE | LANGFILE US LANGUAGE NAME FOR FILE | ||
</p> | </p> | ||
==Sampling the file tables== | |||
<p>While the V TABLES command shows, at a coarse level, the file utilization, often it is useful to see patterns of actual usage in your files. To do this, the TABLE? command can be used ([[TABLEB command|Table B]], [[TABLEC command|TABLEC]], [[TABLEE command|TABLEE]] and [[TABLEX command]]s). There is also a special form of the TABLEB command: [[TABLEBX command|TABLEBX]]. </p> | |||
===[[TABLEB command]]=== | |||
===[[TABLEX command]]=== | |||
<p>The TABLEX command displays the space utilization of Table X.</p> | |||
<p class="code">TABLEX | |||
479 AVG. FREE SPACE PER PAGE | |||
18 AVG. FREE SLOTS PER PAGE | |||
13 NUMBER OF PAGES PROCESSED | |||
100 XRECPPG - TABLE X EXTENSION SLOTS PER PAGE | |||
17 XRESERVE - TABLE X RESERVE SPACE PER PAGE | |||
</p> | |||
===[[TABLEBX command]]=== | |||
<p>The TABLEBX command displays the space utilization for both Table B and Table X.</p> | |||
<p class="code">TABLEBX | |||
4556 AVG. FREE SPACE PER PAGE | |||
234 AVG. FREE SLOTS PER PAGE | |||
2 NUMBER OF PAGES PROCESSED | |||
500 BRECPPG - TABLE B RECORDS PER PAGE | |||
17 BRESERVE - TABLE B RESERVED SPACE PER PAGE | |||
479 AVG. FREE SPACE PER PAGE | |||
18 AVG. FREE SLOTS PER PAGE | |||
13 NUMBER OF PAGES PROCESSED | |||
100 XRECPPG - TABLE X EXTENSION SLOTS PERPAGE | |||
17 XRESERVE - TABLE X RESERVE SPACE PER PAGE | |||
</p> | |||
<p>Additional details regarding Tables B and X can be obtained with other options of the commands such as: </p> | |||
<p class="code">TABLEX LIST | |||
TABLEBX LIST | |||
TABLEX PAGES n TO n | |||
TABLEBX PAGES n TO n | |||
</p> | |||
=== The RECLEN parameter and Table X === | |||
<p>When the 'RECLEN' option is used on TABLEB or TABLEX, the output varies (as of V7R1.0), as follows:</p> | |||
<p>'''If XSIZE=0''':</p> | |||
* TABLEB RECLEN command generates the average logical record length, | |||
meaning a base record and its extension records, all in Table B. | |||
* TABLEX RECLEN command generates the following message: | |||
<p class="code">***M204.2768: FILE filename HAS NO TABLEX PAGES</p> | |||
* TABLEBX RECLEN also generates the average logical record length. | |||
<p>'''If XSIZE>0'''</p> | |||
* TABLEB RECLEN command generates the average base record length in | |||
Table B. No Table X extension record information is included. | |||
* TABLEX RECLEN command generates the average individual extension | |||
record length in Table X. | |||
* TABLEBX RECLEN command generates the average logical records length, | |||
meaning the base record in Table B and the extension records in Table X. | |||
===[[TABLEC command]]=== | |||
===[[TABLEE command]]=== | |||
== Tuning == | == Tuning == |
Revision as of 00:37, 27 April 2013
Overview
Model 204 maintains a variety of utilization statistics for each user and file active during a particular run. These statistics are written to the journal, as explained in the Rocket Model 204 System Manager's Guide, which also provides a complete description of all Model 204 statistics.
But the journal is not the only source of statistics. You can run VIEW commands against your files to get statistics on the current file parameters and usage.
Even if not users of the products, the manuals for the real time monitor SIRMON and SIRTUNE provide useful advice on monitoring and tuning Model 204 files (as well as program and system performance as a whole).
Journal statistics
For more information on using journals in system and media recovery, see Producing and archiving journals.
When journal statistics are printed
File statistics are kept for every file opened during the run. These statistics summarize the activity on the file for all users. File statistics normally are written to the journal when the file is closed in lines that, when printed, start with:
ST $$$ FILE='filename'
Only those statistics whose value is not zero are printed.
File statistics might not appear for every CLOSE command issued. When many users are working with one file, the file is kept open until the last user issues a CLOSE command. The statistics appear along with the last CLOSE. System termination prints file statistics for each file still open at the end of the run.
Journal statistics Summary
In the context of file monitoring (the journal produces many other statistics useful for system tuning) these statistics should be of interest to the File Manager:
Term | Number of... |
---|---|
BADD | Fields added to Table B |
BCHG | Fields changed in place in Table B |
BDEL | Fields deleted from Table B |
BXCHNG | Changes to Ordered Index B-Tree entry length |
BXDELE | Deletions of Ordered Index B-Tree attribute-value pair entries |
BXNEXT | Lateral moves in an Ordered Index B-Tree entry range search |
BXFIND | Finds of Ordered Index B-Tree attribute-value pair entries |
BXINSE | Inserts of new Ordered Index B-Tree attribute-value pair entries |
BXSPLI | Ordered Index B-Tree node splits, all levels |
BXRFND | Ordered Index B-Tree refinds |
BXFREE | Ordered Index B-Tree nodes emptied and freed |
DIRRCD | Table B records searched |
DKRD | Disk reads from the file |
DKWR | Disk writes to the file |
DUPDTS | Records written to the deferred update data sets for the file |
DKUPTIME |
Milliseconds of elapsed clock time that it has taken Model 204 to write a file's pages to disk and to mark it "physically consistent" on disk. Includes all time spent writing pages, even if disk updates are interrupted, as indicated by the message: M204.0440: FILE filename DISK UPDATE ABORTED |
IXADD | Index entries added to Tables C and D, including attempts to add duplicates |
IXDEL | Index entries deleted from Tables C and D |
PNDGTIME | Milliseconds of elapsed clock time that a file waited to be written to disk, after the last update unit completed |
RECADD | Records, not including extension records, started in Table B |
RECDEL | Records, not including extension records, deleted from Table B (does not include records deleted by DELETE RECORDS or IFDSET) |
REQ | Requests and DISPLAY commands run on the file |
RETRYA | Page retries in Table A |
RETRYC | Page retries in Table C |
UPDTTIME | Milliseconds of elapsed clock time that an open file has been part of at least one update unit |
File Statistics
Two of the VIEW commands are useful for file monitoring:
VIEW TABLES
A VIEW (or V) TABLES command produces (for the current or specified file):
CURFILE ROBTEST CURRENT FILE CURLOC (LOCAL) CURFILE LOCATION IF REMOTE ASTRPPG 400 TABLE A STRINGS PER PAGE ATRPG 1 TABLE A ATTRIBUTE PAGES FVFPG 1 TABLE A FEW VALUED FIELD PAGES MVFPG 1 TABLE A MANY VALUED FIELD PAGES ASIZE 3 PAGES IN TABLE A BSIZE 100 PAGES IN TABLE B CSIZE 1 PAGES IN TABLE C DSIZE 100 PAGES IN TABLE D ESIZE 100 PAGES IN TABLE E XSIZE 0 PAGES IN TABLE X FREESIZE 888 PAGES IN FREE SPACE ARETRIES 0 TABLE A PAGE RETRIES BRECPPG 256 TABLE B RECORDS PER PAGE XRECPPG 256 TABLE X SLOTS PER PAGE BRESERVE 17 TABLE B RESERVE SPACE XRESERVE 17 TABLE X RESERVE SPACE PER PAGE BREUSE 20 FREE SPACE REQUIRED TO REUSE TABLE B PAGE XREUSE 20 FREE SPACE REQUIRED TO REUSE TABLE X PAGE BPGPMSTR 0 TABLE B PAGES/MASTER AREA BPGPOVFL 0 TABLE B PAGES/OVERFLOW AREA BEXTOVFL 0 TABLE B EXTRA OVERFLOW AREAS HIGHSORT -1 RECORD WITH HIGHEST SORT KEY BLOWPG 0 TABLE B LOWEST ACTIVE PAGE BHIGHPG 0 TABLE B HIGHEST ACTIVE PAGE XHIGHPG -1 TABLE X HIGHEST ACTIVE PAGE RECRDOPT X'00' RECORD OPTIONS BRLIMSZ 0 TABLE B RECORD LIMIT SIZE MSTRADD 10 RECORDS ADDED TO MASTER AREAS OVFLADD 0 RECORDS ADDED TO OVERFLOW AREAS EOVFLADD 0 RECORDS ADDED TO EXTRA OVERFLOW AREAS EXTNADD 0 EXTENSION RECORDS ADDED MSTRDEL 0 RECORDS DELETED FROM MASTER AREAS OVFLDEL 0 RECORDS DELETED FROM OVERFLOW AREAS EOVFLDEL 0 RECORDS DELETED FROM EXT OVFLOW AREAS EXTNDEL 0 EXTENSION RECORDS DELETED BREUSED 0 RECORDS ADDED REUSING RECORD NUMBERS XREUSED 0 EXTENSION SLOTS REUSED IN TABLE X SPILLADD 0 SPILL INDICATOR - ADDS SPILLDEL 0 SPILL INDICATOR - DELETES BQLEN 0 TABLE B QUEUE LENGTH XQLEN 0 TABLE X QUEUE LENGTH BAUTOINC 0 NUMBER OF PAGES FOR TABLE B AUTO INCREASE XAUTOINC 0 NUMBER OF PAGES FOR TABLE X AUTO INCREASE BAUTOTYP X'00' TABLE B AUTO INCREASE OPTIONS CRETRIES 0 TABLE C PAGE RETRIES DHIGHPG 4 TABLE D HIGHEST PAGE USED DPGSUSED 5 TABLE D PAGES IN USE DPGSRES 4 TABLE D PAGES RESERVED DRESERVE 15 LIST PAGE RESERVE SPACE DACTIVE 0 LIST PAGE ACTIVE PDSIZE 3 PAGES PER PROCEDURE DICTIONARY SECTION PDSTRPPG 128 NAMES/PROCEDURE DICTIONARY PAGE LIMIT OINODES 3 NO. ORDERED INDEX NODES: ALL LVLS OILEAVES 2 NO. ORDERED INDEX NODES: LEAVES OIDEPTH 2 NO. LEVELS OF ORDERED INDEX NODES OILPACT 0 ORDERED INDEX LIST PAGE ACTIVE OINENTRY 11 NO. ORDERED INDEX ENTRIES OINBYTES 154 NO. ORDERED INDEX BYTES DAUTOINC 0 NUMBER OF PAGES FOR TABLE D AUTO INCREASE EHIGHPG 0 TABLE E HIGHEST PAGE USED EPGSUSED 0 TABLE E PAGES IN USE
VIEW FPARMS (or FILE)
A VIEW (or V) FPARMS (the parameter FILE may be used as well) command produces (for the current or specified file):
CURFILE ROBTEST CURRENT FILE CURLOC (LOCAL) CURFILE LOCATION IF REMOTE FILEMODL X'00' (NONE) FILE MODEL ENFORCED FISTAT X'00' (OK) CURRENT STATUS OF FILE FICREATE 9 RELEASE 7.1 (OR BEFORE) CREATE FORMAT FITRANS 8 RELEASE V2R2 TRANSFORM FORMAT FIFORMAT 5 RELEASE 8.0 (OR LATER) CURRENT FPL FORMAT FOPT X'00' FILE OPTIONS FRCVOPT X'24' (NO CP LOGGING,NO RF LOGGING) FILE RECOVERY OPTIONS OPENCTL X'80' (PUBLIC) OPEN CONTROL FLAGS PRIVDEF X'BFFF' DEFAULT FILE PRIVILEGES PRCLDEF 0 DEFAULT USER CLASS FOR PROCEDURES SELLVL 0 DEFAULT SELECT FLS LEVEL READLVL 0 DEFAULT READ FLS LEVEL UPDTLVL 0 DEFAULT UPDATE FLS LEVEL ADDLVL 0 DEFAULT ADD FLS LEVEL SECTY X'00' LOW-LEVEL SECURITY IN EFFECT FILEORG X'01A4' (HALFWORD LENGTHS,REUSE RECORD NUMBERS,UNORDERED,ALL FIELDS COUNTED) FILE ORGANIZATION FIFLAGS X'1F' (FIELD-LEVEL CONSTRAINTS EXIST,NEW TBL C) FILE STATUS SWITCHES VERIFY X'00' WRITE VERIFY IVERIFY X'00' WRITE VERIFY - INITIALIZATION LANGFILE US LANGUAGE NAME FOR FILE
Sampling the file tables
While the V TABLES command shows, at a coarse level, the file utilization, often it is useful to see patterns of actual usage in your files. To do this, the TABLE? command can be used (Table B, TABLEC, TABLEE and TABLEX commands). There is also a special form of the TABLEB command: TABLEBX.
TABLEB command
TABLEX command
The TABLEX command displays the space utilization of Table X.
TABLEX 479 AVG. FREE SPACE PER PAGE 18 AVG. FREE SLOTS PER PAGE 13 NUMBER OF PAGES PROCESSED 100 XRECPPG - TABLE X EXTENSION SLOTS PER PAGE 17 XRESERVE - TABLE X RESERVE SPACE PER PAGE
TABLEBX command
The TABLEBX command displays the space utilization for both Table B and Table X.
TABLEBX 4556 AVG. FREE SPACE PER PAGE 234 AVG. FREE SLOTS PER PAGE 2 NUMBER OF PAGES PROCESSED 500 BRECPPG - TABLE B RECORDS PER PAGE 17 BRESERVE - TABLE B RESERVED SPACE PER PAGE 479 AVG. FREE SPACE PER PAGE 18 AVG. FREE SLOTS PER PAGE 13 NUMBER OF PAGES PROCESSED 100 XRECPPG - TABLE X EXTENSION SLOTS PERPAGE 17 XRESERVE - TABLE X RESERVE SPACE PER PAGE
Additional details regarding Tables B and X can be obtained with other options of the commands such as:
TABLEX LIST TABLEBX LIST TABLEX PAGES n TO n TABLEBX PAGES n TO n
The RECLEN parameter and Table X
When the 'RECLEN' option is used on TABLEB or TABLEX, the output varies (as of V7R1.0), as follows:
If XSIZE=0:
- TABLEB RECLEN command generates the average logical record length,
meaning a base record and its extension records, all in Table B.
- TABLEX RECLEN command generates the following message:
***M204.2768: FILE filename HAS NO TABLEX PAGES
- TABLEBX RECLEN also generates the average logical record length.
If XSIZE>0
- TABLEB RECLEN command generates the average base record length in
Table B. No Table X extension record information is included.
- TABLEX RECLEN command generates the average individual extension
record length in Table X.
- TABLEBX RECLEN command generates the average logical records length,
meaning the base record in Table B and the extension records in Table X.
TABLEC command
TABLEE command
Tuning
Indexing decisions: Monitoring field retrievals in FIND statements
If you have defined a field as NON-KEY, NON-ORDERED, and NON-RANGE, you can monitor the use of the field in FIND statements using information written to the journal data set and redefine it as KEY, NUMERIC RANGE, or ORDERED when necessary.
Whenever such a field is used in a retrieval, an MS line is written to the journal with the message:
*** M204.0179: TABLE B SEARCH IMPLIED FOR FIELD = fieldname
This message indicates that a direct search of the data set might be required. It does not indicate whether the search was actually performed or how many records were searched (which depends on the results of any inverted conditions in the FIND statement).
The DIRRCD statistic provides a count of the total number of Table B records searched directly by FIND. DIRRCD appears in the journal and is accumulated by file, by user, by request, and for the entire system. DIRRCD also can be displayed by the TIME REQUEST command, described in the Model 204 Parameter and Command Reference.
Tuning the data holdings
Tuning the Ordered Index
Tuning the Hash Index
Because Table C is hashed, it can not dynamically increased, so the monitoring and tuning the hash index is critical to avoid file full conditions.
To understand the underlying architecture of Table C, see Table C (File Architecture)
.
For advice on monitoring and tuning Table C, please refer to: Tuning the Hash Index.
Reclaiming space in files
When a VISIBLE field is redefined, renamed, or deleted, space in the various Model 204 file tables is reclaimed for future reuse. The old name of a renamed field or the name of a deleted field is removed from Table A. If that field had the NUMERIC RANGE parameter, the auxiliary names also are removed. This also is true when a field is redefined from NUMERIC RANGE to NON-RANGE.
When a field name is deleted, the space used by that string in Table A is almost completely reclaimed (all but two bytes of overhead). The count of strings stored on the page is not decremented.
Note
Value string entries for CODED and FRV fields are never removed.
Index entries are removed from Table C for the old name of a renamed field, for a deleted field, and for redefinitions to NON-KEY, NON-RANGE, or NON-FRV.
Index entry spaces for the old name of a renamed field in Table D are reused for the new name. Index entries are removed for deleted fields and for certain redefined fields.
Field entries in Table B are removed for deleted fields that are not preallocated. Occurrences of preallocated fields are deleted, but their space is not deleted.