File statistics and tuning: Difference between revisions

From m204wiki
Jump to navigation Jump to search
No edit summary
m (minor cleanup)
Line 1: Line 1:
==Overview==
==Overview==
<p><var class="product">Model&nbsp;204</var> 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 <var class="product">Model&nbsp;204</var> System Manager's Guide, which also provides a complete description of all <var class="product">Model&nbsp;204</var> statistics.   </p>
<var class="product">Model&nbsp;204</var> 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 <var class="book">Rocket Model&nbsp;204 System Manager's Guide</var>, which also provides a complete description of all <var class="product">Model&nbsp;204</var> statistics.  


<p>But the journal is not the only source of statistics. You can run [[VIEW command]]s against your files to get statistics on the current file parameters and usage.<p>
But the journal is not the only source of statistics. You can run <var>[[VIEW command|VIEW]]</var> commands against your files to get statistics on the current file parameters and usage.


<p>Even if not users of the products, the manuals for the real time monitor [http://sirius-software.com/maint/download/monr.pdf SIRMON] and [http://sirius-software.com/maint/download/tunr.pdf SIRTUNE] provide useful advice on monitoring and tuning Model 204 files (as well as program and system performance as a whole).</p>
Even if not users of the products, the manuals for the real time monitor [http://m204wiki.rocketsoftware.com/images/5/5e/MonrNew.pdf SirMon] and for [http://m204wiki.rocketsoftware.com/images/a/ac/TunrNew.pdf SirTune] provide useful advice on monitoring and tuning Model 204 files (as well as program and system performance as a whole).


==Journal statistics==
==Journal statistics==
 
<p>
<p>For more information on using journals in system and media recovery, see [[ Media recovery#Producing and archiving journals|Producing and archiving journals]].</p>
For more information about using journals in system and media recovery, see [[Media recovery#Producing and archiving journals|Producing and archiving journals]].</p>


===When journal statistics are printed===
===When journal statistics are printed===
Line 15: Line 15:
</p>
</p>
<p>Only those statistics whose value is not zero are printed.</p>
<p>Only those statistics whose value is not zero are printed.</p>
<p>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.</p>
<p>File statistics might not appear for every <var>[[CLOSE command|CLOSE]]</var> command issued. When many users are working with one file, the file is kept open until the last user issues a <var>CLOSE</var> command. The statistics appear along with the last <var>CLOSE</var>. System termination prints file statistics for each file still open at the end of the run.</p>
 
===Journal statistics Summary===
<p>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:</p>


===Journal statistics summary===
<p>
In the context of file monitoring (the journal produces many other statistics useful for system tuning), these statistics ought to be of interest to the File Manager:</p>


<table>
<table>
Line 40: Line 40:
<tr>
<tr>
<td>BXCHNG</td>
<td>BXCHNG</td>
<td>Changes to Ordered Index B-Tree entry length </td>
<td>Changes to Ordered Index B-tree entry length </td>
</tr>
</tr>
<tr>
<tr>
<td>BXDELE</td>
<td>BXDELE</td>
<td>Deletions of Ordered Index B-Tree attribute-value pair entries </td>
<td>Deletions of Ordered Index B-tree attribute-value pair entries </td>
</tr>
</tr>
<tr>
<tr>
<td>BXNEXT</td>
<td>BXNEXT</td>
<td>Lateral moves in an Ordered Index B-Tree entry range search </td>
<td>Lateral moves in an Ordered Index B-tree entry range search </td>
</tr>
</tr>
<tr>
<tr>
<td>BXFIND</td>
<td>BXFIND</td>
<td>Finds of Ordered Index B-Tree attribute-value pair entries </td>
<td>Finds of Ordered Index B-tree attribute-value pair entries </td>
</tr>
</tr>
<tr>
<tr>
<td>BXINSE</td>
<td>BXINSE</td>
<td>Inserts of new Ordered Index B-Tree attribute-value pair entries </td>
<td>Inserts of new Ordered Index B-tree attribute-value pair entries </td>
</tr>
</tr>
<tr>
<tr>
<td>BXSPLI</td>
<td>BXSPLI</td>
<td>Ordered Index B-Tree node splits, all levels </td>
<td>Ordered Index B-tree node splits, all levels </td>
</tr>
</tr>
<tr>
<tr>
<td>BXRFND</td>
<td>BXRFND</td>
<td>Ordered Index B-Tree refinds </td>
<td>Ordered Index B-tree refinds </td>
</tr>
</tr>
<tr>
<tr>
<td>BXFREE</td>
<td>BXFREE</td>
<td>Ordered Index B-Tree nodes emptied and freed </td>
<td>Ordered Index B-tree nodes emptied and freed </td>
</tr>
</tr>
<tr>
<tr>
Line 90: Line 90:
<td>
<td>
<p>Milliseconds of elapsed clock time that it has taken <var class="product">Model&nbsp;204</var> 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:</p>
<p>Milliseconds of elapsed clock time that it has taken <var class="product">Model&nbsp;204</var> 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:</p>
<b>M204.0440: FILE filename DISK UPDATE ABORTED</b>
<p class="code">M204.0440: FILE <i>filename</i> DISK UPDATE ABORTED</p>
</td>
</td>
</tr>
</tr>
Line 111: Line 111:
<tr>
<tr>
<td>RECDEL</td>
<td>RECDEL</td>
<td>Records, not including extension records, deleted from Table B (does not include records deleted by DELETE RECORDS or IFDSET) </td>
<td>Records, not including extension records, deleted from Table B (does not include records deleted by <var>DELETE RECORDS</var> or <var>IFDSET</var>) </td>
</tr>
</tr>
<tr>
<tr>
<td>REQ</td>
<td>REQ</td>
<td>Requests and DISPLAY commands run on the file </td>
<td>Requests and <var>DISPLAY</var> commands run on the file </td>
</tr>
</tr>
<tr>
<tr>
Line 132: Line 132:


==File Statistics==
==File Statistics==
The subsections that follow demonstrate two [[VIEW command]]s that are useful for file monitoring.


Two of the [[VIEW command]]s are useful for file monitoring:
===VIEW TABLES===
 
<p>
=== VIEW TABLES ===
A <code>VIEW TABLES</code> (or <code>V TABLES</code>) command produces output like this for the current or specified file:</p>
 
<p>A VIEW (or V) TABLES command produces (for the current or specified file):</p>


<p class="code">
<p class="code">
CURFILE   ROBTEST    CURRENT FILE                               
CURFILE   ROBTEST    CURRENT FILE                               
CURLOC   (LOCAL)    CURFILE LOCATION IF REMOTE                 
CURLOC     (LOCAL)    CURFILE LOCATION IF REMOTE                 
ASTRPPG   400        TABLE A STRINGS PER PAGE                   
ASTRPPG   400        TABLE A STRINGS PER PAGE                   
ATRPG     1          TABLE A ATTRIBUTE PAGES                     
ATRPG     1          TABLE A ATTRIBUTE PAGES                     
FVFPG     1          TABLE A FEW VALUED FIELD PAGES             
FVFPG     1          TABLE A FEW VALUED FIELD PAGES             
MVFPG     1          TABLE A MANY VALUED FIELD PAGES             
MVFPG     1          TABLE A MANY VALUED FIELD PAGES             
ASIZE     3          PAGES IN TABLE A                           
ASIZE     3          PAGES IN TABLE A                           
BSIZE     100        PAGES IN TABLE B                           
BSIZE     100        PAGES IN TABLE B                           
CSIZE     1          PAGES IN TABLE C                           
CSIZE     1          PAGES IN TABLE C                           
DSIZE     100        PAGES IN TABLE D                           
DSIZE     100        PAGES IN TABLE D                           
ESIZE     100        PAGES IN TABLE E                           
ESIZE     100        PAGES IN TABLE E                           
XSIZE     0          PAGES IN TABLE X                           
XSIZE     0          PAGES IN TABLE X                           
FREESIZE 888        PAGES IN FREE SPACE                         
FREESIZE   888        PAGES IN FREE SPACE                         
ARETRIES 0          TABLE A PAGE RETRIES                       
ARETRIES   0          TABLE A PAGE RETRIES                       
BRECPPG   256        TABLE B RECORDS PER PAGE                   
BRECPPG   256        TABLE B RECORDS PER PAGE                   
XRECPPG   256        TABLE X SLOTS PER PAGE                     
XRECPPG   256        TABLE X SLOTS PER PAGE                     
BRESERVE 17          TABLE B RESERVE SPACE                       
BRESERVE   17          TABLE B RESERVE SPACE                       
XRESERVE 17          TABLE X RESERVE SPACE PER PAGE             
XRESERVE   17          TABLE X RESERVE SPACE PER PAGE             
BREUSE   20          FREE SPACE REQUIRED TO REUSE TABLE B PAGE   
BREUSE     20          FREE SPACE REQUIRED TO REUSE TABLE B PAGE   
XREUSE   20          FREE SPACE REQUIRED TO REUSE TABLE X PAGE   
XREUSE     20          FREE SPACE REQUIRED TO REUSE TABLE X PAGE   
BPGPMSTR 0          TABLE B PAGES/MASTER AREA                   
BPGPMSTR   0          TABLE B PAGES/MASTER AREA                   
BPGPOVFL 0          TABLE B PAGES/OVERFLOW AREA                 
BPGPOVFL   0          TABLE B PAGES/OVERFLOW AREA                 
BEXTOVFL 0          TABLE B EXTRA OVERFLOW AREAS               
BEXTOVFL   0          TABLE B EXTRA OVERFLOW AREAS               
HIGHSORT -1          RECORD WITH HIGHEST SORT KEY               
HIGHSORT   -1          RECORD WITH HIGHEST SORT KEY               
BLOWPG   0          TABLE B LOWEST ACTIVE PAGE                 
BLOWPG     0          TABLE B LOWEST ACTIVE PAGE                 
BHIGHPG   0          TABLE B HIGHEST ACTIVE PAGE                 
BHIGHPG   0          TABLE B HIGHEST ACTIVE PAGE                 
XHIGHPG   -1          TABLE X HIGHEST ACTIVE PAGE                 
XHIGHPG   -1          TABLE X HIGHEST ACTIVE PAGE                 
RECRDOPT X'00'      RECORD OPTIONS                             
RECRDOPT   X'00'      RECORD OPTIONS                             
BRLIMSZ   0          TABLE B RECORD LIMIT SIZE                   
BRLIMSZ   0          TABLE B RECORD LIMIT SIZE                   
MSTRADD   10          RECORDS ADDED TO MASTER AREAS               
MSTRADD   10          RECORDS ADDED TO MASTER AREAS               
OVFLADD   0          RECORDS ADDED TO OVERFLOW AREAS             
OVFLADD   0          RECORDS ADDED TO OVERFLOW AREAS             
EOVFLADD 0          RECORDS ADDED TO EXTRA OVERFLOW AREAS       
EOVFLADD   0          RECORDS ADDED TO EXTRA OVERFLOW AREAS       
EXTNADD   0          EXTENSION RECORDS ADDED                     
EXTNADD   0          EXTENSION RECORDS ADDED                     
MSTRDEL   0          RECORDS DELETED FROM MASTER AREAS           
MSTRDEL   0          RECORDS DELETED FROM MASTER AREAS           
OVFLDEL   0          RECORDS DELETED FROM OVERFLOW AREAS         
OVFLDEL   0          RECORDS DELETED FROM OVERFLOW AREAS         
EOVFLDEL 0          RECORDS DELETED FROM EXT OVFLOW AREAS       
EOVFLDEL   0          RECORDS DELETED FROM EXT OVFLOW AREAS       
EXTNDEL   0          EXTENSION RECORDS DELETED                   
EXTNDEL   0          EXTENSION RECORDS DELETED                   
BREUSED   0          RECORDS ADDED REUSING RECORD NUMBERS       
BREUSED   0          RECORDS ADDED REUSING RECORD NUMBERS       
XREUSED   0          EXTENSION SLOTS REUSED IN TABLE X           
XREUSED   0          EXTENSION SLOTS REUSED IN TABLE X           
SPILLADD 0          SPILL INDICATOR - ADDS                       
SPILLADD   0          SPILL INDICATOR - ADDS                       
SPILLDEL 0          SPILL INDICATOR - DELETES                     
SPILLDEL   0          SPILL INDICATOR - DELETES                     
BQLEN     0          TABLE B QUEUE LENGTH                         
BQLEN     0          TABLE B QUEUE LENGTH                         
XQLEN     0          TABLE X QUEUE LENGTH                         
XQLEN     0          TABLE X QUEUE LENGTH                         
BAUTOINC 0          NUMBER OF PAGES FOR TABLE B AUTO INCREASE     
BAUTOINC   0          NUMBER OF PAGES FOR TABLE B AUTO INCREASE     
XAUTOINC 0          NUMBER OF PAGES FOR TABLE X AUTO INCREASE     
XAUTOINC   0          NUMBER OF PAGES FOR TABLE X AUTO INCREASE     
BAUTOTYP X'00'      TABLE B AUTO INCREASE OPTIONS                 
BAUTOTYP   X'00'      TABLE B AUTO INCREASE OPTIONS                 
CRETRIES 0          TABLE C PAGE RETRIES                         
CRETRIES   0          TABLE C PAGE RETRIES                         
DHIGHPG   4          TABLE D HIGHEST PAGE USED                     
DHIGHPG   4          TABLE D HIGHEST PAGE USED                     
DPGSUSED 5          TABLE D PAGES IN USE                         
DPGSUSED   5          TABLE D PAGES IN USE                         
DPGSRES   4          TABLE D PAGES RESERVED                       
DPGSRES   4          TABLE D PAGES RESERVED                       
DRESERVE 15          LIST PAGE RESERVE SPACE                       
DRESERVE   15          LIST PAGE RESERVE SPACE                       
DACTIVE   0          LIST PAGE ACTIVE                             
DACTIVE   0          LIST PAGE ACTIVE                             
PDSIZE   3          PAGES PER PROCEDURE DICTIONARY SECTION       
PDSIZE     3          PAGES PER PROCEDURE DICTIONARY SECTION       
PDSTRPPG 128        NAMES/PROCEDURE DICTIONARY PAGE LIMIT         
PDSTRPPG   128        NAMES/PROCEDURE DICTIONARY PAGE LIMIT         
OINODES   3          NO. ORDERED INDEX NODES: ALL LVLS             
OINODES   3          NO. ORDERED INDEX NODES: ALL LVLS             
OILEAVES 2          NO. ORDERED INDEX NODES: LEAVES               
OILEAVES   2          NO. ORDERED INDEX NODES: LEAVES               
OIDEPTH   2          NO. LEVELS OF ORDERED INDEX NODES             
OIDEPTH   2          NO. LEVELS OF ORDERED INDEX NODES             
OILPACT   0          ORDERED INDEX LIST PAGE ACTIVE               
OILPACT   0          ORDERED INDEX LIST PAGE ACTIVE               
OINENTRY 11          NO. ORDERED INDEX ENTRIES                     
OINENTRY   11          NO. ORDERED INDEX ENTRIES                     
OINBYTES 154        NO. ORDERED INDEX BYTES                       
OINBYTES   154        NO. ORDERED INDEX BYTES                       
DAUTOINC 0          NUMBER OF PAGES FOR TABLE D AUTO INCREASE     
DAUTOINC   0          NUMBER OF PAGES FOR TABLE D AUTO INCREASE     
EHIGHPG   0          TABLE E HIGHEST PAGE USED                     
EHIGHPG   0          TABLE E HIGHEST PAGE USED                     
EPGSUSED 0          TABLE E PAGES IN USE
EPGSUSED   0          TABLE E PAGES IN USE
</p>                         
</p>                         


 
===VIEW FPARMS (or FILE)===
=== VIEW FPARMS (or FILE) ===
<p>
 
<code>VIEW FPARMS</code> (or <code>V FPARMS</code>) command (the parameter FILE may be used as well) produces output like this for the current or specified file:</p>
<p>A VIEW (or V) FPARMS (the parameter FILE may be used as well) command produces (for the current or specified file):</p>


<p class="code">
<p class="code">
CURFILE   ROBTEST    CURRENT FILE                                             
CURFILE   ROBTEST    CURRENT FILE                                             
CURLOC   (LOCAL)    CURFILE LOCATION IF REMOTE                               
CURLOC     (LOCAL)    CURFILE LOCATION IF REMOTE                               
FILEMODL X'00'      (NONE) FILE MODEL ENFORCED                               
FILEMODL   X'00'      (NONE) FILE MODEL ENFORCED                               
FISTAT   X'00'      (OK) CURRENT STATUS OF FILE                               
FISTAT     X'00'      (OK) CURRENT STATUS OF FILE                               
FICREATE 9          RELEASE 7.1 (OR BEFORE) CREATE FORMAT                     
FICREATE   9          RELEASE 7.1 (OR BEFORE) CREATE FORMAT                     
FITRANS   8          RELEASE V2R2 TRANSFORM FORMAT                             
FITRANS   8          RELEASE V2R2 TRANSFORM FORMAT                             
FIFORMAT 5          RELEASE 8.0 (OR LATER) CURRENT FPL FORMAT                 
FIFORMAT   5          RELEASE 8.0 (OR LATER) CURRENT FPL FORMAT                 
FOPT     X'00'      FILE OPTIONS                                             
FOPT       X'00'      FILE OPTIONS                                             
FRCVOPT   X'24'      (NO CP LOGGING,NO RF LOGGING) FILE RECOVERY OPTIONS       
FRCVOPT   X'24'      (NO CP LOGGING,NO RF LOGGING) FILE RECOVERY OPTIONS       
OPENCTL   X'80'      (PUBLIC) OPEN CONTROL FLAGS                               
OPENCTL   X'80'      (PUBLIC) OPEN CONTROL FLAGS                               
PRIVDEF   X'BFFF'    DEFAULT FILE PRIVILEGES                                   
PRIVDEF   X'BFFF'    DEFAULT FILE PRIVILEGES                                   
PRCLDEF   0          DEFAULT USER CLASS FOR PROCEDURES                         
PRCLDEF   0          DEFAULT USER CLASS FOR PROCEDURES                         
SELLVL   0          DEFAULT SELECT FLS LEVEL                                 
SELLVL     0          DEFAULT SELECT FLS LEVEL                                 
READLVL   0          DEFAULT READ FLS LEVEL                                   
READLVL   0          DEFAULT READ FLS LEVEL                                   
UPDTLVL   0          DEFAULT UPDATE FLS LEVEL                                 
UPDTLVL   0          DEFAULT UPDATE FLS LEVEL                                 
ADDLVL   0          DEFAULT ADD FLS LEVEL                                     
ADDLVL     0          DEFAULT ADD FLS LEVEL                                     
SECTY     X'00'      LOW-LEVEL SECURITY IN EFFECT                             
SECTY     X'00'      LOW-LEVEL SECURITY IN EFFECT                             
FILEORG   X'01A4'    (HALFWORD LENGTHS,REUSE RECORD NUMBERS,UNORDERED,ALL FIELDS COUNTED) FILE ORGANIZATION
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                                       
FIFLAGS   X'1F'      (FIELD-LEVEL CONSTRAINTS EXIST,NEW TBL C) FILE STATUS SWITCHES                                       
VERIFY   X'00'      WRITE VERIFY                                             
VERIFY     X'00'      WRITE VERIFY                                             
IVERIFY   X'00'      WRITE VERIFY - INITIALIZATION                             
IVERIFY   X'00'      WRITE VERIFY - INITIALIZATION                             
LANGFILE US          LANGUAGE NAME FOR FILE                                   
LANGFILE   US          LANGUAGE NAME FOR FILE                                   
</p>
</p>


==Sampling the file tables==
==Sampling the file tables==
<p>
While the <code>V TABLES</code> command broadly shows the file utilization, it is often useful when making tuning decisions to see patterns of actual usage in your files. To do this, you can use one or more of the TABLE commands (<var>[[TABLEB command|TABLEB]]</var>, <var>[[TABLEC command|TABLEC]]</var>, <var>[[TABLEE command|TABLEE]]</var>, and <var>[[TABLEX command|TABLEX]]</var> commands). There is also a special form of the <var>TABLEB</var> command:  <var>[[TABLEBX command|TABLEBX]]</var>. </p>


<p>While the V TABLES command shows, at a coarse level, the file utilization, often in making tuning decisions, it is useful to see patterns of actual usage in your files. To do this, the TABLE? command can be used ([[TABLEB command|TABLEB]], [[TABLEC command|TABLEC]], [[TABLEE command|TABLEE]] and [[TABLEX command]]s). There is also a special form of the TABLEB command:  [[TABLEBX command|TABLEBX]]. </p>
<p>The <var>TABLEB</var>, <var>TABLEX</var>, and <var>TABLEBX</var> commands pertain to the data, as described in the subsections below.</p>


<p>The TABLEB, TABLEX and TABLEBX commands pertain to the data:</p>
===TABLEB command===
<p>
For small files, issuing the <var>TABLEB</var> command lets you can scan all of Table B and get a summary of the Table B usage:</p>


===[[TABLEB command]]===
<p class="code"><b>&gt; TABLEB</b>
 
<p>For small files you can scan all of Table B and get a summary of the Table B usage:</p>
 
<p class="code"><b>[[TABLEB command|TABLEB]]</b>
507  AVG. FREE SPACE PER PAGE
507  AVG. FREE SPACE PER PAGE
   2  AVG. FREE SLOTS PER PAGE
   2  AVG. FREE SLOTS PER PAGE
Line 254: Line 251:
</p>
</p>


<p>for larger files, you can sample blocks of pages to get a feel for space versus slot utilization in Table B. In particular, for non RRN files (and, where Table X is not turned on, you can expect most of your growth toward the end of the file and so (presuming BHIGHPG is 117613) issuing a command like:</p>
<p>
for larger files, you can sample blocks of pages to get a feel for space versus slot utilization in Table B. In particular, for non <var>RRN</var> files (and, where Table X is not turned on), you can expect most of your growth toward the end of the file. So (presuming <var>BHIGHPG</var> is 117613), issuing a command like the following:</p>


<p class="code">TABLEB PAGES 117550 TO 117613</p>
<p class="code">TABLEB PAGES 117550 TO 117613</p>


<p>produces details for the pages specified, and a summary for those pages, along the lines of:</p>
<p>produces details for the pages specified, and a summary for those pages, something like this:</p>


<p class="code">PAGE NO.      FREE SPACE    FREE SLOTS     
<p class="code">PAGE NO.      FREE SPACE    FREE SLOTS     
Line 272: Line 270:
  17  BRESERVE - TABLE B RESERVED SPACE PER PAGE
  17  BRESERVE - TABLE B RESERVED SPACE PER PAGE
</p>
</p>
 


 
===TABLEX command===
===[[TABLEX command]]===
<p>
<p>The TABLEX command displays the space utilization of Table X.</p>
The <var>TABLEX</var> command displays the space utilization of Table X:</p>
<p class="code">TABLEX
<p class="code"><b>&gt; TABLEX</b>
479    AVG. FREE SPACE PER PAGE
479    AVG. FREE SPACE PER PAGE
18    AVG. FREE SLOTS PER PAGE
18    AVG. FREE SLOTS PER PAGE
Line 286: Line 283:
</p>
</p>


===[[TABLEBX command]]===
===TABLEBX command===
<p>The TABLEBX command displays the space utilization for both Table B and Table X.</p>
<p>
<p class="code">TABLEBX
The <var>TABLEBX</var> command displays the space utilization for both Table B and Table X:</p>
<p class="code"><b>&gt; TABLEBX</b>
4556  AVG. FREE SPACE PER PAGE
4556  AVG. FREE SPACE PER PAGE
234    AVG. FREE SLOTS PER PAGE
234    AVG. FREE SLOTS PER PAGE
Line 302: Line 300:
17    XRESERVE - TABLE X RESERVE SPACE PER PAGE
17    XRESERVE - TABLE X RESERVE SPACE PER PAGE
</p>
</p>
<p>Additional details regarding Tables B and X can be obtained with other options of the commands such as: </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
<p class="code">TABLEX LIST


TABLEBX LIST
TABLEBX LIST


TABLEX PAGES n TO n
TABLEX PAGES <i>n</i> TO <i>n</i>


TABLEBX PAGES n TO n
TABLEBX PAGES <i>n</i> TO <i>n</i>
</p>
</p>
    
    
=== The RECLEN parameter and Table X ===
===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>
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>
<p>'''If XSIZE=0''':</p>
* TABLEB RECLEN command generates the average logical record length,
* TABLEB RECLEN command generates the average logical record length,
Line 330: Line 330:




===[[TABLEC command]]===
===TABLEC command===
 
</p>
</p>As discussed in [[Tuning the hash index]], the output of the [[TABLEC command]] is used to drive the tuning effort:</p>
As discussed in [[Tuning the hash index]], the output of the [[TABLEC command]] is used to drive the tuning effort:</p>


<p class="code">TABLEC
<p class="code"><b>&gt; TABLEC</b>
M204.0638: NUMBER OF SLOTS = 102400
M204.0638: NUMBER OF SLOTS = 102400
M204.0639: SLOTS USED = 76800
M204.0639: SLOTS USED = 76800
Line 344: Line 344:
</p>
</p>


===TABLEE command===
The output of a [[TABLEE command]] differs depending upon the <var>[[FILEORG parameter|FILEORG]]</var>, as described below.


====TABLEE on non FILEORG x'100' files====
<p>
In non <var>FILEORG</var> X'100' files, the <var>TABLEE</var> command is necessary to look for fragmented segments where [[File reorganization and table compaction#Dynamic data compactor for Table E|compaction]] is needed.</p> 


===[[TABLEE command]]===
<p>The following is an example of <var>TABLEE</var> output:</p>  
 
<p class="code"><b>&gt; IN ''filename'' TABLEE SEG</b>
The output of a [[TABLEE command]] differs depending upon the FILEORG:
 
==== TABLEE on non FILEORG x'100' files ====
 
<p>In non [[FILEORG parameter|FILEORG]] x'100' files, the TABLEE command is necessary to look for fragmented segments where [[File reorganization and table compaction#Dynamic data compactor for Table E|compaction]] is needed:</p> 
 
<p>The following is an example of TABLEE output.</p>  
<p class="code">IN ''filename'' TABLEE SEG
M204.2817: SEGMENT:  0  NUMBER OF GAPS:  364  TOTAL GAP SIZE:  1820
M204.2817: SEGMENT:  0  NUMBER OF GAPS:  364  TOTAL GAP SIZE:  1820
M204.2817: SEGMENT:  1  NUMBER OF GAPS:  229  TOTAL GAP SIZE:  1144
M204.2817: SEGMENT:  1  NUMBER OF GAPS:  229  TOTAL GAP SIZE:  1144
Line 362: Line 359:
</p>
</p>


==== TABLEE on FILEORG x'100' files ====
====TABLEE on FILEORG x'100' files====
 
<p>
<p>Because there are neither overhead pages nor issues with gaps, in [[FILEORG parameter|FILEORG]] x'100' files the [[TABLEE command]] effectively does a:</p>
Because there are neither overhead pages nor issues with gaps, in <var>FILEORG</var> X'100' files, the <var>TABLEE</var> command effectively does this:</p>
<p class="code">VIEW [[ESIZE parameter|ESIZE]] [[EHIGHPG parameter|EHIGHPG]] [[EPGSUSED parameter|EPGSUSED]]</p>
<p class="code">VIEW [[ESIZE parameter|ESIZE]] [[EHIGHPG parameter|EHIGHPG]] [[EPGSUSED parameter|EPGSUSED]]</p>


== Tuning ==
==Tuning==
 
 


===Indexing decisions: Monitoring field retrievals in FIND statements===
===Indexing decisions: Monitoring field retrievals in FIND statements===
<p>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.</p>
<p>
<p>Whenever such a field is used in a retrieval, an MS line is written to the journal with the message:</p>
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.</p>
<p>
Whenever such a field is used in a retrieval, an MS line is written to the journal with the message:</p>
<p class="code"><b></b>*** M204.0179: TABLE B SEARCH IMPLIED FOR FIELD = <var class="term">fieldname</var>
<p class="code"><b></b>*** M204.0179: TABLE B SEARCH IMPLIED FOR FIELD = <var class="term">fieldname</var>
</p>
</p>
<p>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).</p>
<p>
<p>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 <var class="product">Model&nbsp;204</var> Parameter and Command Reference.    </p>
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).</p>
<p>
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 <var class="product">Model&nbsp;204</var> Parameter and Command Reference.    </p>


===Tuning the data holdings===
===Tuning the data holdings===
 
<p>
<p>Prior to the introduction of Table X (Model 204 V7R1), one of the most difficult issues with data holdings was that the  sizes of [[Record (File architecture)#Base records|base]] and [[Record (File architecture)#Extension records|extension records]] tend to differ with the former tending to be much larger. It is also often true that, particularly in systems with a large number of updates, extension records often fragment further.</p>
Prior to the introduction of Table X (Model 204 V7R1), one of the most difficult issues with data holdings was that the  sizes of [[Record (File architecture)#Base records|base]] and [[Record (File architecture)#Extension records|extension records]] tend to differ with the former tending to be much larger. It is also often true that, particularly in systems with a large number of updates, extension records often fragment further.</p>


<p>And so, there was a tradeoff of wasted slots (when [[BRECPPG parameter|BRECPPG]] was set high enough to support lots of little extensions and so few base records fit on a page, which hurts index performance) and wasted space (where a small number of slots per page was set, and so pages with lots of extensions had lots of such space).</p>     
<p>And so, there was a tradeoff of wasted slots (when [[BRECPPG parameter|BRECPPG]] was set high enough to support lots of little extensions and so few base records fit on a page, which hurts index performance) and wasted space (where a small number of slots per page was set, and so pages with lots of extensions had lots of such space).</p>     
Line 389: Line 388:
<p>Beyond that, the growth of [[BHIGHPG parameter|BHIGHPG]] (and [[XHIGHPG parameter|XHIGHPG]]) versus the table sizes is always something to watch. It should be the aim of the File Manager to never have files fill up (note that on [[Sorted files]] the [[BEXTOVFL parameter]] is of more interest, and for [[Hash key files]], BHIGHPG has a quite different meaning (the number of full pages)).</p>
<p>Beyond that, the growth of [[BHIGHPG parameter|BHIGHPG]] (and [[XHIGHPG parameter|XHIGHPG]]) versus the table sizes is always something to watch. It should be the aim of the File Manager to never have files fill up (note that on [[Sorted files]] the [[BEXTOVFL parameter]] is of more interest, and for [[Hash key files]], BHIGHPG has a quite different meaning (the number of full pages)).</p>


===[[Tuning the Ordered Index]]===
===Tuning the Ordered Index===
To understand the underlying architecture of the ordered index, see [[Table D (File architecture)#Ordered Index|Ordered Index architecture]].


To understand the underlying architecture of the ordered index, see [[Table D (File architecture)#Ordered Index|Ordered Index Architecture]].
For advice on setting the parameters, refer to [[Field design (File management)#ORDERED and NON-ORDERED attributes|the design of Ordered fields]].  
 
For advice on setting the parameters, please refer to [[Field design (File management)#ORDERED and NON-ORDERED attributes|the design of Ordered fields]].  


For advice on ongoing monitoring and tuning of the Ordered Index, refer to [[Tuning the Ordered Index]].
For advice on ongoing monitoring and tuning of the Ordered Index, refer to [[Tuning the Ordered Index]].


===[[Tuning the hash index]]===
===Tuning the hash index===
 
<p>
<p>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.</p>
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.</p>


<p>To understand the underlying architecture of Table C, see [[Table C (File architecture)]].</p>
<p>To understand the underlying architecture of Table C, see [[Table C (File architecture)]].</p>
Line 406: Line 404:


==Reclaiming space in files==
==Reclaiming space in files==
<p>When a VISIBLE field is redefined, renamed, or deleted, space in the various <var class="product">Model&nbsp;204</var> 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.</p>
<p>
When a VISIBLE field is redefined, renamed, or deleted, space in the various <var class="product">Model&nbsp;204</var> 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.</p>
<p>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.  </p>
<p>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.  </p>
<p class="note"><b>Note:</b>  
<p class="note"><b>Note:</b>  
Value string entries for CODED and FRV fields are never removed. </p>
Value string entries for CODED and FRV fields are never removed. </p>
<p>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.  </p>
<p>
<p>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.    </p>
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.  </p>
<p>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.  </p>
<p>
<p>&nbsp;</p>
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.    </p>
<p>
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.  </p>


[[Category:File management]]
[[Category:File management]]

Revision as of 01:53, 20 February 2014

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 for 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 about 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 ought to 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

The subsections that follow demonstrate two VIEW commands that are useful for file monitoring.

VIEW TABLES

A VIEW TABLES (or V TABLES) command produces output like this 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)

VIEW FPARMS (or V FPARMS) command (the parameter FILE may be used as well) produces output like this 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 broadly shows the file utilization, it is often useful when making tuning decisions to see patterns of actual usage in your files. To do this, you can use one or more of the TABLE commands (TABLEB, TABLEC, TABLEE, and TABLEX commands). There is also a special form of the TABLEB command: TABLEBX.

The TABLEB, TABLEX, and TABLEBX commands pertain to the data, as described in the subsections below.

TABLEB command

For small files, issuing the TABLEB command lets you can scan all of Table B and get a summary of the Table B usage:

> TABLEB 507 AVG. FREE SPACE PER PAGE 2 AVG. FREE SLOTS PER PAGE 68 NUMBER OF PAGES PROCESSED 16 BRECPPG - TABLE B RECORDS PER PAGE 17 BRESERVE - TABLE B RESERVED SPACE PER PAGE

for larger files, you can sample blocks of pages to get a feel for space versus slot utilization in Table B. In particular, for non RRN files (and, where Table X is not turned on), you can expect most of your growth toward the end of the file. So (presuming BHIGHPG is 117613), issuing a command like the following:

TABLEB PAGES 117550 TO 117613

produces details for the pages specified, and a summary for those pages, something like this:

PAGE NO. FREE SPACE FREE SLOTS 117550 16 12 117551 1234 0 ... 117613 4312 11 507 AVG. FREE SPACE PER PAGE 2 AVG. FREE SLOTS PER PAGE 63 NUMBER OF PAGES PROCESSED 16 BRECPPG - TABLE B RECORDS PER PAGE 17 BRESERVE - TABLE B RESERVED SPACE PER PAGE

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

As discussed in Tuning the hash index, the output of the TABLEC command is used to drive the tuning effort:

> TABLEC M204.0638: NUMBER OF SLOTS = 102400 M204.0639: SLOTS USED = 76800 M204.0640: PERCENTAGE OF TABLE C USED = 75 M204.1833: THE TOTAL NUMBER OF PROPERTY ENTRIES IS 7496 M204.1874: THE NUMBER OF PAGES WITH SPILLS IS 33 M204.1834: 2500 PRIMARY PROPERTY ENTRIES HAVE OVERFLOW M204.1871: THE PERCENTAGE OF PROPERTY ENTRIES WITH OVERFLOW IS 33

TABLEE command

The output of a TABLEE command differs depending upon the FILEORG, as described below.

TABLEE on non FILEORG x'100' files

In non FILEORG X'100' files, the TABLEE command is necessary to look for fragmented segments where compaction is needed.

The following is an example of TABLEE output:

> IN filename TABLEE SEG M204.2817: SEGMENT: 0 NUMBER OF GAPS: 364 TOTAL GAP SIZE: 1820 M204.2817: SEGMENT: 1 NUMBER OF GAPS: 229 TOTAL GAP SIZE: 1144 M204.2815: TOTAL NUMBER OF GAPS: 593 M204.2816: TOTAL GAP SIZE: 2964

TABLEE on FILEORG x'100' files

Because there are neither overhead pages nor issues with gaps, in FILEORG X'100' files, the TABLEE command effectively does this:

VIEW ESIZE EHIGHPG EPGSUSED

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

Prior to the introduction of Table X (Model 204 V7R1), one of the most difficult issues with data holdings was that the sizes of base and extension records tend to differ with the former tending to be much larger. It is also often true that, particularly in systems with a large number of updates, extension records often fragment further.

And so, there was a tradeoff of wasted slots (when BRECPPG was set high enough to support lots of little extensions and so few base records fit on a page, which hurts index performance) and wasted space (where a small number of slots per page was set, and so pages with lots of extensions had lots of such space).

This tradeoff goes away when Table X is enables to separate the base and extension records.

Beyond that, the growth of BHIGHPG (and XHIGHPG) versus the table sizes is always something to watch. It should be the aim of the File Manager to never have files fill up (note that on Sorted files the BEXTOVFL parameter is of more interest, and for Hash key files, BHIGHPG has a quite different meaning (the number of full pages)).

Tuning the Ordered Index

To understand the underlying architecture of the ordered index, see Ordered Index architecture.

For advice on setting the parameters, refer to the design of Ordered fields.

For advice on ongoing monitoring and tuning of the Ordered Index, refer to 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, 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.