Data Mapping

This chapter shows you how the Databridge Client maps DMSII data structures to relational database structures.

DMSII and Relational Database Terms

The following table shows the equivalent terms for DMSII structures and relational database structures:

DMSII and Relational Database Data Types

The Databridge Engine retrieves the requested DMSII data, and DBServer passes the data to the Databridge Client, where it is assigned to standard relational database data types.

The following table lists equivalent data types for DMSII, Microsoft SQL Server, and Oracle.

* VARCHAR if the configuration parameter use_varchar is set to True.

† VARCHAR2 if the configuration parameter use_varchar is set to True.

‡ If the configuration parameter use_bigint is set to False, DEC(n) will be used instead.

§ Note that if the number is signed, SMALLINT is used instead. TINYINT is an unsigned quantity in SQL Server.

Databridge Data Types

IMAGE is a Databridge type that allows you to store an ALPHA item as binary by using the REDEFINE clause of the ALTER command in GenFormat on the host.

When the Databridge Client encounters an item of type IMAGE, it automatically sets the ds_options DIOPT_Clone_as_Binary bit in the DMS_ITEMS Client control table.

Supported DMSII Structures

This section lists DMSII structures that are supported by the Databridge Client. If you are the relational database administrator and have no experience with DMSII databases, this section will be more useful to you if you are working with a DMSII database administrator.

In addition to fixed-format data sets and variable-format data sets, the Databridge Client supports the following DMSII structures:

• Embedded data sets (see exceptions in the following section)

• Remaps

• Logical database

• GROUP

• FIELD items for GROUPs of BOOLEANS

• OCCURS

• GROUP OCCURS

• Data sets with more items than the maximum number of columns supported by the relational database

• Data sets that generate relational tables whose record sizes exceed the Microsoft SQL Server maximum record size.

• DMSII links in DIRECT, UNORDERED, and STANDARD data sets. (Links in variable-format data sets are cloned but not tracked.)

Some of these structures may not be supported by your relational database. The DMSII structures that are not supported by relational databases are mapped into a form that the relational database can use. Each of these structures and the way they are mapped in the relational database are explained in the remainder of this chapter.

Unsupported DMSII Structures

When the Databridge host replication software does not support a particular DMSII structure, the Databridge Client may or may not issue a warning message, depending on the DMSII structure. For example, a message is generated when the data set has no keys.

The Databridge Client does not support the following DMSII structures:

• Embedded data sets within an ORDERED or COMPACT data set

• Embedded data sets if the INDEPENDENTTRANS option is reset

• POPULATION items

• COUNT data items

• FILLER data items

• AGGREGATE data items

Embedded Data Sets

An embedded data set is a DMSII representation of a hierarchical relationship or tree structure. When a DMSII data set contains another data set as an item, that data set is called an embedded data set. The data set in which it is declared is called the parent of the embedded structure. You can think of the embedded data set as the "child" of the "parent" data set.

To represent this parent-child relationship in a relational database, the Databridge Client uses a foreign key that points to the parent data set. This foreign key is represented by the value in the parent_aa column in the table that corresponds to the embedded data set. The parent_aa column holds the parent record's key.

DMSII DASDL Showing an Embedded Data Set

The following is an excerpt from a DMSII DASDL that shows how an embedded data set is defined.

 GENEALOGY DATA SET
  (
    PARENT-FAT-NAME          ALPHA (30);
    PARENT-MOT-NAME          ALPHA (30);
    PARENT-MOT-MAIDEN        ALPHA (30);
    PARENT-FAT-BDATE         NUMBER (06);
    PARENT-MOT-BDATE         NUMBER (06);
    FILLER                   SIZE (06);
 %
    CHILD                    DATA SET;
       (
       CHILD-NAME            ALPHA (30);
       CHILD-STATUS          ALPHA (11);
       CHILD-BDATE           NUMBER (06);
       CHILD-GENDER          FIELD
          (
          CHILD-MALE         BOOLEAN;
          CHILD-FEMALE       BOOLEAN;
          );
       CHILD-FILLER          FIELD (01);
 %
       );
  );

Resulting Tables

The following examples are for Microsoft SQL Server.

Ignoring any set definition, the resulting relational database tables are as follows:

• Genealogy (the parent data set is cloned to its own primary table)

• Child (the embedded data set is cloned to its own secondary table with a pointer to its parent table)

