DEFINE FIELD command: Difference between revisions

From m204wiki
Jump to navigation Jump to search
m (Automatically generated page update)
 
No edit summary
Line 14: Line 14:
   
   
<b>Where:</b>
<b>Where:</b>
<table>  
<table>
<tr>
<tr>
<th>name</th>
<th>name</th>
Line 31: Line 31:
<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. All attributes are described in the <var>Model&nbsp;204 File Manager's Guide.</var><b> </b>
<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. All attributes are described in the <var>Model&nbsp;204 File Manager's Guide.</var><b> </b>
<table>
<!-- ******************************************************************
<caption>Field attributes</caption>
 
   
  The table is in a template (but please do not edit it by hand):
<tr>  <th>Attribute </th> <th>Abbreviation </th> <th>Default</th> </tr>
 
 
-->
<tr> <th><var>AT-MOST-ONE
{{Template:DEFINE FIELD attributes}}
<p>REPEATABLE</p>
<!-- ****************************************************************** -->  
</var></th> <td>ONE
<p>REPT</p>
</td> <td>REPT (except in 1NF files)</td> </tr>
 
<tr>  <th><var>BINARY</var></th> <td>BIN</td> <td>Not a binary field</td> </tr>
<tr> <th><var>BINARY-LARGE-OBJECT</var></th> <td>BLOB</td> <td>Not binary large object</td> </tr>
<tr> <th><var>CHARACTER-LARGE-OBJECT</var></th> <td>CLOB</td> <td>Not character large object</td> </tr>
<tr> <th><var>CODED
<p>NON-CODED</p>
</var></th> <td>COD
<p>NCOD</p>
</td> <td>NON-CODED</td> </tr>
 
<tr> <th><var>DEFERRABLE
<p>NON-DEFERRABLE</p>
</var></th> <td>DEF
<p>NDEF</p>
</td> <td>DEFERRABLE</td> </tr>
 
<tr> <th><var>FEW-VALUED
<p>MANY-VALUED</p>
</var></th> <td>FV
<p>MV</p>
</td> <td>MANY-VALUED</td> </tr>
 
<tr> <th><var>FLOAT</var></th> <td>FL</td> <td>Not a floating point field</td> </tr>
<tr> <th><var>FRV (for-each-value)
<p>NON-FRV</p>
</var></th> <td>
<p>--</p>
<p>NFRV</p>
</td> <td>NON-FRV</td> </tr>
 
<tr> <th><var>
<p>KEY</p>
<p>NON-KEY</p>
</var></th> <td>
<p>--</p>
<p>NKEY</p>
</td> <td>
<p>NON-KEY</p>
</td> </tr>
 
<tr> <th><var>LENGTH m</var></th> <td>LEN</td> <td>No preallocated length</td> </tr>
<tr> <th><var>LEVEL k</var></th> <td>LVL</td> <td>No level</td> </tr>
<tr> <th><var>LRESERVE</var></th> <td>LRES</td> <td>15</td> </tr>
<tr> <th><var>NRESERVE</var></th> <td>NRES</td> <td>15</td> </tr>
<tr> <th><var>NUMERIC RANGE
<p>NON-RANGE</p>
</var></th> <td>RANGE, NR
<p>NNR</p>
</td> <td>NON-RANGE </td> </tr>
 
<tr> <th><var>OCCURS n</var></th> <td>OCC</td> <td>No preallocated occurrences</td> </tr>
<tr> <th><var>ORDERED
<p>NON-ORDERED</p>
</var></th> <td>ORD
<p>NON-ORD</p>
</td> <td>Non-ordered field</td> </tr>
 
<tr> <th><var>ORDERED CHARACTER</var></th> <td>ORD CHAR</td> <td>--</td> </tr>
<tr> <th><var>ORDERED NUMERIC</var></th> <td>ORD NUM</td> <td>--</td> </tr>
<tr> <th><var>SPLITPCT</var></th> <td>SPLT</td> <td>50</td> </tr>
<tr> <th><var>IMMED</var></th> <td>IMM</td> <td>1</td> </tr>
<tr> <th><var>PAD character</var></th> <td>--</td> <td>PAD X'00'</td> </tr>
   
