CREATE TABLE

Invocation

• CREATE TABLE syntax
• column-definition syntax
• data-type syntax
• ims-column syntax
• inline-constraints syntax
• constraint syntax
• references-clause syntax
• map-definition syntax
• case-definition syntax
• lchild-definition syntax
• lchild-option syntax (HISAM)
• lchild-option syntax (HDAM)
• lchild-option syntax (HIDAM)
• lchild-option syntax (PHDAM or PHIDAM)
• lchild-option syntax (INDEX for Full-Function secondary index database)
• lchild-option syntax (PSINDEX)
• xdfld-options syntax (HISAM, SHISAM, HDAM, HIDAM, PHDAM, or PHIDAM)
• table-options syntax (PHIDAM or PHDAM)
• table-options syntax (HIDAM or HDAM)
• table-options syntax (DEDB)
• table-options syntax (HISAM or SHISAM)
• table-options syntax (HSAM or SHSAM)
• table-options syntax (INDEX)
• table-options syntax (LOGICAL)
• source-clause syntax
• editproc-clause syntax
• lparent-clause syntax
• data_capture syntax
• exit_changes syntax
• exit_attributes syntax

CREATE TABLE syntax

Notes:

• ¹ The constraints and lchild-definition fragments are invalid for a GSAM database.
• ² INTERNALNAME is invalid for a GSAM database.
• ³ The table-options fragment is invalid for a GSAM database.

column-definition syntax

Notes:

• ¹ INTERNALNAME is invalid for a GSAM database.
• ² The inline-constraints fragment is invalid for a GSAM database.

data-type syntax

Notes:

• ¹ The default bytes for CHAR is 1.

ims-column syntax

inline-constraints syntax

constraint syntax

references-clause syntax

map-definition syntax

Notes:

• ¹ Specifies the control segment name and control field name

case-definition syntax

lchild-definition syntax

lchild-option syntax (HISAM)

lchild-option syntax (HDAM)

lchild-option syntax (HIDAM)

lchild-option syntax (PHDAM or PHIDAM)

lchild-option syntax (INDEX for Full-Function secondary index database)

lchild-option syntax (PSINDEX)

xdfld-options syntax (HISAM, SHISAM, HDAM, HIDAM, PHDAM, or PHIDAM)

table-options syntax (PHIDAM or PHDAM)

table-options syntax (HIDAM or HDAM)

table-options syntax (DEDB)

table-options syntax (HISAM or SHISAM)

table-options syntax (HSAM or SHSAM)

table-options syntax (INDEX)

table-options syntax (LOGICAL)

source-clause syntax

Notes:

• ¹ KEY is not allowed for HISAM or SHISAM.
• ² Second portion not allowed for HISAM or SHISAM.

editproc-clause syntax

lparent-clause syntax

data_capture syntax

exit_changes syntax

exit_attributes syntax

Notes:

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

Keyword parameters for CREATE TABLE

TABLE table_name

Specifies an external name as a 1- to 128-character uppercase alphanumeric string. A table name can include the underscore character. The table name must be unique within a database.

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

lchild_definition

Must be preceded by a column-definition.

INTERNALNAME internalname

Specifies the internal name of the segment type being defined. The specified name is used by DL/I and application programs in all references to this segment. Duplicate segment names are not allowed. The internalname parameter must be a 1- to 8-character alphanumeric value. Each character must be in the range of A - Z or 0 - 9, or the character $, #, or @.

Restriction: The first character of the name cannot be numeric.

Restriction: After you specify the internal name of the segment type being defined, you cannot change the internal name by using the ALTER TABLE statement. To change the internal name of the segment type, you must first use the DROP TABLE statement to delete the table. Then, you must re-create the table by using the CREATE TABLE statement and specify the new internal name.

The default value of the INTERNALNAME parameter will start with a 'TBL' prefix followed by an incremented number. Although listed as optional since IMS will generate a default internal name, it is highly recommended that you provide your own internal name. This way you have control of the internal name used by IMS and the program views (PSB).

For example:

TBL00001
TBL00255 

IN dbname.tablespace_name

Specifies the database and tablespace to which the table belongs.

Note: This clause does not apply for DEDB, LOGICAL, PSINDEX, PHIDAM or PHDAM databases. Use IN DATABASE instead.

IN DATABASE dbname

Specifies the database to which the table belongs. When the tablespace name is not provided, the table will be associated with the latest defined tablespace.

Keyword parameters for CREATE 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

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.

DIRECT DEPENDENT | SEQUENTIAL DEPENDENT

For databases defined with a DEDB access type only. Describes the type of DEDB dependent segment. Must not be specified for root segments. Only one sequential dependent segment is permitted per DEDB, and, if specified, it must be the first dependent segment type. Direct dependent segment type is the default.

Restriction: After you define the type of DEDB dependent segment by specifying DIRECT DEPENDENT or SEQUENTIAL DEPENDENT, you cannot change the segment type by using the ALTER TABLE statement. To change the type of DEDB dependent segment, you must first use the DROP TABLE statement to delete the table. Then, you must re-create the table by using the CREATE TABLE statement and specify the keyword for the segment type that you require.

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 CREATE 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 CREATE 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 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 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 IN keyword in the column.
• Fields that are defined as structures that contain one or more nested dynamic arrays. A field that is defined as a structure includes STRUCT in the column.
• 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 IN keyword in the column.
• 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 XML.

