Metadata Mapping

This section provides a review of the transformations used by the SQL engine while reading the definitions of network record types. When SQL is used to access network record types, the entity names coded in the SQL syntax must follow the conventions described next.
idmscu19
This section provides a review of the transformations used by the SQL engine while reading the definitions of network record types. When SQL is used to access network record types, the entity names coded in the SQL syntax must follow the conventions described next.
This article describes the following information:
2
2
SQL Schemas for Network Databases
SQL tables are referenced in SQL DML statements by coding the table name preceded by a schema name qualifier. For example, in SELECT * FROM DEMOSCH.SAMPLE, SAMPLE is the table name and DEMOSCH is the SQL schema where it is defined. The combination of schema name and table name allows the SQL compiler to look up the definition of the table in the SQL catalog.
To access a network record type from an SQL statement, you must code the record name in the same manner used for SQL tables. An SQL schema which maps onto the corresponding network schema must be defined in the SQL catalog. This SQL schema name is used to qualify all subsequent references to network record types in SQL DML statements, as shown in the following example:
CREATE SCHEMA SQLNET FOR NONSQL SCHEMA PRODDICT.CUSTSCHM; SELECT * FROM SQLNET."ORDER-NET";
Network Record and Set Name Transformations
Because hyphen is the subtraction operator in SQL, network record names containing embedded hyphens must be delimited by double quotes (for example, "CUST-REC-123"). Any network set names containing embedded hyphens must be delimited by double quotes before they can be used in an SQL statement (for example, "CUST-ORDER").
Network Element Name Transformations
Unlike hyphens embedded in record and set names, hyphens embedded in network element names are automatically transformed to underscores (_) during the definition loading phase of the SQL compiler. So, to access the CUST-NUMBER element in a network record type, you must code CUST_NUMBER in an SQL statement.
When a FOR LANGUAGE SQL synonym is defined for a network record type, the element synonyms are used for all SQL access. SQL synonyms are
only
used for element names. Defining SQL synonyms for network record types is sometimes the only way to overcome column name limitations within SQL.
Some network element names don't make satisfactory SQL column names, even after the hyphen-to-underscore transformation. For example, if a network element name starts with a numeric character, the double quote delimiter must again be used (123-ORD-NUM would be accessed using "123_ORD_NUM" in an SQL statement).
Group elements, redefines elements, FILLERS and OCCURS ... DEPENDING ON elements are simply not available for access by SQL. The SQL user views these elements as not being defined in the network record type. However, the subordinate elements of a group definition are available, as are the base elements to which a REDEFINES is directed.
Though OCCURS ... DEPENDING ON declarations are not available for SQL access, fixed OCCURS definitions are made available. The SQL user's perception of a fixed OCCURS element is that there is one column for each occurrence of the element. The name which is used to access each such occurrence is the original element name followed by an underscore and an occurrence number to make the column name distinct. If the element is declared with nested OCCURS clauses, the corresponding column names contain one underscore and one occurrence number for each "dimension" of the OCCURS declaration. For example, the element definition BUD-AMT OCCURS 12 TIMES generates the following column names: BUD_AMT_01, BUD_AMT_02, BUD_AMT_03, ..., BUD_AMT_12.
In the preceding example, the occurrence number appended to the column name is made large enough to hold the largest subscript from the corresponding element definition. If the base element name in combination with the appended occurrence information makes the generated column names larger than 32 characters, you receive an error when the SQL statement is compiled. In this situation, you
must
define an SQL synonym for the network record type. The synonym element names must be short enough so the appended occurrence information will not make the resulting column names larger than 32 characters.
Although the SQL implementation in CA IDMS allows 32 character column names, other SQL implementations restrict column names to 18 characters. In particular, some ODBC client software may require you to use SQL synonyms for network record types to limit the size of the transformed column name to 18 characters.
Definition Anomalies of Network Record Types
Certain definition anomalies of network record types can result in errors during attempts to access them with SQL. These anomalies pertain to the definition of CALC keys, system-owned index set keys, and user-owned sorted set keys. They result in a DB002024 error in Release 12.0 or a DB002038 error in later releases.
The DB002038 message includes both the set name and record type in question. The DB002024 message only includes the set name. The DB002024 message presents a problem if a CALC definition is the cause of the error. CALC is the set name, and if there are several CALC records involved in the SQL statement, or if you are compiling an access module with references to numerous CALC records, you may have to examine the definitions of all CALC records to locate the problem.
Another characteristic of the CA IDMS SQL engine can further complicate the process of finding such errors. The SQL compiler loads the definitions of all SQL tables and network record types explicitly referenced by the SQL statements being compiled. However, it also loads the definitions of the network record types which (through network set definitions) either own or are owned by the records which are explicitly referenced in the SQL statements, so that set-based access strategies can be considered when it optimizes each statement. This may result in a 2024 or 2038 error being generated for a record type which isn't even referenced by the SQL being compiled.
These errors have only two known causes, both of them easily fixed:
  1. The control key definition of the CALC, INDEX, or sorted set includes a FILLER element. To overcome this problem, simply modify the network record definition to assign a name other than FILLER to the element in question.
  2. The control key definition incorporates subordinate elements of a group level REDEFINES, and these elements are smaller in size than the base element being redefined. For example:
    02  ELEM1 PIC X(8). 02  ELEM1REDEF REDEFINES ELEME1.     03 ELEM1A PIC S9(8) COMP.     03 ELEM1B PIC S9(8) COMP.  .  .  .
    An error occurs if ELEM1A and ELEM1B are used in the control key definition; since they are smaller than the element which they redefine even though together they are as large as ELEM1. The solution to this error is to change the redefining group, which contains the smallest subordinate elements, into the base element definition. This base definition should be used in the control key specification. In the previous example, ELEM1REDEF should be the base element definition, and ELEM1 should be coded so that it redefines ELEM1REDEF.