<tr> <th><var>STRING
<p>BINARY</p>
</var></th> <td>STR
<p>BIN</p>
</td> <td>STRING </td> </tr>
<tr> <th><var>UPDATE IN PLACE
<p>UPDATE AT END</p>
</var></th> <td>UP
<p>UE</p>
</td> <td>UPDATE IN  PLACE</td> </tr>
 
<tr> <th><var>VISIBLE
<p>INVISIBLE</p>
</var></th> <td>VIS
<p>INV</p>
</td> <td>VISIBLE</td> </tr>
 
<tr> <th><var>UNIQUE
<p>NON-UNIQUE</p>
</var></th> <td>UNIQ
<p>NUNIQ </p>
</td> <td>NON-UNIQUE</td> </tr>
   
</table>
</td>
</td>
</tr>
</tr>
Line 149: Line 45:
<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> <th>This attribute...</th> <th>Cannot be specified with the attribute(s)...</th> </tr>
 
<tr> <th><var>INVISIBLE</var></th> <td>NON-KEY, NON-RANGE, and NON-ORDERED</td> </tr>
<tr> <th><var>INVISIBLE</var></th> <td>NON-KEY, NON-RANGE, and NON-ORDERED</td> </tr>
   
   
Line 161: Line 57:
   
   
<tr> <th><var>NON-ORDERED, NON-KEY, and NON-RANGE</var></th> <td>DEFERRABLE or NON-DEFERRABLE</td> </tr>
<tr> <th><var>NON-ORDERED, NON-KEY, and NON-RANGE</var></th> <td>DEFERRABLE or NON-DEFERRABLE</td> </tr>
 
<tr> <th><var>CODED, BINARY, CLOB, and BLOB</var></th> <td>LENGTH</td> </tr>
<tr> <th><var>CODED, BINARY, CLOB, and BLOB</var></th> <td>LENGTH</td> </tr>
   
   
Line 167: Line 63:
   
   
<tr> <th><var>FLOAT</var></th> <td>STRING, BINARY, NUMERIC RANGE, and INVISIBLE</td> </tr>
<tr> <th><var>FLOAT</var></th> <td>STRING, BINARY, NUMERIC RANGE, and INVISIBLE</td> </tr>
 
<tr> <th><var>OCCURS</var></th> <td>INVISIBLE</td> </tr>
<tr> <th><var>OCCURS</var></th> <td>INVISIBLE</td> </tr>
   
   
Line 183: Line 79:
   
   
<tr> <th><var>UNIQUE</var></th> <td>DEFERRABLE </td> </tr>
<tr> <th><var>UNIQUE</var></th> <td>DEFERRABLE </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> <th>This attribute...</th> <th>Must be specified with the attribute(s)...</th> </tr>
 
<tr> <th><var>FLOAT</var></th> <td>LENGTH</td> </tr>
<tr> <th><var>FLOAT</var></th> <td>LENGTH</td> </tr>
   
   
Line 198: Line 94:
   
   
<tr> <th><var>UNIQUE</var></th> <td>ORDERED</td> </tr>
<tr> <th><var>UNIQUE</var></th> <td>ORDERED</td> </tr>
 