Parent Table: genealogy (table name). Where the my_aa column is a unique key for the record derived from the DMSII AA Value of this record.

    (
    my_aa             char(12),
    parent_fat_name   char(30),
    parent_mot_name   char(30),
    parent_mot_maiden char(30),
    parent_fat_bdate  int,
    parent_mot_bdate  int
  )

Child Table: child (table name)

(
      my_aa             char(12), - child table's key
      parent_aa         char(12), - foreign key of parent table
      child_name        char(30),
      child_status      char(11),
      child_bdate       int,
      child_male        bit,
      child_female      bit,
      child_filler      smallint
  )

Selecting Embedded Data Sets for Cloning

When you run a clone command, by default, the Databridge Client selects embedded data sets along with the parent structures. If you have altered the values for the active column in the Databridge Client control tables, however, check the values for the embedded data set and its parent data set.

Record Serial Numbers

A record serial number (RSN) is a unique number (48-bits long) that is associated with a record in a data set. The RSN is guaranteed to be unique, and it stays with a record for the life of the record. Updates do not affect the RSN; therefore, RSNs are ideal keys for Databridge tables. However, RSNs are available only when using DMSII XE.

DMSII XE adds the RSN to every record of a data set that has the EXTENDED attribute set. As long as the EXTENDED attribute is set, Databridge can access the RSN, unlike application programs that can access the RSN only if it has been explicitly declared in the DASDL source. The Databridge Engine is designed to use RSNs instead of AA Values whenever possible. Regardless of whether RSNs are present, AA Values are used for data sets that contain embedded data sets or DMSII links.

Since RSNs and AA Values are the same length, they are interchangeable, unless the data sets are embedded data sets or contain DMSII links. If the Databridge Engine is not handling either of these types of data sets and an RSN is present, Databridge Engine uses the RSN rather than the AA Value. In this case, the resulting column of the DATAITEMS Client control table is named my_rsn instead of my_aa to differentiate it from an AA Value. In both cases, the set_name column of the DATASETS Client control table will have a value of "aa_set".

AA Values

AA is a DMSII term that stands for absolute address. An absolute address value is an A Series WORD (48-bits in length). In the Databridge Client, AA is the hexadecimal representation (12 character strings containing the characters 0--9 and A--F) of the AA Value on the host. Databridge Client uses the AA Values to implement unique keys for the parent structures of embedded data set records. It also uses AA Values to reference the records of data sets that do not have DMSII SETS with the NO DUPLICATES ALLOWED attribute.

AA Values are not constant. Any DMSII reorganization (record conversion, file format, or garbage collection) changes these values.

DMSII Links

The Databridge Client implements DMSII link items, such as MEMO items in LINC databases, using an AA Value. You can use the AA Value as a foreign key when you want data from the tables associated with the link item. To enable support for DMSII links, you must do the following:

• Enable DMSII link support in the Databridge Engine control file.

• Set the Client configuration file parameter enable_dms_links to True.

Variable-Format Data Sets

DMSII variable-format data sets consist of a collection of dissimilar records containing a fixed part that is common to all records, and a variable part that depends on the record type. The individual records are identified by the record type, which is a special data item that has a value between 0 and 254.

• A record type of 0 indicates that the record does not contain a variable part.

• A record type of 1--254 indicates that the record contains the corresponding variable part in addition to the fixed part that is always present.

The concept of variable-format tables does not exist in relational databases. Therefore, the Databridge host software handles the various types of variable-format records as different structures. Databridge references these structures by a data set name and a record type (all other data set types have a record type value of 0). The Databridge Client uses the notation datasetname/rectype when referring to variable-format data sets in all messages. The Databridge Client handles these structures as if they were logical data sets; thus, each individual record type of a variable-format data set is mapped to a different relational database table. Variable-format data sets are tracked and updated like fixed-format data sets. Links in variable-format data sets, however, are not tracked, but retain their values from the initial clone. The link values for any records created after the clone will be null. (See track_vfds_nolinks.)

