Sorted files - Revision history

JAL: /* SORT RECORDS statement */ link repair

2017-09-28T23:31:51Z

SORT RECORDS statement: link repair

← Older revision		Revision as of 23:31, 28 September 2017
Line 37:		Line 37:
	The <var>SORT RECORDS</var> statement does have the drawback that the sort is performed each time you run the User Language procedure. Because each sort takes time and system resources, you might find it more efficient to use a sort key, ordered index, or an external sort package to sort large amounts of data. </p>		The <var>SORT RECORDS</var> statement does have the drawback that the sort is performed each time you run the User Language procedure. Because each sort takes time and system resources, you might find it more efficient to use a sort key, ordered index, or an external sort package to sort large amounts of data. </p>
	<p>		<p>
	For the Host Language Interface, user programs can invoke the <var class="product">Model 204</var> internal sort to sort data passed to them by a call that performs a Find function by calling <var~~>IFSORT</var>. See the <var class="book"~~>[[~~Media:M204_HLIReference_V75.pdf~~\|~~Rocket Model204 Host Language Interface Reference Manual~~]]</var>.</p>		For the Host Language Interface, user programs can invoke the <var class="product">Model 204</var> internal sort to sort data passed to them by a call that performs a Find function by calling <var>[[IFSORT (HLI function)\|IFSORT]]</var>. </p>

	===SORT RECORD KEYS statement===		===SORT RECORD KEYS statement===

JAL: add link

2015-03-02T20:17:22Z

add link

← Older revision		Revision as of 20:17, 2 March 2015
Line 37:		Line 37:
	The <var>SORT RECORDS</var> statement does have the drawback that the sort is performed each time you run the User Language procedure. Because each sort takes time and system resources, you might find it more efficient to use a sort key, ordered index, or an external sort package to sort large amounts of data. </p>		The <var>SORT RECORDS</var> statement does have the drawback that the sort is performed each time you run the User Language procedure. Because each sort takes time and system resources, you might find it more efficient to use a sort key, ordered index, or an external sort package to sort large amounts of data. </p>
	<p>		<p>
	For the Host Language Interface, user programs can invoke the <var class="product">Model 204</var> internal sort to sort data passed to them by a call that performs a Find function by calling <var>IFSORT</var>. See the <var class="book">Rocket Model204 Host Language Interface Reference Manual</var>.</p>		For the Host Language Interface, user programs can invoke the <var class="product">Model 204</var> internal sort to sort data passed to them by a call that performs a Find function by calling <var>IFSORT</var>. See the <var class="book">[[Media:M204_HLIReference_V75.pdf\|Rocket Model204 Host Language Interface Reference Manual]]</var>.</p>

	===SORT RECORD KEYS statement===		===SORT RECORD KEYS statement===

JAL: more conversion cleanup

2015-01-14T21:26:13Z

more conversion cleanup

← Older revision		Revision as of 21:26, 14 January 2015
Line 97:		Line 97:
	==Parameters for sorted files==		==Parameters for sorted files==
	<p>		<p>
	The following subsections describe parameters that are relevant to sorted files. </p>		The following subsections describe parameters that are relevant to sorted files. </p>

	===FILEORG parameter===		===FILEORG parameter===
Line 107:		Line 107:
	<th>Meaning</th>		<th>Meaning</th>
	</tr>		</tr>

	<tr>		<tr>
	<td align="right"> X'04'</td>		<td align="right"> X'04'</td>
	<td>Reuse record numbers (RRN). Record numbers of deleted records are reused for new records added, if the record number is available on the Table B page to which the new record is being added. </td>		<td>Reuse record numbers (<var>RRN</var>). Record numbers of deleted records are reused for new records added, if the record number is available on the Table B page to which the new record is being added. </td></tr>
	</tr>

	<tr>		<tr>
Line 116:		Line 116:
	<td>Sort key required. Every record in the file must have a value for the sort key, or a compilation error results.</td>		<td>Sort key required. Every record in the file must have a value for the sort key, or a compilation error results.</td>
	</tr>		</tr>

	<tr>		<tr>
	<td align="right"> X'01'</td>		<td align="right"> X'01'</td>
	<td>Sorted file. Records that do not have sort keys are stored in the next available master area space in Table B.</td>		<td>Sorted file. Records that do not have sort keys are stored in the next available master area space in Table B.</td></tr>
	</tr>
	</table>		</table>
	<p>		<p>
