ALTER TABLE

Invocation

• ALTER TABLE syntax
• table-options syntax
• data capture changes syntax
• exit_attributes syntax
• alter-options syntax
• column-alteration syntax
• datatype syntax
• ims-column syntax
• inline-constraint syntax
• constraint syntax
• references-clause syntax
• map-definition syntax
• case-definition syntax

ALTER TABLE syntax

table-options syntax

data capture changes syntax

exit_attributes syntax

Notes:

• ¹ BEFORE, NOBEFORE, DLET, NODLET, SSPCMD, NOSSPCMD, FLD, and NOFLD are for DEDB only.

alter-options syntax

Notes:

• ¹ The same clause must not be specified more than one time, except for the ADD COLUMN or ALTER COLUMN clauses. If multiple ADD COLUMN clauses are specified in the same statement, at most one ADD COLUMN clause can contain a references-clause.
• ² See column-definition descriptions from CREATE TABLE section.
• ³ ADD constraint, DROP PRIMARY KEY, DROP FOREIGN KEY clauses are mutually exclusive and can only have one per ALTER TABLE statement.
• ⁴ ADD constraint, DROP PRIMARY KEY, DROP FOREIGN KEY clauses are mutually exclusive and can only have one per ALTER TABLE statement.

column-alteration syntax

datatype syntax

ims-column syntax

inline-constraint syntax

constraint syntax

references-clause syntax

map-definition syntax

Notes:

• ¹ Specifies the control segment name and control field name

case-definition syntax

Keyword parameters for ALTER TABLE

Specifying the DATA CAPTURE keyword replaces all data capture exits that were previously supplied, if any. If multiple data capture exits were previously supplied they must all be respecified if needed.

ALTER TABLEtblname

Identifies the table to be altered. The name must identify a table that exists at the current database.

Specify an external name as a 1 to 128 character uppercase alphanumeric string. A table name can include the underscore character. Table names cannot be reserved SQL keywords or begin with DFS.

Keyword parameters for ALTER TABLE (table-options)

SOURCE

Is the IMS internal table name and is used for two purposes:

• To identify the real logical child segment type that is to be represented by the virtual logical child segment type that is being defined
• To identify the segment type or types in physical databases that are represented by the segment type being defined in a logical database

Restriction: The SOURCE keyword is not allowed for PHDAM and PHIDAM databases because they support only physical pairing.

When defining a virtual logical child the statement is:

segname

Specifies the name of the real, logical child

DATA

Indicates that both the key and the data portions of segname are to be used in constructing the segment. This parameter is required.

dbname

Specifies the name of the physical database that contains the real logical child.

When defining a segment type in a logical database the statement is:

(segname, KEY | DATA,dbname)

The first occurrence refers to the segment in a physical database that is being defined as a logical segment, or it refers to the logical child segment type in a physical database that is used for the first portion of a concatenated segment type in this logical database.

segname

Is the name of the segment type in the physical database.

KEY

Specifies that the key portion of the segment specified in segname is to be placed in the key feedback area. The segment must not be placed in the user I/O area when a call is issued to process the logical segment type that represents segname.

DATA

Specifies that the key portion of the segment specified in segname must be placed in the key feedback area, and the segment must be placed in the user I/O area when a call is issued to process the logical segment type that represents segname.

dbname

Specifies the name of the physical database that contains segname. The second occurrence of (segname, KEY|DATA, dbname) refers to the logical or physical parent segment type in a physical database that is used for the destination parent part of a concatenated segment in this logical database. The description of each parameter for the second occurrence is the same as described for the first occurrence.

When the first occurrence of (segname, KEY | DATA, dbname) refers to a virtual logical child, the second occurrence, if specified, must refer to the real logical child's physical parent.

When the source segments is used to represent a concatenated segment, the KEY and DATA parameters are used to control which of the two segments (or both) are placed in the user's I/O area on retrieval calls. If DATA is specified, the segment is placed in the user's I/O area. If KEY is specified, the segment is not placed in the user's I/O area, but the sequence field key, if one exists, is placed in the key feedback area of the PCB. The key of a concatenated segment is the key of the logical child, either the physical twin sequence field or the logical twin sequence field, depending on which path the logical child is accessed from. The KEY and DATA parameters apply to retrieval type calls only.

On insert calls, the user's I/O area must always contain the logical child segment and, unless the insert rule is physical, the logical parent segment. Even if KEY is specified for a segment, the database containing that segment must be available to IMS when calls are issued against the logical database containing the referenced segment. When the first occurrence of the SOURCE segment specification references a logical child, the second occurrence referencing the destination parent for the concatenated segment should also be specified. If not explicitly specified it is included with the KEY parameter by default when the blocks are built.

The segments defined with a logical DBD generation must gain their physical definition from segments previously defined in one or more physical DBD generations.

If the SEGM statement defines a segment in an INDEX data set, the SOURCE parameter is invalid.

MAXBYTES maxbytes

MINBYTES minbytes

Defines a segment type as variable-length if the minbytes parameter is included. The maxbytes field specifies the maximum length of any occurrence of this segment type. The maximum and minimum allowable values for the maxbytes parameter are the same values as described for a fixed-length segment.

If the segment is processed by a compression routine, set the maxbytes field to accommodate control information to indicate whether the segment length can be longer than the specified maximum definition. in order to avoid an abend 0799. To allow for the expansion, add an arbitrary value of 10 bytes to the maxbytes.

The minbytes parameter specifies the minimum amount of storage used by a variable-length segment. The maximum value for minbytes is the value specified for maxbytes. The minimum value for minbytes must be:

• For a segment type that is not processed by an edit/compression routine or is processed by an edit/compression routine but the key compression option has not been specified, minbytes must be large enough to contain the complete sequence field if a sequence field has been specified for the segment type.
• For a segment type that is processed by an edit/compression routine that includes the key compression option or a segment that is not sequenced, the minimum value is 4.

Because segments in an HSAM, SHSAM, INDEX, PSINDEX or SHISAM database cannot be variable-length, the minbytes parameter is invalid for these databases.

In a Fast Path DEDB, a segment starts with a 2-byte field, which defines the length of the segment including the 2-byte length field, followed by user data specified by a column. The value of minbytes can be specified from a minimum of 4 bytes to a maximum of maxbytes; however, the minbytes value must be large enough to contain this segment's sequence field (that is, minbytes ≥ START - 1 + BYTES of the sequence field following the table). For example, the smallest minbyte value for a segment with a 20-byte sequence field length and START = 7 is 26. On any given DL/I call, the actual segment length can fall anywhere between a length that includes the sequence field and the value of maxbytes. The value of maxbytes must not exceed the control interval size minus 120.

TWINBWD | NOTWIN | TWIN | HIER | HIERBWD

