File statistics and tuning - Revision history

JAL: /* VIEW FPARMS (or FILE) */ misc formatting, shrink table width

2016-10-26T22:39:00Z

VIEW FPARMS (or FILE): misc formatting, shrink table width

← Older revision		Revision as of 22:39, 26 October 2016
Line 205:		Line 205:
	===VIEW FPARMS (or FILE)===		===VIEW FPARMS (or FILE)===
	<p>		<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>		<code>VIEW FPARMS</code> (or <code>V FPARMS</code>) command (the parameter <var>FILE</var> may be used as well) produces output like this for the current or specified file:</p>

	<p class="code">		<p class="code" style="font-size:90%">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>

JAL: /* Overview */ link repair

2016-10-26T22:30:02Z

Overview: link repair

← Older revision		Revision as of 22:30, 26 October 2016
Line 4:		Line 4:
	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.		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.

	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).		Even if not users of the products, the documentation for the real time monitor <var class="product">[[SirMon]]</var> and for <var class="product">[[SirTune]]</var> provides useful advice on monitoring and tuning Model 204 files (as well as program and system performance as a whole).

	==Journal statistics==		==Journal statistics==

JAL: /* VIEW TABLES */ typo

2016-10-26T22:26:32Z

VIEW TABLES: typo

← Older revision		Revision as of 22:26, 26 October 2016
Line 138:		Line 138:
	A <code>VIEW TABLES</code> (or <code>V TABLES</code>) command produces output like this for the current or specified file:</p>		A <code>VIEW TABLES</code> (or <code>V TABLES</code>) command produces output like this 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
Line 202:		Line 201:
	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)===

JAL: remove Sys Man guide

2014-10-31T21:08:20Z

remove Sys Man guide

← Older revision		Revision as of 21:08, 31 October 2014
Line 1:		Line 1:
	==Overview==		==Overview==
	<var class="product">Model 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 204 System Manager's Guide</var>~~, which also provides a complete description of all <var class="product">Model 204</var> statistics.		<var class="product">Model 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 [[Using system statistics#Overview\|Using system statistics]], which also provides a complete description of all <var class="product">Model 204</var> statistics.

	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.		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.

ELowell at 16:52, 12 May 2014

2014-05-12T16:52:18Z

← Older revision		Revision as of 16:52, 12 May 2014
Line 403:		Line 403:
	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, refer to [[Field design#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]].

DmeWiccan: Automatically generated page update

2014-05-09T22:45:49Z

Automatically generated page update

← Older revision		Revision as of 22:45, 9 May 2014
Line 430:		Line 430:
	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>		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:Model 204 files]]

JAL: /* File Statistics */

2014-03-06T21:40:07Z

File Statistics

← Older revision		Revision as of 21:40, 6 March 2014
Line 131:		Line 131:
	</table>		</table>

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

JAL: /* Reclaiming space in files */

2014-02-21T21:39:35Z

Reclaiming space in files

← Older revision		Revision as of 21:39, 21 February 2014
Line 417:		Line 417:
	==Reclaiming space in files==		==Reclaiming space in files==
	<p>		<p>
	When a VISIBLE field is redefined, renamed, or deleted, space in the various <var class="product">Model 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>		When a <var>VISIBLE</var> field is redefined, renamed, or deleted, space in the various <var class="product">Model 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 <var>NUMERIC RANGE</var> attribute, the auxiliary names also are removed. This also is true when a field is redefined from <var>NUMERIC RANGE</var> to <var>NON-RANGE</var>.</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 <var>CODED</var> and <var>FRV</var> fields are never removed. </p>
	<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>		Index entries are removed from Table C for the old name of a renamed field, for a deleted field, and for redefinitions to <var>NON-KEY</var>, <var>NON-RANGE</var>, or <var>NON-FRV</var>. </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 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>		<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>		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]]

JAL: /* Tuning */

2014-02-21T21:35:38Z

Tuning

