Correspondence between SQL and Non-SQL-defined Entities

Accessing Non-SQL-Defined Databases › Correspondence between SQL and Non-SQL-defined Entities

Correspondence between SQL and Non-SQL-defined Entities

Procedure

Define an SQL SCHEMA using the CREATE SCHEMA statement containing a FOR NONSQL SCHEMA parameter. This causes a logical relationship to be established between the two schemas.

The records defined in the non-SQL schema can now be accessed as tables in SQL DML and CREATE VIEW statements. Each record element is represented as a column except as noted under Record Structure Considerations.

Table Names

The table name used when referring to a non-SQL defined record is always the name specified in the ADD RECORD statement of the non-SQL schema definition. If hyphens appear in the record name, the table name must be enclosed in double quotation marks.

Column Names

The column name used when referring to a record element depends on whether a synonym for LANGUAGE SQL has been defined for the record:

If such a synonym has been defined, the column name is the element synonym name associated with the SQL record synonym.
If no SQL synonym has been defined for the record, the column names are based on the names of record elements either defined in the schema RECORD statement or associated with the version of the record whose structure is shared by the schema record.

In either case, column names are transformed by replacing hyphens with underscores.

Elements occurring a fixed number of times are represented by multiple columns whose names are constructed from the element name appended with an underscore (_) and an occurrence count. For example:

02  MONTHLY-BUDGET OCCURS 12 TIMES ...

is represented as 12 columns whose names are:

MONTHLY_BUDGET_01
MONTHLY_BUDGET_02 etc.

The length of the suffix is one greater than the number of digits in the number of occurrences of the element.

Up to three levels of nesting are supported. For example:

02  ANNUAL-BUDGET OCCURS 4 TIMES.
   03  MONTHLY-BUDGET OCCURS 12 TIMES ...

is represented as 48 columns whose names are:

MONTHLY_BUDGET_1_01
MONTHLY_BUDGET_1_02
 .
 .
 .
MONTHLY_BUDGET_1_12
MONTHLY_BUDGET_2_01
MONTHLY_BUDGET_2_02 etc.

In constructing column names for multiply-occurring elements, if the length of the resulting name exceeds 32 characters, the record is not accessible through SQL. To overcome this, define a synonym for LANGUAGE SQL where the affected element names are shortened.

Record Structure Considerations

Not all record elements can be referred to as columns and, in some cases, a single record element is represented by multiple columns.

Elements, other than those whose usage is BIT, are handled as follows:

Group elements are not represented by columns.
FILLERs (elements with names of 'FIL nnnn') are not represented by columns.
Level 88 elements (condition names) are not represented by columns.
Redefining elements and elements subordinate to a redefining group are not represented by columns.
Elements occurring a fixed number of times and elements subordinate to a group occurring a fixed number of times are represented by multiple columns, one for each occurrence of the element.
Elements occurring a variable number of times and elements subordinate to a group occurring a variable number of times are not represented by columns.
All other elements in the record are represented by columns.

USAGE BIT

Elements whose usage is BIT are not represented by columns except as noted following:

Group elements where all subordinate elements have a usage of BIT and which start on a byte boundary are represented by columns with a data type of BINARY. The length of the column is the length in bytes from the start of the group element to the start of the next element at the same level which begins on a byte boundary. If groups are nested within groups, the group element with the lowest level number where all subordinate elements are BITs is the element represented by a column. Intervening and subordinate elements are not represented by columns.
BIT elements occurring a fixed number of times and beginning on a byte boundary are represented by columns with a data type of BINARY. The length of the column is the length in bytes from the start of the element to the start of the next element at the same level which also begins on a byte boundary. Intervening elements are not represented by columns.
Other BIT elements which begin on a byte boundary are represented by columns with a data type of BINARY. The length of the column is the length in bytes from the start of the element to the start of the next element at the same level which also begins on a byte boundary. Intervening elements are not represented by columns.

Data Type of Columns

The data type of a column representing a record element is derived from the picture and usage of the element:

Picture and usage	Data type

PIC X(n) usage DISPLAY	CHAR(n)
PIC A(n) usage DISPLAY	CHAR(n)
Numeric edited&sub1.	CHAR(l), l=byte length
External floating point&sub2.	CHAR(l), l=byte length
PIC G(n) usage DISPLAY	GRAPHIC(n)
PIC S9(p)V9(s) usage DISPLAY	NUMERIC(p-s,s)
PIC SP..9(p) usage DISPLAY&sub3.	NUMERIC(p,p)
PIC S9(p)P.. usage DISPLAY&sub3.	NUMERIC(p,0)
PIC 9(p-s)V9(s) usage DISPLAY	UNSIGNED NUMERIC(p,s)
PIC P..9(p) usage DISPLAY&sub3.	UNSIGNED NUMERIC(p,p)
PIC 9(p)P.. usage DISPLAY&sub3.	UNSIGNED NUMERIC(p,0)
PIC S9(p-s)V9(s) usage COMP-3	DECIMAL(p,s)
PIC SP..9(p) usage COMP-3&sub3.	DECIMAL(p,p)
PIC S9(p)P.. usage COMP-3&sub3.	DECIMAL(p,0)
PIC 9(p-s)V9(s) usage COMP-3	UNSIGNED DECIMAL(p,s)
PIC P..9(p) usage COMP-3&sub3.	UNSIGNED DECIMAL(p,p)
PIC 9(p)P.. usage COMP-3&sub3.	UNSIGNED DECIMAL(p,0)
PIC S9(n), n<5 usage COMP&sub4.	SMALLINT
PIC S9(n), 4<n<10 usage COMP&sub4.	INTEGER
PIC S9(n), 9<n usage COMP&sub4.	LONGINT
PIC 9(n) usage COMP&sub4.	BINARY(l), l=byte length
PIC X(n) usage BIT	BINARY(l), l=byte length
USAGE POINTER	BINARY(4)
USAGE COMP-1	REAL
USAGE COMP-2	DOUBLE PRECISION
Note: 1. Numeric edited includes any element whose usage is DISPLAY and: Whose picture contains any of the editing symbols: + - Z B 0 $ CR DB . , * Whose picture clause contains only the symbols: 9 (n) V S P but whose element description also includes the SIGN LEADING or SEPARATE CHARACTER specification 2. External floating point includes any element whose usage is DISPLAY and whose picture is: +/- mantissa E +/- exponent 3. The scaling character "P" in a picture clause is ignored in value representations of associated columns. This has the effect of representing values of such columns as a power of 10 greater than or smaller than their actual value. For example, if an element is described as PIC S9(5)PPP, a value of 123000 is represented in SQL as 123. If an element is described as PIC SPPP9(5), a value of .000123 is represented in SQL as .123. 4. Computational elements also include those whose USAGE is BINARY and COMP-4. If the picture of a computational item includes an implied decimal point, it is ignored in determining the data type of the column. This has the effect of representing values of such columns as a power of 10 greater than their actual values. For example, if an element is described as PIC S9(5)V99 USAGE COMP, a value of 123.56 is represented in SQL as 12345. :etnote.