Specifies the pointer fields to be reserved in the prefix area of occurrences of the segment type being defined. These fields are used to relate this segment to its immediate parent segments and twin segments.

TWINBWD

Reserves a 4-byte physical twin forward pointer field and a 4-byte physical twin backward pointer field in the segment prefix being defined. The twin backward pointers provide increased delete performance.

Recommendation: This option is recommended for HIDAM and PHIDAM database root segments.

NOTWIN

Prevents space from being reserved for a physical twin forward pointer in the prefix of occurrences of the segment type being defined.

NOTWIN can be specified for a dependent segment type if:

• The physical parent does not have hierarchic pointers specified.
• No more than one occurrence of the dependent segment type is stored as a physical child of any occurrence of the physical parent segment type.

In addition, NOTWIN can be specified for the root segment type of HDAM and PHIDAM databases, but only when the randomizing module does not produce synonyms (keys with different values having the same block and anchor point).

When NOTWIN is specified for a dependent segment type and an attempt is made to load or insert a second occurrence of the dependent segment as a physical child of a given physical parent segment:

• An LB status code is returned when trying to insert the second occurrence during initial load.
• An II status code is returned when trying to insert the second occurrence after initial load.

Any attempt to load or insert a synonym is rejected with an LB or II status code.

TWIN

Reserves a 4-byte physical twin forward pointer field in the segment prefix being defined.

HIER

Reserves a 4-byte hierarchic forward pointer field in the prefix of occurrences of the segment type being defined. HALDB does not support HIER.

HIERBWD

Reserves a 4-byte hierarchic forward pointer field and a 4-byte hierarchic backward pointer field in the prefix of occurrences of the segment type being defined. Hierarchic backward pointers provide increased delete performance. HALDB does not support HIERBWD.

LPARNTYES | LPARNTNO

Specifies the type of logical parent.

LPARNTYES

This parameter can be specified only when the segment type that is being defined is a logical child and the logical parent is in an HDAM, HIDAM, PHDAM, or PHIDAM database. If the logical parent is in a HISAM database, omit this parameter and specify PHYSICAL in the PARENT= parameter for the segment that is being defined.

For HDAM, HIDAM, and HISAM databases, LPARNT reserves a 4-byte logical parent pointer field in the prefix of occurrences of the segment type being defined.

For PHDAM and PHIDAM databases, LPARNT reserves a 28-byte extended pointer set in the prefix of occurrences of the segment type being defined.

LPARNTNO

Specifies that the segment type that is being defined is not a logical child or the logical parent is not in an HDAM, HIDAM, PHDAM, or PHIDAM database.

PAIREDYES | PAIREDNO

Specifies whether this segment participates in a bidirectional logical relationship.

PAIREDYES

Indicates that this segment participates in a bidirectional logical relationship. This parameter is specified for the following types:

• A virtual logical child segment type
• Both physically paired logical child segment types in a bidirectional logical relationship

If PAIRED is specified, the LTWIN and LTWINBWD parameters are invalid.

PAIREDNO

Indicates that this segment does not participate in a bidirectional logical relationship.

CTRNO | CTRYES

Specifies...

CTRNO

Does not reserve a 4-byte counter field in the prefix of occurrences of the segment type being defined.

CTRYES

Reserves a 4-byte counter field in the prefix of occurrences of the segment type being defined. A counter is required if a logical parent segment in a HISAM, HDAM, or HIDAM database has logical child segments which are not connected to it by logical child pointers. Counters are placed in all segments requiring them automatically during DBD generation without the user specifying this parameter. To avoid a later DBD generation, however, the user can anticipate future requirements for counters and reserve a counter field in the prefix of occurrences of a segment type by using this parameter. HALDB does not support CTR.

INSERT {LOGICAL | PHYSICAL | VIRTUAL}

DELETE {LOGICAL | PHYSICAL | VIRTUAL | BIDIRECTIONAL}

REPLACE {LOGICAL | PHYSICAL | VIRTUAL}

Specifies the rules used for insertion, deletion, and replacement of occurrences of the segment type being defined. These parameters are specified for logical child segments and for their physical and logical parent segments. They should be omitted for all segment types that do not participate in logical relationships.

AMBIGUOUS INSERT {LAST | FIRST | HERE}

Specifies where new occurrences of the segment type defined by this table are inserted into their physical database (establishes the physical twin sequence). This value is used only when processing segments with no sequence field or with a nonunique sequence field. The value is ignored when specified for a segment type with a unique sequence field defined.

Except for HDAM and PHDAM roots, the rules of FIRST, LAST, or HERE do not apply to the initial loading of a database and segments are loaded in the sequence presented in load mode. If a unique sequence field is not defined for the HDAM root on initial load or HD reload, the insert rules of FIRST, LAST, or HERE determine the sequence in which roots are chained. Thus the reload of an HDAM or PHDAM database reverses the order of the unsequenced roots when HERE or FIRST is used.

LAST is the default except for DEDB segments.

For Fast Path sequential dependent segment processing, the insert rule of FIRST is always used and cannot be overridden. For direct dependent segment processing, you can specify FIRST, LAST, or HERE. HERE is the default.

FIRST

For segments without a sequence field defined, a new occurrence is inserted before all existing physical twins. For segments with a nonunique sequence field defined, a new occurrence is inserted before all existing physical twins with the same sequence field value.

LAST

For segments without a sequence field defined, a new occurrence is inserted after all existing physical twins. For segments with a nonunique sequence field defined, a new occurrence is inserted after all existing physical twins with the same sequence field value.

HERE

For segments without a sequence field, a new occurrence is inserted immediately before the physical twin on which position was established. If a position was not established on a physical twin of the segment being inserted, the new occurrence is inserted before all existing physical twins. For segments with a nonunique sequence field defined, a new occurrence is inserted immediately before the physical twin with the same sequence field value on which position was established. If a position was not established on a physical twin with the same sequence field value, the new occurrence is inserted before all physical twins with the same sequence field value. The insert position is dependent on the position established by the previous DL/I call.

A command code of L (last) takes precedence over the insert rule specified causing a new occurrence to be inserted according to the insert rule of LAST, for insert calls issued against a physical path.

DSGROUP

Specifies multiple data set groups for PHDAM and PHIDAM databases. The format is DSGROUP c, where c is equivalent to the letters A through J. This enables you to divide PHDAM and PHIDAM databases into a maximum of ten data set groups. The default for every segment is A (single set for data per partition). If specified on the root segment, it must be DSGROUP A.

Restriction: Gaps in the A-J sequence are not allowed. For example, if DSGROUP C is specified on a CREATE TABLE statement, there must also be at least one CREATE TABLE statement with DSGROUP B, and each HALDB partition will have A, B, and C data sets.

FREQ frequency

