This section provides a review of the transformations used by the SQL engine while reading the definitions of non-SQL record types. When SQL is used to access non-SQL record types, the entity names coded in the SQL syntax must follow the conventions described next.
Note: For more information about defining and using table procedures to process non-SQL-defined data in a relational way even if the data does not conform to the rules for such access, see Defining and Using Table Procedures.
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 non-SQL 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 non-SQL schema must be defined in the SQL catalog. This SQL schema name is used to qualify all subsequent references to non-SQL record types in SQL DML statements. For example,
CREATE SCHEMA SQLNET FOR NONSQL SCHEMA PRODDICT.CUSTSCHM; SELECT * FROM SQLNET."ORDER-NET";
Because hyphen is the subtraction operator in SQL, non-SQL record names containing embedded hyphens must be delimited by double quotes (for example, "CUST-REC-123"). Any non-SQL set names containing embedded hyphens must be delimited by double quotes before they can be used in an SQL statement (for example, "CUST-ORDER").
Unlike hyphens embedded in record and set names, hyphens embedded in non-SQL 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 non-SQL record type, you must code CUST_NUMBER in an SQL statement.
When a FOR LANGUAGE SQL synonym is defined for a non-SQL record type, the element synonyms are used for all SQL access. SQL synonyms are only used for element names. Defining SQL synonyms for non-SQL record types is sometimes the only way to overcome column name limitations within SQL.
Some non-SQL element names don't make satisfactory SQL column names, even after the hyphen-to-underscore transformation. For example, if a non-SQL 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 non-SQL 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 non-SQL 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 non-SQL record types to limit the size of the transformed column name to 18 characters.
Certain definition anomalies of non-SQL 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 non-SQL record types explicitly referenced by the SQL statements being compiled. However, it also loads the definitions of the non-SQL record types which (through non-SQL 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:
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.
|
Copyright © 2014 CA.
All rights reserved.
|
|