CREATE TABLE statement for IMS

You can use the CREATE TABLE statement to define a logical table that references an IMS database.

Authorization

The connected user ID must have one of the following privileges to run the CREATE TABLE statement:

SYSADM
DBADM for the database type that is referenced in the DBTYPE clause

The owner has all table privileges on the table (such as SELECT, UPDATE, and so on) and the authority to drop the table. The owner can grant equivalent use privileges on the table.

authorization-ID.table-name

Identifies the owner of the table and the name of the table that you want to create.

If you do not provide an authorization ID, the ID in the CURRENT SQLID special register is used.

You must create more than one table if you map to a database or a file that meets either of these criteria:

The database or file contains repeating data.
The database or file contains information about one or more distinct sub-objects, because the database or file is not designed to follow the third normalization rules, which are part of the standards to eliminate redundancies and inconsistencies in table data. In a table designed according to third normalization rules, each non-key column is independent of other non-key columns, and is dependent only upon the key.

DBTYPE IMS

Specifies that the CREATE TABLE statement defines a logical table that references an IMS database.

DBD-name

Identifies the IMS DBD (database definition) that the table references. DBD-name corresponds to the name in the NAME parameter for the DBD statement. That statement is in the DBDGEN source definition for the IMS logical or physical database that the IMS table references. DBD-name follows z/OS® load-module naming conventions.

INDEX ROOT perceived-root-segment-name

Identifies the root segment for the database hierarchy that the IMS logical table definition maps. By default, the root segment is the physical or logical root segment for the database that DBD-name specifies. The physical or logical root segment for the database constitutes the root segment for verification of the IMS hierarchy to the segment that leaf-segment-name specifies.

The INDEXROOT clause is required when the logical table references an IMS secondary data structure hierarchy because the intent is to use a secondary index to access or update the IMS database. The INDEXROOT clause is required only when a secondary index is used and the target segment of the secondary index is not the root segment of the database. For additional information about secondary indexes, see the IMS Administration Guide: Database Manager.

The name is a short native identifier that follows IMS segment naming conventions. The segment must exist in the DBD that DBD-name specifies.

The segment must be either the root segment of the database or the root segment because a secondary index accesses the table and database.

The following caveats apply for the INDEXROOT clause:

When a secondary index exists in the database that your table references and the target segment in the segment hierarchy is the root segment of the database, create separate tables for each access path.
You can use one table to access the IMS database by using the primary key sequence field. You can use the additional tables to access the database by using the secondary index XDFLD definition. Each of these additional tables must use either a different PSB to access the database, or the PCB prefix option if for a single PSB.
To use a single mapping for both primary key and secondary index access, you must specify the PCB prefix option and explicitly define indexes by using the CREATE INDEX statement for each access technique (primary key or XDFLD). On each index definition, you identify the PCB prefix that selects the PCB that accesses the IMS database.
In this situation, the PCB that accesses the database depends upon the contents of the WHERE clause:
- If columns in the WHERE clause provide references to all of the columns that make up an XDFLD definition, the PCB prefix for the index that contains the XDFLD columns is used to access the database.
- If the WHERE clause contains references to the columns that map to the primary key sequence field, the index that contains the primary key sequence field columns is selected. The PCB prefix that is associated with that index is used to access the database.
- If the WHERE clause does not contain sufficient information to select either index, the PCB prefix at the table level determines which PCB accesses the database.

leaf-segment-name

Identifies the lowest level segment in the database hierarchy that the table maps to.

The database hierarchy is determined by traversing the parent chain (PARENT= keyword in the DBD definition) for leaf-segment-name to either the explicitly identified perceived-root-segment-name or the root segment of the physical or logical database.

The name is a short native identifier that follows IMS segment naming conventions. The segment must exist in the DBD that is identified by DBD-name.

When perceived-root-segment-name is the physical or logical root segment of the database, leaf-segment-name must be a physical or logical child of perceived-root-segment-name.

When perceived-root-segment-name is the root segment in a secondary data structure, leaf-segment-name must be a child of perceived-root-segment-name.