Specifies the estimated number of times that this segment is likely to occur for each occurrence of its physical parent. The frequency parameter must be an unsigned decimal number in the range 0.01 to 2²⁴-1. If this is a root segment, frequency is the estimate of the maximum number of database records that appear in the database being defined. The value of the FREQ parameter when applied to dependent segments is used to determine the logical record length and physical storage block sizes for each data set group of the database.

CCSID encoding

An optional 1- to 25-character field that specifies the encoding of the character data in the segment.

The value specified on the CCSID parameter cannot contain the following characters:

• Single and double quotation marks
• Blanks
• Less than (< ) and greater than ( >) symbols
• Ampersands (&)

The value of the CCSID parameter in the table overrides the value of the CCSID parameter in the database for this segment. If the CCSID parameter is not specified on the table, the default value is either the value of the CCSID parameter on the database or, if CCSID was not specified on the database, the value Cp1047, which specifies EBCDIC encoding.

This value can be overridden in individual fields by the CCSID parameter in the column definition.

SSPTR n

For databases defined with a DEDB access type only. Specifies the number of subset pointers. You can specify from 0 to 8. When you specify 0 or if SSPTR is not specified, you are not using a subset pointer.

EDITPROC routinename

Selects a Segment Edit/Compression exit routine for either DEDB or full-function database.

For segment edit/compression of full-function database

Do not specify this keyword if the SOURCE keyword is used. The DL/I EDITPROC keyword is invalid for HSAM, SHSAM, SHISAM, INDEX, and logical databases. It is also invalid for logical child segments in any database. When used for a HISAM database, it must not change the sequence field offset for HISAM root segments. In addition, the minimum segment length that can be specified for a segment type where the segment edit/compression option is specified is 4 bytes.

Remember: If you are using a segment edit/compression exit routine and defined your segments as variable-length, be aware that when a variable-length segment is compressed, it is padded with null bytes up to the minimum segment length that was defined in the DBD. Minimum segment length essentially overrides the compression; this enables you to provide additional space during load time for segments that are heavily compressed.

routinename

Specifies the name of the user-supplied edit/compression exit routine. This name must be a 1- to 8-character alphanumeric value, must not be the same as any other name in IMS.SDFSRESL, and must not be the same as a database name.

DATA

Specifies that the indicated exit routine condenses or modifies data fields only. Sequence fields must not be modified, nor data fields that change the position of the sequence field in respect to the start of the segment. DATA is the default value if a compression routine is named but no parameter is selected.

KEY

Specifies that the exit routine can condense or modify any fields within the named segment. This parameter is invalid for the root segment of a HISAM database.

INIT

Indicates that initialization and termination processing control is required by the segment exit routine. When this parameter is specified, the edit/compression routine gains control after database open and after database close.

max

Specifies the maximum number of bytes by which fixed-length segments can increase during compression exits. You can specify from 1 to 32 767 bytes. The default for max is 10.

PAD

Indicates that the numeric value supplied by MAX should be used for padding and not for MAX. The numeric range of 1 to 32 767 indicates a size to which an inserted segment will be padded when the compression of that segment results in a length somewhat less than the PAD value.

For segment edit/compression of DEDB

routinename

Specifies the z/OS® load module name of the user-supplied segment edit/compression exit routine. The routine name is required.

DATA

Specifies that only the user data part of the segment is compressed. DATA is the default.

Restriction: The KEY parameter is not supported for DEDB. If you specify the KEY parameter, an error message is issued.

INIT

Allows the segment compression exit routine to gain control immediately after the first area in the database is opened and returns control immediately before the last area in the database is closed. As long as the segment length is within the values specified, no errors occur while checking the field qualification for segment compression or expansion.

Restriction: The EDITPROC clause is prohibited on DEDB tables containing a unique key field located at the end of the table.

LPARENT table_name {VIRTUAL | PHYSICAL}

Specifies the logical parent of the table being defined.

table_name

Specifies the IMS internal table name and the name of the logical parent of the table being defined. If the logical parent resides within the same database then you may just specify the table name. If the logical parent resides in a different database then you must specify both the database and table name, such as "database_name.tablename".

VIRTUAL | PHYSICAL

Specifies whether the concatenated key of the logical parent (LPCK) is stored as a part of the logical child segment. Specify the parameter only for logical child segments. If PHYSICAL is specified, the LPCK is stored with each logical child segment. If VIRTUAL is specified, the LPCK is not stored in the logical child segment. PHYSICAL must be specified for a logical child segment whose logical parent is in a HISAM database. It must be specified also for a logical child segment that is sequenced on its physical twin chain through use of any part of the concatenated key of the logical parent.

• PHDAM and PHIDAM
  • PHYSICAL is the default for PHDAM and PHIDAM.
  • If VIRTUAL is specified for PHDAM or PHIDAM, it is ignored, and PHYSICAL is used.
• HDAM and HIDAM
  • VIRTUAL is the default for HDAM and HIDAM.
  • Symbolic pointers in HDAM and HIDAM databases use the LPCK and require the PHYSICAL specification.

Keyword parameters for ALTER TABLE data capture changes (change_syntax)

DATA CAPTURE

When DATA CAPTURE is specified on the CREATE DATABASE statement, these options apply to all tables within the physical database. If you specify this parameter in the CREATE or ALTER TABLE statement, it overrides the specification for this statement.

The following physical databases support DATA CAPTURE:

• HISAM
• SHISAM
• HDAM
• PHDAM
• HIDAM
• PHIDAM
• DEDB

NONE

Indicates no data capture options.

CHANGES

You can specify any number of exit routines, each with its own set of change options. If you do not provide an exit routine, they can only specify 1 set of change options for logging. This method is equivalent to specifying an asterisk (*) in place of an exit routine name on the EXIT= parameter in a DBD macro statement. Each set is separated by a comma. NOCASCADE is mutually exclusive with any combination of the C* (for example, CKEY) options.

The following options are valid for DATA CAPTURE CHANGES:

NOBEFORE | BEFORE

Before data is included in X'99' log records for REPL calls. NOBEFORE is the default. This attribute is valid only for DEDB.

CDATA | CNODATA

Passes segment data to the exit routine for a cascade delete. CDATA also identifies the segment being deleted when the physical concatenated key is unable to do so. This attribute is mutually exclusive with NOCASCADE.

CKEY | CNOKEY

Passes the physical concatenated key to the exit. This key identifies the segment being deleted by a cascade delete. This attribute is mutually exclusive with NOCASCADE.

CNOPATH | CPATH

Indicates the exit routine does not require segment data in the physical root's hierarchical path. Use CNODATA to eliminate the substantial amount of path data needed for a cascade delete. This attribute is mutually exclusive with NOCASCADE.

DATA | NODATA

DATA specifies that the physical table data is passed to the exit routine for updating. When DATA is specified and an EDITPROC exit routine is also used on a table, the data passed is expanded data. DATA is the default.