The INTERNALNAME parameter must be specified for /CK and /SX columns. When specifying /CK or /SK names, they must be enclosed in double quotes (").

• HSAM, SHSAM, INDEX, PSINDEX, and DEDB do not allow /CK or /SX columns.
• HISAM and SHISAM only allow /CK columns.
• HDAM, HIDAM, PHDAM, and PHIDAM allow /CK and /SX columns.

Keyword parameters for datatype

ARRAY | BINARY | ...

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

If DATE, TIME, or TIMESTAMP is specified, you can specify either LONG or CHAR on the INTERNAL TYPECONVERTER keyword or specify a USER TYPECONVERTER. 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, the default INTERNAL TYPECONVERTER is XML_CLOB, which is the only valid value when XML is specified.

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

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

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.

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 ARRAY in the field definition.

A field that is an array element specifies the name of an array field on the IN keyword in the column.

BINARY

BINARY can be specified with TYPE P or TYPE X. It defaults to a column size of 1 byte, however you can specify your own size using the MAXBYTES keyword.

BIT

If you specify BIT, you set a column size of 1 byte. If you specify MAXBYTES you can only specify a value of 1.

BYTE

If you specify BYTES, you set a column size of 1 byte. If you specify MAXBYTES you can only specify a value of 1.

UBYTE

If you specify UBYTE, you set a column size of 1 byte. If you specify MAXBYTES you can only specify a value of 1.

CHAR

If you specify CHAR, the default column size of 1 byte. You can specify the actual column size by including the value in parens next to CHAR or on the MAXBYTES keyword. For example: CHAR(8).

DATE

When DATE is specified, you can only specify MAXBYTES 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 can only specify MAXBYTES 8.

FLOAT

If you specify FLOAT, you can only specify MAXBYTES 4.

INT

If you specify INT, you can only specify MAXBYTES 4.

UINT

If you specify UINT, you can only specify MAXBYTES 4.

LONG

If you specify LONG, you can only specify MAXBYTES 8.

ULONG

If you specify ULONG, you can only specify MAXBYTES 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 can only specify MAXBYTES 2.

USHORT

If you specify USHORT, you can only specify MAXBYTES 2.

STRUCT

When STRUCT is specified, you cannot define this column as a primary key if the structure contains a dynamic array. Dynamic arrays are defined with a data type of ARRAY and the DEPENDSON and MAXBYTES keywords.

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.

TIME

When TIME is specified, you can only specify MAXBYTES 8, unless you also specify a column definition that includes either INTERNAL TYPECONVERTER CHAR or USER TYPECONVERTER convertername.

TIMESTAMP

When TIMESTAMP is specified, you can only specify MAXBYTES 8, unless you also specify a column definition that includes either INTERNAL TYPECONVERTER CHAR or USER TYPECONVERTER convertername.

XML

Restriction: XML is not supported when the INTERNALNAME keyword is specified or for columns defined as a primary key.

Keyword parameters for CREATE 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 column of the referenced field must precede the FIELD statement that specifies the DEPENDSON parameter.

The DEPENDSON parameter is valid only when 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 of XML values:

• INT
• SHORT
• LONG
• UINT
• USHORT
• ULONG
• 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.

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 | BINARY | ...

If DECIMAL data type, the default INTERNAL TYPECONVERTER is signed PACKEDDECIMAL.

If DATE, TIME, or TIMESTAMP data type, you must specify either LONG or CHAR on the INTERNAL TYPECONVERTER keyword or specify a USER TYPECONVERTER. 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 data type, the default INTERNAL TYPECONVERTER is XML_CLOB, which is the only valid value for XML.

If STRUCT or ARRAY data type, the default INTERNAL TYPECONVERTER is STRUCT or ARRAY, respectively, which are the only valid values.

For all other data types, the value is used as the default INTERNAL TYPECONVERTER.

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.

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

An array element specifies the name of the array on the IN keyword for the column.

BIT

If you specify BIT, you can only specify MAXBYTES 1.

BYTE

If you specify BYTE, you can only specify MAXBYTES 1.

UBYTE

If you specify UBYTE, you can only specify MAXBYTES 1.

CLOB

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

DOUBLE

If you specify DOUBLE, you can only specify MAXBYTES 8.

FLOAT

If you specify FLOAT, you can only specify MAXBYTES 4.

INT

If you specify INT, you can only specify MAXBYTES 4.

UINT

If you specify UINT, you can only specify MAXBYTES 4.

LONG

If you specify LONG, you can only specify MAXBYTES 8.

ULONG

If you specify ULONG, you can only specify MAXBYTES 8.

SHORT

If you specify SHORT, you can only specify MAXBYTES 2.

USHORT

If you specify USHORT, you can only specify MAXBYTES 2.

XML_CLOB

Restriction: Datatype of XML is not supported when the NAME parameter is specified.

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 of XML.

The value that is specified on the INTERNAL TYPECONVERTER parameter must be consistent with the value specified as the column's data type. In most cases, you must specify the same value on INTERNAL TYPECONVERTER that you specify as the data type.

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 internal 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 XML.

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 as the data typeand 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 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 CREATE TABLE (inline-constraints)

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 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 CREATE 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 CREATE TABLE (references-clause)

FOREIGN KEY REFERENCES

For dependent segment types, specifies the name of this segment's physical parent.

REFERENCES table_name

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

Keyword parameters for CREATE 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 DFS prefix is reserved by IMS and cannot be part of a user-created name.

Keyword parameters for CREATE 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 DFS prefix is reserved by IMS and cannot be part of a user-created name.

Keyword parameters for CREATE TABLE (lchild)

lchild_definition

Must be preceded by a column-definition.

database_name.table_name

The table_name parameter specifies the IMS internal name of the logical child, index pointer, index target, HIDAM or PHIDAM root segment type that is to be associated with the segment type defined by a preceding TABLE. The database_name parameter is the name of the database that contains the segment type specified in the table_name parameter. The database_name parameter can be omitted when the table_name parameter is defined in this database.

The database_name parameter must be one- to eight-character alphanumeric values. The table_name parameter and must be one- to 128-character alphanumeric values.

SINGLE|DOUBLE|NONE|INDEX|SYMBOL

Specifies the pointers used in logical or index relationships. When omitted from any index database generation, SINGLE is the default. You must specify INDEX or SYMBOL for any LCHILD statement following an index target segment type; no default is provided for this part of the index relationship. When omitted from an LCHILD statement which establishes a unidirectional or physically paired bidirectional logical relationship, NONE is the default. When omitted or specified as NONE for an LCHILD statement which establishes a virtually paired bidirectional logical relationship, SINGLE is the default.

Restriction:

• For PHDAM and PHIDAM databases, only the operands INDEX and NONE are supported. All other operands are treated as if errors are present.
• For DEDB secondary index databases, only the SYMBOL operand is supported.

SINGLE

Is used for logical relationships, or index relationships implemented with direct address pointers. Specifies that a logical child first pointer field is to be reserved in each occurrence of the segment type defined by the preceding TABLE. When the preceding TABLE defines a logical parent, the pointer field contains a direct address pointer to the first occurrence of a logical child segment type. When the preceding TABLE defines the HIDAM Primary index database segment type, the pointer field contains a direct address pointer to a HIDAM database root segment. When the preceding TABLE defines an index pointer segment type in a secondary index database, the pointer field contains a direct address pointer to an index target segment.

DOUBLE

Is used to specify two 4-byte pointer fields, logical child first and logical child last, reserved in the logical parent segment. The two pointers point to the first and last occurrences of logical child segment type under a logical parent. The logical child last pointer is of value when the logical child is not sequenced and the rules parameter is LAST.

NONE

Should be used when the logical relationship from the logical parent to the logical child segment is not implemented or not implemented with direct address logical child pointers. In this case, the relationship from logical parent to logical child does not exist or is maintained by using physically paired segments. No pointer fields are reserved in the logical parent segment.

INDEX

Is specified on the LCHILD statement in a HIDAM database used to establish the index relationship between the HIDAM root segment type and the HIDAM Primary index during a HIDAM database DBD generation. INDX can also be specified on the LCHILD statement in the DBD for the target database that establishes the index relationship between an index target segment type and a secondary index. In these cases, omit the parameter or specify SINGLE on the LCHILD statement of the primary or secondary index DBD. An LCHILD statement for a HIDAM primary index must precede the LCHILD statements for secondary indexes.

Requirement: If the target database is a HALDB, the index database must be defined as a HALDB index by use of the PSINDEX parameter in the DBD statement ACCESS parameter.

SYMBOL

Can be used in the DBD generation for the target database of a secondary index to specify that the concatenated keys of the index target segments are to be placed in the index pointer segments in lieu of a direct pointer. You must specify SYMBOL when the index target segment type is in a HISAM database. SYMBOL is optional when the index target segment type is in an HDAM or HIDAM database.

An additional use of the SYMBOL parameter in the INDEX DBD is to prevent reserving space in the prefix of index pointer segments for the 4-byte direct address index target segment pointer that is not used when the index pointer is symbolic.

PAIR

Is specified for bidirectional logical relationships only. The provide name is the logical child segment/table that is, physically or virtually, paired with the logical child segment/table specified on the LCHILD statement. The name must be a 1- to 128-character alphanumeric value.

Restriction: This parameter is not allowed for virtual pairing when using PHDAM and PHIDAM databases, because they only support physical pairing.

INDEXFIELD

Is specified on LCHILD statements for an Index DBD generation only. It specifies the name of the sequence field of a HIDAM root segment type during DBD generation of the primary index for a HIDAM database, or the name of an indexed field, defined through an XDFLD statement in an index target segment type during DBD generation of a secondary index database. This parameter is not needed for a primary index of a PHIDAM database.

RKSIZE

Specifies the root key size of the target database. This parameter is for partitioned secondary index (PSINDEX) databases only, and is invalid for any other database type. (Required in DBD source, optional in DDL).

FIRST|LAST|HERE

Is used for logical relationships when no sequence field or a nonunique sequence field has been defined for a virtual logical child. Under these conditions, the rule of FIRST, LAST, or HERE controls the sequence in which occurrences of the real logical child in the logical relationship are sequenced from the logical parent through logical child and logical twin pointers (this establishes the logical twin sequence).

Restriction: This parameter is not allowed for virtual pairing when using PHDAM and PHIDAM databases, because they only support physical pairing.

FIRST

Indicates that, if no sequence field is specified for the logical child, a new occurrence is inserted before the first existing occurrence of the logical child. If a nonunique sequence field is specified for the logical child, a new occurrence is inserted before all existing occurrences with the same key.

LAST

Indicates that, if no sequence field is specified for the logical child, a new occurrence is inserted after the last existing occurrence of the logical child. If a nonunique sequence field is specified for the logical child, a new occurrence is inserted after all existing occurrences with the same keys. LAST is the default option.

HERE

Indicates that the insert is dependent on the position established by the previous DL/I call. If no sequence field is defined, the segment is inserted before the logical twin that position was established on through the previous call. If no position was established by a previous call, the new twin is inserted before all existing logical twins. If a nonunique sequence field is defined, the segment is inserted before the logical twin with the same sequence field value on which position was established by a previous call. If no position was established on a logical twin with the same sequence field value, the segment is inserted before all twins with the same sequence field value.

When a new occurrence of a logical child is inserted from its physical parent, no previous position exists for the logical child on its logical twin chain. Therefore, the new occurrence is placed before all existing occurrences on the logical twin chain when no sequence field has been defined, or before all existing occurrences with the same sequence field value when a nonunique sequence field has been defined.

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 logical path.

Keyword parameters for CREATE TABLE (xdfld)

xdfld_definition

Must be preceded by a lchild-definition.

xdfld_name

Specifies the name of the indexed data field of an index target segment. The name specified actually represents the search field of an index pointer segment type as being a field in the index target segment type. You can use the name specified to qualify SSAs of calls for an index target segment type through the search field keys of index pointer segments. This enables accessing occurrences of an index target segment type through a primary or secondary processing sequence based on data contained in a secondary index. fldname must be a 1- to 26-character alphanumeric value.

Since the name specified is used to access occurrences of the index target segment type based on the content of a secondary index, the name specified must be unique among all field names specified for the index target segment type.

INTERNALNAME

Optional IMS internal name. Must be a 1- to 8-character alphanumeric value.

SEGMENT

Specifies the index source segment type for this secondary index relationship. Must be the name of a subsequently defined segment type, which is hierarchically below the index target segment type or it can be the name of the index target segment type itself. The segment name specified must not be a logical child segment. If this parameter is omitted, the index target segment type is assumed to be the index source segment.

CONST

Specifies a character with which every index pointer segment in a particular secondary index is identified. This parameter is optional. The purpose of this parameter is to identify all index pointer segments associated with each secondary index when multiple secondary indexes reside in the same secondary index database. Must be specified as a 1-byte hexstring term, for example X'F9'.

Restriction: CONST is not supported for HALDB or DEDB databases.

SRCH

Specifies which field or fields of the index source segment you must use as the search field of a secondary index. list1 must be a list of one to five field names defined in the index source segment type by column definitions. If two or more names are included, they must be separated by commas and enclosed in parentheses. The sequence of names in the list is the sequence in which the field values are concatenated in the index pointer segment search field. The sum of the lengths of the participating fields constitutes the index target segment indexed field length which must be reflected in segment search arguments.

SUBSEQ

Specifies which, if any, fields of the index source segment you must use as the subsequence field of a secondary index. list2 must be a list of one to five field names defined in the index source segment by column definitions. If two or more names are included, they must be separated by commas and enclosed in parentheses. The sequence of names in the list is the sequence in which field values are concatenated in the index pointer segment subsequence field. This parameter is optional.

DDATA

Specifies which, if any, fields of the index source segment you must use as the duplicate data field of a secondary index. list3 must be a list of one to five field names defined in the index source segment by column definitions. If two or more names are included, they must be separated by commas and enclosed in parentheses. The sequence of names in the list is the sequence in which field values are concatenated in the index pointer segment duplicate data field. This parameter is optional.

NULLVAL

Suppresses the creation of index pointer segments when the index source segment data used in the search field of an index pointer segment contains the specified value.

The value must be a 1-byte hex-string term. For example, X'10', X’40’ for blank, X’00’ for zero. If a packed decimal value is required, it must be specified as a hexadecimal term with a valid number digit and zone or sign digit (X'3F' for a packed positive 3 or X'9D' for negative 9).

No indexing is performed when each field of the index source segment specified in the SRCH parameter has the value of this parameter in every byte. For example, if the NULLVAL X'F9' were specified, the associated index would have no entries indexed on the value C'9999...9'.

There is a slight difference in the case of packed fields. For packed fields, each field that composes the search field is considered to be a separate packed value. For example, if the NULLVAL X'9F' were specified in a case where the search field was composed of three 2-byte packed source fields, there would be no index entries with the search field value of X'999F999F999F' because all index entries containing a X'9F' would be suppressed.

Also, with the same NULLVAL X'9F', if the search field were one 6-byte field, no indexing would be performed whenever the value of the search field was X'99999999999F'.

The only form of the sign that is checked is the form specified. For example, if X'9C' is specified, X'9F' does not cause suppression. If both the NULLVAL and the EXTRTN operands are specified, indexing of a segment is performed only if neither causes suppression.

EXTRTN

Specifies the name of a user-supplied index maintenance exit routine that is used to suppress the creation of selected index pointer segments. The parameter (name1) must be the name of a user-supplied routine which receives control whenever DL/I attempts to insert, delete or replace an index entry because of changes occurring in the indexed database. This exit routine can inspect the affected index source segment and decide whether an index pointer segment should be generated. If both the NULLVAL and the EXTRTN operands are specified, indexing of a segment is performed only if neither causes suppression.

Multiple EXITDATA keyword is not allowed in RMNAME, EDITPROC, and EXTRTN. Multiple key and data pairs are not supported on EXITDATA. The key parameter must be a 1- to 8-character alphanumeric value. Each character must be in the range of A - Z or 0 - 9, or the character $, #, or @. key cannot start with number, or be blank. The data parameter must be enclosed in single quotation marks. The following characters are not supported within the data:[ ] } \ ; ' tilde caret. The maximum length for a data is 4000 characters.

Note: Use EXITDATA NONE on IMS ALTER DDL statement to get rid of user data for RMNAME and EDITPROC. Use DROP LCHILD and ADD LCHILD to change EXITDATA on XDFLD.

Usage notes

This statement is equivalent to the AREA statement of the IMS DBD generation utility.

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=(20),                                             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
      SEGM     NAME=TSINT,                                             C
               PARENT=ROOT,                                            C
               BYTES=(8,6),                                            C
               REMARKS='This describes table TSINT.',                  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
               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                                             

CREATE DATABASE COGDBD
ACCESS HDAM OSAM
RMNAME(DFSHDC40 RMANCH 3 RMRBN 3 RMBYTES 25)
CCSID 'Cp1047';

CREATE TABLESPACE COGDATA
  SIZE PRIMARY 8192;

CREATE TABLE TEST_ROOT (
    ROOT_KEY CHAR(12) INTERNALNAME ROOTKEY PRIMARY KEY ,
    TABLE_TYPE CHAR(8) INTERNALNAME TABTYPE
) IN COGDBD.COGDATA
  INTERNALNAME ROOT 
  MAXBYTES 20 
  AMBIGUOUS INSERT HERE;

CREATE TABLE TEST_SHORT_INTEGER (
    TABLE_LENGTH SHORT INTERNALNAME LL,
    R_NUMBER INT INTERNALNAME RNUM,
    C_SHORT_INTEGER SHORT INTERNALNAME CSINT,
    FOREIGN KEY REFERENCES TEST_ROOT
) IN COGDBD.COGDATA
  INTERNALNAME TSINT
  MAXBYTES 8
  MINBYTES 6
  AMBIGUOUS INSERT HERE;

COMMENT ON TABLE TEST_SHORT_INTEGER IN COGDBD IS 'This describes table TSINT.';

CREATE TABLE TESTINTEGER (
TABLE_LENGTH SHORT INTERNALNAME LL,
R_NUMBER INT INTERNALNAME RNUM,
CINTEGER INT INTERNALNAME CINT,
FOREIGN KEY REFERENCES TEST_ROOT
) IN COGDBD.COGDATA
  INTERNALNAME TINT
  MAXBYTES 10
  MINBYTES 6
  AMBIGUOUS INSERT HERE;

COMMENT ON TABLE TESTINTEGER IN COGDBD IS 'This describes table TINT.';

Example: Continuation of COGDBD with DECIMAL

SEGM     NAME=TDEC,                                              C
               PARENT=ROOT,                                            C
               BYTES=(10,6),                                           C
               REMARKS='This describes table TDEC.',                   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=CDEC,                                              C
               EXTERNALNAME=CDECIMAL,                                  C
               BYTES=4,                                                C
               START=7,                                                C
               DATATYPE=DECIMAL(7,2)

CREATE TABLE TEST_DECIMAL (
    TABLE_LENGTH SHORT INTERNALNAME LL,
    R_NUMBER INT INTERNALNAME RNUM,
    CDECIMAL DECIMAL(7,2) INTERNALNAME CDEC,
    FOREIGN KEY REFERENCES TEST_ROOT
) IN COGDBD.COGDATA
  INTERNALNAME TDEC
  MAXBYTES 10
  MINBYTES 6
  AMBIGUOUS INSERT HERE;

COMMENT ON TABLE TEST_DECIMAL IN COGDBD IS 'This describes table TDEC.';

Example: Continuation of COGDBD with DFSMARSH

SEGM     NAME=TNCHAR,                                            C
               PARENT=ROOT,                                            C
               BYTES=(38,6),                                           C
               REMARKS='This describes table TNCHAR.',                 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=CNCHAR,                                            C
               EXTERNALNAME=CNCHAREXT,                                 C
               BYTES=32,                                               C
               START=7,                                                C
               DATATYPE=CHAR
   DFSMARSH    ENCODING=UTF-8,                                         C
               INTERNALTYPECONVERTER=CHAR

CREATE TABLE TEST_NEW_CHAR (
    TABLE_LENGTH SHORT INTERNALNAME LL,
    R_NUMBER INT INTERNALNAME RNUM,
    CNCHAREXT CHAR(32) INTERNALNAME CNCHAR CCSID 'UTF-8',
    FOREIGN KEY REFERENCES TEST_ROOT
) IN COGDBD.COGDATA
  INTERNALNAME TNCHAR
  MAXBYTES 38
  MINBYTES 6
  AMBIGUOUS INSERT HERE;

COMMENT ON TABLE TEST_NEW_CHAR IN COGDBD IS 'This describes table TNCHAR.';

Example: Continuation of COGDBD with PATTERN

SEGM     NAME=TTS,                                               C
               PARENT=ROOT,                                            C
               BYTES=(35,6),                                           C
               REMARKS='This describes table TTS.',                    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=CTS,                                               C
               EXTERNALNAME=CTSNAME,                                   C
               BYTES=29,                                               C
               START=7,                                                C
               DATATYPE=TIMESTAMP
      DFSMARSH ENCODING=Cp1047,                                        C
               INTERNALTYPECONVERTER=CHAR,                             C
               PATTERN='yyyy-MM-dd HH:mm:ss.fffffffff'

CREATE TABLE TEST_TIMESTAMP (
    TABLE_LENGTH SHORT INTERNALNAME LL,
    R_NUMBER INT INTERNALNAME RNUM,
    CTSNAME TIMESTAMP INTERNALNAME CTS CCSID 'Cp1047' 
        PATTERN 'yyyy-MM-dd HH:mm:ss.fffffffff',
    FOREIGN KEY REFERENCES TEST_ROOT
) IN DATABASE COGDBD 
  INTERNALNAME TTS
  MAXBYTES 35
  MINBYTES 6
  AMBIGUOUS INSERT HERE;

COMMENT ON TABLE TEST_TIMESTAMP IN COGDBD IS 'This describes table TTS.';

Example: Arrays

SEGM    NAME=HOSPITAL,                                             
               EXTERNALNAME=HOSPITAL,                                  
               ENCODING=Cp1047,                                        
               PARENT=0,                                               
               BYTES=(900),                                            
               RULES=(LLL,HERE)
...

      FIELD    EXTERNALNAME=TABLEARRAY,                                
               BYTES=14,                                               
               START=224,                                              
               MINOCCURS=1,                                            
               MAXOCCURS=1,                                            
               DATATYPE=ARRAY
      FIELD    EXTERNALNAME=TABLEARRAY1,                               
               BYTES=2,                                                
               START=224,                                              
               TYPE=X,                                                 
               PARENT=TABLEARRAY,                                      
               DATATYPE=CHAR
      DFSMARSH INTERNALTYPECONVERTER=CHAR                              
      FIELD    EXTERNALNAME=TABLEARRAY2,                               
               BYTES=4,                                                
               START=226,                                              
               TYPE=X,                                                 
               PARENT=TABLEARRAY,                                      
               DATATYPE=CHAR
      DFSMARSH INTERNALTYPECONVERTER=CHAR                              
      FIELD    EXTERNALNAME=TABLEARRAY3,                               
               BYTES=8,                                                
               START=230,                                              
               TYPE=X,                                                 
               PARENT=TABLEARRAY,                                      
               DATATYPE=CHAR
      DFSMARSH INTERNALTYPECONVERTER=CHAR
...


CREATE TABLE hospital (
    ...
    tablearray ARRAY MAXBYTES 14 START 224 MINOCCURS 1 MAXOCCURS 1,
    tablearray1 CHAR(2) IN tablearray,
    tablearray2 CHAR(4) IN tablearray,
    tablearray3 CHAR(8) IN tablearray,
    ...
) IN DATABASE dedbjn21
  INTERNALNAME hospital
  MAXBYTES 900
  AMBIGUOUS INSERT HERE

Example: Dynamic Arrays

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

CREATE TABLE dynamic_array_table (
    arraynum DECIMAL(7,0)
    dynarray ARRAY MAXBYTES 100 MINOCCURS 10 MAXOCCURS 50 DEPENDSON arraynum,
    fld01 SHORT RELSTART 1 IN dynarray,
    fld02 CHAR(10) STARTAFTER dynarray,
    struct01 STRUCT BYTES 10 STARTAFTER fld02
) IN DATABASE dynarrdb
  INTERNALNAME dynarrs;

Example: Maps

DFSMAP   NAME=MAP1,                                              C
               DEPENDINGON=CASENUM
~~~~~~~DFSCASE NAME=CASE1 redefines the schema for bytes 791 to 831.~~~~ 
      DFSCASE  NAME=CASE1,                                             C
               CASEID=CASEONE,                                         C
               CASEIDTYPE=C,                                           C
               MAPNAME=MAP1
      FIELD    EXTERNALNAME=FIELDB,                                    C
               CASENAME=CASE1,                                         C
               BYTES=20,                                               C
               START=791,                                              C
               DATATYPE=CHAR
      FIELD    EXTERNALNAME=FIELDC,                                    C
               CASENAME=CASE1,                                         C
               BYTES=20,                                               C
               START=811,                                              C
               DATATYPE=CHAR
~~~~~~~DFSCASE NAME=CASE2 redefines the schema for bytes 831 to 855~~~~
      DFSCASE  NAME=CASE2,                                             C
               CASEID=CASETWO,                                         C
               CASEIDTYPE=C,                                           C
               MAPNAME=MAP1
      FIELD    EXTERNALNAME=FIELDD,                                    C
               CASENAME=CASE2,                                         C
               BYTES=20,                                               C
               START=831,                                              C
               DATATYPE=CHAR
      FIELD    EXTERNALNAME=CARDTYPE,                                  C
               BYTES=4,                                                C
               START=851,                                              C
               DATATYPE=CHAR

CREATE TABLE customer (
    id INT PRIMARY KEY,
    …
    casenum CHAR(12),
    cardtype CHAR(4),
    …
    MAP casenum AS MAP1 (
      CASE caseone AS case1 (
        fieldb CHAR(20) START 792 ,
        fieldc CHAR(20) START 811
      ) ,
      CASE casetwo AS case2 (
        fieldd CHAR(20) START 831,
        cardtype CHAR(4) START 851)
      )
    )
) IN DATABASE dedbjn21
  INTERNALNAME customer
  MAXBYTES 900

