Table X (File architecture): Difference between revisions

From m204wiki
Jump to navigation Jump to search
mNo edit summary
 
(6 intermediate revisions by 3 users not shown)
Line 1: Line 1:


Stores all [[Record (File architecture)|Record]] Extensions in a separate table from the the base records (so that [[Table B (File Architecture)|Table B]] contains only the base records).   
Stores all extensions to a Model 204 [[Record (File architecture)|record]] in a separate table from the the base records (so that [[Table B (File architecture)|Table B]] contains only the base records).   
   
   
Enabled by reorganizing / [[CREATE command: File|CREATE]]ing a file with [[XSIZE parameter|XSIZE]] greater than 0.
Table X is enabled when you reorganize or <var>[[CREATE command: File|CREATE]]</var> a file with <var>[[XSIZE parameter|XSIZE]]</var> greater than 0.


Available as of Model 204 version 7.1.
Table X is available as of Model 204 version 7.1.


==Summary==
==Summary==
The Table B of a Model 204 file has a maximum number of 16.7 million record numbers (IRNs, for Internal Record Numbers). By default (<var>[[XSIZE parameter|XSIZE]]</var> = 0), these "slots" are used for both base records and extension records.


The [[Table B (File Architecture)|Table B]] of a Model 204 file has a maximum number of 16.7 million record numbers (IRNs for Internal Record Numbers). By default ([[XSIZE parameter|XSIZE]] = 0), these 'slots' are used for both base records and extension records.  
By implementing Table X (<var>XSIZE</var> > 0) all defined IRNs (up to 16.7 million) in Table B are available for use by base records only, allowing the storage of the maximum possible number of records in a file regardless of the number of extensions.


By implementing Table X ([[XSIZE parameter|XSIZE]] > 0) all defined IRNs (up to 16.7 million) in Table B are available for use by base records only, allowing the storage of the maximum possible number of records in a file regardless of the number of extensions.
Table X can be significantly larger than the 16.7 million limit of Table B: the product of <code>XSIZE * <var>[[XRECPPG parameter|XRECPPG]]</var></code> can be up to 512 million extension slots.  


Table X can be significantly larger than the 16.7 million limit of Table B; as the product of [[XSIZE parameter|XSIZE]] * [[XRECPPG parameter|XRECPPG]] may be up to 512 million extension slots.  
When the defined capacity of Table B or Table X is exceeded, the file is marked full or the updating transaction is backed out.


When the defined capacity of Table B or Table X is exceeded the file is marked full or the updating transaction is backed out.
To view information about memory use in Table X, use the <var>[[TABLEX command|TABLEX]]</var> command.


== Using Table X ==
==Using Table X==
While similar in concept to Table B, the actual "extension record number" in Table X is far more straightforward:   
<ul>
<li>Because the number of slots in Table X is so large, and there is effectively no impact in wasting slots (until you approach that limit) <var>[[XRECPPG parameter|XRECPPG]]</var> can usually be set relatively high.  </li>


While similar in concept to [[Table B (File Architecture)|Table B]], the actual 'extension record number' in Table X is far more straightforward.  
<li>Similarly, depending on how dynamic record growth is, you might want to set <var>[[XRESERVE parameter|XRESERVE]]</var> higher than you would normally consider setting <var>[[BRESERVE parameter|BRESERVE]]</var>. This will enable records to grow without lengthening the extension chain. </li>


* Because the number of slots in Table X is so large, and there is effectively no impact in wasting slots (until you approach that limit) [[XRECPPG parameter|XRECPPG]] can usually be set relatively high. 
<li>Table X slots are always reusable. There is no special setting (equivalent to <var>[[FILEORG parameter|FILEORG]]</var> X'04') necessary to enable the use of the reuse queue. </li>
 
</ul>
* Similarly, depending on how dynamic record growth is, you might want to set [[XRESERVE parameter|XRESERVE]] higher than you would normally consider setting [[BRESERVE parameter|BRESERVE]]. This will enable records to grow without lengthening the extension chain.
 
* Table X slots are always reusable. There is no special setting (equivalent to [[FILEORG parameter|FILEORG]] x'04') necessary to enable the use of the reuse queue.


==RECRDOPT parameter==
==RECRDOPT parameter==
Table X can be implemented in one of two ways depending on the setting of the <var>[[RECRDOPT parameter|RECRDOPT]]</var> parameter.


Table X can be implemented in one of two ways depending on the setting of the [[RECRDOPT parameter]].
<var>RECRDOPT</var> is set only at file creation; you cannot reset it later. To use <var>RECRDOPT</var> with files created prior to Model 204 version 7.1, you must reorganize the files under Model 204 version 7.1 or later.
 