NODLET |DLET

X'99' log records are written for DLET calls. NODLET is the default. This attribute is valid only for DEDB.

exitname

Specifies the name of the exit routine that processes the data. The name must match the name of a Data Capture exit routine as defined by the user to IMS. A maximum of 8 alphanumeric characters is allowed.

KEY | NOKEY

KEY specifies the exit routine is passed the physical concatenated key. This key identifies the physical table updated by the application. KEY is the default.

NOCASCADE

Indicates the exit routine is not called when DL/I deletes this segment. Cascade delete is not necessary when a segment without dependents is deleted.

NOFLD | FLD

The FLD option requests that updates made by a DEDB FLD call be captured. This option is valid only for a DEDB, and this information is logged only in the X'9904' log records if the option log is specified. This information is not passed to the data capture exit. This attribute is valid only for DEDB.

NOINPOS | INPOS

The INPOS option requests that twin data be passed when an ISRT is done for an unkeyed or non-unique keyed segment when an insert rule of HERE is used and the F or L command code is not used. The twin data IMS is positioned on at the time before the ISRT will be captured.

NOLOG | LOG

The LOG option requests that the data capture control blocks and data be written to the IMS system log.

For more information, see Asynchronous data propagation.

NOPATH | PATH

NOPATH indicates the exit routine does not require data from tables in the physical root's hierarchical path. NOPATH is an efficient way to avoid the processing time needed to retrieve path data. NOPATH is the default.

PATH can be specified when the data from each segment in the physical root's hierarchical path must be passed to the exit routine for an updated segment. Use PATH to allow an application to separately access several segments for insertion, replacement, or deletion.

You can use the PATH option when information from tables in the path is needed to compose the DB2® for z/OS primary key. The DB2 for z/OS primary key would then be used in a propagation request for a dependent table update. Typically, you need this kind of table information when the parent contains the key information and the dependent contains additional data that would not fit in the parent table.

You can also use PATH when additional processing is necessary. It could be that you are not accessing several tables with one call; for example, you did not invoke the D command code. In this case, additional processing is necessary if the application is to access each table with a separate call.

NOSSPCMD | SSPCMD

The SSPCMD option requests that DEDB subset pointer command codes are captured. This option is valid for DEDBs only.

Keyword parameters for ALTER TABLE (alter-options)

Specifying the DATA CAPTURE keyword replaces all data capture exits that were previously supplied, if any. If multiple data capture exits were previously supplied they must all be respecified if needed.

Keyword parameters for ALTER TABLE (column-definition)

column_name

column_name represents the external name that is stored only in the IMS catalog, not in the database that you are defining. Specify an external name as a 1- to 128-character uppercase alphanumeric string. An external name can include underscore characters. Column names must be unique within a segment.

Restriction: Column names cannot be reserved SQL keywords or begin with DFS.

For a list of the reserved SQL keywords that are restricted by the IMS Universal drivers, refer to Portable SQL keywords restricted by the IMS Universal JDBC drivers.

INTERNALNAME internalname

Specifies the name of this field within a segment type. The name can be referred to by an application program in a DL/I call SSA. Field names must be unique within a segment definition. The fldname1 value must be a 1- to 8-character alphanumeric value. The INTERNALNAME parameter is required on the following types of fields:

• Key-sequenced field types, which specify the SEQ parameter
• Field types that are referenced by a segment search argument (SSA)
• Field types that are referenced by a PSB as a sensitive field.
• Field types that are referenced by an XDFLD

For other field types, you can omit the INTERNALNAME parameter. Omitting the INTERNALNAME parameter can save storage in the data management block (DMB) of a database. However, to be able to search on this field, you must specify the INTERNALNAME parameter. The INTERNALNAME parameter cannot be specified on the following types of fields:

• Fields that are defined as arrays. A field that is defined as an array includes DATATYPE=ARRAY in the field definition.
• Fields that are defined as array elements. A field that is an array element specifies the name of an array field on the PARENT parameter in the FIELD statement.
• Fields that are defined as structures that contain one or more nested dynamic arrays. A field that is defined as a structure includes DATATYPE=STRUCT in the field definition.
• Fields that are contained in a structure that also contains a dynamic array. A field that is contained within a structure specifies the name of the structure field on the PARENT parameter in the FIELD statement.
• Fields that follow a dynamic array in a segment. A field that follows a dynamic array specifies the STARTAFTER parameter.
• Fields that include the RELSTART parameter to specify a starting position that is relative to the starting position of another field.
• Fields that are defined with DATATYPE=XML.

Keyword parameters for ALTER TABLE (datatype)

ARRAY | BINARY | ...

An optional 3- to 9-character alphanumeric field that specifies the external data type of the field.

If DECIMAL is specified on the DATATYPE parameter, the default INTERNAL TYPECONVERTER is signed PACKEDDECIMAL.

If DATE, TIME, or TIMESTAMP is specified on the DATATYPE parameter, you must specify either LONG or CHAR on the INTERNAL TYPECONVERTER parameter in the column definition or specify a USER TYPECONVERTER. If a column definition is not included for this field, INTERNAL TYPECONVERTER LONG is the default. When LONG is used, the value is stored on DASD as the number of milliseconds since January 1, 1970.

If XML is specified on the DATATYPE parameter, the default INTERNAL TYPECONVERTER is XML_CLOB, which is the only valid value when DATATYPE=XML is specified.

If STRUCT or ARRAY is specified on the DATATYPE parameter, the default INTERNAL TYPECONVERTER is STRUCT or ARRAY, respectively, which are the only valid values when either one is specified on the DATATYPE parameter.

For all other values for DATATYPE, the value is used as the default INTERNAL TYPECONVERTER.

If TYPE=C, DATATYPE defaults to CHAR. For any other specification of the TYPE parameter, DATATYPE defaults to BINARY.

Valid values are:

ARRAY

When ARRAY is specified:

• The INTERNALNAME parameter is not supported
• The byte value specified on either the BYTES or MAXBYTES parameter must be equal to or greater than the sum total of the bytes of all fields contained in the array.

The MSDB database type does not support the ARRAY data type.

You cannot redefine a field that has been defined as an ARRAY or that contains an ARRAY.

A field that is defined as an array includes DATATYPE=ARRAY in the field definition.

A field that is an array element specifies the name of an array field on the PARENT parameter in the FIELD statement.

BINARY

If TYPE=P or TYPE=X is specified, BINARY is the default value of the DATATYPE parameter.

BIT

If you specify BIT, you must also specify BYTES=1.

BYTE

If you specify BYTE, you must also specify BYTES=1.

UBYTE

If you specify UBYTE, you must also specify BYTES=1.

CHAR

If TYPE=C is specified, CHAR is the default value of the DATATYPE parameter.