Example: Structs

FIELD NAME=(NAME),                                               C
               EXTERNALNAME=PAYEE_NAME,                                C
               BYTES=20,                                               C
               START=11,                                               C
               DATATYPE=CHAR
      DFSMARSH ENCODING=Cp1047,                                        C
               INTERNALTYPECONVERTER=CHAR

      FIELD EXTERNALNAME=INDIVIDUALNAME,                               C
               BYTES=20,                                               C
               START=11,                                               C
               DATATYPE=STRUCT,                                        C
               REDEFINES=PAYEE_NAME,                                   C
               REMARKS='This is a STRUCT with lastname and firstname'

      FIELD EXTERNALNAME=LASTNAME,                                     C
               BYTES=10,                                               C
               START=11,                                               C
               DATATYPE=CHAR,                                          C
               PARENT=INDIVIDUALNAME
      DFSMARSH ENCODING=Cp1047,                                        C
               INTERNALTYPECONVERTER=CHAR

      FIELD EXTERNALNAME=FIRSTNAME,                                    C
               BYTES=10,                                               C
               START=21,                                               C
               DATATYPE=CHAR,                                          C
               PARENT=INDIVIDUALNAME
      DFSMARSH ENCODING=Cp1047,                                        C
               INTERNALTYPECONVERTER=CHAR