The combined maximum segment length of all segments that are included from the index-root down to and including the leaf-segment-name cannot exceed 32767 bytes.

SUBSYSTEM IMS-subsystem-ID

Identifies the IMS subsystem that is the location of the database that DBD-name specifies. The Open Database Access (ODBA) interface uses IMS-subsystem-ID when it accesses or updates the IMS database for two-phase commit.

The ID is a native identifier that follows IMS subsystem naming conventions. IMS-subsystem-ID is 1 to 4 characters in length.

SCHEDULEPSB

Identifies the names of one or two PSBs that access or update the IMS database when the DRA or ODBA interface is used.

standard-PSB-name

Identifies the name of the default PSB that accesses the IMS database.

JOIN-PSB-name

Optionally specifies the name of the PSB that is scheduled to access the IMS database. The DRA and ODBA interfaces use DBD-name to identify the IMS database. JOIN-PSB-name corresponds to a PSB definition that is defined to the IMS system being accessed and as a PDS member under the same name that exists in the active ACB library of the target IMS subsystem.

If your applications issue joins against multiple IMS tables, specify JOIN-PSB-name.

JOIN-PSB-name follows the naming conventions for the z/OS load module.

The PSB is scheduled when a SELECT statement is run that contains a JOIN predicate that references multiple IMS tables. This first table is the one that JOIN references.

PCBPREFIX PCB PCB-name-prefix

Identifies a partial PCBNAME that specifies the PCB that accesses or updates the IMS database.

The prefix is a native identifier that follows IMS PCB naming conventions. PCB-name prefix is from 1 to 7 characters in length.

PCBNAME (PCB_name,...)

Specifies up to 5 PCB names, each of which access an IMS database for a table. Multiple names are required if the same table is referenced more than one time in an SQL statement, or when the same PCB name is associated with more than one table, and the additional tables are referenced in a single SQL statement.

Each PCB name in the list is 1 to 8 characters in length.

PCBNUM (PCB_number (count),...)

Allows more potential PCBs to access the IMS database for the table than the PCBNAME keyword allows. Multiple numbers are required if the same table is referenced more than once in an SQL statement, or when the same PSB is associated with more than one table, the PCBs in the PSB have sensitivity to the segments that the table accesses and the same PCB ordinal numbers are specified for these tables. These additional tables are referenced in a single SQL statement.

You can specify up to ten sets of PCB number ranges. The order of the numbers represent which order a PSB is checked to determine whether a PCB accesses the IMS database.

For each item in the list, different methods identify the PCB numbers that are checked. The simplest method is separate each PCB number with commas. The second method identifies a range that consists of a starting PCB number that is followed by parenthesis and a number that identifies the number of PCBs to check from the starting number.

With either method, the PCB number represents the relative 1 ordinal number of the PCB that is checked. Because an input or output PCB must be defined in each PSB, the minimum practical PCB number is 2.

column_definition

Provides SQL descriptions of the contents of the segments in the IMS database hierarchy that this table accesses. Optionally, the column can include one or more record arrays that identify repeating data in the sequential file. IMS databases have limited metadata. The DBD definition defines the segments that make up the database and its hierarchical structure.

A table must contain at least one column and can contain up to 5,000 columns. Columns are named, and each name must be unique within the table.

At a minimum, the DBD definition contains an IMS FIELD definition for the keys for each segment in the database and XDFLD definitions that identifies the keys of any secondary indexes. Whether the DBD contains additional FIELD statements that define the other fields in each segment, is site dependent. A common practice is to only define additional FIELD statements for those attributes that are referenced in segment search arguments (SSAs) that the applications issue. Then IMS filters the data that is returned to the application.

record_array_definition

Identifies repeating data. Record array definitions contain column definitions and possibly more record array definitions.

Federated queries can read record array data if you perform one of the following procedures:

Map the columns in a flattened structure that provides a separate column for each array instance and field.
Map a separate table for each array.

You can read, update, and capture changes to redefined data if you create a separate table and view for each record type. You can update and capture changes to record array data if you map the columns in a flattened structure.

PRIMARY KEY column-name

Identifies the columns that uniquely identify an IMS database record and the segment instances that this table references in an IMS database.