Record Structure Considerations
Occurring Elements
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.
Following is an example that represents the element occurring 12 times:
02  MONTHLY-BUDGET OCCURS 12 TIMES ...
The following example shows how the column names are represented:
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. The following example shows the nesting for 48 columns:
02  ANNUAL-BUDGET OCCURS 4 TIMES.    03  MONTHLY-BUDGET OCCURS 12 TIMES ...
The following example shows how the nested column names are represented:
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.
Other 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, as follows:
Picture and usage
Data type
PIC X(
n
) usage DISPLAY
CHAR(
n
)
PIC A(
n
) usage DISPLAY
CHAR(
n
)
Numeric edited1.
CHAR
(l)
,
l
=byte length
External floating point2.
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 DISPLAY3
NUMERIC(
p
,
p
)
PIC S9(
p
)P.. usage DISPLAY3.
NUMERIC(
p
,0)
PIC 9(
p
-
s
)V9(
s
) usage DISPLAY
UNSIGNED NUMERIC(
p
,
s
)
PIC P..9(
p
) usage DISPLAY3.
UNSIGNED NUMERIC(
p
,
p
)
PIC 9(p)P.. usage DISPLAY3.
UNSIGNED NUMERIC(
p
,0)
PIC S9(
p
-
s
)V9(
s
) usage COMP-3
DECIMAL(
p
,
s
)
PIC SP..9(
p
) usage COMP-33.
DECIMAL(
p
,
p
)
PIC S9(
p
)P.. usage COMP-33.
DECIMAL(
p
,0)
PIC 9(
p
-
s
)V9(
s
) usage COMP-3
UNSIGNED DECIMAL(
p
,
s
)
PIC P..9(
p
) usage COMP-33.
UNSIGNED DECIMAL(
p
,
p
)
PIC 9(
p
)P.. usage COMP-33.
UNSIGNED DECIMAL(
p
,0)
PIC S9(
n
),
n
<5 usage COMP4.
SMALLINT
PIC S9(
n
), 4<
n
<10 usage COMP4.
INTEGER
PIC S9(
n
), 9<
n
usage COMP4.
LONGINT
PIC 9(
n
) usage COMP4.
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
1 Numeric edited includes any element whose usage is DISPLAY, whose picture contains any of the editing symbols: + - Z B 0 $ CR DB . , * and 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.45 is represented in SQL as 12345.