CREATE TABLE customer (
    …
    payee_name CHAR(20) START 11 CCSID 'Cp1047',
    individualname STRUCT BYTES 20 START 11,
    lastname CHAR(10) IN individualname,
    firstname CHAR(10 IN individualname
) IN DATABASE dedbjn21
  INTERNALNAME customer
  MAXBYTES 900

COMMENT ON COLUMN customer.individualname IN dedbjn21 IS 'This is 
     a STRUCT with lastname and firstname'

Example: Continuation of Structs

FIELD    EXTERNALNAME=PERSONAL_INFO,                             C
               BYTES=184,                                              C
               START=156,                                              C
               DATATYPE=STRUCT
      DFSMARSH INTERNALTYPECONVERTER=STRUCT

      FIELD    EXTERNALNAME=ADDRESS,                                   C
               BYTES=184,                                              C
               START=156,                                              C
               MINOCCURS=2,                                            C
               MAXOCCURS=2,                                            C
               PARENT=PERSONAL_INFO,                                   C
               DATATYPE=ARRAY

      FIELD    EXTERNALNAME=NAME_TYPE,                                 C
               BYTES=1,                                                C
               RELSTART=1,                                             C
               PARENT=ADDRESS,                                         C
               DATATYPE=CHAR

      FIELD    EXTERNALNAME=INDIVIDUAL_NAME,                           C
               BYTES=20,                                               C
               RELSTART=2,                                             C
               PARENT=ADDRESS,                                         C
               DATATYPE=STRUCT

      FIELD    EXTERNALNAME=LAST_NAME,                                 C
               BYTES=12,                                               C
               RELSTART=1,                                             C
               PARENT=INDIVIDUAL_NAME,                                 C
               DATATYPE=CHAR

      FIELD    EXTERNALNAME=FIRST_NAME,                                C
               BYTES=8,                                                C
               RELSTART=13,                                            C
               PARENT=INDIVIDUAL_NAME,                                 C
               DATATYPE=CHAR

      FIELD    EXTERNALNAME=ADDRESS_LINE2,                             C
               BYTES=40,                                               C
               RELSTART=22,                                            C
               PARENT=ADDRESS,                                         C
               DATATYPE=CHAR

      FIELD    EXTERNALNAME=CITY,                                      C
               BYTES=20,                                               C
               RELSTART=62,                                            C
               PARENT=ADDRESS,                                         C
               DATATYPE=CHAR

      FIELD    EXTERNALNAME=STATE,                                     C
               BYTES=2,                                                C
               RELSTART=82,                                            C
               PARENT=ADDRESS,                                         C
               DATATYPE=CHAR

      FIELD    EXTERNALNAME=ZIP,                                       C
               BYTES=9,                                                C
               RELSTART=84,                                            C
               PARENT=ADDRESS,                                         C
               DATATYPE=CHAR
…
      FIELD    EXTERNALNAME=COMPANY_CARDS,                             C
               BYTES=200,                                              C
               START=584,                                              C
               DATATYPE=STRUCT

      FIELD    EXTERNALNAME=CARDS,                                     C
               BYTES=200,                                              C
               START=584,                                              C
               MINOCCURS=5,                                            C
               MAXOCCURS=5,                                            C
               PARENT=COMPANY_CARDS,                                   C
               DATATYPE=ARRAY

      FIELD    EXTERNALNAME=COMPANY_NAME,                              C
               BYTES=20,                                               C
               RELSTART=1,                                             C
               PARENT=CARDS,                                           C
               DATATYPE=STRUCT

      FIELD    EXTERNALNAME=CO_TYPE,                                   C
               BYTES=12,                                               C
               RELSTART=1,                                             C
               PARENT=COMPANY_NAME,                                    C
               DATATYPE=CHAR

      FIELD    EXTERNALNAME=NEW_TYPE,                                  C
               BYTES=12,                                               C
               RELSTART=1,                                             C
               REDEFINES=CO_TYPE,                                      C
               PARENT=COMPANY_NAME,                                    C
               DATATYPE=CHAR

      FIELD    EXTERNALNAME=CO_NAME,                                   C
               BYTES=8,                                                C
               RELSTART=13,                                            C
               PARENT=COMPANY_NAME,                                    C
               DATATYPE=CHAR

      FIELD    EXTERNALNAME=EMPLOYEE_NAME,                             C
               BYTES=20,                                               C
               RELSTART=21,                                            C
               PARENT=CARDS,                                           C
               DATATYPE=STRUCT

      FIELD    EXTERNALNAME=EMPLOYEE_LAST_NAME,                        C
               BYTES=12,                                               C
               RELSTART=1,                                             C
               PARENT=EMPLOYEE_NAME,                                   C
               DATATYPE=CHAR

      FIELD    EXTERNALNAME=EMPLOYEE_MAIDEN_NAME,                      C
               BYTES=12,                                               C
               RELSTART=1,                                             C
               REDEFINES=EMPLOYEE_LAST_NAME,                           C
               PARENT=EMPLOYEE_NAME,                                   C
               DATATYPE=CHAR

      FIELD    EXTERNALNAME=EMPLOYEE_FIRST_NAME,                       C
               BYTES=8,                                                C
               RELSTART=13,                                            C
               PARENT=EMPLOYEE_NAME,                                   C
               DATATYPE=CHAR


CREATE TABLE employee (
    …
    personal_info STRUCT BYTES 184 START 156,
    address ARRAY BYTES 184 START 156 MINOCCURS 2 MAXOCCURS 2 IN personal_info,
    name_type CHAR RELSTART 1 IN address,
    individual_name STRUCT BYTES 20 RELSTART 2 IN address,
    last_name CHAR(12) RELSTART 1 IN individual_name,
    first_name CHAR(8) RELSTART 13 IN individual_name,
    address_line2 CHAR(40) RELSTART 22 IN address,
    city CHAR(20) RELSTART 62 IN address,
    state CHAR(20) RELSTART 82 IN address,
    zip CHAR(9) RELSTART 84 IN address,
    company_cards STRUCT BYTES 200 START 584,
    cards ARRAY BYTES 200 START 584 MINOCCURS 5 MAXOCCURS 5 IN company_cards,
    company_name STRUCT BYTES 20 RELSTART 1 IN cards,
    co_type CHAR(12) RELSTART 1 IN company_name,
    new_type CHAR(12) RELSTART 1 IN company_name,
    co_name CHAR(8) RELSTART 13 IN company_name,
    employee_name STRUCT BYTES 20 RELSTART 21 IN cards,
    employee_last_name CHAR(12) RELSTART 1 IN employee_name,
    employee_maiden_name CHAR(12) RELSTART 1 IN employee_name,
    employee_first_name CHAR(8) RELSTART 13 IN employee_name
) IN dedbjn21
  INTERNALNAME employee
  MAXBYTES 900

Example: Logical relationships EMPDB2

DBD    NAME=EMPDB2,ACCESS=(HDAM,OSAM),                         X
               RMNAME=(DFSHDC40,1,5,200)                                
        DATASET  DD1=DFSEMPL                                            
        SEGM   NAME=EMPL,PARENT=0,BYTES=56                              
        LCHILD NAME=(SALESPER,AUTODB),PAIR=EMPSAL,POINTER=DBLE          
        FIELD  NAME=(EMPNO,SEQ,U),BYTES=6,START=1,TYPE=C                
        FIELD  NAME=LASTNME,BYTES=25,START=7,TYPE=C                     
        FIELD  NAME=FIRSTNME,BYTES=25,START=32,TYPE=C                   
        SEGM   NAME=EMPSAL,PARENT=EMPL,PTR=PAIRED,                     X
               SOURCE=((SALESPER,DATA,AUTODB))                          
        FIELD  NAME=(DLRNO,SEQ,U),BYTES=4,START=1,TYPE=C    (LPK)       
        SEGM   NAME=EMPLINFO,PARENT=EMPL,BYTES=61                       
        FIELD  NAME=(STATE,SEQ,M),BYTES=2,START=51,TYPE=C               
        FIELD  NAME=ADDRESS,BYTES=61,START=1,TYPE=C                     
        FIELD  NAME=STREET,BYTES=25,START=1,TYPE=C                      
        FIELD  NAME=CITY,BYTES=25,START=26,TYPE=C                       
        FIELD  NAME=ZIP,BYTES=9,START=53,TYPE=C                         
       DBDGEN                                                           
       FINISH                                                           
       END

CREATE DATABASE empdb2
  ACCESS HDAM OSAM
  RMNAME(DFSHDC40 RMANCH 1 RMRBN 5 RMBYTES 200);

CREATE TABLESPACE dfsempl
  IN empdb2;

CREATE TABLE employee (
    empno CHAR(6) INTERNALNAME empno PRIMARY KEY,
    lastnme CHAR(25) INTERNALNAME lastnme,
    firstnme CHAR(25) INTERNALNAME firstnme,
    LCHILD autodb.sales_person PAIR empyee_salary DOUBLE
) IN empdb2.DFSEMP1
  INTERNALNAME empl
  MAXBYTES 56;

CREATE TABLE employee_salary (
    dealer_number CHAR(4) INTERNALNAME dlrno PRIMARY KEY,
    FOREIGN KEY REFERENCES employee	
) IN empdb2.DFSEMP1
  INTERNALNAME empsal
  SOURCE (autodb.sales_person);

CREATE TABLE employee_information (
    address CHAR(61) START 1 INTERNALNAME address,
    street CHAR(25) START 1 INTERNALNAME street,
    city CHAR(25) START 26 INTERNALNAME city,
    state CHAR(2) START 51 INTERNALNAME state PRIMARY KEY NON UNIQUE,
    zip CHAR(9) START 53 INTERNALNAME zip,
    FOREIGN KEY REFERENCES employee
) IN empdb2.dfsemp1
  INTERNALNAME emplinfo
  MAXBYTES 61;

Example: Logical database

DBD    NAME=EMPLDB2,ACCESS=LOGICAL
       DATASET LOGICAL
       SEGM    NAME=EMPL,PARENT=0,SOURCE=((EMPL,,EMPDB2))
       SEGM    NAME=DEALER,PARENT=EMPL,                                X
               SOURCE=((EMPSAL,KEY,EMPDB2),(DEALER,DATA,AUTODB))
       SEGM    NAME=SALESINF,PARENT=DEALER,                            X
               SOURCE=((SALESINF,,AUTODB))
       SEGM    NAME=EMPLINFO,PARENT=EMPL,                              X
               SOURCE=((EMPLINFO,,EMPDB2))

CREATE DATABASE empldb2
	ACCESS LOGICAL;

CREATE TABLE empl
IN empldb2
SOURCE(empdb2.empl); 

CREATE TABLE dealer (
    FOREIGN KEY REFERENCES empl
) IN empldb2
  SOURCE(empdb2.empsal KEY, autodb.dealer DATA);

CREATE TABLE salesinf (
    FOREIGN KEY REFERENCES empl
) IN empldb2
  SOURCE(autodb.salesinf);

CREATE TABLE emplinfo (
    FOREIGN KEY REFERENCES empl
) IN empldb2
  SOURCE(empdb2.emplinfo);

Example: Secondary Index database

DBD    NAME=SINDEX22,ACCESS=(INDEX,VSAM)                     
        DATASET  DD1=SINDX2P                                         
        SEGM   NAME=SINDXB,PARENT=0,BYTES=34                         
        FIELD  NAME=(XFLDB,SEQ,U),BYTES=28,START=1,TYPE=C   SEARCH   
        FIELD  NAME=COUNT,BYTES=2,START=25,TYPE=C           DUP DATA 
        FIELD  NAME=ENQUIRS,BYTES=4,START=25,TYPE=P         USER DATA
        LCHILD NAME=(DEALER,AUTODB),INDEX=XFLD2                      
        DBDGEN                                                       
        FINISH                                                       
        END

CREATE DATABASE sindex22
  ACCESS INDEX;

CREATE TABLESPACE sindx2p
IN sindex22;

CREATE TABLE secondary_indexb (
    xfldb CHAR(28) INTERNALNAME xfldb PRIMARY KEY,
    count CHAR(2) INTERNALNAME count start(25),
    enquirs BINARY(4) START(25) INTERNALNAME enquirs,
    LCHILD autodb.dealer LCINDEX xfld2
) IN sindex22
  INTERNALNAME sindxb
  MAXBYTES 34;

Example: Data Capture Exit

***********************************************************************
*        DBD DHVNTZ02 FROM CMVC (CDCI19-3.DBDGEN)                     *
***********************************************************************
  DBD       NAME=DHVNTZ02,                                             C
               ACCESS=(HIDAM,VSAM),PASSWD=NO,VERSION=CDCTEST
***********************************************************************
*        DATASET GROUP NUMBER 1
***********************************************************************
DSG001 DATASET DD1=HIDAM,DEVICE=3300,SIZE=(2048),SCAN=3
***********************************************************************
*        SEGMENT NUMBER 1                                             *
***********************************************************************
    SEGM    NAME=K1,                                                   C
               PARENT=0,BYTES=10,RULES=(LLL,LAST),PTR=(NOTWIN,,,,)     C
               EXIT=(*,LOG,PATH,KEY,DATA) 
      FIELD NAME=(K1,SEQ,U),                                           C
               START=1,BYTES=5,TYPE=C
      FIELD NAME=(ID),                                                 C
               START=6,BYTES=4,TYPE=C
       LCHILD  NAME=(INDEX,DXVNTZ02),                                  C
               PTR=INDX,RUTLES=LAST,TYPE=C
. . . .
. . . .
***********************************************************************
*        SEGMENT NUMBER 5                                             *
***********************************************************************
    SEGM    NAME=K5,PARENT=((K1,SNGL)),                                C
               BYTES=10,RULES=(LLL,LAST),PTR=(TWIN,,,,)                C
               EXIT=(*,LOG,PATH,KEY,DATA)
      FIELD NAME=(K5,SEQ,U),                                           C
               START=1,BYTES=5,TYPE=C
      FIELD NAME=(ID),                                                 C
               START=6,BYTES=4,TYPE=C
       LCHILD  NAME=(K3),                                              C
               PTR=SNGL,PAIR=K5X,RULES=LAST
       LCHILD  NAME=(K8),                                              C
               PTR=DBLE,PAIR=K5Y,RULES=HERE
. . . .
. . . .
    DBDGEN
    FINISH
    END

Example: Data Capture Exit

***********************************************************************
*        DBD DIVNTZ02 FROM CMVC (CDCI29-3 DBDGEN)                     *
***********************************************************************
 DBD NAME=DIVNTZ02,ACCESS=(HISAM,VSAM),VERSION=DIVNTZ02,               X
               EXIT=(COBXSQL,LOG,PATH,KEY,DATA,NOCASCADE)               
*
DSG01 DATASET  DD1=DBHVSAM1,DEVICE=3330,OVFLW=DBHVSAM2,                X
               BLOCK=(00004,00002),RECORD=(00200,00200)                 
*                                                                       
 SEGM          NAME=J1,                                                X
               PARENT=0,                                               X
               BYTES=10,                                               X
               FREQ=1,                                                 X
               POINTER=NONE,                                           X
               RULES=(PPP,LAST)                                         
 FIELD  NAME=(J1,SEQ,U),BYTES=005,START=00001,TYPE=C                    
 FIELD  NAME=ID,BYTES=4,START=6,TYPE=C                                   
********************************************************                
* DLI Change Data Capture - Change delete rule to virtual               
********************************************************                
 SEGM          NAME=J2,                                                X
               PARENT=((J1,SNGL),(J6,PHYSICAL,DIVNTZ02)),              X
               BYTES=37,                                               X
               FREQ=0000000001.00,                                     X
               POINTER=NONE,                                           X
               RULES=(PVP,LAST)                                         
 FIELD  NAME=(J2,SEQ,U),BYTES=005,START=00016,TYPE=C                    
 FIELD  NAME=ID,BYTES=4,START=21,TYPE=C                                  
 FIELD  NAME=(1J2),BYTES=004,START=00026,TYPE=C                         
 FIELD  NAME=(2J2),BYTES=004,START=00030,TYPE=C                         
 FIELD  NAME=(3J2),BYTES=004,START=00034,TYPE=C                         
. . . .
. . . .
 SEGM          NAME=J15,EXIT=NONE,                                     X
               PARENT=J12,                                             X
               POINTER=PAIRED,                                         X
               SOURCE=((K10,DATA,DHVNTZ02))                             
 FIELD  NAME=(J15,SEQ,U),BYTES=5,START=16,TYPE=C                         
 FIELD  NAME=ID,BYTES=4,START=22,TYPE=C                                  
               DBDGEN                                                   
               FINISH                                                   
               END