← Older revision		Revision as of 21:35, 21 February 2014
Line 386:		Line 386:
	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>		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 204~~</var> ~~Parameter and Command Reference~~. </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 issuing <code>TIME REQUEST</code> (see the <var>[[TIME command\|TIME]]</var> command). </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>
	<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>		Therefore, wasted slots (when <var>[[BRECPPG parameter\|BRECPPG]]</var> was set high enough to support many small extensions, few base records fit on a page, which hurt index performance) were traded off with wasted space (setting a small number of slots per page meant pages with lots of extensions had lots of such space).</p>
			<p>
	<p>This tradeoff goes away when [[Table X (File architecture)\|Table X]] is ~~enables~~ to separate the [[Record (File architecture)#Base records\|base]] and [[Record (File architecture)#Extension records\|extension records]].</p>		This tradeoff goes away when [[Table X (File architecture)\|Table X]] is enabled to separate the [[Record (File architecture)#Base records\|base]] and [[Record (File architecture)#Extension records\|extension records]].</p>
			<p>
			Beyond that, the growth of <var>[[BHIGHPG parameter\|BHIGHPG]]</var> (and <var>[[XHIGHPG parameter\|XHIGHPG]]</var>) versus the table sizes is always something to watch. It should be the aim of the File Manager to never have files fill up. </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>		<p class="note"><b>Note:</b> On [[Sorted files\|sorted files]], the <var>[[BEXTOVFL parameter\|BEXTOVFL]]</var> parameter is of more interest, and for [[Hash key files\|hash key files]], <var>BHIGHPG</var> has a quite different meaning (the number of full pages).</p>

	===Tuning the Ordered Index===		===Tuning the Ordered Index===
Line 407:		Line 409:
	===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 cannot be dynamically increased, so 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>

	<p>For advice on monitoring and tuning Table C, refer to [[Tuning the hash index]].</p>		<p>For advice about monitoring and tuning Table C, refer to [[Tuning the hash index]].</p>

	==Reclaiming space in files==		==Reclaiming space in files==

JAL: minor cleanup

2014-02-21T02:37:04Z

minor cleanup

← Older revision		Revision as of 02:37, 21 February 2014
Line 252:		Line 252:

	<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>		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), if you issue 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, something like this:</p>		<p>
			You get 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 273:		Line 274:
	===TABLEX command===		===TABLEX command===
	<p>		<p>
	The <var>TABLEX</var> command displays the space utilization of Table X:</p>		The <var>TABLEX</var> command displays the space utilization of Table X. For example:</p>
	<p class="code"><b>> TABLEX</b>		<p class="code"><b>> TABLEX</b>
	479 AVG. FREE SPACE PER PAGE		479 AVG. FREE SPACE PER PAGE
Line 285:		Line 286:
	===TABLEBX command===		===TABLEBX command===
	<p>		<p>
	The <var>TABLEBX</var> command displays the space utilization for both Table B and Table X:</p>		The <var>TABLEBX</var> command displays the space utilization for both Table B and Table X. For example:</p>
	<p class="code"><b>> TABLEBX</b>		<p class="code"><b>> TABLEBX</b>
	4556 AVG. FREE SPACE PER PAGE		4556 AVG. FREE SPACE PER PAGE
Line 301:		Line 302:
	</p>		</p>
	<p>		<p>
	~~Additional~~ details regarding Tables B and X ~~can be obtained with~~ other options of the commands such as: </p>		You can obtain additional details regarding Tables B and X using other options of the TABLE commands, such as: </p>
	<p class="code">TABLEX LIST		<p class="code">TABLEX LIST

Line 313:		Line 314:
	===The RECLEN parameter and Table X===		===The RECLEN parameter and Table X===
	<p>		<p>
	When the 'RECLEN' option ~~is used on~~ TABLEB or TABLEX, the output varies (as of V7R1.0), as follows:</p>		When you use the <var>RECLEN</var> option of <var>TABLEB</var> or <var>TABLEX</var>, the output varies (as of V7R1), as follows:</p>
	<p>'''If XSIZE=0''':</p>		<p>
	* TABLEB RECLEN ~~command~~ generates the average logical record length,		'''If XSIZE = 0:'''</p>
	meaning a base record and its extension records, all in Table B.		<ul>
	* TABLEX RECLEN ~~command~~ generates the following message:		<li><var>TABLEB RECLEN</var> generates the average logical record length,
	<p class="code">***M204.2768: FILE filename HAS NO TABLEX PAGES</p>		meaning a base record and its extension records, all in Table B.</li>
	* TABLEBX RECLEN ~~also~~ generates the average ~~logical~~ record length.
			<li><var>TABLEBX RECLEN</var> also generates the average logical record length.
			<p>
			In addition, <var>TABLEX RECLEN</var> generates the following message: </p>
			<p class="code">***M204.2768: FILE <i>filename</i> HAS NO TABLEX PAGES</p></li>
			</ul>

			<p>
			'''If XSIZE > 0:'''</p>
			<ul>
			<li><var>TABLEB RECLEN</var> generates the average base record length in
			Table B. No Table X extension record information is included.</li>

	<p>~~'''If XSIZE~~>~~0'''~~</p>		<li><var>TABLEX RECLEN</var> generates the average individual extension
	* TABLEB RECLEN command generates the average base record length in		record length in Table X.</li>
	~~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.~~

			<li><var>TABLEBX RECLEN</var> command generates the average logical records length,
			meaning the base record in Table B and the extension records in Table X.</li>
			</ul>

	===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. For example:</p>

	<p class="code"><b>> TABLEC</b>		<p class="code"><b>> TABLEC</b>
Line 345:		Line 355:

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

	====TABLEE on non FILEORG x'100' files====		====TABLEE on non FILEORG X'100' files====
	<p>		<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>		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>
Line 359:		Line 369:
	</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 <var>FILEORG</var> X'100' files, the <var>TABLEE</var> command effectively does this:</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>