DMSII DASDL Showing Fixed- and Variable-Length Records

      MAIN              DATA SET
      (
          VAR-REC       RECORD TYPE (3);
          CUST-NO       NUMBER (08);
          CPU           ALPHA (06);
          FILLER        SIZE (05);
      ),

      %

      1:
      (
          SMSA          ALPHA (04);
          SALES         ALPHA (06);
      )

      %

      2:
      (
          STATUS        ALPHA (01);
          RECEIVED      ALPHA (06);
          ORDER-DATE    NUMBER (08);
      )

      %

      3:
      (
          SITE          ALPHA (07);
          SOURCE        ALPHA (07);
          CLASS         ALPHA (02);
      );

      %

      MAINSET           SET OF MAIN
                        KEY (CUST-NO)
                        NO DUPLICATES;

Resulting Tables

The examples in this section assume that the Microsoft SQL Server database is being used and that the Client uses the SET MAINSET as the source for index for the various tables.

The following tables are derived from the variable-format data set MAIN:

• main (type 0 records)

• main_type1 (type 1 records)

• main_type2 (type 2 records)

• main_type3 (type 3 records)

  Note

  All four tables contain the fixed part of the data set. The var_rec column is the record type; all records in the individual tables will have the same value in this field.

Record Type 0 Table

The table named main represents all type 0 records that do not have a variable part. The var_rec column of all records in this table will have a value of 0. Note that this table may be empty if your application does not use type 0 records. The SQL statement to create this table is shown as follows:

  create table main
  (
    cust_no int,
    var_rec smallint,
    cpu char(6)
  )

Record Type 1 Table

The table named main_type1 represents all type 1 records. The var_rec column of all records in this table will have a value of 1. The SQL statement to create this table is shown as follows:

create table main_type1
(
    cust_no    int,
    var_rec    smallint,
    cpu        char(6),
    smsa       char(4),
    sales      char(6)
)

Record Type 2 Table

The table named main_type2 represents all type 2 records. The var_rec column of all records in this table will have a value of 2. The SQL statement to create this table is shown as follows:

create table main_type2
(
    cust_no    int,
    var_rec    smallint,
    cpu        char(6),
    status     char(1),
    received   char(6),
    order_date int
)

Record Type 3 Table

The table named main_type3 represents all type 3 records. The var_rec column of all records in this table will have a value of 3. The SQL statement to create this table is shown as follows:

create table main_type3
(
   cust_no     int,
   var_rec     smallint,
   cpu         char(6),
   site        char(7),
   source      char(7),
   class       char(2)
)

Split Variable Format Data Sets Option

When the ds_options bit DSOPT_Split_Varfmt_ds (bit value 65536) is set, variable format data sets are treated slightly differently. The record type 0 tables contains the fixed part of all records regardless of their record types. However, the table has exactly the same layout as above. The tables for all the other records only contain the variable part of the records and the keys from the fixed part.

The table named main_type1 in the above example will now contain the key cust_no and the variable part. The SQL statement to create this table is shown as follows:

create table main_type1 ( cust_no int, smsa char(4), sales char(6) )

Changing the Default Data Type

In most cases, the default data types are sufficient. If you want to change the data type, however, use a relational database query tool to edit the sql_type column in the DATAITEMS Client control table, or put the SQL statements in user scripts as explained in Customizing with User Scripts.

Most of these relational database data types can be changed using data table customization user scripts or the Administrative Console's Customize command is the Settings menu for the data source.

Handling DMSII GROUPs

A GROUP is a DMSII construct that allows the data items that belong to the group to be referenced at one time (for example, as one item). The concept of GROUP does not exist in a relational database. Therefore, if the DMSII database you replicate has one or more GROUPs, the Databridge Client ignores the GROUP name and instead treats each item within the GROUP as a regular data item. All items in a DMSII GROUP share the same parent item number, which is the item number of the GROUP item.

Following is an example of the DMSII GROUP item in the data set called ADDRESS. This GROUP item consists of the data item CITY and the data item STATE.

DMSII DASDL Showing GROUP

The following is an excerpt from a DMSII DASDL that shows how a GROUP item is defined. With the GROUP item, you can access both city and state with one reference.

      ADDRESS           DATA SET
      (
          STREET        ALPHA (20);
          APARTMENT     ALPHA (5);
          CITY-STATE    GROUP
          (
              CITY      ALPHA (20);
              STATE     ALPHA (2);
          );
          COUNTRY       ALPHA (20);
          ZIPCODE       NUMBER (5);
          POSTFIX       NUMBER (4);
      );

The next example shows how the same DMSII GROUP item is mapped to a relational database.

Relational Database Table

The following example is for Microsoft SQL Server.