The specification of primary key information is always valid for a table that references an IMS database.

The ability to determine what constitutes a good set of primary key columns versus a bad set of columns depends primarily upon whether perceived-root-segment-name is explicitly specified or implicitly identified, for example by the root segment.

The primary key does not enforce the same restrictions that the CREATE INDEX statement does. There is no prohibition against identifying the columns that make up the primary key sequence field or a DEDB, HDAM or PHDAM database as primary key columns. Likewise, you can use primary key columns for child segments. References to columns that map to the sequence fields of all of the child segments in the database hierarchy are also good primary key column references. Under the assumption that the fully concatenated key is unique, references to child segment sequence fields that are not unique are also acceptable . A warning message is generated when a reference is made to a non-unique child sequence field.

Example

The following is an example of a CREATE TABLE statement for IMS.

 CREATE TABLE CXAIMS.IMSALDB DBTYPE IMS
   FVT52901 INDEXROOT FVTROOT FVTROOT
   SCHEDULEPSB(PF52901U) PCBPREFIX FVT
 (
  ALDBIMSKEY  SOURCE DEFINITION ENTRY FVTROOT
   DATAMAP OFFSET 0 LENGTH 4 DATATYPE F
   USE AS INTEGER,
  ALDBIMSCHAR  SOURCE DEFINITION ENTRY FVTROOT
   DATAMAP OFFSET 4 LENGTH 254 DATATYPE C
   USE AS CHAR(254)
   NULL IS X'4040',

  ALDBIMSLDECIMAL SOURCE DEFINITION ENTRY FVTROOT
   DATAMAP OFFSET 266 LENGTH  8 DATATYPE P
   USE AS DECIMAL(15,0)
   NULL IS X'000000000000000C',
  ALDBIMSDECIMALMAX SOURCE DEFINITION ENTRY FVTROOT
   DATAMAP OFFSET 282 LENGTH  8 DATATYPE P
   USE AS DECIMAL(15,15)
   NULL IS X'000000000000000C',
/*                                                 */
  ALDBIMSLFLOAT SOURCE DEFINITION ENTRY FVTROOT
   DATAMAP OFFSET 294 LENGTH 8 DATATYPE D
   USE AS FLOAT(53)
   NULL IS X'0000',
  ALDBIMSDBLPREC SOURCE DEFINITION ENTRY FVTROOT
   DATAMAP OFFSET 302 LENGTH 8 DATATYPE D
   USE AS FLOAT(53)
   NULL IS X'0000',
  ALDBIMSINTEGER SOURCE DEFINITION ENTRY FVTROOT
   DATAMAP OFFSET 310 LENGTH 4 DATATYPE F
   USE AS INTEGER
   NULL IS X'00000000',
  ALDBIMSREAL SOURCE DEFINITION ENTRY FVTROOT
   DATAMAP OFFSET 314 LENGTH 4 DATATYPE D
   USE AS FLOAT(21)
   NULL IS X'0000',
  ALDBIMSSMALLINT SOURCE DEFINITION ENTRY FVTROOT
   DATAMAP OFFSET 318 LENGTH 2 DATATYPE H
   USE AS SMALLINT
   NULL IS X'0000',
  ALDBIMSVCHAR SOURCE DEFINITION ENTRY FVTROOT
   DATAMAP OFFSET 320 LENGTH 256 DATATYPE V
   USE AS VARCHAR(254)
   NULL IS X'4040',
/*                                                 */
  ALDBIMSLVCHAR SOURCE DEFINITION ENTRY FVTROOT
   DATAMAP OFFSET 576 LENGTH 1026 DATATYPE V
   USE AS VARCHAR(1026)
   NULL IS X'4040',
/*                                                 */

  ALDBIMSLVGRAPHIC SOURCE DEFINITION ENTRY FVTROOT
   DATAMAP OFFSET 2112 LENGTH 1025 DATATYPE V
   USE AS VARGRAPHIC(1025)
/* USE AS LONG VARGRAPHIC                           */
   NULL IS X'4040',
  PRIMARY KEY (ALDBIMSKEY)
 );