Accessing Network-Defined Databases
CA IDMS/DB is unique in offering both a network data model and an SQL data model for data definition. You can use SQL not only with SQL-defined databases, but also with existing network databases.
Access to network data using standard SQL requires no CA IDMS/DB expertise and allows applications to take advantage of open source and third party tools for better flexibility and improved productivity. CA IDMS provides referential sets by Virtual Foreign Keys and Primary Keys.
Defining an SQL Schema for Access to Network Data
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.
To use the virtual foreign key feature, the WITH VIRTUAL KEYS clause must be specified on the CREATE or ALTER SCHEMA statement. This allows all virtual foreign keys to be referenced in SQL DML and for ROWID and virtual foreign keys to be projected in a SELECT * and INSERT column list. In addition, the use of the set that underlies the ROWID/virtual foreign key relationship is considered when determining the optimal access to the data.
Although virtual key schemas may be created for network schemas that contain sets with primary/foreign keys defined on the SET statement, records that participate in a set defined with primary/foreign keys cannot be referenced in SQL statements that use a virtual key schema and, if done, result in an error.
Creating a referencing schema without virtual foreign keys
CREATE SCHEMA EMPSCHM FOR NONSQL SCHEMA EMPDICT.EMPSCHM VERSION 100;
Change a referencing schema to add the use of virtual foreign keys
ALTER SCHEMA EMPSCHM FOR NONSQL SCHEMA EMPDICT.EMPSCHM VERSION 100 WITH VIRTUAL KEYS;
After defining a referencing schema (an SQL schema that references a network schema), the records defined in the network schema can 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. The correspondence is between the tables and records, and columns and elements.
- Table NamesThe table name used when referring to a network-defined record is always the name specified in the ADD RECORD statement of the network schema definition. If hyphens appear in the record name, the table name must be enclosed in double quotation marks.
- Column NamesThe column name used when referring to a record element depends on whether a synonym for LANGUAGE SQL is defined for the record:
In either case, column names are transformed by replacing hyphens with underscores.
- If a synonym is 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.
For more information on managing schemas see the following topics: