Disjoint data sets are modeled as SQL tables, with the following exceptions:

In addition, a view containing all records in the Enterprise Database Server data set, regardless of format value, is also created. The view can be used to select rows irrespective of the format. See “Variable-Format Data Set Behavior” later in this section for more information. The default naming behavior for views is <data set name>.

If the length of the default name exceeds the SQL maximum name length, the default name is truncated to the maximum name length of 30 single-byte characters or 14 double-byte characters. If this truncation causes a naming conflict, it is resolved according to the method outlined under “Handling Naming Conflicts” earlier in this section.

You must have SELECT permissions on the master data set to perform the following actions for an embedded data set:

Data Set Example

The following example illustrates how a simple data set can be mapped in SQL:

Enterprise Database Server

PERSON DATA SET (
    NAME           ALPHA(20);
    INSTRUCTOR-ID NUMBER(10);
                );
BY-NAME SET OF PERSON KEY IS NAME NO DUPLICATES;

SQL Description with Default Names

CREATE TABLE PERSON
   (NAME        CHAR(20) NOT NULL,
    INSTRUCTOR_ID NUMERIC(10),
    PRIMARY KEY (NAME)   
   )

CREATE VIEW BY_NAME AS
    SELECT * FROM PERSON
    --ORDER BY : NAME
INDEX BY_NAME_INDEX ON PLAYER
    (ASCENDING NAME) UNIQUE

Variable-Format Data Set Example

The following example illustrates how an Enterprise Database Server variable-format data set can be mapped in SQL:

Enterprise Database Server

PERSON DATA SET
  (NAME           ALPHA(20);
   PROFESSION     RECORD TYPE(2))
1: (STUDENT-ID    NUMBER(10)),     % Student data
2: (INSTRUCTOR-ID NUMBER(10));     % Instructor data

SQL Description with Default Names

                                                                                
CREATE TABLE PERSON_FORMAT_0                                             
 (NAME                           CHARACTER (20) NOT NULL                 
 ,PROFESSION                     INTEGER DEFAULT 0 NOT NULL READONLY)    
                                                                                
CREATE TABLE PERSON_FORMAT_1                                             
 (NAME                         CHARACTER (20) NOT NULL                 
 ,PROFESSION                   INTEGER DEFAULT 1 NOT NULL READONLY     
 ,STUDENT_ID                   NUMERIC (10))                           
                                                                                
CREATE TABLE PERSON_FORMAT_2                                             
 (NAME                         CHARACTER (20) NOT NULL                 
 ,PROFESSION                   INTEGER DEFAULT 2 NOT NULL READONLY     
 ,INSTRUCTOR_ID                   NUMERIC (10))                           
                                                                                
CREATE VIEW PERSON AS                                                    
 SELECT NAME, PROFESSION                                                
 FROM PERSON_FORMAT_0                                                   
   UNION ALL                                                        
 SELECT NAME, PROFESSION                                                
 FROM PERSON_FORMAT_1                                                   
   UNION ALL                                                        
 SELECT NAME, PROFESSION                                                
 FROM PERSON_FORMAT_2                      

SQL Description Modified with More Meaningful Names

CREATE TABLE PERSON_FIXED_FMT                                            
    (NAME                        CHARACTER (20) NOT NULL                 
    ,PROFESSION                  INTEGER DEFAULT 0 NOT NULL READONLY)    
                                                                                
CREATE TABLE STUDENT                                                     
    (NAME                         CHARACTER (20) NOT NULL                 
    ,PROFESSION                   INTEGER DEFAULT 1 NOT NULL READONLY     
    ,STUDENT_ID                   NUMERIC (10))                           
                                                                                
CREATE TABLE INSTRUCTOR                                                  
    (NAME                         CHARACTER (20) NOT NULL                 
    ,PROFESSION                   INTEGER DEFAULT 2 NOT NULL READONLY     
    ,INSTRUCTOR_ID                NUMERIC (10))                           
                                                                                
CREATE VIEW PERSON AS                                                    
    SELECT NAME, PROFESSION                                                
    FROM PERSON_FIXED_FMT                                                  
        UNION ALL                                                        
    SELECT NAME, PROFESSION                                                
    FROM STUDENT                                                           
        UNION ALL                                                        
    SELECT NAME, PROFESSION                                                
    FROM INSTRUCTOR       

As shown in this example, for variable-format data sets Relational Design Center normally uses identifiers that are named with the following format:

<data set name>_FORMAT_<format number>

You can make the identifier names created by Relational Design Center more meaningful by defining schema modifications.

Variable-Format Data Set Behavior

The STUDENT table contains only those records from the Enterprise Database Server data set that have PROFESSION equal to 1 as shown by the DEFAULT value for PROFESSION. Similarly, the INSTRUCTOR table contains only those records from the Enterprise Database Server data set that have PROFESSION equal to 2 as shown by the DEFAULT value for PROFESSION. The PERSON_BASE table contains only those records with no variable-format part. When PROFESSION is equal to 0, a record contains no variable-format parts.

The view PERSON contains all records in the Enterprise Database Server data set, regardless of their PROFESSION value. Normally, updates to a view based on multiple tables would not be allowed in SQL. However, updates are allowed for a view created for a variable-format data set such as PERSON because the underlying Enterprise Database Server construct is really a single data set. Updates to the view PERSON also appear in the PERSON_FORMAT_0, INSTRUCTOR, and STUDENT tables.

If you insert a row into the PERSON_BASE, INSTRUCTOR, or STUDENT table, that row automatically becomes part of the view table. In the preceding example, when a row is added to the STUDENT or INSTRUCTOR table, that row automatically belongs to the PERSON view.

If you delete a row from the PERSON table, that row is automatically deleted from the PERSON_BASE, STUDENT, or INSTRUCTOR table. When a row is deleted from the STUDENT or INSTRUCTOR table (the variable-format part tables), the whole record is deleted, including the fixed-format part.