DEFINE FIELD command: Difference between revisions

From m204wiki
Jump to navigation Jump to search
 
(79 intermediate revisions by 7 users not shown)
Line 2: Line 2:
<dl>
<dl>
<dt>Status
<dt>Status
<dd>New in Model 204 release 7.5:  
<dd>Generally Available in Model 204 version 7.5:  
<ul>
<ul>
<li>fields defined within field groups and the attributes that are available when FILEORG=X’100’ is used to define the file.</li>
<li>Fields as members of field groups
<li>CHUNK field attribute</li>
<li>Field attributes specific to <var>[[FILEORG_parameter|FILEORG]]</var> X'100' files</li>
<li>Ordered index <var>[[Field_design#CHUNK_attribute_2|CHUNK]]</var> field attribute</li>
</ul>
</ul>
<dt>Privileges
<dt>Privileges
Line 15: Line 16:
==Syntax==
==Syntax==
<b>For a field</b>
<b>For a field</b>
<p class="syntax">DEFINE [FIELD] <span class="term">name</span> [(<span class="term">attribute</span> [<span class="term">attribute</span>]...)]
<p class="syntax"><span class="squareb">[</span>IN <span class="term">fileName</span><span class="squareb">]</span> DEFINE [FIELD] <span class="term">name</span> [(<span class="term">attribute</span> [<span class="term">attribute</span>]...)]
</p>
</p>
<p>or</p>
<p>or</p>
<p class="syntax">DEFINE [FIELD] <span class="term">name</span> WITH [<span class="term">attribute</span> [<span class="term">attribute</span>]...]
<p class="syntax"><span class="squareb">[</span>IN <span class="term">fileName</span><span class="squareb">]</span> DEFINE [FIELD] <span class="term">name</span> WITH [<span class="term">attribute</span> [<span class="term">attribute</span>]...]
</p>
</p>
<br>
<br>
Line 29: Line 30:
   [<span class="term">fieldgroupname</span> | *] [AND <span class="term">attribute</span> [<span class="term">attribute</span>]...]
   [<span class="term">fieldgroupname</span> | *] [AND <span class="term">attribute</span> [<span class="term">attribute</span>]...]
</p>
</p>
<br>
 
<b>Where:</b>
<p>Where:</p>
<table>
<table>
<tr>
<tr><th><var>IN</var> fileName</th>
<th>name or fieldgroupname</th>
<td>Specifies the file within which the field is defined.  This may not be a <var class="product">Model 204</var> group; it must be a single file. If this clause is not present, the default file, as given by the <var>[[CURFILE parameter|CURFILE]]</var> parameter, is the file in which the field is defined.
<p>
The file in which the field is defined must be <var>[[OPEN FILE command|OPEN]]</var>, with file manager [[CURPRIV parameter|privileges]].</p>
</td></tr>
 
<tr><th>name <var>or</var> fieldgroupname</th>
<td> (1 to 255 characters) must begin with a letter and can contain any alphanumeric characters (including space) except:
<td> (1 to 255 characters) must begin with a letter and can contain any alphanumeric characters (including space) except:
<p>?? </p>
<p>?? </p>
Line 47: Line 53:
<tr>
<tr>
<th>attribute</th>
<th>attribute</th>
<td> is one of the field characteristics listed in the table below. Note that each attribute has a default. Therefore, when selected, the attribute need be specified only if it differs from the default. If no attributes are specified, the field is defined with all the defaults. See [[Field Design (File Management)]] for more information on field attributes.
<td>One of the field characteristics listed in the table below. Note that each attribute has a default. Therefore, when selected, the attribute need be specified only if it differs from the default. If no attributes are specified, the field is defined with all the defaults. See [[Field design]] for more information on field attributes.




Line 61: Line 67:


<tr>
<tr>
<th><var>WITH FIELDGROUP</var> fieldgroupname</th>  
<th nowrap><var>FIELDGROUP</var> fieldgroupname</th>  
<td>identifies the containing field group. When <var class="term">fieldgroupname</var> is specified as an asterisk (*), it means
<td>The containing [[Field_design#Assigning_fields_to_field_groups|field group]]. When <var class="term">fieldgroupname</var> is specified as an asterisk (*), it means that the field can be included in any field group as well as existing outside of any field group.</td>
that the field can be included in any field group as well as existing outside of any field group.</td>
</tr>
</tr>
</table>
</table>
<p>Conflicting attributes, listed in the table below, cannot be specified together. </p>
 
<p>
Conflicting attributes, listed in the table below, cannot be specified together. </p>
<table>
<table>
<caption>Conflicting field attributes</caption>
<caption>Conflicting field attributes</caption>
   
   
<tr> <th>This attribute...</th> <th>Cannot be specified with the attribute(s)...</th> </tr>
<tr class="head"> <th>This attribute...</th> <th>Cannot be specified with the attribute(s)...</th> </tr>
   
   
<tr> <th><var>BINARY, BLOB, CLOB, or CODED</var></th> <td>LENGTH</td> </tr>
<tr> <th><var>BINARY, BLOB, CLOB, or CODED</var></th> <td>LENGTH</td> </tr>
Line 98: Line 104:
<tr> <th><var>DATETIME-GE, DATETIME-GT, DATETIME-LE, DATETIME-LT</var></th> <td>CODED, CONCATENATION-OF, CREATE-USER, KEY, OCCURS, UPDATE AT END, UPDATE-USER</td> </tr>  
<tr> <th><var>DATETIME-GE, DATETIME-GT, DATETIME-LE, DATETIME-LT</var></th> <td>CODED, CONCATENATION-OF, CREATE-USER, KEY, OCCURS, UPDATE AT END, UPDATE-USER</td> </tr>  


<tr> <th><var>DEFAULT-VALUE</var></th> <td>REPEATABLE</td> </tr>  
<tr> <th><var>DEFAULT-VALUE</var></th> <td>REPEATABLE, NO-DEFAULT-VALUE, STORE-NULL LITERAL, STORE-NULL NONE</td> </tr>  


<tr> <th><var>ESCAPE</var></th> <td>SEPARATOR NONE</td> </tr>  
<tr> <th><var>ESCAPE</var></th> <td>SEPARATOR NONE</td> </tr>  


<tr> <th><var>EXACTLY-ONE</var></th> <td>CONCATENATION-OF, INVISIBLE, LITERAL, OCCURS, STORE-NULL, UNIQUE</td> </tr>  
<tr> <th><var>EXACTLY-ONE</var></th> <td>CONCATENATION-OF, INVISIBLE, STORE-DEFAULT LITERAL, OCCURS, STORE-NULL LITERAL, UNIQUE</td> </tr>  


<tr> <th><var>FEW-VALUED</var></th> <td>MANY-VALUED</td> </tr>
<tr> <th><var>FEW-VALUED</var></th> <td>MANY-VALUED</td> </tr>
Line 110: Line 116:
<tr> <th><var>FLOAT</var></th> <td>BINARY, INVISIBLE, NUMERIC RANGE, and STRING</td> </tr>
<tr> <th><var>FLOAT</var></th> <td>BINARY, INVISIBLE, NUMERIC RANGE, and STRING</td> </tr>


<tr> <th><var>FLOAT-GE, FLOAT-GT, FLOAT-LE, FLOAT-LT</var></th> <td>Other range attributes</td> </tr>
<tr> <th><var>FLOAT-GE, FLOAT-GT, FLOAT-LE, FLOAT-LT, INTEGER-GE, INTEGER-GT, INTEGER-LE, INTEGER-LT</var></th> <td>Other range attributes</td> </tr>


<tr> <th><var>FRV</var></th> <td>NON-KEY</td> </tr>
<tr> <th><var>FRV</var></th> <td>NON-KEY</td> </tr>
Line 121: Line 127:
<tr> <th><var>LENGTH-EQ</var></th> <td>Any attribute that is automatically maintained by the system; BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, LENGTH-GE, LENGTH-LE</td> </tr>
<tr> <th><var>LENGTH-EQ</var></th> <td>Any attribute that is automatically maintained by the system; BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, LENGTH-GE, LENGTH-LE</td> </tr>


<tr> <th><var>LENGTH-GE, LENGTH-LLE</var></th> <td>Any attribute that is automatically maintained by the system; BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, LENGTH-EQ</td> </tr>
<tr> <th><var>LENGTH-GE, LENGTH-LE</var></th> <td>Any attribute that is automatically maintained by the system; BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, LENGTH-EQ</td> </tr>


<tr> <th><var>LIKE</var></th> <td>Any attribute that is automatically maintained by the system; BINARY LARGE OBJECT, CHARACTER LARGE OBJECT</td> </tr>
<tr> <th><var>LIKE</var></th> <td>Any attribute that is automatically maintained by the system; BINARY LARGE OBJECT, CHARACTER LARGE OBJECT</td> </tr>
Line 131: Line 137:
<tr> <th><var>MINLOBE</var></th> <td>OCCURS</td> </tr>
<tr> <th><var>MINLOBE</var></th> <td>OCCURS</td> </tr>


<tr> <th><var>NO-CONSTRAINTS</var></th> <td>Any constraint attribute</td> </tr>
<tr> <th><var>NO-DOMAIN-CONSTRAINTS</var></th> <td>Any constraint attribute</td> </tr>


<tr> <th><var>NO-DEFAULT-VALUE</var></th> <td>DEFAULT-VALUE</td> </tr>
<tr> <th><var>NO-DEFAULT-VALUE</var></th> <td>DEFAULT-VALUE, STORE-DEFAULT</td> </tr>


<tr> <th><var>NON-CODED and NON-FRV</var></th> <td>MANY-VALUED or FEW-VALUED</td> </tr>
<tr> <th><var>NON-CODED and NON-FRV</var></th> <td>MANY-VALUED or FEW-VALUED</td> </tr>
Line 164: Line 170:


<tr> <th><var>UPDATE-USER</var></th> <td>BINARY, BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, DATETIME, FLOAT, INVISIBLE, NUMERIC RANGE, OCCURS > 1, STORE-DEFAULT, STORE-NULL, UNIQUE, UPDATE AT END</td> </tr>
<tr> <th><var>UPDATE-USER</var></th> <td>BINARY, BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, DATETIME, FLOAT, INVISIBLE, NUMERIC RANGE, OCCURS > 1, STORE-DEFAULT, STORE-NULL, UNIQUE, UPDATE AT END</td> </tr>
</table>
</table>
<p>Paired attributes, listed in the table below, must be specified together. </p>
 
<p>
Paired attributes, listed in the table below, must be specified together. </p>
<table>
<table>
<caption>Paired field attributes</caption>
<caption>Paired field attributes</caption>
   
   
<tr> <th>This attribute...</th> <th>Must be specified with the attribute(s)...</th> </tr>
<tr class="head"> <th>This attribute...</th> <th>Must be specified with the attribute(s)...</th> </tr>


<tr> <th><var>CHUNK</var></th> <td>ORDERED NUMERIC and INVISIBLE</td> </tr>
<tr> <th><var>CHUNK</var></th> <td>ORDERED NUMERIC and INVISIBLE</td> </tr>
   
   
<tr> <th><var>CONCATENATION-OF</var></th> <td>AT-MOST-ONE or EXACTLY-ONE or OCCURS 1</td> </tr>
<tr> <th><var>CONCATENATION-OF</var></th> <td>AT-MOST-ONE, EXACTLY-ONE, or OCCURS 1</td> </tr>


<tr> <th><var>COUNT-OCCURRENCES-OF</var></th> <td>AT-MOST-ONE or EXACTLY-ONE or OCCURS 1</td> </tr>
<tr> <th><var>COUNT-OCCURRENCES-OF</var></th> <td>AT-MOST-ONE, EXACTLY-ONE, or OCCURS 1</td> </tr>


<tr> <th><var>CREATE-TIME</var></th> <td>AT-MOST-ONE</td> </tr>
<tr> <th><var>CREATE-TIME</var></th> <td>AT-MOST-ONE</td> </tr>
Line 205: Line 212:


<tr> <th><var>UPDATE-TIME, UPDATE-TIMEUTC</var></th> <td>AT-MOST-ONE</td> </tr>
<tr> <th><var>UPDATE-TIME, UPDATE-TIMEUTC</var></th> <td>AT-MOST-ONE</td> </tr>
</table>
<tr> <th><var>UNIQUE</var></th> <td>ORDERED</td> </tr>


</table>
===Syntax notes===
===Syntax notes===
<p>Attributes must be separated by commas or by one or more blanks.</p>
<p>
<p>You can specify DEFINE FIELD only in file context.</p>
Attributes must be separated by commas or by one or more blanks.</p>
<p>
You can specify <var>DEFINE FIELD</var> only in file context.</p>
 
<p>For general syntax and usage notes that apply to all forms of the <var>DEFINE</var> command, see <var>[[DEFINE command]]</var>.</p>


==Example==
==Example==
Line 218: Line 227:
DEFINE FIELD EMPLOYEE
DEFINE FIELD EMPLOYEE
</p>
</p>
==Usage notes==
==Usage notes==
The DEFINE FIELD command describes the names and attributes of the fields in a <var class="product">Model&nbsp;204</var> file. The file must be initialized, using the INITIALIZE command, before fields can be defined.
The <var>DEFINE FIELD</var> command describes the names and attributes of the fields in a <var class="product">Model&nbsp;204</var> file. The file must be initialized, using the <var>[[INITIALIZE_command|INITIALIZE]]</var> command, before fields can be defined.
<p class="note"><b>Note:</b> Sort key fields for sorted files, hash key fields, and record security fields can be defined only with the INITIALIZE command, not with the DEFINE FIELD command.</p>
<p>
<p>Once fields have been defined, a user with the requisite privileges can perform the following commands on them:</p>
Once fields have been defined, a user with the requisite privileges can perform the following commands on them:</p>
<ul>
<ul>
<li>
<li><var>[[DELETE command: Field|DELETE FIELD]]</var></li>
<p>[[DELETE command: Field|DELETE FIELD]]</p>
</li>
   
   
<li>
<li><var>[[DISPLAY FIELD command|DISPLAY FIELD]]</var></li>
<p>[[DISPLAY command|DISPLAY]] </p>
</li>
   
   
<li>
<li><var>[[REDEFINE command|REDEFINE]]</var></li>
<p>[[REDEFINE command|REDEFINE]]</p>
</li>
   
   
<li>
<li><var>[[RENAME FIELD command|RENAME FIELD]]</var></li>
<p>[[RENAME FIELD command|RENAME FIELD]]</p>
</ul>
</li>
 
</ul>
===Record security and sort and hash key fields===
<p>You can define three types of floating point fields through the FLOAT and LENGTH attributes. The table below shows the characteristics of the three types. </p>
Sort key fields for [[Sorted_files|sorted files]], [[Hash_key_files#Defining_hash_key_fields|hash key fields]], and [[Model_204_security_features#Record_security|record security]] fields can be defined only with the <var>INITIALIZE</var> command, not with the <var>DEFINE FIELD</var> command.
 
===Floating point fields===
<p>
You can define three types of floating point fields through the <var>[[ield_design#FLOAT_attribute|FLOAT]]</var> and <var>[[Field_design#LENGTH_and_PAD_clauses|LENGTH]]</var> attributes. The table below shows the characteristics of the three types. </p>
<table>
<table>
<caption>Types of floating point fields</caption>
<caption>Types of floating point fields</caption>
   
   
<tr> <th>Type</th> <th>Number  of bytes</th> <th>Maximum number of significant digits</th> </tr>
<tr class="head"> <th>Type</th> <th>Number  of bytes</th> <th>Maximum number of significant digits</th> </tr>
   
   
<tr> <th><var>Short precision</var></th> <td> 4</td> <td> 6</td> </tr>
<tr> <th><var>Short precision</var></th> <td> 4</td> <td> 6</td> </tr>
Line 250: Line 258:
   
   
<tr> <th><var>Extended precision</var></th> <td>16</td> <td>31 </td> </tr>
<tr> <th><var>Extended precision</var></th> <td>16</td> <td>31 </td> </tr>
</table>
</table>
<p>You supply a LENGTH attribute of 4, 8, or 16. The following example defines a long-precision floating-point field: </p>
 
<p>
You supply a <var>LENGTH</var> attribute of 4, 8, or 16. The following example defines a long-precision floating-point field: </p>
<p class="code">DEFINE FIELD SALARY WITH FLOAT LEN 8
<p class="code">DEFINE FIELD SALARY WITH FLOAT LEN 8
</p>
</p>
<p>Consider making a field a floating-point field if its value is always or often numerical. Very large numbers or numbers with many significant digits use much less space in floating-point form than they do in character form. Floating-point fields are valid only for files with the format of Release 8.0 or later or that have been converted by the TRANSFORM FLOAT command. Floating-point numbers in ORDERED NUMERIC trees are discussed as follows.     </p>
<p>
Consider making a field a floating-point field if its value is always or often numerical. Very large numbers or numbers with many significant digits use much less space in floating-point form than they do in character form. Floating-point fields are valid only for files with the format of Release 8.0 or later or that have been converted by the <var>TRANSFORM FLOAT</var> command. Floating-point numbers in <var>[[Field_design#ORDERED_NUMERIC_fields|ORDERED NUMERIC]]</var> trees are discussed as follows. </p>
 
===Defining a field group field===
===Defining a field group field===
A field defined within a field group supports the same default attributes as fields defined outside or independent of a field group, except for REPEATABLE. A field defined within a field group defaults to EXACTLY-ONE. You can override the default with REPEATABLE.
A field defined within a field group supports the same default attributes as fields defined outside or independent of a field group, except for <var>[[Field_design#AT-MOST-ONE.2C_REPEATABLE.2C_and_EXACTLY-ONE_attributes|REPEATABLE]]</var>. A field defined within a field group defaults to <var>[[Field_design#Using_the_EXACTLY-ONE_.28EXONE.29_attribute|EXACTLY-ONE]]</var>. You can override the default with <var>REPEATABLE</var>.
The frequency of occurrence of FieldName can be:
The attribute for frequency of occurrence of the field being is one of these:
<ul>
<ul>
<li>EXACTLY-ONE, the default for a field defined within a DEFINE FIELDGROUP block</li>
<li><var>EXACTLY-ONE</var>, the default for a field defined within a <var>[[DEFINE_FIELDGROUP_command|DEFINE FIELDGROUP]]</var> block</li>
<li>REPEATABLE, the default for a field defined outside of a field group</li>
 
<li>AT-MOST-ONE</li>
<li><var>REPEATABLE</var>, the default for a field defined outside of a field group</li>
 
<li><var>[[Field_design#Using_the_AT-MOST-ONE_attribute|AT-MOST-ONE]]</var></li>
</ul>
</ul>


==ORDERED attribute==
==ORDERED attribute==
<p>The ORDERED attribute is used to define fields for the Ordered Index feature. The ORDERED attribute can be followed by a tree type to specify the kind of ordering for the index. The tree type can be either CHARACTER (CHAR) or NUMERIC (NUM). If a tree type is not provided, a default tree type is used.     </p>
<p>
The <var>[[Field_design#ORDERED_and_NON-ORDERED_attributes|ORDERED]]</var> attribute is used to define fields for the Ordered Index feature. The <var>ORDERED</var> attribute can be followed by a tree type to specify the kind of ordering for the index. The tree type can be either <var>[[Field_design#ORDERED_CHARACTER_fields|CHARACTER]]</var> (<var>CHAR</var>) or <var>NUMERIC</var> (<var>NUM</var>). If a tree type is not provided, a default tree type is used. </p>


===Syntax===
===Syntax===
<p>The format for the DEFINE FIELD command with solely the ORDERED option and its related attributes is as follows:</p>
<p>
The format for the <var>DEFINE FIELD</var> command with solely the <var>ORDERED</var> option and its related attributes is as follows:</p>
<p class="syntax">DEFINE [FIELD] <var class="term">name</var> WITH [ORDERED [<b>CHAR</b>ACTER | <b>NUM</b>ERIC]
<p class="syntax">DEFINE [FIELD] <var class="term">name</var> WITH [ORDERED [<b>CHAR</b>ACTER | <b>NUM</b>ERIC]
  [LRESERVE <var class="term">n</var>] [NRESERVE <var class="term">n</var>] [SPLITPCT <var class="term">n</var>] [IMMED <var class="term">n</var>]]
  [LRESERVE <var class="term">n</var>] [NRESERVE <var class="term">n</var>] [SPLITPCT <var class="term">n</var>] [IMMED <var class="term">n</var>]]
</p>
</p>
   
   
<b>Where:</b>
<p>Where:</p>
<table>
<table>
<tr>
<tr>
<th><var>LRESERVE</var></th>
<th><var>LRESERVE</var></th>
<td> defines the percentage of space to leave free on the leaf page during deferred updates or a REORGANIZE command. The default is 15 percent. The value must be between 0 and 99.</td>
<td>The percentage of space to leave free on the leaf page during deferred updates or a <var>REORGANIZE</var> command. The default is 15 percent. The value must be between 0 and 99.</td>
</tr>
</tr>
   
   
<tr>
<tr>
<th><var>NRESERVE</var></th>
<th><var>NRESERVE</var></th>
<td> defines the percentage of space to leave free on the node page during deferred updates or a REORGANIZE command. The default is 15 percent. The value must be between 0 and 99.</td>
<td>The percentage of space to leave free on the node page during deferred updates or a REORGANIZE command. The default is 15 percent. The value must be between 0 and 99.</td>
</tr>
</tr>
   
   
<tr>
<tr>
<th><var>SPLITPCT</var></th>
<th><var>SPLITPCT</var></th>
<td> defines the percentage of the node data to go to the left node when a split occurs during nondeferred User Language or IFAM requests. The default is 50 percent. The value must be between 1 and 100.</td>
<td>The percentage of the node data to go to the left node when a split occurs during nondeferred SOUL or IFAM requests. The default is 50 percent. The value must be between 1 and 100.</td>
</tr>
</tr>
   
   
<tr>
<tr>
<th><var>IMMED</var></th>
<th><var>IMMED</var></th>
<td> defines the number of immediate pointers per segment in the Ordered Index. The default is 1. The value must be between 0 and 255. </td>
<td>The number of immediate pointers per segment in the Ordered Index. The default is 1. The value must be between 0 and 255. </td>
</tr>
</tr>  
</table>
</table>


===Usage notes===
===Usage notes===
<p>The LRESERVE, NRESERVE, SPLITPCT, and IMMED parameters represent approximate values. The actual movement of data during an update might not conform exactly to the value of the attribute in question. </p>
<p>
<p>The default ordering for a field is NON-ORDERED. Ordered Index attributes do not apply for a NON-ORDERED field.</p>
The <var>[[LRESERVE_parameter|LRESERVE]]</var>, <var>[[NRESERVE_parameter|NRESERVE]]</var>, <var>[[SPLITPCT_parameter|SPLITPCT]]</var>, and <var>[[IMMED_parameter|IMMED]]</var> parameters represent approximate values. The actual movement of data during an update might not conform exactly to the value of the attribute in question. </p>
<p>If a field is defined as ORDERED without specifying the type of tree to create, the default depends on whether the field is STRING, BINARY, or FLOAT. If STRING, an ORDERED CHARACTER tree is defined. If FLOAT or BINARY, an ORDERED NUMERIC tree is defined.</p>
<p>
<p>The UNIQUE attribute specifies that there can be only one occurrence of a unique field in the file. Multiple occurrences are not allowed.</p>
The default ordering for a field is NON-ORDERED. Ordered Index attributes do not apply for a NON-ORDERED field.</p>
<p>For a detailed discussion of field attributes, see [[Field Design (File Management)]].</p>
<p>
If a field is defined as ORDERED without specifying the type of tree to create, the default depends on whether the field is [[Field_design#STRING_attribute|STRING]], [[Field_design#BINARY_attribute|BINARY]], or FLOAT. If STRING, an ORDERED CHARACTER tree is defined. If FLOAT or BINARY, an ORDERED NUMERIC tree is defined.</p>
<p>
The [[Field_design#UNIQUE_and_NON-UNIQUE_attributes|UNIQUE]] attribute specifies that there can be only one occurrence of a unique field in the file. Multiple occurrences are not allowed.</p>
<p>For a detailed discussion of field attributes, see [[Field design]].</p>


==CHUNK attribute==
<div id="CHUNK attribute"></div>
The CHUNK field attribute is available in Model 204 Relase 7.5.  It improves the efficiency of range finds using ordered index processing.
 
<p>The CHUNK attribute enables more efficient searching on ordered index numeric range [[Field_Design_(File_Management)#ORDERED_and_NON-ORDERED_attributes|(ORDERED NUMERIC)]] fields by specifying data "chunks." CHUNK is used to define a related field containing data from the original field (the "target" field) rounded down to the CHUNK size.  </p>
==Ordered index CHUNK attribute==
The Ordered Index <var>CHUNK</var> field attribute is available in Model&nbsp;204 version 7.5.  It improves the efficiency of range finds using Ordered Index (OI) processing.
<p>
The OI <var>CHUNK</var> attribute enables more efficient searching on Ordered Index <var>NUMERIC RANGE</var> (<var>[[Field design#ORDERED and NON-ORDERED_attributes|ORDERED NUMERIC]]</var>) fields by specifying "OI chunks" of data. <var>CHUNK</var> is used to define a related field containing data from the original field (the "target" field) rounded down to the OI chunk size.  </p>


===Syntax===
===Syntax===
<p>The format for the DEFINE FIELD command with the CHUNK option and its related attributes is as follows.</p>
<p>
<p>Note that the "CHUNK" keyword can be abbreviated "CNK".</p>
The format for the <var>DEFINE FIELD</var> command with the <var>CHUNK</var> option and its related attributes is as follows.</p>
<p>
Note that the <var>CHUNK</var> keyword can be abbreviated <var>CNK</var>.</p>


<p class="syntax">
<p class="syntax"><span class="literal">DEFINE FIELD</span> <span class="term">chunkFieldName</span> <span class="literal">WITH INVISIBLE ORDERED NUMERIC  
<span class="literal">DEFINE FIELD</span> <span class="term">chunkFieldName</span> <span class="literal">WITH INVISIBLE ORDERED NUMERIC {CHUNK|CNK}</span> <span class="term">chunkSize</span> <span class="literal">FOR</span> <span class="term">chunkTargetFieldName</span>
  {CHUNK|CNK}</span> <span class="term">chunkSize</span> <span class="literal">FOR</span> <span class="term">chunkTargetFieldName</span>
</p>
</p>
   
   
<b>Where:</b>
<p>
<table class="syntaxTable">
Where:</p>
<table>
<tr>
<tr>
<th>chunkFieldName</th>
<th>chunkFieldName</th>
<td>A standard M204 field name.</td>
<td>A standard Model 204 field name.</td>
</tr>
</tr>
<tr>
<tr>
<th>chunkSize</th>
<th>chunkSize</th>
<td>A positive integer that is an integral multiple or divisor of all CHUNK fields previously defined for <var class="term">chunkTargetFieldName</var>.
<td>A positive integer that is an integral multiple or divisor of all OI CHUNK fields previously defined for <var class="term">chunkTargetFieldName</var>.
<p>The maximum chunk size is 2,147,483,647.</p>
<p>
Chunk size also cannot be the same as the chunk size for any other CHUNK field defined for <var class="term">chunkTargetFieldName</var>. If either chunk size rule is violated, the DEFINE command is rejected with an error message.</td>
The maximum OI chunk size is 2,147,483,647.</p>
<p>
OI chunk size also cannot be the same as the OI chunk size for any other <var>CHUNK</var> field defined for <var class="term">chunkTargetFieldName</var>. If either OI chunk size rule is violated, the <var>DEFINE</var> command is rejected with an error message. </p></td>
</tr>
</tr>
<tr>
<tr>
<th>chunkTargetFieldName</th>
<th>chunkTargetFieldName</th>
<td>A previously defined ORDERED NUMERIC VISIBLE field (it must be defined before any CHUNK fields that reference it). The
<td>A previously defined <var>ORDERED NUMERIC VISIBLE</var> field (it must be defined before any <var>CHUNK</var> fields that reference it). The <var>DEFINE</var> command is rejected with an error message if the <var>CHUNK</var> target is not <var>ORDERED NUMERIC VISIBLE</var>. </td></tr>
DEFINE command is rejected with an error message if the chunk target is not ORDERED NUMERIC VISIBLE.
</td>
</tr>
</table>
</table>


===Usage notes===
===Usage notes===
<ul>
<ul>
<li>CHUNK fields must be ORDERED NUMERIC INVISIBLE.</li>
<li>OI <var>CHUNK</var> fields must be <var>ORDERED NUMERIC [[Field_design#VISIBLE_and_INVISIBLE_attributes|INVISIBLE]]</var>. </li>
<li>CHUNK fields can only be defined for ORDERED NUMERIC VISIBLE fields.</li>
 
<li>Any given CHUNK target field can have at most 20 CHUNK fields defined for it.</li>
<li>OI <var>CHUNK</var> fields can only be defined for <var>ORDERED NUMERIC VISIBLE</var> fields. </li>
<li>CHUNK target fields cannot be part of a field group.</li>
 
<li>CHUNK fields are an [[Field_Design_(File_Management)#Automatic_Fields|AUTOMATIC]] field type. Therefore:
<li>Any given OI <var>CHUNK</var> target field can have at most 20 <var>CHUNK</var> fields defined for it.</li>
 
<li>In version 7.5, OI <var>CHUNK</var> target fields cannot be field group members. <br />As of version 7.6, OI <var>CHUNK</var> target fields can be members of field groups.</li>
 
<li>OI <var>CHUNK</var> fields are an [[Field design#Automatic fields|Automatic]] field type. Therefore:
<ul>
<ul>
<li>The CHUNK type is mutually exclusive with all other AUTOMATIC field types (CONCATENATION-OF, COUNT-OCCURRENCES-OF, CREATE-TIME,  
<li>The <var>CHUNK</var> type is mutually exclusive with all other automatic field types (<var>[[Field_design#CONCATENATION-OF_.28CAT.29_attribute|CONCATENATION-OF]]</var>, <var>[[Field_design#COUNT-OCCURRENCES-OF_.28CTO.29_attribute|COUNT-OCCURRENCES-OF]]</var>, <var>[[Field_design#CREATE.2A_and_UPDATE.2A_attributes|CREATE-TIME]]</var>, <var>CREATE-TIMEUTC</var>, <var>CREATE-USER</var>, <var>UPDATE-TIME</var>, <var>UPDATE-TIMEUTC</var>, <var>UPDATE-USER</var>). </li>
CREATE-TIMEUTC, CREATE-USER, UPDATE-TIME, UPDATE-TIMEUTC, UPDATE-USER).</li>
 
<li>Field values for CHUNK fields are set internally as instances of the CHUNK target field are added, deleted, and changed. The value of a CHUNK field is updated at the start of a transaction by Model 204 and cannot be set explicitly by a program.</li>  
<li>Field values for OI <var>CHUNK</var> fields are set internally as instances of the <var>CHUNK</var> target field are added, deleted, and changed. The value of a <var>CHUNK</var> field is updated at the start of a transaction by Model&nbsp;204, and it cannot be set explicitly by a program. </li>  
</ul></li>
</ul></li>
<li>Once you have defined an OI <var>CHUNK</var> field, you cannot redefine it, rename it, or delete it. </li>
</ul>
</ul>
====File restrictions when using CHUNK fields====
 
====File restrictions when using OI CHUNK fields====
<ul>
<ul>
<li>CHUNK fields can only be defined in a FILEORG X'100' file.</li>
<li><var>CHUNK</var> fields can only be defined in a <var>[[FILEORG parameter|FILEORG]]</var> X'100' file. </li>
<li>Once a file has CHUNK fields defined, it cannot be opened with
 
a Model 204 release prior to 7.5.</li>
<li>Once a file has <var>CHUNK</var> fields defined, it cannot be opened with a Model&nbsp;204 version prior to 7.5. </li>
<li>A CHUNK field cannot be defined in a non-empty file.</li>
 
<li>A <var>CHUNK</var> field cannot be defined in a non-empty file.</li>
</ul>
</ul>


====CHUNK target value restrictions====
====OI CHUNK target field value restrictions====
A CHUNK target field can only store values between  
An Ordered Index <var>CHUNK</var> target field can only store values between  
-999999999999999 and 999999999999999 (15 digit signed integer).
-999999999999999 and 999999999999999 (15 digit signed integer).
If an attempt is made to store a value outside of that range, the request is cancelled with the
If an attempt is made to store a value outside of that range, the request is cancelled with the following error:
following error:
<p class="syntax"><span class="literal">CANCELLING REQUEST: M204.1402: Invalid value for CHUNK target:</span>  
<p class="syntax"><span class="literal">CANCELLING REQUEST: M204.1402: Invalid value for chunk target:</span>  
<span class="term">target-field-name</span><span class="literal"> = </span><span class="term">bad-value</span><span class="literal">; values must be -999999999999999->999999999999999</span>       
<span class="term">target-field-name</span><span class="literal"> = </span><span class="term">bad-value</span><span class="literal">; values must be -999999999999999->999999999999999</span>       
</p>
</p>


====DATETIME fields used as CHUNK targets====
====DATETIME fields used as OI CHUNK targets====
<p>
<p>
A DATETIME field that also has the ORD NUM (ORDERED NUMERIC) attribute can be used as a CHUNK target. Remember that independent of CHUNKS, only the 15 leftmost digits
A <var>[[Field_design#DATETIME_.28DT.29_attribute|DATETIME]]</var> field that also has the <var>ORD NUM</var> (<var>ORDERED NUMERIC</var>) attribute can be used as a <var>CHUNK</var> target.  
of a DATETIME field's 20 digits are stored in the ORD NUM index. When
 
computing the values of CHUNKS for a DATETIME target, base the computation
Remember that independent of OI chunks, only the 15 leftmost digits of a <var>DATETIME</var> field's 20 digits are stored in the <var>ORD NUM</var> index. When computing the values of OI chunks for a <var>DATETIME</var> target, base the computation on 15 digits, not 20.  For example:
on 15 digits, not 20.  For example:
</p>
<p class="syntax">
DEFINE TIMESTAMP WITH DT ORD NUM                                           
DEFINE TIMESTAMP_YYYYMM WITH ORD NUM INV CHUNK 1000000000 FOR TIMESTAMP     
DEFINE TIMESTAMP_YYYYMMDD WITH ORD NUM INV CHUNK 10000000 FOR TIMESTAMP     
DEFINE TIMESTAMP_YYYYMMDDHH WITH ORD NUM INV CHUNK 100000 FOR TIMESTAMP     
DEFINE TIMESTAMP_YYYYMMDDHHMM WITH ORD NUM INV CHUNK 1000 FOR TIMESTAMP     
</p>
</p>
 
<p class="code">DEFINE TIMESTAMP WITH DT ORD NUM
====Commands for CHUNK fields====
DEFINE TIMESTAMP_YYYYMM WITH ORD NUM INV CHUNK 1000000000 FOR TIMESTAMP
The following other commands can use CHUNK fields:
DEFINE TIMESTAMP_YYYYMMDD WITH ORD NUM INV CHUNK 10000000 FOR TIMESTAMP
<ul>
DEFINE TIMESTAMP_YYYYMMDDHH WITH ORD NUM INV CHUNK 100000 FOR TIMESTAMP
<li>[[DISPLAY_FIELD_command#Displaying_chunk_fields_and_chunk_targets|DISPLAY FIELD]]</li>
DEFINE TIMESTAMP_YYYYMMDDHHMM WITH ORD NUM INV CHUNK 1000 FOR TIMESTAMP </p>
<li>REDEFINE FIELD. You cannot REDEFINE a non-CHUNK field to a CHUNK field or vice versa. See [[REDEFINE_command#Redefining_CHUNK_fields|REDEFINE FIELD]] for additional rules.</li>
<p>
</ul>
This rule also applies to the automatic date field types (<var>UPTM</var>, <var>UPTMU</var>, <var>CRTM</var>, <var>CRTMU</var>). </p>
 
DELETE is not allowed for CHUNK and CHUNK target fields.
 
RENAME is not allowed for CHUNK and CHUNK target fields.


==Date/time stamp field==
==Date/time stamp field==


===Specifying the date/time stamp field name===
===Specifying the date/time stamp field name===
<p>The system parameter [[DTSFN parameter|DTSFN]] specifies the date/time stamp field name. This field name is the same for all files participating in DTS processing in a run. Though this is a system-level parameter, the feature is a file-level feature.</p>
<p>
<p>The date/time stamp field name you specify is not edited for validity.</p>
The system parameter <var>[[DTSFN parameter|DTSFN]]</var> specifies the date/time stamp field name. This field name is the same for all files participating in DTS processing in a run. Though this is a system-level parameter, the feature is a file-level feature.</p>
<p>The DTSFN value can be specified in CUST source code so you do not have to change all your jobs to specify the parameter. By default, <var class="product">Model&nbsp;204</var> does not provide a date/time stamp field name.</p>
<p>
The date/time stamp field name you specify is not edited for validity.</p>
<p>
The <var>DTSFN</var> value can be specified in [[CUST_module|CUST]] source code so you do not have to change all your jobs to specify the parameter. By default, <var class="product">Model&nbsp;204</var> does not provide a date/time stamp field name.</p>


===Specifying a date/time stamp field definition===
===Specifying a date/time stamp field definition===
<p>A date/time stamp field is defined as is any other <var class="product">Model&nbsp;204</var> field but has the following field attribute restrictions:</p>
<p>
A date/time stamp field is defined as is any other <var class="product">Model&nbsp;204</var> field but has the following field attribute restrictions:</p>
<ul>
<ul>
<li>
<li>A date/time field cannot be INVISIBLE or UNIQUE.</li>
<p>A date/time field cannot be INVISIBLE or UNIQUE.</p>
</li>
   
   
<li>
<li>If the date/time stamp field is preallocated, [[Field_design#Preallocated_fields_.28OCCURS_attribute.29|OCCURS]] cannot be greater than 1.</li>
<p>If the date/time stamp field is preallocated, OCCURS cannot be greater than 1.</p>
</ul>
</li>
<p>
</ul>
Disregarding these restrictions causes the following error:</p>
<p>Disregarding these restrictions causes the following error:</p>
<p class="code">M204.2728: DATE TIME STAMP FIELD MAY NOT BE INVISIBLE, UNIQUE, NOR HAVE MULTIPLE OCCURRENCES
<p class="code">M204.2728: DATE TIME STAMP FIELD MAY NOT BE INVISIBLE, UNIQUE, NOR HAVE MULTIPLE OCCURRENCES
</p>
</p>
<p>Rocket Software recommends using the ORDERED CHARACTER attribute. By default <var class="product">Model&nbsp;204</var> generates a character string date/time stamp field value.</p>
<p>
Rocket Software recommends using the ORDERED CHARACTER attribute. By default <var class="product">Model&nbsp;204</var> generates a character string date/time stamp field value.</p>


===Defining the date/time stamp field value format===
===Defining the date/time stamp field value format===
<p>Your custom code formats the data entered into the field. The format for the default date/time stamp string is:</p>
<p>
Your custom code formats the data entered into the field. The format for the default date/time stamp string is:</p>
<p class="code">CCYYMMDDHHMMSSMMM
<p class="code">CCYYMMDDHHMMSSMMM
</p>
</p>
<p class="note"><b>Note:</b> The DTSFN field can have differing definitions in different <var class="product">Model&nbsp;204</var> databases.</p>
<p class="note"><b>Note:</b> The <var>DTSFN</var> field can have differing definitions in different <var class="product">Model&nbsp;204</var> databases.</p>
[[Category: General user commands]]
 
[[Category: User commands]]
[[Category:Commands]]
[[Category:Commands]]

Latest revision as of 12:28, 12 February 2019

Summary

Status
Generally Available in Model 204 version 7.5:
  • Fields as members of field groups
  • Field attributes specific to FILEORG X'100' files
  • Ordered index CHUNK field attribute
Privileges
Any user
Function
Defines the names and attributes of the fields in a Model 204 file

Syntax

For a field

[IN fileName] DEFINE [FIELD] name [(attribute [attribute]...)]

or

[IN fileName] DEFINE [FIELD] name WITH [attribute [attribute]...]


For a field within a field group

DEFINE [FIELD] name [(attribute [attribute]...)] WITH FIELDGROUP [fieldgroupname | *]

or

DEFINE [FIELD] name WITH FIELDGROUP [fieldgroupname | *] [AND attribute [attribute]...]

Where:

IN fileName Specifies the file within which the field is defined. This may not be a Model 204 group; it must be a single file. If this clause is not present, the default file, as given by the CURFILE parameter, is the file in which the field is defined.

The file in which the field is defined must be OPEN, with file manager privileges.

name or fieldgroupname (1 to 255 characters) must begin with a letter and can contain any alphanumeric characters (including space) except:

??

?$

?&

@ (or an alternative delete character)

# (or an alternative flush character)

 ;

name or fieldgroupname must not start with a reserved word or character. An embedded reserved word or character must be part of a string preceded and followed by a single quotation mark.

attribute One of the field characteristics listed in the table below. Note that each attribute has a default. Therefore, when selected, the attribute need be specified only if it differs from the default. If no attributes are specified, the field is defined with all the defaults. See Field design for more information on field attributes.


Field attributes
Attribute Abbreviation Default
AT-MOST-ONE

REPEATABLE

ONE

REPT

REPT (except in 1NF files)
BINARY BIN Not a binary field
BINARY-LARGE-OBJECT BLOB Not binary large object
CHARACTER-LARGE-OBJECT CLOB Not character large object
CODED

NON-CODED

COD

NCOD

NON-CODED
DATETIME DT Data format YYYYMMDDHHMMSSXXXXXX
(the nearest millionth of a second)
DEFERRABLE

NON-DEFERRABLE

DEF

NDEF

DEFERRABLE
FEW-VALUED

MANY-VALUED

FV

MV

MANY-VALUED
FLOAT FL Not a floating point field
FRV (for-each-value)

NON-FRV

--

NFRV

NON-FRV

KEY

NON-KEY

--

NKEY

NON-KEY

LENGTH m LEN No preallocated length
LEVEL k LVL No level
LRESERVE LRES 15
NRESERVE NRES 15
NUMERIC RANGE

NON-RANGE

RANGE, NR

NNR

NON-RANGE
OCCURS n OCC No preallocated occurrences
ORDERED

NON-ORDERED

ORD

NON-ORD

Non-ordered field
ORDERED CHARACTER ORD CHAR --
ORDERED NUMERIC ORD NUM --
SPLITPCT SPLT 50
IMMED IMM 1
PAD character -- PAD X'00'
STRING

BINARY

STR

BIN

STRING
UPDATE IN PLACE

UPDATE AT END

UP

UE

UPDATE IN PLACE
VISIBLE

INVISIBLE

VIS

INV

VISIBLE
UNIQUE

NON-UNIQUE

UNIQ

NUNIQ

NON-UNIQUE
FIELDGROUP fieldgroupname The containing field group. When fieldgroupname is specified as an asterisk (*), it means that the field can be included in any field group as well as existing outside of any field group.

Conflicting attributes, listed in the table below, cannot be specified together.

Conflicting field attributes
This attribute... Cannot be specified with the attribute(s)...
BINARY, BLOB, CLOB, or CODED LENGTH
CHUNK CONCATENATION-OF, COUNT-OCCURRENCES-OF, CREATE-TIME, CREATE-TIMEUTC, CREATE-USER, UPDATE-TIME, UPDATE-TIMEUTC, UPDATE-USER
CONCATENATION-OF BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, DATETIME, DEFAULT-VALUE, OCCURS > 1, REPEATABLE, STORE-DEFAULT, STORE-NULL, UPDATE AT END
COUNT-OCCURRENCES-OF BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, DATETIME, DBCS, DEFAULT-VALUE, INVISIBLE, OCCURS > 1, REPEATABLE, STORE-DEFAULT, STORE-NULL, UNIQUE, UPDATE AT END
CREATE-TIME BINARY, BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, DBCS, FLOAT, INVISIBLE, NUMERIC RANGE, OCCURS > 1, REPEATABLE, STORE-DEFAULT, STORE-NULL, STRING, UNIQUE, UPDATE AT END
CREATE-TIMEUTC BINARY, BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, FLOAT, INVISIBLE, NOT UNIQUE, NUMERIC RANGE, OCCURS > 1, REPEATABLE, STORE-DEFAULT, STORE-NULL, STRING, UPDATE AT END
CREATE-USER BINARY, BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, DATETIME, FLOAT, INVISIBLE, NUMERIC RANGE, OCCURS > 1, STORE-DEFAULT, STORE-NULL, UNIQUE, UPDATE AT END
DATETIME BINARY, BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, CODED, CONCATENATION-OF, CREATE-USER, KEY, OCCURS, STRING, UPDATE AT END, UPDATE-USER
DATETIME-GE, DATETIME-GT, DATETIME-LE, DATETIME-LT CODED, CONCATENATION-OF, CREATE-USER, KEY, OCCURS, UPDATE AT END, UPDATE-USER
DEFAULT-VALUE REPEATABLE, NO-DEFAULT-VALUE, STORE-NULL LITERAL, STORE-NULL NONE
ESCAPE SEPARATOR NONE
EXACTLY-ONE CONCATENATION-OF, INVISIBLE, STORE-DEFAULT LITERAL, OCCURS, STORE-NULL LITERAL, UNIQUE
FEW-VALUED MANY-VALUED
FIELDGROUP FRV, INVISIBLE, NUMERIC RANGE, OCCURS
FLOAT BINARY, INVISIBLE, NUMERIC RANGE, and STRING
FLOAT-GE, FLOAT-GT, FLOAT-LE, FLOAT-LT, INTEGER-GE, INTEGER-GT, INTEGER-LE, INTEGER-LT Other range attributes
FRV NON-KEY
IMMED NON-ORDERED
INVISIBLE NON-KEY, NON-RANGE, and NON-ORDERED;

UPDATE IN PLACE or UPDATE AT END

LENGTH-EQ Any attribute that is automatically maintained by the system; BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, LENGTH-GE, LENGTH-LE
LENGTH-GE, LENGTH-LE Any attribute that is automatically maintained by the system; BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, LENGTH-EQ
LIKE Any attribute that is automatically maintained by the system; BINARY LARGE OBJECT, CHARACTER LARGE OBJECT
LRESERVE NON-ORDERED
MANY-VALUED FEW-VALUED
MINLOBE OCCURS
NO-DOMAIN-CONSTRAINTS Any constraint attribute
NO-DEFAULT-VALUE DEFAULT-VALUE, STORE-DEFAULT
NON-CODED and NON-FRV MANY-VALUED or FEW-VALUED
NON-KEY, NON-ORDERED, and NON-RANGE DEFERRABLE or NON-DEFERRABLE
NRESERVE NON-ORDERED
NUMERIC RANGE OCCURS n (if n > 1)
OCCURS INVISIBLE
ORDERED FRV
ORDERED NUMERIC NUMERIC RANGE
PAD character LENGTH
SPLITPCT NON-ORDERED
STORE-DEFAULT CONCATENATION-OF, COUNT-OCCURRENCES-OF, CREATE-TIME, CREATE-USER, UPDATE-TIME, UPDATE-USER
STORE-NULL BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, CONCATENATION-OF, COUNT-OCCURRENCES-OF, CREATE-TIME, CREATE-USER, UPDATE-TIME, UPDATE-USER
UNIQUE DEFERRABLE
UPDATE-TIME BINARY, BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, DBCS, FLOAT, INVISIBLE, NUMERIC RANGE, OCCURS > 1, STORE-DEFAULT, STORE-NULL, REPEATABLE, UNIQUE, UPDATE AT END, UPDATE-TIMEUTC
UPDATE-TIMEUTC BINARY, BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, DBCS, FLOAT, INVISIBLE, NUMERIC RANGE, OCCURS > 1, STORE-DEFAULT, STORE-NULL, REPEATABLE, UNIQUE, UPDATE AT END
UPDATE-USER BINARY, BINARY LARGE OBJECT, CHARACTER LARGE OBJECT, DATETIME, FLOAT, INVISIBLE, NUMERIC RANGE, OCCURS > 1, STORE-DEFAULT, STORE-NULL, UNIQUE, UPDATE AT END

Paired attributes, listed in the table below, must be specified together.

Paired field attributes
This attribute... Must be specified with the attribute(s)...
CHUNK ORDERED NUMERIC and INVISIBLE
CONCATENATION-OF AT-MOST-ONE, EXACTLY-ONE, or OCCURS 1
COUNT-OCCURRENCES-OF AT-MOST-ONE, EXACTLY-ONE, or OCCURS 1
CREATE-TIME AT-MOST-ONE
CREATE-TIMEUTC AT-MOST-ONE
DEFAULT-VALUE AT-MOST-ONE or EXACTLY-ONE
ESCAPE CONCATENATION-OF
FEW-VALUED FRV or CODED
FLOAT LENGTH
MANY-VALUED FRV or CODED
MINLOBE BINARY LARGE OBJECT or CHARACTER LARGE OBJECT
OCCURS CODED, BINARY, or LENGTH
PAD LENGTH
SEPARATOR CONCATENATION-OF
STORE-DEFAULT DEFAULT-VALUE, AT-MOST-ONE, or EXACTLY-ONE
UNIQUE ORDERED
UPDATE-TIME, UPDATE-TIMEUTC AT-MOST-ONE

Syntax notes

Attributes must be separated by commas or by one or more blanks.

You can specify DEFINE FIELD only in file context.

For general syntax and usage notes that apply to all forms of the DEFINE command, see DEFINE command.

Example

DEFINE FIELD CUSTID WITH KEY FRV RANGE DEFINE FIELD AGE WITH KEY RANGE OCCURS 1 LENGTH 3 DEFINE FIELD EMPLOYEE

Usage notes

The DEFINE FIELD command describes the names and attributes of the fields in a Model 204 file. The file must be initialized, using the INITIALIZE command, before fields can be defined.

Once fields have been defined, a user with the requisite privileges can perform the following commands on them:

Record security and sort and hash key fields

Sort key fields for sorted files, hash key fields, and record security fields can be defined only with the INITIALIZE command, not with the DEFINE FIELD command.

Floating point fields

You can define three types of floating point fields through the FLOAT and LENGTH attributes. The table below shows the characteristics of the three types.

Types of floating point fields
Type Number of bytes Maximum number of significant digits
Short precision 4 6
Long precision 8 15
Extended precision 16 31

You supply a LENGTH attribute of 4, 8, or 16. The following example defines a long-precision floating-point field:

DEFINE FIELD SALARY WITH FLOAT LEN 8

Consider making a field a floating-point field if its value is always or often numerical. Very large numbers or numbers with many significant digits use much less space in floating-point form than they do in character form. Floating-point fields are valid only for files with the format of Release 8.0 or later or that have been converted by the TRANSFORM FLOAT command. Floating-point numbers in ORDERED NUMERIC trees are discussed as follows.

Defining a field group field

A field defined within a field group supports the same default attributes as fields defined outside or independent of a field group, except for REPEATABLE. A field defined within a field group defaults to EXACTLY-ONE. You can override the default with REPEATABLE. The attribute for frequency of occurrence of the field being is one of these:

  • EXACTLY-ONE, the default for a field defined within a DEFINE FIELDGROUP block
  • REPEATABLE, the default for a field defined outside of a field group
  • AT-MOST-ONE

ORDERED attribute

The ORDERED attribute is used to define fields for the Ordered Index feature. The ORDERED attribute can be followed by a tree type to specify the kind of ordering for the index. The tree type can be either CHARACTER (CHAR) or NUMERIC (NUM). If a tree type is not provided, a default tree type is used.

Syntax

The format for the DEFINE FIELD command with solely the ORDERED option and its related attributes is as follows:

DEFINE [FIELD] name WITH [ORDERED [CHARACTER | NUMERIC] [LRESERVE n] [NRESERVE n] [SPLITPCT n] [IMMED n]]

Where:

LRESERVE The percentage of space to leave free on the leaf page during deferred updates or a REORGANIZE command. The default is 15 percent. The value must be between 0 and 99.
NRESERVE The percentage of space to leave free on the node page during deferred updates or a REORGANIZE command. The default is 15 percent. The value must be between 0 and 99.
SPLITPCT The percentage of the node data to go to the left node when a split occurs during nondeferred SOUL or IFAM requests. The default is 50 percent. The value must be between 1 and 100.
IMMED The number of immediate pointers per segment in the Ordered Index. The default is 1. The value must be between 0 and 255.

Usage notes

The LRESERVE, NRESERVE, SPLITPCT, and IMMED parameters represent approximate values. The actual movement of data during an update might not conform exactly to the value of the attribute in question.

The default ordering for a field is NON-ORDERED. Ordered Index attributes do not apply for a NON-ORDERED field.

If a field is defined as ORDERED without specifying the type of tree to create, the default depends on whether the field is STRING, BINARY, or FLOAT. If STRING, an ORDERED CHARACTER tree is defined. If FLOAT or BINARY, an ORDERED NUMERIC tree is defined.

The UNIQUE attribute specifies that there can be only one occurrence of a unique field in the file. Multiple occurrences are not allowed.

For a detailed discussion of field attributes, see Field design.

Ordered index CHUNK attribute

The Ordered Index CHUNK field attribute is available in Model 204 version 7.5. It improves the efficiency of range finds using Ordered Index (OI) processing.

The OI CHUNK attribute enables more efficient searching on Ordered Index NUMERIC RANGE (ORDERED NUMERIC) fields by specifying "OI chunks" of data. CHUNK is used to define a related field containing data from the original field (the "target" field) rounded down to the OI chunk size.

Syntax

The format for the DEFINE FIELD command with the CHUNK option and its related attributes is as follows.

Note that the CHUNK keyword can be abbreviated CNK.

DEFINE FIELD chunkFieldName WITH INVISIBLE ORDERED NUMERIC {CHUNK|CNK} chunkSize FOR chunkTargetFieldName

Where:

chunkFieldName A standard Model 204 field name.
chunkSize A positive integer that is an integral multiple or divisor of all OI CHUNK fields previously defined for chunkTargetFieldName.

The maximum OI chunk size is 2,147,483,647.

OI chunk size also cannot be the same as the OI chunk size for any other CHUNK field defined for chunkTargetFieldName. If either OI chunk size rule is violated, the DEFINE command is rejected with an error message.

chunkTargetFieldName A previously defined ORDERED NUMERIC VISIBLE field (it must be defined before any CHUNK fields that reference it). The DEFINE command is rejected with an error message if the CHUNK target is not ORDERED NUMERIC VISIBLE.

Usage notes

  • OI CHUNK fields must be ORDERED NUMERIC INVISIBLE.
  • OI CHUNK fields can only be defined for ORDERED NUMERIC VISIBLE fields.
  • Any given OI CHUNK target field can have at most 20 CHUNK fields defined for it.
  • In version 7.5, OI CHUNK target fields cannot be field group members.
    As of version 7.6, OI CHUNK target fields can be members of field groups.
  • OI CHUNK fields are an Automatic field type. Therefore:
    • The CHUNK type is mutually exclusive with all other automatic field types (CONCATENATION-OF, COUNT-OCCURRENCES-OF, CREATE-TIME, CREATE-TIMEUTC, CREATE-USER, UPDATE-TIME, UPDATE-TIMEUTC, UPDATE-USER).
    • Field values for OI CHUNK fields are set internally as instances of the CHUNK target field are added, deleted, and changed. The value of a CHUNK field is updated at the start of a transaction by Model 204, and it cannot be set explicitly by a program.
  • Once you have defined an OI CHUNK field, you cannot redefine it, rename it, or delete it.

File restrictions when using OI CHUNK fields

  • CHUNK fields can only be defined in a FILEORG X'100' file.
  • Once a file has CHUNK fields defined, it cannot be opened with a Model 204 version prior to 7.5.
  • A CHUNK field cannot be defined in a non-empty file.

OI CHUNK target field value restrictions

An Ordered Index CHUNK target field can only store values between -999999999999999 and 999999999999999 (15 digit signed integer). If an attempt is made to store a value outside of that range, the request is cancelled with the following error:

CANCELLING REQUEST: M204.1402: Invalid value for CHUNK target: target-field-name = bad-value; values must be -999999999999999->999999999999999

DATETIME fields used as OI CHUNK targets

A DATETIME field that also has the ORD NUM (ORDERED NUMERIC) attribute can be used as a CHUNK target. Remember that independent of OI chunks, only the 15 leftmost digits of a DATETIME field's 20 digits are stored in the ORD NUM index. When computing the values of OI chunks for a DATETIME target, base the computation on 15 digits, not 20. For example:

DEFINE TIMESTAMP WITH DT ORD NUM DEFINE TIMESTAMP_YYYYMM WITH ORD NUM INV CHUNK 1000000000 FOR TIMESTAMP DEFINE TIMESTAMP_YYYYMMDD WITH ORD NUM INV CHUNK 10000000 FOR TIMESTAMP DEFINE TIMESTAMP_YYYYMMDDHH WITH ORD NUM INV CHUNK 100000 FOR TIMESTAMP DEFINE TIMESTAMP_YYYYMMDDHHMM WITH ORD NUM INV CHUNK 1000 FOR TIMESTAMP

This rule also applies to the automatic date field types (UPTM, UPTMU, CRTM, CRTMU).

Date/time stamp field

Specifying the date/time stamp field name

The system parameter DTSFN specifies the date/time stamp field name. This field name is the same for all files participating in DTS processing in a run. Though this is a system-level parameter, the feature is a file-level feature.

The date/time stamp field name you specify is not edited for validity.

The DTSFN value can be specified in CUST source code so you do not have to change all your jobs to specify the parameter. By default, Model 204 does not provide a date/time stamp field name.

Specifying a date/time stamp field definition

A date/time stamp field is defined as is any other Model 204 field but has the following field attribute restrictions:

  • A date/time field cannot be INVISIBLE or UNIQUE.
  • If the date/time stamp field is preallocated, OCCURS cannot be greater than 1.

Disregarding these restrictions causes the following error:

M204.2728: DATE TIME STAMP FIELD MAY NOT BE INVISIBLE, UNIQUE, NOR HAVE MULTIPLE OCCURRENCES

Rocket Software recommends using the ORDERED CHARACTER attribute. By default Model 204 generates a character string date/time stamp field value.

Defining the date/time stamp field value format

Your custom code formats the data entered into the field. The format for the default date/time stamp string is:

CCYYMMDDHHMMSSMMM

Note: The DTSFN field can have differing definitions in different Model 204 databases.