Line 212:		Line 212:
	===BEXTOVFL parameter===		===BEXTOVFL parameter===
	<p>		<p>
	The <var>BEXTOVFL</var> parameter establishes the number of extra overflow areas. <var>BEXTOVFL</var> defaults to 0. Each extra overflow area contains the same number of pages as a normal overflow area. </p>		The <var>[[BEXTOVFL parameter\|BEXTOVFL]]</var> parameter establishes the number of extra overflow areas. <var>BEXTOVFL</var> defaults to 0. Each extra overflow area contains the same number of pages as a normal overflow area. </p>

	===SORTKEY parameter===		===SORTKEY parameter===
Line 228:		Line 228:
	This special <var>INITIALIZE</var> command must be used each time the file is initialized. </p>		This special <var>INITIALIZE</var> command must be used each time the file is initialized. </p>

	~~<b>~~Defining a field name with a sort key~~</b>~~		====Defining a field name with a sort key====
	<p>		<p>
	Unlike ordinary field names, the field name of the sort key is not defined with a <var>DEFINE</var> command but with the <var>INITIALIZE</var> command.</p>		Unlike ordinary field names, the field name of the sort key is not defined with a <var>DEFINE</var> command but with the <var>INITIALIZE</var> command.</p>
Line 234:		Line 234:
	Specify the desired field description for the sort key in the <var>INITIALIZE</var> command.</p>		Specify the desired field description for the sort key in the <var>INITIALIZE</var> command.</p>

	~~<b>~~Rules for defining sort key fields~~</b>~~		====Rules for defining sort key fields====
	<p>		<p>
	Sort key fields cannot have the <var>CODED</var>, <var>INVISIBLE</var>, <var>FLOAT</var>, or <var>BINARY</var> attributes and cannot have an UPDATE option specified for them. The sort key is not required to be <var>KEY</var>. [[Initializing files]] provides a full discussion of the <var>INITIALIZE</var> command and its use with sort key fields. </p>		Sort key fields cannot have the <var>CODED</var>, <var>INVISIBLE</var>, <var>FLOAT</var>, or <var>BINARY</var> attributes and cannot have an <var>UPDATE</var> option specified for them. The sort key is not required to be <var>KEY</var>. [[Initializing files]] provides a full discussion of the <var>INITIALIZE</var> command and its use with sort key fields. </p>
	<p>		<p>
	The following sequence creates a sorted <code>PEOPLE</code> file with <code>LAST NAME</code> as the sort key:</p>		The following sequence creates a sorted <code>PEOPLE</code> file with <code>LAST NAME</code> as the sort key:</p>

JAL: more conversion cleanup

2015-01-13T22:45:18Z

more conversion cleanup

← Older revision		Revision as of 22:45, 13 January 2015
Line 1:		Line 1:
	==Overview==		==Overview==
	<p>		<p>
	In an ordinary <var class="product">Model 204</var> file, each new record is stored in the next available space in Table B. When a set of records is retrieved by a SOUL <var>FIND</var> statement (or an HLI call that performs a Find function) and is processed one by one by a FOR statement (or successive <var>IFFTCH</var> or <var>IFGET</var> calls), <var class="product">Model 204</var> processes the records in the (usually chronological) order in which they are stored.</p>		In an ordinary <var class="product">Model 204</var> file, each new record is stored in the next available space in Table B. When a set of records is retrieved by a [[SOUL]] <var>FIND</var> statement (or an HLI call that performs a Find function) and is processed one by one by a <var>FOR</var> statement (or successive <var>IFFTCH</var> or <var>IFGET</var> calls), <var class="product">Model 204</var> processes the records in the (usually chronological) order in which they are stored.</p>
	<p>		<p>
	You might, however, want to process a set of <var class="product">Model 204</var> records in alphabetic or numeric order depending upon the value of one or more fields of those records. To accomplish this, you can use: </p>		You might, however, want to process a set of <var class="product">Model 204</var> records in alphabetic or numeric order depending upon the value of one or more fields of those records. To accomplish this, you can use: </p>
Line 10:		Line 10:
	<p>		<p>
	These methods are described in this topic. </p>		These methods are described in this topic. </p>
	<p>For Parallel Query Option/204 (PQO) sites, you can use the <var>SORT RECORDS</var> and <var>SORT RECORD KEYS</var> ~~statement~~ to sort data in remote files. See the <var class="book">Parallel Query Option/204 User's Guide</var> for details. </p>		<p>
			For Parallel Query Option/204 (PQO) sites, you can use the <var>SORT RECORDS</var> and <var>SORT RECORD KEYS</var> statements to sort data in remote files. See the <var class="book">Parallel Query Option/204 User's Guide</var> for details. </p>

	==Sorting files using SOUL statements==		==Sorting files using SOUL statements==