DATE

When DATE is specified, you must also specify BYTES=8, unless you also specify a column definition that includes either INTERNAL TYPECONVERTER CHAR or USER TYPECONVERTER convertername.

DECIMAL(pp,ss)

pp

Precision. A 1- to 2-byte numeric field greater than 0.

ss

Scale. A 1- to 2-byte numeric field greater than or equal to 0. The value specified for ss cannot be greater than the value of pp.

You must specify a value on the BYTES parameter that matches the decimal format that is used.

The default decimal format is signed packed decimal. To calculate the required value of the BYTES parameter for the signed packed decimal format, use the following formula: length = ceiling ( ( pp + 1) / 2 )

The default decimal format can be changed by specifying the INTERNAL TYPECONVERTER parameter.

When the zoned decimal format is used, as specified by INTERNAL TYPECONVERTER ZONEDDECIMAL, use the following formula to calculate the value of the BYTES parameter: length = pp

DOUBLE

If you specify DOUBLE, you must also specify BYTES=8.

FLOAT

If you specify FLOAT, you must also specify BYTES=4.

INT

If you specify INT, you must also specify BYTES=4.

UINT

If you specify UINT, you must also specify BYTES=4.

LONG

If you specify LONG, you must also specify BYTES=8.

ULONG

If you specify ULONG, you must also specify BYTES=8.

OTHER

Specifies the use of a user-defined data type. When OTHER is specified, a column definition must also be specified with a user-provided type converter specified on the USER TYPECONVERTER parameter.

SHORT

If you specify SHORT, you must also specify BYTES=2.

USHORT

If you specify USHORT, you must also specify BYTES=2.

STRUCT

When STRUCT is specified, you cannot also specify the SEQ parameter if this structure field contains a dynamic array field as a child. Dynamic array fields are defined with DATATYPE=ARRAY and the DEPENDSON and MAXBYTES parameters, among others.

Also, the byte value specified on either the BYTES or MAXBYTES parameter must be equal to or greater than the sum total of the bytes of all fields contained in the structure.

The MSDB database type does not support the STRUCT datatype.

TIME

When TIME is specified, you must also specify BYTES=8, unless you also specify a column definition that includes either INTERNAL TYPECONVERTER CHAR or USER TYPECONVERTER convertername.

TIMESTAMP

When TIMESTAMP is specified, you must also specify BYTES=8, unless you also specify a column definition that includes either INTERNAL TYPECONVERTER CHAR or USER TYPECONVERTER convertername.

XML

Restriction: DATATYPE=XML is not supported when the NAME parameter is specified.

Keyword parameters for ALTER TABLE (ims-column-syntax)

BYTES bytes

Specifies the length of the field being defined in bytes. For fields other than system-related fields, BYTES must be a valid self-defining term whose value does not exceed 255.

If a concatenated key or a portion of a concatenated key of an index source segment type is defined as a system-related field, the value specified can be greater than 255, but must not exceed the length of the concatenated key of the index source segment.

A case in which the byte length can be greater than 255 is when the column is defined as not searchable by IMS. These columns cannot be defined as primary keys and cannot have the NAME keyword specified.

The length of a /SX system-related field is always 4 bytes; therefore, when specified, the BYTES parameter is disregarded.

If this field is defined as either a structure or an array by STRUCT or ARRAY, the value specified on BYTES must be greater than or equal to the sum total of the bytes of all fields contained in the structure or array.

When XML, the BYTES parameter is optional and the valid values for BYTES range from 0 to the maximum size of the segment. If the BYTES parameter is omitted when XML, BYTES and MAXBYTES are not allowed.

CCSID encoding

An optional 1- to 25-character field enclosed in single quotation marks that specifies the encoding of the character data in the column. It is valid only when the INTERNAL TYPECONVERTER is CHAR.

The value specified cannot contain the following characters:

• Single and double quotation marks
• Blanks
• Less than (< ) and greater than ( >) symbols
• Ampersands (&)

If not specified on for the column, the default value is determined by the value specified on either the table or, if not specified on the table, the database. If the parameter is not specified on either the table or database, the default value is Cp1047, which specifies EBCDIC encoding.

DEPENDSON=

Specifies the name of a field that defines the number of elements in a dynamic array. The FIELD statement of the referenced field must precede the FIELD statement that specifies the DEPENDSON parameter. The name specified must be the value, whether explicitly defined or accepted by default, of the EXTERNALNAME parameter in the definition of the referenced field.

The DEPENDSON parameter is valid only when DATATYPE=ARRAY is also specified. DEPENDSON is required if the values of MINOCCURS and MAXOCCURS are different.

The field referenced by the DEPENDSON parameter must be defined with one of the following DATATYPE values:

• INT
• SHORT
• LONG
• DECIMAL with either (pp) or (pp,ss) specified, where ss is either 0 or 00.

TYPE {C | X | P}

Determines the type of character that IMS uses to mask or pad the data in this field.

C

Specifies alphanumeric data or a combination of types of data. When C is specified, if IMS needs to fill unused bytes in the field, IMS left justifies the value and fills the unused bytes to the right of the value with X'40'. For example, a 3-byte value X'F5F4F3' in a 5-byte field is written out as X'F5F4F34040'.

X

Specifies hexadecimal data. When X is specified, if IMS needs to fill unused bytes in the field, IMS right justifies the value and fills the unused bytes to the left of the value with X'00'. For example, a 3-byte value X'543210' in a 5-byte field is written out as X'0000543210'.

P

Packed decimal data. When P is specified, if IMS needs to fill unused bytes in the field, IMS right justifies the value and fills the unused bytes to the left of the value with X'00'. For example, a 3-byte value X'54321C' in a 5-byte field is written out as X'000054321C'.

MAXBYTES max_array_bytes

Specifies the maximum size of a field in bytes when the byte-length of the field instance can vary based on the number of elements in a dynamic array. MAXBYTES and BYTES are mutually exclusive.

The value of MAXBYTES must be greater than or equal to the maximum total of the byte values of all fields nested under this field.

The MAXBYTES parameter is required and valid only in the following cases:

• The field is defined as a dynamic array. A field is a dynamic array when the number of elements in the array can vary from one instance of the field to another. In the definition of a dynamic array, the DEPENDSON parameter references another field in the segment definition that defines the number of array elements for an instance of the dynamic array.
• For a field defined as a static array or a structure that contains a nested field that is defined as a dynamic array.

The MSDB database type does not support the MAXBYTES parameter.

IN column_name

Specifies the name of a field that is defined as a structure or array in which this field is contained. The referenced field must be defined with either DATATYPE=ARRAY or DATATYPE=STRUCT.

INTERNAL TYPECONVERTER

Specifies the internal conversion routine that IMS uses to convert the IMS data into the data types that are expected by the application program.