The table name is the lowercase form of the DMSII data set name. The GROUP item CITY-STATE is ignored. The data items in that group are included in the relational database table as if they were ordinary DMSII data items.

address (table name)
street       apartment    city       state       country   zipcode  postfix

May St.      3            Paris      OH          USA       15010    2146
Elm Ln.                   River      SD          USA       24906    3381

If there are duplicate names among members of various groups within a data set, the Databridge Client resolves the conflict by appending a digit to the column name to make it unique.

Handling DMSII OCCURS

An OCCURS clause is a DMSII construct that describes the number of times an item is present or repeats within a data set. Because relational databases do not support the OCCURS construct, these clauses generate additional tables, which can degrade the performance of update processing.

You can control how items with an OCCURS clause are mapped on an item by item basis by flattening OCCURS. See Flattening OCCURS Clauses.

Default OCCURS Handling

If you don't flatten OCCURS, Databridge Client creates a new table for each data item that contains an OCCURS clause. The keys from the data item's parent data set are used as keys in the new table. In addition, a new key (named index1) is created to establish a unique composite key for each recurring data item.

For example, a DMSII data set has a data item with an OCCURS clause will result in two relational database tables:

• The first table (called the primary table) is named using the lowercase form of the DMSII data set name with all hyphens changed to underscores. It contains the key items as well as all data items that do not have OCCURS clauses.

• The second table (called the secondary table) is named by appending an underscore and the data item name to the primary table name. This table contains all of the OCCURS items; however, each table has a unique key created by index1. (Names that exceed the character limit are truncated. If the truncation results in a duplicate item names, the last characters of the name are changed to digits).

Handling OCCURS items this way can significantly degrade the performance of update processing if the number of occurrences is large. The storage required to hold the keys of the secondary table items can also be substantial. For example, an OCCURS 100 TIMES clause can turn a single DMSII update into 101 relational database updates. See DMSII DASDL with OCCURS for an example of a DMSII data set that has a data item with an OCCURS clause.

DMSII DASDL with OCCURS

The following excerpt from a DMSII DASDL shows how an OCCURS clause is defined.

 ORDERS                   DATA SET
 (
     ORDER-ID             ALPHA (4);
     ORDER-DATE           ALPHA (5);
     ORDER-ITEM           OCCURS 10 TIMES NUMBER (8);
 );

 BY-ORDER-ID SET OF ORDERS
 KEY IS
 (
     ORDER-ID
 )
 NO DUPLICATES,
 INDEX SEQUENTIAL;

The OCCURS clause allows access by subscripting (indexing) within an application program. Because relational databases do not allow subscripting (indexing), the Databridge Client maps the subscript into an additional key. The OCCURS items, then, are available by row.

When this ORDERS data set is cloned into the relational database, it is mapped into the following two tables. These tables show how the DMSII OCCURS clause appears in a relational database.

Table 1

This table is named the same as the ORDERS DMSII data set, and it contains the key item plus all non-OCCURS items. Assuming the ORDERS DMSII data set has 50 records, this table has 50 rows.

orders (table name)
order_id       order_date
--------       ----------
1201           jan12
 .              .
 .              .
 .              .
1250           feb12

Table 2

This table name combines the DMSII data set name and the name of the data item which has an OCCURS clause. It contains all the occurrences of the OCCURS data item ORDER-NUM.

Continuing with the example from Table 1 with 50 records (rows), this table has 500 total rows. For every order_id key (50 total), there are ten OCCURS items (as declared in the DASDL on the previous page).

orders_order_item (table name)
order_id      index1    order_item
--------      ------    ----------
1201          1         00007390
1201          2         00001293
1201          3         00007748
1201          4         00009856
1201          5         00003736
1201          6         00002278
1201          7         00004327
1201          8         00009463
1201          9         00008638
1201          10        00008954
1202          1         00001754
1202          .         00005309
1202          .         00004537
1202          10        00005940
1203          1         00005430
1203          .         00005309
1203          .         00004537
1203          10        00006587
  .           .             .
  .           .             .
  .           .             .

Flattening OCCURS Clauses

The flatten_all_occurs parameter makes the defineand redefinecommands set the value of the bit DIOPT_Flatten_Occurs (1) in the di_options column in the DMS_ITEMS table for all items that have OCCURS clauses. You can set this parameter from the Client Configurator or by editing the configuration file to specify whether to globally flatten OCCURS clauses for a data source. By using user scripts, you can control this option for individual items.