RECRDOPT is set only at file creation; you cannot reset it later. To use RECRDOPT with files created prior to Model 204 version 7.1, you must reorganize the files under Model 204 version 7.1 or later.
 
===The default setting of RECRDOPT (x'00')===


If the default setting of the RECRDOPT parameter is used, while all extensions are stored in Table X, the record allocation in Table B is identical to what it is without Table X enabled.
===The default setting of RECRDOPT (X'00')===
If the default setting of the <var>RECRDOPT</var> parameter is used, while all extensions are stored in Table X, the record allocation in Table B is identical to what it is without Table X enabled.


[[BRECPPG parameter|BRECPPG]] remains the '''maximum number''' of records which can occur on a page, and so IRNs might still be wasted (never assigned to a record) as data fills up a page.
<var>[[BRECPPG parameter|BRECPPG]]</var> remains the ''maximum number'' of records that can occur on a page, so IRNs might still be wasted (never assigned to a record) as data fills up a page.
 
===Setting RECRDOPT parameter to x'01'===


===Setting RECRDOPT parameter to X'01'===
This setting changes the base record allocation to ensure that all defined IRNs are available for use.  
This setting changes the base record allocation to ensure that all defined IRNs are available for use.  


For all x'01' [[RECRDOPT parameter|RECRDOPT]] files, a base record size limit (the read only parameter [[BRLIMSZ parameter|BRLIMSZ]]) is calculated:
For all X'01' <var>RECRDOPT</var> files, a base record size limit (the read only parameter <var>[[BRLIMSZ parameter|BRLIMSZ]]</var>) is calculated:
<ul>
<li>For an entry order file:
<p class="code">BRLIMSZ = (6140 / BRECPPG) - 2 </p></li>


:For an entry order file:
<li>For an unordered file:
::BRLIMSZ = (6140 / BRECPPG) - 2
<p class="code">BRLIMSZ = (6136 / BRECPPG) &#x2013; 2 </p></li>
: For an unordered file:
</ul>
::BRLIMSZ = (6136 / BRECPPG) &#x2013; 2
    
    
If RECRDOPT=1, the maximum allowed value of BRECPPG is 1022 for ordered files and 1023 for unordered files.
If <code>RECRDOPT=1</code>, the maximum allowed value of <var>BRECPPG</var> is 1022 for ordered files and 1023 for unordered files.


When a new record is stored, rather than using any and all space available on a page, only up to [[BRLIMSZ parameter|BRLIMSZ]] bytes will be stored in Table B. This ensures that the full [[BSIZE parameter|BSIZE]] * [[BRECPPG parameter|BRECPPG]] number of IRNs can actually be stored in the file.   
When a new record is stored, rather than using any and all space available on a page, only up to <var>BRLIMSZ</var> bytes will be stored in Table B. This ensures that the full <var>[[BSIZE parameter|BSIZE]]</var> * BRECPPG number of IRNs can actually be stored in the file.   


[[BRLIMSZ parameter|BRLIMSZ]] must be large enough to hold all preallocated fields.
<var>BRLIMSZ</var> must be large enough to hold all preallocated fields.


(Note that the [[BRESERVE parameter]] is ignored (treated as a 0) with x'01' [[RECRDOPT parameter|RECRDOPT]] files).
(Note that the <var>BRESERVE</var> parameter is ignored (treated as a 0) with X'01' <var>RECRDOPT</var> files).


==Advantages of Using Table X==
==Advantages of Using Table X==


===Scalability===
===Scalability===
The obvious advantage of implementing Table X relates to the ability to store the full 16.7 million base records in a Model 204 file.


The obvious advantage of implementing Table X relates to the ability to store the full 16.7 million base records in a Model 204 file,
===Indexing performance===
 
===Indexing Performance===
 
Even if a file has significantly fewer records than the limit, you should consider the performance of your indices in deciding whether to utilize Table X, notably where you expect to have fields with non unique values.  
Even if a file has significantly fewer records than the limit, you should consider the performance of your indices in deciding whether to utilize Table X, notably where you expect to have fields with non unique values.  