You can specify either INTERNAL TYPECONVERTER or USER TYPECONVERTER, but not both. INTERNAL TYPECONVERTER or USER TYPECONVERTER are mutually exclusive.

Valid values for the INTERNAL TYPECONVERTER parameter are:

ARRAY

An array is a data structure that contains as element that repeats.

BINARY

Binary integer includes small integer, large integer, and big integer. Binary numbers are exact representations of integers.

BIT

If you specify BIT, you must also specify BYTES 1 on the corresponding column.

BLOB

The BLOB value can be specified on the INTERNALTYPECONVERTER parameter only when DATATYPE=BINARY is specified in the preceding FIELD statement.

BYTE

If you specify BYTE, you must also specify BYTES 1 on the corresponding column.

UBYTE

If you specify UBYTE, you must also specify BYTES 1 and either DATATYPE BYTE or DATATYPE UBYTE on the corresponding column.

CHAR

The CHAR value can be specified on the INTERNALTYPECONVERTER parameter only when CHAR, DATE, TIME, or TIMESTAMP is specified on the DATATYPE parameter in the preceding FIELD statement.

DOUBLE

If you specify DOUBLE, you must also specify BYTES 8 on the corresponding column.

FLOAT

If you specify FLOAT, you must also specify BYTES 4 on the corresponding column.

INT

If you specify INT, you must also specify BYTES 4 on the corresponding column.

UINT

If you specify UINT, you must also specify BYTES 4 and either DATATYPE INT or DATATYPE UINT on the corresponding column.

LONG

If you specify LONG, you must also specify BYTES 8 on the corresponding column.

ULONG

If you specify ULONG, you must also specify BYTES 8 and either DATATYPE LONG or DATATYPE ULONG on the corresponding column.

PACKEDDECIMAL

PACKEDDECIMAL is a data type extension for the IMS Universal JDBC driver and the IMS Universal DL/I driver.

SHORT

If you specify SHORT, you must also specify BYTES 2 on the corresponding column.

USHORT

If you specify USHORT, you must also specify BYTES 2 and either DATATYPE SHORT or DATATYPE USHORT on the corresponding column.

STRUCT

XML_CLOB

A Character Large Object (CLOB) is a collection of character data in a database management system.

ZONEDDECIMAL

ZONEDDECIMAL is a data type extension for the IMS Universal JDBC driver and the IMS Universal DL/I driver. You need to specify DATATYPE DECIMAL.

When you specify INTERNAL TYPECONVERTER, you must also specify the DATATYPE parameter.

The value that is specified on the INTERNAL TYPECONVERTER parameter must be consistent with the value specified on the DATATYPE parameter. In most cases, you must specify the same value on INTERNAL TYPECONVERTER that you specify on the DATATYPE parameter.

ISSIGNEDYES | ISSIGNEDNO

This parameter is valid only for a DECIMAL data type. The options supported are ISSIGNEDYES and ISSIGNEDNO.

MINOCCURS min_array_elements

For ARRAY only, a required numeric value that specifies the minimum number of elements in an ARRAY. MINOCCURS must be lesser than or equal to MAXOCCURS.

MAXOCCURS max_array_elements

For ARRAY only, a required numeric value that specifies the maximum number of elements in an ARRAY. MAXOCCURS must be greater than or equal to MINOCCURS and not zero.

OVERFLOW table_name

A 1- to 8-character name of a dependent table that can be used to store any portion of an XML document that does not fit into the column that is defined to hold the XML document. The parent of the dependent table is the table that contains the XML data column. OVERFLOW applies only to columns that specify DATATYPE XML for XML_CLOB data.

PATTERN

An optional 1- to 50-character field, enclosed in single quotation marks, that specifies the pattern to use for the date, time, and time stamp Java datatypes.

PATTERN applies only when DATE, TIME, or TIMESTAMP is specified on the DATATYPE keyword and CHAR is specified on the INTERNAL TYPECONVERTER keyword. PATTERN is invalid for other datatypes.

Patterns are case-sensitive and must be enclosed in single quotation marks.

Except for single quotation marks that are used as delimiters for the keyword value, the value that is specified on the PATTERN keyword cannot contain the following characters:

• Single and double quotation marks
• Less than (< ) and greater than ( >) symbols
• Ampersands (&)

Patterns that you can specify are defined by the Java class java.text.SimpleDateFormat. DDL does not check that the value entered on PATTERN conforms to the patterns defined by Java.

For example, if you enter the Java format yyyy.MM.dd, the resulting time format is "2013.01.01".

PROPERTIES name=value

Specifies properties for a user type converter that is specified on the USER TYPECONVERTER parameter. These properties are passed to the user type converter.

The PROPERTIES parameter is valid only when USER TYPECONVERTER is specified.

The names and properties that are specified on the PROPERTIES keyword are case-sensitive and must be enclosed in single quotation marks.

The following characters are not supported by the PROPERTIES keyword:

• Single and double quotation marks
• Blanks
• Less than (< ) and greater than ( >) symbols
• Ampersands (&)

The maximum length for a property name is 128 characters. The maximum length for a property value is also 128 characters.

The format is:

PROPERTIES=('name1' = 'value1' , 'name2' = 'value2')

For example,

PROPERTIES=('DOG' = 'BUTCH' , 'CAT' = 'LUCY')

PUREDBCSYES | PUREDBCSNO

This parameter is valid only for a CHAR data type. See the DFSMARSH statement parameter description for this parameter in DFSMARSH statements.

RELSTART relative_start_position

Specifies the starting position of a field that is defined as an element of an array or, in some circumstances, a structure. Valid values are from 1 to 32767.

The value specified on RELSTART is the starting byte offset of the field relative to the start of the array or structure. For example, the first field in an array would typically specify RELSTART 1, even if the array that contains the field starts at byte 50 of a segment.

For fields that specify an array field as a parent, RELSTART is required.

For fields that specify a structure as a parent, RELSTART is required if the structure field is defined with RELSTART or STARTAFTER.

In the following example, the field DYNARRAY is a dynamic array. The field STRUCT01 is a structure. The fields FLD03 and FLD04 both specify STRUCT01 as a parent. Because a dynamic array precedes STRUCT01 in the segment, the starting offsets of FLD03 and FLD04 can be specified only relative to the start of STRUCT01.

FIELD EXTERNALNAME=ARRAYNUM,DATATYPE=DECIMAL(7,0),START=1,BYTES=4
FIELD EXTERNALNAME=DYNARRAY,DATATYPE=ARRAY,START=5,MAXBYTES=100 
      MINOCCURS=10,MAXOCCURS=50,DEPENDSON=ARRAYNUM