The Databridge Client provides two options for handling OCCURS clauses.

When using the Administrative Console you can set the parameter flatten_all_occurs by using the Configure command in the Settings menu for the data source. To customize the handling of individual items with OCCURS clause use the Customize command in the Settings menu for the data source. Click on the data set involved and click on the wrench (properties) of the item in question in the DMS Items view and pick the entry you want from the drop down list for the "Flatten Occurs" property. The choices are "Don't Flatten", "Flatten Within Table", "Flatten into Secondary Table" and if applicable "Flatten to String".

Flattening OCCURS Clauses to a String

Single items of type NUMBER(n) or ALPHA(n) with an OCCURS clause can be flattened to a character string represented by a CHAR or VARCHAR data type. You can have fixed format strings or CSV format strings, where the delimiter character can be selected via the dms_subtype column in DMS_ITEMS. This feature is controlled by the DIOPT_Flatten2String bit in the di_options and the dms_subtype column. If the dms_subtype is 0, fixed format is used and if the dms_subtype is non-zero it specifies the delimiter character used in the CSV format. NULL data is represented by blanks in fixed format and empty fields in CSV format (i.e. two consecutive delimiters or a delimiter at the end of the data). For example a NUMBER(1) OCCURS 20 TIMES can be flattened to a column that is a CHAR(20) when using fixed format.

Flattening OCCURS Clause for Three-Bit Numeric Flags

MISER systems store certain flags as arrays of single-digit numbers, where each number is used to hold three Boolean values. The Databridge Client can be directed to map these items as a series of Booleans data items (bit in SQL Server). To do this, set the DIOPT_Flatten_Occurs bit (1) and the DIOPT_Clone_as_Tribit bit (16) in the di_options column of the corresponding DMS_ITEMS record.

An example for the item L-LOCK-FLAG in the data set LOAN follows:

Filename: script.user_layout.loan

  update DMS_ITEMS set di_options=17
  where dataset_name = 'LOAN' and rectype=0 and dms_item_name = 'L-LOCK-FLAG'
        and data_source = 'MISDB'

In the above example, if the L-LOCK_FLAG has an OCCURS 20 TIMES clause, 60 items of type bit named l_lock_flag_01 to l_lock_flag_60 are created.

These items can also be flattened to a secondary table by setting the bit DIOPT_FlatSecondary(4096) in the di_options column for the corresponding entry in the DMS_ITEMS table.

Flattening OCCURS Clause for Items Cloned as Dates

The following script directs the define and redefine commands to map an item with an OCCURS clause as a series of columns, whose data type is a relational database date type, in the corresponding table. Furthermore, it specifies that the DMSII item, which is of type NUMBER(8), contains a date in the mm/dd/yyyy format.

Filename: script.user_layout.billing

  update DMS_ITEMS set di_options=3, dms_subtype=23
  where dms_item_name = 'BILLING-DATES' and dataset_name = 'BILLING'

DMSII GROUP OCCURS

The following is an excerpt from a DMSII DASDL that shows a GROUP item that has an OCCURS clause.

SALES                           DATA SET
(
    PRODUCT-CODE                ALPHA (10);
    PRODUCT-NAME                ALPHA (20);
    SALES-HISTORY GROUP         OCCURS 5 TIMES  %FIVE YEAR HISTORY
    (
        TOTAL-UNITS-SOLD        NUMBER (10);    %FOR THE YEAR
        YEARLY-SALES-AMOUNT     NUMBER (S12,2); %BY MONTH
    );

);
SH-PRODUCT-CODE-SET SET OF SALES-HISTORY
   KEY IS
   (
        PRODUCT-CODE
   )
NO DUPLICATES,
INDEX SEQUENTIAL;

When this SALES data set is cloned into the relational database, it is mapped into the following tables:

Table 1 (primary table)

This table is named the same as the SALES DMSII data set, and it contains the key item and the data items that do not have OCCURS clauses. Because the GROUP item has an OCCURS clause, none of the GROUP items are included in this table. Assuming there are five records in the DMSII data set, there are also five rows in this relational database table.

sales  (table name)
product_code     product_name
------------     ------------
BC99992121       Widget
TR55553440       Mixer
HM44447322       Gadget
PP77778299       Twirler
DG22221163       SuperMix