</table>
</table>
===Syntax notes===
===Syntax notes===
Line 204: Line 100:
<p>You can specify DEFINE FIELD only in file context.</p>
<p>You can specify DEFINE FIELD only in file context.</p>
==Example==
==Example==
<p class="code">DEFINE FIELD CUSTID WITH KEY FRV RANGE  
<p class="code">DEFINE FIELD CUSTID WITH KEY FRV RANGE
DEFINE FIELD AGE WITH KEY RANGE OCCURS 1 LENGTH 3
DEFINE FIELD AGE WITH KEY RANGE OCCURS 1 LENGTH 3
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 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.
<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 DEFINE.</p>
<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 DEFINE.</p>
<p>Once fields have been defined, a user with the requisite privileges can perform the following functions on them:</p>
<p>Once fields have been defined, a user with the requisite privileges can perform the following functions on them:</p>
<ul>  
<ul>
<li>
<li>
<p>Delete (DELETE command)</p>
<p>Delete (DELETE command)</p>
Line 232: Line 128:
<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> <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 240: Line 136:
   
   
<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 LENGTH 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 TRANSFORM FLOAT command. Floating-point numbers in ORDERED NUMERIC trees are discussed as follows.    </p>
Line 249: Line 145:
<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 DEFINE FIELD command with solely the ORDERED option and its related attributes is as follows:</p>
==Syntax==
==Syntax==
<p class="syntax">DEFINE [FIELD] <i>name</i> WITH [ORDERED [<b>CHAR</b>ACTER | <b>NUM</b>ERIC]  
<p class="syntax">DEFINE [FIELD] <i>name</i> WITH [ORDERED [<b>CHAR</b>ACTER | <b>NUM</b>ERIC]
  [LRESERVE <i>n</i>] [NRESERVE <i>n</i>] [SPLITPCT <i>n</i>] [IMMED <i>n</i>]]
  [LRESERVE <i>n</i>] [NRESERVE <i>n</i>] [SPLITPCT <i>n</i>] [IMMED <i>n</i>]]
</p>
</p>
   
   
<b>Where:</b>
<b>Where:</b>
<table>  
<table>
<tr>
<tr>
<th><var>LRESERVE</var></th>
<th><var>LRESERVE</var></th>
Line 276: Line 172:
   
   
</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>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>
Line 289: Line 185:
====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. The restrictions on permissible field attributes are:</p>
<p>A date/time stamp field is defined as is any other <var class="product">Model&nbsp;204</var> field. The restrictions on permissible field attributes are:</p>
<ul>  
<ul>
<li>
<li>
<p>May not be INVISIBLE or UNIQUE.</p>
<p>May not be INVISIBLE or UNIQUE.</p>

Revision as of 19:23, 8 March 2013

Summary

Privileges
Any user
Function
Defines the names and attributes of the fields in a Model 204 file

Syntax

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

or

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

Where:

name (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 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 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. All attributes are described in the Model 204 File Manager's Guide.
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

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

Conflicting field attributes
This attribute... Cannot be specified with the attribute(s)...
INVISIBLE NON-KEY, NON-RANGE, and NON-ORDERED
INVISIBLE UPDATE IN PLACE or UPDATE AT END
FRV NON-KEY
NON-CODED and NON-FRV MANY-VALUED or FEW-VALUED
NON-ORDERED, NON-KEY, and NON-RANGE DEFERRABLE or NON-DEFERRABLE
CODED, BINARY, CLOB, and BLOB LENGTH
NUMERIC RANGE OCCURS n (if n > 1)
FLOAT STRING, BINARY, NUMERIC RANGE, and INVISIBLE
OCCURS INVISIBLE
ORDERED FRV
ORDERED NUMERIC NUMERIC RANGE
LRESERVE NON-ORDERED
NRESERVE NON-ORDERED
SPLITPCT NON-ORDERED
IMMED NON-ORDERED
UNIQUE DEFERRABLE

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

Paired field attributes
This attribute... Must be specified with the attribute(s)...
FLOAT LENGTH
OCCURS CODED, BINARY, or LENGTH
PAD LENGTH
UNIQUE ORDERED

Syntax notes

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

You can specify DEFINE FIELD only in file context.

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.

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

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

  • Delete (DELETE command)

  • Display (DISPLAY command)

  • Redefine (REDEFINE command)

  • Rename (RENAME command)

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.

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.

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

Syntax

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

Where:

LRESERVE 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.
NRESERVE 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.
SPLITPCT 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.
IMMED defines 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, refer to the Model 204 File Manager's Guide.

Specifying the date/time stamp field name

The new 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 may be specified in CUST source code, so you do not have to change all your jobs to specify the parameter. In the Rocket Model 204 Parameter and Command Reference see "DTSFN: Name of the date/time stamp field." As shipped, Model 204 does not provide a field name.

Specifying a date/time stamp field definition

A date/time stamp field is defined as is any other Model 204 field. The restrictions on permissible field attributes are:

  • May not be INVISIBLE or UNIQUE.

  • If preallocated, OCCURS may not 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 may have differing definitions in different Model 204 databases.