Line 16:		Line 17:
	Use the following SOUL statements to sort <var class="product">Model 204</var> records:</p>		Use the following SOUL statements to sort <var class="product">Model 204</var> records:</p>
	<ul>		<ul>
	<li>FOR EACH RECORD IN ORDER BY fieldname</li>		<li><var>FOR EACH RECORD IN ORDER BY <i>fieldname</i></var></li>
	<li>FOR EACH VALUE</li>		<li><var>FOR EACH VALUE</var></li>
	<li>SORT RECORDS</li>		<li><var>SORT RECORDS</var></li>
	<li>SORT RECORD KEYS</li>		<li><var>SORT RECORD KEYS</var></li>
	</ul>		</ul>

	===FOR EACH RECORD IN ORDER BY fieldname statement===		===FOR EACH RECORD IN ORDER BY fieldname statement===
	<p>		<p>
	For ORDERED fields, whose values are stored in order in the Ordered Index, you can use the <var>FOR EACH RECORD IN ORDER BY <i>fieldname</i></var> statement to print records in a found set in order using any <var>ORDERED</var> field in the file. </p>		For <var>ORDERED</var> fields, whose values are stored in order in the Ordered Index, you can use the <var>FOR EACH RECORD IN ORDER BY <i>fieldname</i></var> statement to print records in a found set in order using any <var>ORDERED</var> field in the file. </p>

	===FOR EACH VALUE statement===		===FOR EACH VALUE statement===
Line 46:		Line 47:
	==Sorting files using a sorted file structure==		==Sorting files using a sorted file structure==
	<p>		<p>
	Instead of using the ordinary <var class="product">Model 204</var> file structure, you can use the sorted file structure. In a sorted file, you can designate one (and only one) field as the sort key when the file is initialized (for example, Social Security number, last name, or part number). When a record is stored in Table B, it is stored in such a way that any set of records can be processed efficiently in the order of their sort keys. For example, if a file is sorted, you can append the <var>IN ORDER</var> phrase to the <var>FOR EACH RECORD</var> statement. For sorted files, the Host Language Interface IFFTCH and IFGET calls automatically retrieve records in sorted order. </p>		Instead of using the ordinary <var class="product">Model 204</var> file structure, you can use the sorted file structure. In a sorted file, you can designate one (and only one) field as the sort key when the file is initialized (for example, Social Security number, last name, or part number). When a record is stored in Table B, it is stored in such a way that any set of records can be processed efficiently in the order of their sort keys. For example, if a file is sorted, you can append the <var>IN ORDER</var> phrase to the <var>FOR EACH RECORD</var> statement. For sorted files, the Host Language Interface <var>IFFTCH</var> and <var>IFGET</var> calls automatically retrieve records in sorted order. </p>
	<p>		<p>
	You can also use the SORT statement with sorted files if special reports require ordering on some field other than the designated sort key.</p>		You can also use the <var>SORT</var> statement with sorted files if special reports require ordering on some field other than the designated sort key.</p>

	===Sorted file structure characteristics===		===Sorted file structure characteristics===
Line 79:		Line 80:
	===Sort groups===		===Sort groups===
	<p>		<p>
	A sort group consists of a fixed number of pages of master area in which the records are stored in strict order, and a fixed number of pages of overflow area that contains records of the sort group that could not be inserted into the master area in order. The file generally contains many sort groups. In addition, a number of extra overflow areas are reserved at the beginning of Table B. </p>		A sort group consists of a fixed number of pages of master area in which the records are stored in strict order, and a fixed number of pages of overflow area that contains records of the sort group that could not be inserted into the master area in order. The file generally contains many sort groups. In addition, a number of extra overflow areas are reserved at the beginning of Table B. </p>

	====Initial record loading====		====Initial record loading====
	<p>When a file is initially loaded, the input data must be presorted into the correct order. During this initial load, the records are stored sequentially on master area pages.</p>		<p>
	<p>For example, in a file in which each master area has two pages, each overflow area has one page, and there are two extra overflow areas, Table B looks like the following figure after the initial load. </p>		When a file is initially loaded, the input data must be presorted into the correct order. During this initial load, the records are stored sequentially on master area pages.</p>
			<p>
			For example, in a file in which each master area has two pages, each overflow area has one page, and there are two extra overflow areas, Table B looks like the following figure after the initial load: </p>

	====Table B after initial load=~~===~~		<p class="caption" style="width:459px">Table B after initial load </p>
	[[File:Table B after init load.png]]		<p class="figure">[[File:Table B after init load.png\|459px]] </p>
	<p>		<p>
	This initial load has determined that the first sort group (pages 2 through 4) is to contain all records that have sort keys from <code>ABBEY</code> to <code>AHERN</code>.</p>		This initial load has determined that the first sort group (pages 2 through 4) is to contain all records that have sort keys from <code>ABBEY</code> to <code>AHERN</code>.</p>