When Table X is enabled and [[RECRDOPT parameter|RECRDOPT]] = x'01', the fact there are no wasted slots means that the bitmaps containing the bitmap and list pages (and, of course, the [[Table D (File architecture)#Existence bitmap|existence bitmap]]) are as compressed as possible.
When Table X is enabled and <code>RECRDOPT = X'01'</code>, the fact there are no wasted slots means that the bitmaps containing the bitmap and list pages (and, of course, the [[Table D (File architecture)#Existence bitmap|existence bitmap]]) are as compressed as possible.


===Avoidance of 'Sick Record' Snaps===
===Avoidance of "sick record" snaps===
When large sets of unlocked records (either through the use of <var>[[Record level locking and concurrency control#FIND WITHOUT LOCKS statement|Find Without Locks]]</var> (<var>FDWOL</var>) or by referencing records on a Model 204 list) are processed, it is very possible that the records will have had their contents changed or even been deleted by the time the record is read. There are three problems that might be encountered when this happens:
<ul>
<li>The record should no longer be selected, its contents no longer matching the selection criteria
<li>The record has been deleted (and so 'Nonexistent record' messages are produced)
<li>The record now (due to delete and update processing) contains an extension record
</ul>


When large sets of unlocked records (either through the use of [[Record Level Locking and Concurrency Control#FIND WITHOUT LOCKS statement|Find Without Locks]] (FDWOL) or by referencing records on a Model 204 List) are processed, it is very possible that the records will have had their contents changed or even been deleted by the time the record is read. There are three problems that might be encountered when this happens:
The last of these problems causes a "sick record" snap.


: the record should no longer be selected, its contents no longer matching the selection criteria
While Rocket Software encourages the use of coding techniques to prevent all of these issues, File Managers can protect themselves from the snaps (at least) by enabling Table X in files where such processing is expected.
: the record has been deleted (and so 'Nonexistent record' messages are produced)
: the record now (due to delete and update processing) contains an extension record
 
The last of these problems causes a 'Sick Record' snap.
 
While Rocket encourages the use of coding techniques to prevent all of these issues, File Managers can protect themselves from the snaps (at least) by enabling Table X in files where such processing is expected.


==Parameters related to the use of Table X==
==Parameters related to the use of Table X==
 
<table class="thJustBold">
{|
<tr><th>[[BRLIMSZ parameter|BRLIMSZ]]</th>
|[[BRLIMSZ parameter]]
<td>Calculated maximum size of base records</td></tr>
|Calculated maximum size of base records  
|-
<tr><th>[[RECRDOPT parameter|RECRDOPT]]</th>
|[[RECRDOPT parameter]]
<td>Controls the method by which base records are stored</td></tr>
|Controls the method by which base records are stored
|-
<tr><th>[[XAUTOINC parameter|XAUTOINC]]</th>
|[[XAUTOINC parameter]]
<td>Number of pages for Table X auto increase</td></tr>
|Number of pages for Table X auto increase
|-
<tr><th>[[XHIGHPG parameter|XHIGHPG]]</th>
|[[XHIGHPG parameter]]
<td>Table X highest active page</td></tr>
|Table X highest active page
|-
<tr><th>[[XQLEN parameter|XQLEN]]</th>
|[[XQLEN parameter]]
<td>The number of Table X pages in the reuse queue</td></tr>
|The number of Table X pages in the reuse queue
|-
<tr><th>[[XRECPPG parameter|XRECPPG]]</th>
|[[XRECPPG parameter]]
<td>The maximum number of extension slots on a Table X page</td></tr>
|The maximum number of extension slots on a Table X page
|-
<tr><th>[[XRESERVE parameter|XRESERVE]]</th>
|[[XRESERVE parameter]]
<td>Table X reserve space per page</td></tr>
|Table X reserve space per page
|-
<tr><th>[[XREUSE parameter|XREUSE]]</th>
|[[XREUSE parameter]]
<td>Free space required to reuse a table X page</td></tr>
|Free space required to reuse a table X page
|-
<tr><th>[[XREUSED parameter|XREUSED]]</th>
|[[XREUSED parameter]]
<td>The number of extension slots reused in table X</td></tr>
|The number of extension slots reused in table X
|-
<tr><th>[[XSIZE parameter|XSIZE]]</th>
|[[XSIZE parameter]]
<td>The number of pages in Table X</td></tr>
|The number of pages in Table X
</table>
|}


The use of these parameters is discussed in [[File size calculation in detail#Sizing Tables B and X|Sizing Tables B and X]].
The use of these parameters is discussed in [[File size calculation in detail#Sizing Tables B and X|Sizing Tables B and X]].


[[Category:File architecture]]
[[Category:File architecture]]

Latest revision as of 22:21, 29 January 2014

Stores all extensions to a Model 204 record in a separate table from the the base records (so that Table B contains only the base records).

Table X is enabled when you reorganize or CREATE a file with XSIZE greater than 0.

Table X is available as of Model 204 version 7.1.

Summary

The Table B of a Model 204 file has a maximum number of 16.7 million record numbers (IRNs, for Internal Record Numbers). By default (XSIZE = 0), these "slots" are used for both base records and extension records.

By implementing Table X (XSIZE > 0) all defined IRNs (up to 16.7 million) in Table B are available for use by base records only, allowing the storage of the maximum possible number of records in a file regardless of the number of extensions.

Table X can be significantly larger than the 16.7 million limit of Table B: the product of XSIZE * XRECPPG can be up to 512 million extension slots.

When the defined capacity of Table B or Table X is exceeded, the file is marked full or the updating transaction is backed out.

To view information about memory use in Table X, use the TABLEX command.

Using Table X

While similar in concept to Table B, the actual "extension record number" in Table X is far more straightforward:

  • Because the number of slots in Table X is so large, and there is effectively no impact in wasting slots (until you approach that limit) XRECPPG can usually be set relatively high.
  • Similarly, depending on how dynamic record growth is, you might want to set XRESERVE higher than you would normally consider setting BRESERVE. This will enable records to grow without lengthening the extension chain.
  • Table X slots are always reusable. There is no special setting (equivalent to FILEORG X'04') necessary to enable the use of the reuse queue.

RECRDOPT parameter

Table X can be implemented in one of two ways depending on the setting of the RECRDOPT parameter.

RECRDOPT is set only at file creation; you cannot reset it later. To use RECRDOPT with files created prior to Model 204 version 7.1, you must reorganize the files under Model 204 version 7.1 or later.

The default setting of RECRDOPT (X'00')

If the default setting of the RECRDOPT parameter is used, while all extensions are stored in Table X, the record allocation in Table B is identical to what it is without Table X enabled.

BRECPPG remains the maximum number of records that can occur on a page, so IRNs might still be wasted (never assigned to a record) as data fills up a page.

Setting RECRDOPT parameter to X'01'

This setting changes the base record allocation to ensure that all defined IRNs are available for use.

For all X'01' RECRDOPT files, a base record size limit (the read only parameter BRLIMSZ) is calculated:

  • For an entry order file:

    BRLIMSZ = (6140 / BRECPPG) - 2

  • For an unordered file:

    BRLIMSZ = (6136 / BRECPPG) – 2

If RECRDOPT=1, the maximum allowed value of BRECPPG is 1022 for ordered files and 1023 for unordered files.

When a new record is stored, rather than using any and all space available on a page, only up to BRLIMSZ bytes will be stored in Table B. This ensures that the full BSIZE * BRECPPG number of IRNs can actually be stored in the file.

BRLIMSZ must be large enough to hold all preallocated fields.

(Note that the BRESERVE parameter is ignored (treated as a 0) with X'01' RECRDOPT files).

Advantages of Using Table X

Scalability

The obvious advantage of implementing Table X relates to the ability to store the full 16.7 million base records in a Model 204 file.

Indexing performance

Even if a file has significantly fewer records than the limit, you should consider the performance of your indices in deciding whether to utilize Table X, notably where you expect to have fields with non unique values.

When Table X is enabled and RECRDOPT = X'01', the fact there are no wasted slots means that the bitmaps containing the bitmap and list pages (and, of course, the existence bitmap) are as compressed as possible.

Avoidance of "sick record" snaps

When large sets of unlocked records (either through the use of Find Without Locks (FDWOL) or by referencing records on a Model 204 list) are processed, it is very possible that the records will have had their contents changed or even been deleted by the time the record is read. There are three problems that might be encountered when this happens:

  • The record should no longer be selected, its contents no longer matching the selection criteria
  • The record has been deleted (and so 'Nonexistent record' messages are produced)
  • The record now (due to delete and update processing) contains an extension record

The last of these problems causes a "sick record" snap.

While Rocket Software encourages the use of coding techniques to prevent all of these issues, File Managers can protect themselves from the snaps (at least) by enabling Table X in files where such processing is expected.

Parameters related to the use of Table X

BRLIMSZ Calculated maximum size of base records
RECRDOPT Controls the method by which base records are stored
XAUTOINC Number of pages for Table X auto increase
XHIGHPG Table X highest active page
XQLEN The number of Table X pages in the reuse queue
XRECPPG The maximum number of extension slots on a Table X page
XRESERVE Table X reserve space per page
XREUSE Free space required to reuse a table X page
XREUSED The number of extension slots reused in table X
XSIZE The number of pages in Table X

The use of these parameters is discussed in Sizing Tables B and X.