Table 2 (secondary table)

This table is named: datasetname + GROUP_OCCURS_name

Assuming there are five records in the DMSII data set, there are 25 records in this relational database table. The main difference here is the addition of an index to denote the occurrence number of the item.

sales_sales_history (table name)

product_code   index1    total_units_sold   yearly_sales_amount
------------   ------    ----------------   -------------------
BC99992121          1            55543665          123456789.01
BC99992121          2            83746994          234567890.12
BC99992121          3            33847295          345678901.23
BC99992121          4            57483037          456789123.45
BC99992121          5            10947377          567891234.56
TR55553440          1            56722221          678912345.67
TR55553440          2            74838976          789123456.78
TR55553440          3            54793873          891234567.89
TR55553440          4            99048900          912345678.90
TR55553440          5            22308459          123456789.01
HM44447322          1            75032948          234567890.12
HM44447322          2            30750344          345678901.23
HM44447322          3            90570340          456789123.45
HM44447322          4            57948755          567891234.56
HM44447322          5            44874733          678912345.67
     .              .                   .                   .   
     .              .                   .                   .

DMSII Nested OCCURS

The following is an excerpt from a DMSII DASDL showing a GROUP with an OCCURS clause that contains an item with an OCCURS clause.

This example helps to reinforce the previous examples of how DMSII GROUP and OCCURS are mapped to a relational database.

SALES                    DATA SET
 PRODUCT-CODE            ALPHA (10);
 PRODUCT-NAME            ALPHA (20);
 SALES-HISTORY GROUP     OCCURS 5 TIMES %FIVE YEAR HISTORY
   (
   TOTAL-UNITS-SOLD      NUMBER (10); %FOR THE YEAR
   MONTHLY-SALES-AMOUNT  NUMBER (S12,2) OCCURS 12 TIMES;
   );
SH-PRODUCT-CODE-SET      SET OF SALES-HISTORY
   KEY IS
   (
   PRODUCT-CODE
   )
NO DUPLICATES,
INDEX SEQUENTIAL;

When this SALES data set is cloned into the relational database, it is mapped into the following three tables:

• sales

  (primary table, table name derived from datasetname)

• sales_sales_history

  (secondary table, table name derived from datasetname + GROUPOCCURSname)

• sales_monthly_sales_amount

  (secondary table, table name derived from datasetname + OCCURSitemname)

Table 1

This table is named the same as the SALES DMSII data set.

It contains the key item and all non-OCCURS data items. Because the GROUP has an OCCURS clause, none of the GROUP items are included in this table. Assuming there are five records in the DMSII data set, there are five rows in the resulting relational database table.

sales  (table name)
product_code      product_name
------------      ------------
BC99992121        Widget
TR55553440        Mixer
HM44447322        Gadget
PP77778299        Twirler
DG22221163        SuperMix

Table 2

This table is named: datasetname + GROUP_OCCURS_name

Assuming there are five records in the DMSII data set, there are 25 rows in this table. Note the addition of the index1 column to denote the occurrence number of the group.

sales_sales_history (table name)

product_code   index1     total_units_sold
------------   ------     ----------------
BC99992121     1          55543665
BC99992121     2          98075300
BC99992121     3          77476478
BC99992121     4          76593939
BC99992121     5          33728282
TR55553440     1          87548974
TR55553440     2          56722221
TR55553440     3          11910078
TR55553440     4          47589474
TR55553440     5          57987999
HM44447322     1          75533785
HM44447322     2          33673391
HM44447322     3          74904532
HM44447322     4          98724498
HM44447322     5          39875992
   .           .             .    
   .           .             .

Table 3

This table is named: datasetname + OCCURSitemname

Assuming there are five records in the DMSII data set, there are 300 rows in this table (12 occurrences of monthly_sales_amount for each of 5 occurrences of sales_history for each product code). In the table below, index1 is the subscript of the GROUP OCCURS (1--5) and index2 is the subscript of the monthly sales amount, with subscripts (1--12).

In this example, the OCCURS level of the items MONTHLY-SALES-AMOUNT is 2, while the OCCURS level of the item SALES-HISTORY is 1.