Dme at 14:32, 10 May 2014

2014-05-10T14:32:59Z

← Older revision		Revision as of 14:32, 10 May 2014
Line 217:		Line 217:
	==Creating a sorted file==		==Creating a sorted file==
	<p>		<p>
	A sorted file is created in the normal way, as described in [[~~Creating a file (File management)\|~~Creating a file]]. At this time, set the five parameters discussed in [[#Parameters for sorted files\|Parameters for sorted files]].</p>		A sorted file is created in the normal way, as described in [[Creating a file]]. At this time, set the five parameters discussed in [[#Parameters for sorted files\|Parameters for sorted files]].</p>

	===Initializing sorted files===		===Initializing sorted files===

JAL at 17:16, 16 April 2014

2014-04-16T17:16:43Z

← Older revision		Revision as of 17:16, 16 April 2014
Line 1:		Line 1:
	==Overview==		==Overview==
	<p>		<p>
	In an ordinary <var class="product">Model 204</var> file, each new record is stored in the next available space in Table B. When a set of records is retrieved by a SOUL <var>FIND</var> statement (or an HLI call that performs a Find function) and is processed one by one by a FOR statement (or successive IFFTCH or IFGET calls), <var class="product">Model 204</var> processes the records in the (usually chronological) order in which they are stored.</p>		In an ordinary <var class="product">Model 204</var> file, each new record is stored in the next available space in Table B. When a set of records is retrieved by a SOUL <var>FIND</var> statement (or an HLI call that performs a Find function) and is processed one by one by a FOR statement (or successive <var>IFFTCH</var> or <var>IFGET</var> calls), <var class="product">Model 204</var> processes the records in the (usually chronological) order in which they are stored.</p>
	<p>		<p>
	You might, however, want to process a set of <var class="product">Model 204</var> records in alphabetic or numeric order depending upon the value of one or more fields of those records. To accomplish this, you can use: </p>		You might, however, want to process a set of <var class="product">Model 204</var> records in alphabetic or numeric order depending upon the value of one or more fields of those records. To accomplish this, you can use: </p>
Line 24:		Line 24:
	===FOR EACH RECORD IN ORDER BY fieldname statement===		===FOR EACH RECORD IN ORDER BY fieldname statement===
	<p>		<p>
	For ORDERED fields, whose values are stored in order in the Ordered Index, you can use the FOR EACH RECORD IN ORDER BY fieldname statement to print records in a found set in order using any ORDERED field in the file. </p>		For ORDERED fields, whose values are stored in order in the Ordered Index, you can use the <var>FOR EACH RECORD IN ORDER BY <i>fieldname</i></var> statement to print records in a found set in order using any <var>ORDERED</var> field in the file. </p>

	===FOR EACH VALUE statement===		===FOR EACH VALUE statement===
	<p>		<p>
	For ORDERED fields, you can use the FOR EACH VALUE statement to very efficiently process the values in ascending or descending order. </p>		For <var>ORDERED</var> fields, you can use the <var>FOR EACH VALUE</var> statement to very efficiently process the values in ascending or descending order. </p>

	===SORT RECORDS statement===		===SORT RECORDS statement===
Line 83:		Line 83:
	====Initial record loading====		====Initial record loading====
	<p>When a file is initially loaded, the input data must be presorted into the correct order. During this initial load, the records are stored sequentially on master area pages.</p>		<p>When a file is initially loaded, the input data must be presorted into the correct order. During this initial load, the records are stored sequentially on master area pages.</p>
	<p>For example, in a file in which each master area has two pages, each overflow area has one page, and there are two extra overflow areas, Table B looks like the following figure after the initial load: </p>		<p>For example, in a file in which each master area has two pages, each overflow area has one page, and there are two extra overflow areas, Table B looks like the following figure after the initial load. </p>

	====Table B after initial load====		====Table B after initial load====
	[[File:Table B after init load.png]]		[[File:Table B after init load.png]]
	<p>		<p>
	This initial load has determined that the first sort group (pages 2 through 4) is to contain all records that have sort keys from ABBEY to AHERN.</p>		This initial load has determined that the first sort group (pages 2 through 4) is to contain all records that have sort keys from <code>ABBEY</code> to <code>AHERN</code>.</p>
	<p>		<p>
	After the initial load, <var class="product">Model 204</var> attempts to store a new record first in the appropriate master area. If it cannot be inserted in correct order, it is stored instead on a page of that sort group's overflow area. If all the pages of this preferred overflow area are full, the record is said to "spill" and <var class="product">Model 204</var> attempts to store it in the previous overflow or extra overflow area. </p>		After the initial load, <var class="product">Model 204</var> attempts to store a new record first in the appropriate master area. If it cannot be inserted in correct order, it is stored instead on a page of that sort group's overflow area. If all the pages of this preferred overflow area are full, the record is said to "spill" and <var class="product">Model 204</var> attempts to store it in the previous overflow or extra overflow area. </p>
Line 94:		Line 94:
	==Parameters for sorted files==		==Parameters for sorted files==
	<p>		<p>
	The following parameters are relevant to sorted files. </p>		The following subsections describe parameters that are relevant to sorted files. </p>

	===FILEORG parameter===		===FILEORG parameter===
Line 108:		Line 108:
	<td>Reuse record numbers (RRN). Record numbers of deleted records are reused for new records added, if the record number is available on the Table B page to which the new record is being added. </td>		<td>Reuse record numbers (RRN). Record numbers of deleted records are reused for new records added, if the record number is available on the Table B page to which the new record is being added. </td>
	</tr>		</tr>

	<tr>		<tr>
	<td align="right"> X'02'</td>		<td align="right"> X'02'</td>
Line 118:		Line 119:
	</table>		</table>
	<p>		<p>
	Set FILEORG to the sum of the desired options. </p>		Set <var>FILEORG</var> to the sum of the desired options. </p>

	===BPGPMSTR and BPGPOVFL parameters===		===BPGPMSTR and BPGPOVFL parameters===
	<p>		<p>
	BPGPMSTR and BPGPOVFL establish the number of pages per master area in each sort group and per overflow area in each sort group, respectively. These parameters' values depend upon how large a percentage of the file is loaded in sorted order. Recommended values are listed in [[#BPGPMSTR and BPGPOVFL parameters\|BPGPMSTR and BPGPOVFL parameters]].</p>		<var>BPGPMSTR</var> and <var>BPGPOVFL</var> establish the number of pages per master area in each sort group and per overflow area in each sort group, respectively. These parameters' values depend upon how large a percentage of the file is loaded in sorted order. Recommended values are listed in [[#BPGPMSTR and BPGPOVFL parameters\|BPGPMSTR and BPGPOVFL parameters]].</p>
	<table>		<table>
	<caption>Recommended master and overflow area pages</caption>		<caption>Recommended master and overflow area pages</caption>
Line 208:		Line 209:
	===BEXTOVFL parameter===		===BEXTOVFL parameter===
	<p>		<p>
	The BEXTOVFL parameter establishes the number of extra overflow areas. BEXTOVFL defaults to 0. Each extra overflow area contains the same number of pages as a normal overflow area. </p>		The <var>BEXTOVFL</var> parameter establishes the number of extra overflow areas. <var>BEXTOVFL</var> defaults to 0. Each extra overflow area contains the same number of pages as a normal overflow area. </p>

	===SORTKEY parameter===		===SORTKEY parameter===
	<p>		<p>
	The VIEW SORTKEY command displays the sort key field of the file, if the file is a sorted file and the user has a sufficient security level to view the field name. <var class="product">Model 204</var> sets the parameter at file initialization according to the sort key specified for the file. </p>		The <code>VIEW SORTKEY</code> command displays the sort key field of the file, if the file is a sorted file and the user has a sufficient security level to view the field name. <var class="product">Model 204</var> sets the parameter at file initialization according to the sort key specified for the file. </p>

	==Creating a sorted file==		==Creating a sorted file==
	<p>A sorted file is created in the normal way, as described in [[Creating ~~Files Manually~~]]. At this time, set the five parameters discussed in [[#Parameters for sorted files\|Parameters for sorted files]].</p>		<p>
			A sorted file is created in the normal way, as described in [[Creating a file (File management)\|Creating a file]]. At this time, set the five parameters discussed in [[#Parameters for sorted files\|Parameters for sorted files]].</p>

	===Initializing sorted files===		===Initializing sorted files===
	<p>		<p>
	A special form of the INITIALIZE command is provided for sorted files. This form enables you to establish the field name of the file's sort key. </p>		A special form of the <var>[[INITIALIZE command\|INITIALIZE]]</var> command is provided for sorted files. This form enables you to establish the field name of the file's sort key. </p>
	<p>		<p>
	This special INITIALIZE command must be used each time the file is initialized. </p>		This special <var>INITIALIZE</var> command must be used each time the file is initialized. </p>

	<b>Defining a field name with a sort key</b>		<b>Defining a field name with a sort key</b>
	<p>		<p>
	Unlike ordinary field names, the field name of the sort key is not defined with a DEFINE command but with the INITIALIZE command.</p>		Unlike ordinary field names, the field name of the sort key is not defined with a <var>DEFINE</var> command but with the <var>INITIALIZE</var> command.</p>
	<p>		<p>
	Specify the desired field description for the sort key in the INITIALIZE command.</p>		Specify the desired field description for the sort key in the <var>INITIALIZE</var> command.</p>

	<b>Rules for defining sort key fields</b>		<b>Rules for defining sort key fields</b>
	<p>		<p>
	Sort key fields cannot have the CODED, INVISIBLE, FLOAT, or BINARY attributes and cannot have an UPDATE option specified for them. The sort key is not required to be KEY. [[Initializing files]] provides a full discussion of the INITIALIZE command and its use with sort key fields. </p>		Sort key fields cannot have the <var>CODED</var>, <var>INVISIBLE</var>, <var>FLOAT</var>, or <var>BINARY</var> attributes and cannot have an UPDATE option specified for them. The sort key is not required to be <var>KEY</var>. [[Initializing files]] provides a full discussion of the <var>INITIALIZE</var> command and its use with sort key fields. </p>
	<p>		<p>
	The following sequence creates a sorted PEOPLE file with LAST NAME as the sort key:</p>		The following sequence creates a sorted <code>PEOPLE</code> file with <code>LAST NAME</code> as the sort key:</p>
	<p class="code">CREATE FILE PEOPLE		<p class="code">CREATE FILE PEOPLE
	PARAMETER ASTRPPG=200,ATRPG=2,FVFPG=3		PARAMETER ASTRPPG=200,ATRPG=2,FVFPG=3

ELowell: /* Initial record loading */

2014-04-16T16:25:15Z

Initial record loading

← Older revision		Revision as of 16:25, 16 April 2014
Line 83:		Line 83:
	====Initial record loading====		====Initial record loading====
	<p>When a file is initially loaded, the input data must be presorted into the correct order. During this initial load, the records are stored sequentially on master area pages.</p>		<p>When a file is initially loaded, the input data must be presorted into the correct order. During this initial load, the records are stored sequentially on master area pages.</p>
	<p>For example, in a file in which each master area has two pages, each overflow area has one page, and there are two extra overflow areas, Table B looks like ~~[[#Table B after initial load\|~~the following figure]] after the initial load: </p>		<p>For example, in a file in which each master area has two pages, each overflow area has one page, and there are two extra overflow areas, Table B looks like the following figure after the initial load: </p>

	====Table B after initial load====		====Table B after initial load====

JAL at 00:38, 16 April 2014

2014-04-16T00:38:57Z

← Older revision		Revision as of 00:38, 16 April 2014
Line 86:		Line 86:

	====Table B after initial load====		====Table B after initial load====
	~~[[File: Table B after initial load.jpg]]~~		[[File:Table B after init load.png]]
	~~<p>x</p>~~
	~~<p>x</p>~~
	~~<p>x</p>~~
	[[File:Table B after init load.png~~\|698px~~]]
	<p>		<p>
	This initial load has determined that the first sort group (pages 2 through 4) is to contain all records that have sort keys from ABBEY to AHERN.</p>		This initial load has determined that the first sort group (pages 2 through 4) is to contain all records that have sort keys from ABBEY to AHERN.</p>

JAL at 00:35, 16 April 2014

2014-04-16T00:35:49Z

← Older revision		Revision as of 00:35, 16 April 2014
Line 1:		Line 1:
	==Overview==		==Overview==
	<p>		<p>
	In an ordinary <var class="product">Model 204</var> file, each new record is stored in the next available space in Table B. When a set of records is retrieved by a ~~User Language~~ FIND statement (or an HLI call that performs a Find function) and is processed one by one by a FOR statement (or successive IFFTCH or IFGET calls), <var class="product">Model 204</var> processes the records in the (usually chronological) order in which they are stored.</p>		In an ordinary <var class="product">Model 204</var> file, each new record is stored in the next available space in Table B. When a set of records is retrieved by a SOUL <var>FIND</var> statement (or an HLI call that performs a Find function) and is processed one by one by a FOR statement (or successive IFFTCH or IFGET calls), <var class="product">Model 204</var> processes the records in the (usually chronological) order in which they are stored.</p>
	<p>		<p>
	You might, however, want to process a set of <var class="product">Model 204</var> records in alphabetic or numeric order depending upon the value of one or more fields of those records. To accomplish this, you can use: </p>		You might, however, want to process a set of <var class="product">Model 204</var> records in alphabetic or numeric order depending upon the value of one or more fields of those records. To accomplish this, you can use: </p>
	<ul>		<ul>
	<li>~~User Language~~ statements (or HLI function calls)</li>		<li>SOUL statements (or HLI function calls)</li>
	<li>Sorted file structure</li>		<li>Sorted file structure</li>
	</ul>		</ul>
	<p>		<p>
	These methods are described in this topic. </p>		These methods are described in this topic. </p>
	<p>For Parallel Query Option/204 (PQO) sites, you can use the SORT RECORDS and SORT RECORD KEYS statement to sort data in remote files. See the Parallel Query Option/204 User's Guide for details. </p>		<p>For Parallel Query Option/204 (PQO) sites, you can use the <var>SORT RECORDS</var> and <var>SORT RECORD KEYS</var> statement to sort data in remote files. See the <var class="book">Parallel Query Option/204 User's Guide</var> for details. </p>

	==Sorting files using SOUL statements==		==Sorting files using SOUL statements==
	<p>		<p>
	Use the following ~~User Language~~ statements to sort <var class="product">Model 204</var> records:</p>		Use the following SOUL statements to sort <var class="product">Model 204</var> records:</p>
	<ul>		<ul>
	<li>FOR EACH RECORD IN ORDER BY fieldname</li>		<li>FOR EACH RECORD IN ORDER BY fieldname</li>
Line 40:		Line 40:
	===SORT RECORD KEYS statement===		===SORT RECORD KEYS statement===
	<p>		<p>
	You can use a variation of the <var>SORT RECORDS</var> statement, <var>[[Sorting#Sorting by record key\|SORT RECORD KEYS]]</var>. In cases where the quantity of data to be sorted is very large, or where the CCATEMP space available is relatively small, it might be advantageous to limit the data sorted to the sort key and the record number. SORT RECORD KEYS accomplishes this, by building, in effect, a sorted index to the database for the designated key(s). </p>		You can use a variation of the <var>SORT RECORDS</var> statement, <var>[[Sorting#Sorting by record key\|SORT RECORD KEYS]]</var>. In cases where the quantity of data to be sorted is very large, or where the CCATEMP space available is relatively small, it might be advantageous to limit the data sorted to the sort key and the record number. <var>SORT RECORD KEYS</var> accomplishes this, by building, in effect, a sorted index to the database for the designated key(s). </p>
	<p>		<p>
	The Host Language Interface call, <var>IFSKEY</var>, performs the same function and is documented in the <var class="book">Rocket Model 204 Host Language Interface Reference Manual</var>. </p>		The Host Language Interface call, <var>IFSKEY</var>, performs the same function and is documented in the <var class="book">Rocket Model 204 Host Language Interface Reference Manual</var>. </p>
Line 46:		Line 46:
	==Sorting files using a sorted file structure==		==Sorting files using a sorted file structure==
	<p>		<p>
	Instead of using the ordinary <var class="product">Model 204</var> file structure, you can use the sorted file structure. In a sorted file, you can designate one (and only one) field as the sort key when the file is initialized (for example, Social Security number, last name, or part number). When a record is stored in Table B, it is stored in such a way that any set of records can be processed efficiently in the order of their sort keys. For example, if a file is sorted, you can append the IN ORDER phrase to the FOR EACH RECORD statement. For sorted files, the Host Language Interface IFFTCH and IFGET calls automatically retrieve records in sorted order. </p>		Instead of using the ordinary <var class="product">Model 204</var> file structure, you can use the sorted file structure. In a sorted file, you can designate one (and only one) field as the sort key when the file is initialized (for example, Social Security number, last name, or part number). When a record is stored in Table B, it is stored in such a way that any set of records can be processed efficiently in the order of their sort keys. For example, if a file is sorted, you can append the <var>IN ORDER</var> phrase to the <var>FOR EACH RECORD</var> statement. For sorted files, the Host Language Interface IFFTCH and IFGET calls automatically retrieve records in sorted order. </p>
	<p>		<p>
	You can also use the SORT statement with sorted files if special reports require ordering on some field other than the designated sort key.</p>		You can also use the SORT statement with sorted files if special reports require ordering on some field other than the designated sort key.</p>
Line 72:		Line 72:
	<li>Value of a record's sort key cannot be changed in any way. You can, however, delete the entire record and store it again with a different sort key.</li>		<li>Value of a record's sort key cannot be changed in any way. You can, however, delete the entire record and store it again with a different sort key.</li>

	<li>Special forms of the SOUL <var>STORE RECORD</var> statement and the Host Language Interface <var>IFBREC</var> function allow a sort key to be specified for a new record. See ~~the Rocket <var class="product">Model 204</var> User Language Manual and~~ the <var class="book">Rocket Model 204 Host Language Interface Reference Manual</var> for details. </li>		<li>Special forms of the SOUL <var>[[Data maintenance#STORE RECORD statement\|STORE RECORD]]</var> statement and the Host Language Interface <var>IFBREC</var> function allow a sort key to be specified for a new record. See the <var class="book">Rocket Model 204 Host Language Interface Reference Manual</var> for <var>IFBREC</var> details. </li>
	</ul>		</ul>
	<p>		<p>
Line 78:		Line 78:

	===Sort groups===		===Sort groups===
	<p>A sort group consists of a fixed number of pages of master area in which the records are stored in strict order, and a fixed number of pages of overflow area that contains records of the sort group that could not be inserted into the master area in order. The file generally contains many sort groups. In addition, a number of extra overflow areas are reserved at the beginning of Table B. </p>		<p>
			A sort group consists of a fixed number of pages of master area in which the records are stored in strict order, and a fixed number of pages of overflow area that contains records of the sort group that could not be inserted into the master area in order. The file generally contains many sort groups. In addition, a number of extra overflow areas are reserved at the beginning of Table B. </p>

	~~<b>~~Initial record loading~~</b>~~		====Initial record loading====
	<p>When a file is initially loaded, the input data must be presorted into the correct order. During this initial load, the records are stored sequentially on master area pages.</p>		<p>When a file is initially loaded, the input data must be presorted into the correct order. During this initial load, the records are stored sequentially on master area pages.</p>
	<p>For example, in a file in which each master area has two pages, each overflow area has one page, and there are two extra overflow areas, Table B looks like [[#Table B after initial load\|the following figure]] after the initial load: </p>		<p>For example, in a file in which each master area has two pages, each overflow area has one page, and there are two extra overflow areas, Table B looks like [[#Table B after initial load\|the following figure]] after the initial load: </p>

	====Table B after initial load====		====Table B after initial load====
	[[File:~~_Table_B_after_initial_load~~.jpg]]		[[File: Table B after initial load.jpg]]
	<p>This initial load has determined that the first sort group (pages 2 through 4) is to contain all records that have sort keys from ABBEY to AHERN.</p>		<p>x</p>
	<p>After the initial load, <var class="product">Model 204</var> attempts to store a new record first in the appropriate master area. If it cannot be inserted in correct order, it is stored instead on a page of that sort group's overflow area. If all the pages of this preferred overflow area are full, the record is said to "spill" and <var class="product">Model 204</var> attempts to store it in the previous overflow or extra overflow area. </p>		<p>x</p>
			<p>x</p>
			[[File:Table B after init load.png\|698px]]
			<p>
			This initial load has determined that the first sort group (pages 2 through 4) is to contain all records that have sort keys from ABBEY to AHERN.</p>
			<p>
			After the initial load, <var class="product">Model 204</var> attempts to store a new record first in the appropriate master area. If it cannot be inserted in correct order, it is stored instead on a page of that sort group's overflow area. If all the pages of this preferred overflow area are full, the record is said to "spill" and <var class="product">Model 204</var> attempts to store it in the previous overflow or extra overflow area. </p>

	==Parameters for sorted files==		==Parameters for sorted files==
	<p>The following parameters are relevant to sorted files. </p>		<p>
			The following parameters are relevant to sorted files. </p>

	===FILEORG parameter===		===FILEORG parameter===

JAL at 22:33, 14 April 2014

2014-04-14T22:33:32Z

Show changes