FIELD EXTERNALNAME=FLD01,RELSTART=1,BYTES=2,PARENT=DYNARRAY
FIELD EXTERNALNAME=FLD02,STARTAFTER=DYNARRAY,BYTES=10
FIELD EXTERNALNAME=STRUCT01,DATATYPE=STRUCT,STARTAFTER=FLD02,BYTES=10
FIELD EXTERNALNAME=FLD03,RELSTART=1,BYTES=5,PARENT=STRUCT01
FIELD EXTERNALNAME=FLD04,RELSTART=6,BYTES=5,PARENT=STRUCT01

START, STARTAFTER, and RELSTART are mutually exclusive.

START start_position

Specifies the starting position of the field being defined in terms of bytes relative to the beginning of the segment. The value of START must be a numeric term whose value does not exceed 32767. The starting position for the first byte of a segment is one. For variable-length segments, the first 2 bytes contain the length of the segment. Therefore the first actual user data field starts in byte 3. Overlapping fields are permitted. When defining a logical child segment, the first n number of bytes of the segment type is the concatenated key of the logical or physical parent. A field starting in position one would define all or a portion of this field. A field starting in position n+1 would start with intersection data.

START can be used for a system-related field, to describe a portion of the concatenated key as a field in an index source segment type. If used in this way, START specifies the starting position of the relevant portion of the concatenated key relative to the beginning of the concatenated key. The first byte of the concatenated key is considered to have a position of one. It must be a numeric term whose value does not exceed the length of the concatenated key plus one. Subtract the value specified in the BYTES parameter. The starting position parameter for the /SX system-related field is disregarded.

START, STARTAFTER, and RELSTART are mutually exclusive.

When XML, the START parameter is optional and START 0 can be specified. If the START parameter is omitted when XML, START 0 is the default.

STARTAFTER field_name

When the starting byte offset of a field cannot be calculated because the field starts after a dynamic array, specifies the name of the field that directly precedes this field in the segment. The name cannot be the name provided on the NAME keyword.

STARTAFTER is required and valid only when the starting position of a field cannot be calculated because the field is preceded at a prior offset by a field defined as a dynamic array.

Dynamic arrays make it impossible to calculate the starting offsets of subsequent fields in a segment, because the byte lengths of dynamic arrays can vary from one instance of a segment to another. The columns of dynamic array fields can be identified by the inclusion of the DEPENDSON and MAXBYTES parameters.

The STARTAFTER parameter cannot be specified on fields that define an array field as a parent. Instead, specify the RELSTART parameter.

START, STARTAFTER, and RELSTART are mutually exclusive.

The STARTAFTER data is not returned if the GUR application is using IMS Universal Drivers (UDB).

URL xml_schema_url

An optional 1- to 256-character field, enclosed in single quotation marks, for the URL that references the XML schema that describes this field.

For example,

URL='MySchema.xsd'

The value that is specified on the URL keyword cannot contain the following characters:

• Single and double quotation marks
• Blanks
• Less than (< ) and greater than ( >) symbols
• Ampersands (&)

The URL parameter applies only with DATATYPE XML for XML_CLOB data.

USER TYPECONVERTER typeconverter

Specifies a 1- to 256-character, enclosed in single quotation marks, fully qualified Java class name of the user-provided Java class to be used for type conversion.

For example,

USER TYPECONVERTER 'class://com.ibm.ims.dli.types.PackedDateConverter'

The value that is specified on the USER TYPECONVERTER keyword cannot contain the following characters:

• Single and double quotation marks
• Blanks
• Less than (< ) and greater than ( >) symbols
• Ampersands (&)

USER TYPECONVERTER is mutually exclusive with INTERNAL TYPECONVERTER.

Keyword parameters for ALTER TABLE (constraint)

CONSTRAINT constraint_name

Names the constraint. If a constraint name is not specified, a unique constraint name is generated. If the name is specified, it must be different from the names of any constraints previously specified on the table.

PRIMARY KEY(column_name) NON UNIQUE

Identifies this field as a sequence field in the segment type.

NON UNIQUE

An optional keyword that indicates that duplicate values are allowed in the sequence field of occurrences of the segment type. For a root segment type, the sequence field of each occurrence must contain a unique value, except in HDAM. The root segment type in an HDAM database does not need a key field; if a key field is defined, it does not have to be unique.

If not specified, only unique values are allowed in the sequence field of occurrences of the segment type. For a dependent segment type, the sequence field of each occurrence under a given physical parent segment must contain a unique value.

Keyword parameters for ALTER TABLE (references-clause)

FOREIGN KEY REFERENCES

For dependent segment types, specifies the name of this segment's physical parent. The column_name must follow the format of x_y, where x is the parent table name and y is that table's primary key column name.

REFERENCES table_name

Specifies the dependent segments parent segment and is the IMS external table name.

SINGLE|DOUBLE

Specifies the type of physical child pointers to be placed in all occurrences of the physical parent of the current table. SINGLE and DOUBLE can be specified only for tables in PHDAM, PHIDAM, HDAM, HIDAM or DEDB databases and are ignored if the physical parent specifies hierarchic pointers (HIER or HIERBWD).

SINGLE causes a 4-byte physical child first pointer to be placed in all occurrences of the physical parent of the current table. SINGLE is the default.

DOUBLE causes a 4-byte physical child first pointer and a 4-byte child last pointer to be placed in all occurrences of the physical parent of the current table.

DROP PRIMARY KEY

Drops the definition of the primary key.

DROP FOREIGN KEY

Drops the definition of the foreign key.

Keyword parameters for ALTER TABLE (map-definition)

MAP

A map definition must be preceded by a column definition. The MAP statement enables the alternate mapping of columns within a table. A group of one or more CASE statements that relate to a control column is nested within the table. The control column identifies which CASE is used in a table instance.

column_name

The column_name parameter is the external name of the control field within this segment that contains the value to determine which map case is used for a given segment instance. When table_name is also specified, the external name of the control field must be a field within the key range of the segment specified in table_name. Otherwise, the external name of the control field must be within this segment. If the column does not contain a value that corresponds to a caseid value in a CASE statement for this map, this map is not used for this table instance.

table_name.column_name

Specifies the control segment name and control field name.

The table_name parameter is an optional parameter that specifies the internal name of a segment with key feedback data that is used to determine which map case to use for a given segment instance. The segment specified must be in the hierarchical path of the current segment for its value to be stored in the IMS Catalog.

The table_name parameter specifies an internal name as a 8-character alphanumeric string. Each character must be in the range of A through Z, or 0 through 9, or be the character $, #, or @. The first character cannot be a numeric character. An external name as a 128-character alphanumeric string is also accepted but DDL stores an internal segment name in the IMS Catalog and runtime blocks. GUR DL/I call will return an internal segment name in the mapping tag of the XML.

Notes:

1. The table_name.column_name value cannot be specified for GSAM, MSDB, logical and index databases. For these access types, use column_name instead of table_name.column_name.
2. The table_name value is ignored if it is the name of the current segment.