sales_monthly_sales_amount (table name)
product_code   index1   index2   monthly_sales_amount
------------   ------   ------   --------------------
BCS9992121     1        1        1075.36
BCS9992121     1        2        49397.90
BCS9992121     1        3        49375.93
BCS9992121     1        4        22840.97
BCS9992121     1        5        38984.02
BCS9992121     1        6        40039.84
BCS9992121     1        7        33875.93
BCS9992121     1        8        35000.22
BCS9992121     1        9        65876.52
BCS9992121     1        10       20402.55
BCS9992121     1        11       17575.00
BCS9992121     1        12       41938.74
BCS9992121     2        1         . 
BCS9992121     2        2          . 
BCS9992121     2        3          . 
BCS9992121     2        4          . 
BCS9992121     2        5          . 
BCS9992121     2        6          . 
BCS9992121     2        7          . 
BCS9992121     2        8          . 
BCS9992121     2        9          . 
BCS9992121     2        10         . 
BCS9992121     2        11         . 
BCS9992121     2        12         . 
BCS9992121     3        1          . 
     .         .        .          . 
     .         .        .          . 
     .         .        .          . 
     .         .        .          .

OCCURS DEPENDING ON

DMSII uses the DEPENDING ON clause (usually with COMPACT data sets) to conserve disk space. For COMPACT data sets, the DMSII work area always contains a fully expanded version of the record; however, the record is compacted when it is stored on disk. The exact syntax for OCCURS DEPENDING ON clause is as follows:

    item_name OCCURS n TIMES DEPENDING ON depends_item_name;

The value n defines the maximum number of occurrences of the data item item_name, while the value of the depends item depends_item_name controls the number of occurrences of the item that are stored. This last number cannot exceed n. Information on an OCCURS DEPENDING ON clause is relayed to the Databridge Client, enabling the Databridge Client to suppress extraneous columns that do not actually exist. If the DEPENDS data item has a value of 3, and the OCCURS clause is OCCURS 10 TIMES, the last 7 columns are not included.

Handling Unflattened OCCURS DEPENDING ON Clauses

To handle a changing depends item, the Databridge Client uses before-image/after-image (BI/AI) pairs for data sets that have items with OCCURS DEPENDING ON clauses that are not flattened.

First, the Databridge Client checks the old and new values of the DEPENDS data item to determine how to execute the modify. The modify is handled in one of the following ways:

• If the value of the DEPENDS data item is unchanged, the Databridge Client updates the corresponding rows in the secondary tables as usual. (Redundant updates are suppressed if the ds_options bit DSOPT_Use_bi_ai is set.)

• If the value of the DEPENDS data item increases from m to n, the first m items are updated normally. The newly added items (m+1 through n) are inserted into the secondary table.

• If the value of the DEPENDS data item decreases from m to n, the first n items are updated normally. Items that are no longer present (n+1 through m) are deleted from the secondary table.

Relational Database Split Tables

A split table occurs when a DMSII data set record requires more than one table in the relational database to hold the data. Split tables occur in the following circumstances:

• When a table mapped from a DMSII data set has more than the maximum number of columns allowed by the relational database. The maximum_columns parameter in the configuration file allows you to reduce this value.

• When a relational database table's record size exceeds the Microsoft SQL Server maximum record size (approximately 8K -- the actual value depends on the number of columns in the table).

When the define (or redefine) command reaches the point where one of the above conditions is satisfied, it stops adding columns to the table (named the same as the DMSII data set). It then starts a new table that contains the same keys as in the original record of the primary table, followed by the remaining items in the data set at the point the split occurred. Note that there is always the possibility of having multiple splits for data sets that have a large number of columns. The flattening of OCCURS items can easily lead to split tables.

A split can occur in the middle of flattening an OCCURS clause, which can be rather awkward. In order to better control where a split occurs we added the di_options2 bit DIOPT_Split_Here (4) that forces the table split to occur after the item in question is processed.

Split Table Names

The new table is named using the original (parent) table name with a number (usually 1) appended to it to make it unique. All subsequent tables created from the same data set have the original table name with a numeric suffix that is incremented by 1 each time a new split table is created.

Keys for Split Tables

For a data set with keys, the keys of the original data set are duplicated in the split tables because you must access each of these tables individually. The process of splitting the data set into tables continues until there are no more data items left in the data set.

The following examples show the mapping of a data set that has 600 items (5 of which are keys) to a relational database that limits the number of columns in a table to 250. The result is tables that contain a total of 610 columns, where the 5 keys are duplicated across all 3 tables. If the original table is named savings, the remaining two tables are named savings1 and savings2, unless these names are already in use.

The five keys are duplicated in each table. To search these split tables, you must explicitly open each table. The tables are not automatically linked.

Relational Database Table and Column Names

When you clone a DMSII database, the Databridge Client names the relational database tables and columns the same as their equivalent DMSII data sets and data items. However, some differences exist. In this section, the differences between the names are explained.

Uppercase and Lowercase

All DMSII data set, data item, and set names are uppercase. These names are also stored in uppercase in the DATASETS and DMS_ITEMS Client control tables. Their equivalent relational database table, column, and index names are stored in lowercase in the DATATABLES and DATAITEMS Client control tables.

• All DMSII data set names are stored in the DATASETS Client control table in uppercase, just as they appear in the DMSII database. The equivalent relational database table name is converted to lowercase and is stored in the DATATABLES Client control table. Thus, a data set named CREDIT in the DMSII database is named credit in the relational database.

• All DMSII data item names are stored in the DMS_ITEMS Client control table in uppercase, just as they appear in the DMSII database. The equivalent relational database data item name is converted to lowercase and is stored in the DATAITEMS Client control table. Thus, a data item named LIMIT in the DMSII database is named limit in the relational database.

  Note

  You must type these names in the correct case. If you are using the relational database table name as a character string value in a SQL statement (for example, 'tablename'), you must use lowercase.

Hyphens and Underscores

The hyphen (-) in the DMSII name becomes an underscore (_) in the relational database name. The only exception is a data source name that is allowed to contain hyphens.

Name Length

The limit for a DMSII data set name is 17 characters, and DMSII item name is limited to 128 characters. Relational databases typically limit table names to 30 characters; however, the Databridge Client reserves two characters for the prefix of the stored procedure names for updating the table (i_ tablename, d_ tablename, u_ tablename). Thus, the table names are actually limited to 28 characters. Similarly, the Databridge Client adds a one or two character prefix to the item names to create a unique name for the parameters of the stored procedures. The Databridge Client for Microsoft SQL Server uses a prefix of @ while the Databridge Client for Oracle uses a prefix of p_. To avoid using names that are too long for the relational database, items names are limited to 29 characters for SQL Server or 28 characters for Oracle.

With this limit of 28 characters for a table name, typically all the DMSII names fit into the relational database table name or column name. In cases where data set, data item, or other structure names are concatenated and therefore become too long for a relational database, the Databridge Client truncates the name.

Duplicate Names

If two data sets have the same name in two different DMSII databases (or data sources, from the Client perspective), the Databridge Client appends the number 1 to the duplicate table name the first time it is encountered. If a table already exists with the duplicate name with "1" appended to it, the Databridge Client appends the number "2" instead and so on until a unique table name is created.

For example, if DMSII database A has a data set named PRODUCTS and DMSII database B also has a data set named PRODUCTS, the resulting Databridge table names would be products and products1.

If you combine this duplicate data set name convention with the convention for naming split tables (when one data set results in more than one table), you can have multiple suffixes for short names.

For example, if you have two data sources with a data set named CUSTOMER, which also generates split tables, the tables are renamed as follows:

• customers and customers1in the first data source

• customers11and customers12in the second data source (as the primary table was renamed customers1)

Duplicate item names may result in the following cases:

• When you use the same name for items in two different GROUPs. DMSII allows this, but the Databridge Client ignores GROUPs.

• When you truncate two long DMSII item names that are almost identical

The Databridge Client handles duplicate item names the same way that it handles duplicate table names.

Reserved Keywords

You cannot use reserved keywords for relational database object (table, column, index, etc.) names. For example, "order" is an SQL keyword; therefore, you cannot rename a relational database table or column as "order".

If an existing DMSII data set is named ORDER, the Databridge Client stores ORDER in the DATASETS Client control table and an equivalent relational database table called "order_x" in the DATATABLES Client control table. This same convention of adding "_x" to rename a table whose name is a reserved word applies to DMSII data items. For information on reserved words in your relational database, see the related database documentation.

The SQL Server Client allows you to use reserved words as object names, as long you enclose them in square brackets in SQL statement. The configuration file parameter bracket_tabnames allow you use reserved words like "order" as table names. Setting this parameter to True makes the Client use brackets around table names that are reserved words.