AS map_name

An optional 1- to 128-character alphanumeric field that defines the name of this map. If not provided, IMS will automatically generate a unique name within this table. The name must be in the form of DFSMAPxxxxxxxx, where xxxxxxxx is an incremental number. The DFS prefix is reserved by IMS and cannot be part of a user-created name.

Keyword parameters for ALTER TABLE (case-definition)

CASE

The CASE statement defines a map case, which is a set of columns that define an optional, alternative field layout for a particular byte range within a table.

Map cases that map the same byte range in a segment are grouped by a MAP statement. The MAP statement also links the map cases to a separately defined control field in the table definition.

Each map case has a unique ID. In an instance of the table, the ID of the map case that is in effect is stored in the control field when the segment is created.

Unless the IMS Universal drivers are used, the field layouts that are defined by the map cases must be defined to the application programs that access this byte range by a COBOL copybook or other programming artifact. When a table instance is accessed, the application programs determine which copybook to use by checking the value of the control field.

When application programs access IMS through the IMS Universal drivers, no additional programming artifacts are needed to define the field layouts to the application programs.

caseid

A 1- to 128 byte field that defines a unique character or hex string. A table instance specifies the caseid value in a user-defined control field when part or all of the field structure of the segment is mapped by this case.

When specified as a character string the value must be specified within single quotes, for example: 'name01'. When specified as a hex string the value must be specified within single quotes followed by a hex indicator, for example: '00000001'x.

The caseid value can contain alphanumeric characters, underscore (_), @, $, and #. Or, it can be a hexadecimal string. The length of the value must be supported by the length of the user-defined control field. If alphanumeric, the length of the value must be less than or equal to the value specified on the BYTES parameter of the control field. If it is a hexadecimal string, the length of the CASEID value must be exactly equal to twice the value that is specified on the BYTES parameter of the control field.

A case ID must be unique within the map that the case belongs to.

AS case_name

An optional 1- to 128-character alphanumeric field that defines the name of this case. A case name must be unique within a table. If not provided, IMS will automatically generate a unique name within this table. The name must be in the form of DFSCASExxxxxxxx, where xxxxxxxx is an incremental number. The DFS prefix is reserved by IMS and cannot be part of a user-created name.

Example: COGDBD

DBD   NAME=COGDBD,                                               C
               ENCODING=Cp1047,                                        C
               ACCESS=(HDAM,OSAM),                                     C
               RMNAME=(DFSHDC40,3,3,25),                               C
               PASSWD=NO
      DATASET  DD1=COGDATA,                                            C
               DEVICE=3390,                                            C
               SIZE=(8192)
      SEGM  NAME=ROOT,                                                 C
               PARENT=0,                                               C
               BYTES=(28),                                             C
               RULES=(LLL,HERE)
      FIELD NAME=(ROOTKEY,SEQ,U),                                      C
               BYTES=12,                                               C
               START=1,                                                C
               TYPE=C,                                                 C
               DATATYPE=CHAR
      FIELD NAME=TABTYPE,                                              C
               BYTES=8,                                                C
               START=13,                                               C
               TYPE=C,                                                 C
               DATATYPE=CHAR
      FIELD NAME=NEWFLD01,                                             C
               EXTERNALNAME=New_Field_01,                              C
               BYTES=8,                                                C
               START=21,                                               C
               TYPE=X,                                                 C
      DFSMARSH ENCODING=Cp1047,                                        C
               INTERNALTYPECONVERTER=DOUBLE
      SEGM     NAME=TSINT,                                             C
               PARENT=ROOT,                                            C
               BYTES=(8,6),                                            C
               REMARKS='This describes table TSINT for testing.',      C
               RULES=(LLL,HERE)
      FIELD    NAME=RNUM,                                              C
               BYTES=4,                                                C
               START=3,                                                C
               DATATYPE=INT
      FIELD    NAME=LL,                                                C
               BYTES=2,                                                C
               START=1,                                                C
               DATATYPE=SHORT
      FIELD    NAME=CSINT,                                             C
               EXTERNALNAME=CSINT,                                     C
               BYTES=2,                                                C
               START=7,                                                C
               DATATYPE=SHORT
      SEGM     NAME=TINT,                                              C
               EXTERNALNAME=TESTINTEGER,                               C
               PARENT=ROOT,                                            C
               BYTES=(10,6),                                           C
               RULES=(PPP,FIRST)                                       C
               REMARKS='This describes table TINT.',                   C
               RULES=(LLL,HERE)                                         
      FIELD    NAME=RNUM,                                              C
               BYTES=4,                                                C
               START=3,                                                C
               DATATYPE=INT                                             
      FIELD    NAME=LL,                                                C
               BYTES=2,                                                C
               START=1,                                                C
               DATATYPE=SHORT                                           
      FIELD    NAME=CINT,                                              C
               EXTERNALNAME=CINTEGER,                                  C
               BYTES=4,                                                C
               START=7,                                                C
               DATATYPE=INT                                             

ALTER TABLE root (
    ADD COLUMN New_Field_01 DOUBLE INTERNALNAME newfld01 
) IN DATABASE COGDBD 
  MAXBYTES 28;

Example: Modifying for Database Versioning

For more information about database versioning, see Database versioning.

DBD   NAME=COGDBD,                                               C
         ENCODING=Cp1047,                                        C
         ACCESS=(HDAM,OSAM),                                     C
         RMNAME=(DFSHDC40,3,3,25),                               C
         PASSWD=NO                                                
DATASET  DD1=COGDATA,                                            C
         DEVICE=3390,                                            C
         SIZE=(8192)                                              
SEGM  NAME=ROOT,                                                 C
         PARENT=0,                                               C
         BYTES=(28),              <-- From 20 to 28              C
         RULES=(LLL,HERE)                                         
FIELD NAME=(ROOTKEY,SEQ,U),                                      C
         BYTES=12,                                               C
         START=1,                                                C
         TYPE=C,                                                 C
         DATATYPE=CHAR                                            
FIELD NAME=TABTYPE,                                              C
         BYTES=8,                                                C
         START=13,                                               C
         TYPE=C,                                                 C
         DATATYPE=CHAR                                            
FIELD NAME=NEWFLD01,                                             C
         EXTERNALNAME=New_Field_01,                              C
         BYTES=8,                                                C
         START=21,                                               C
         TYPE=X,                                                 C
DFSMARSH ENCODING=Cp1047,                                        C
         INTERNALTYPECONVERTER=DOUBLE


ALTER DATABASE COGDBD
  DBVER 1;

ALTER TABLE root (
    ADD COLUMN New_Field_01 DOUBLE INTERNALNAME newfld01 
) IN DATABASE COGDBD 
  MAXBYTES 28;