Accessing Non-SQL-Defined Databases › SQL Schema Considerations

SQL Schema Considerations

SQL Schemas and Non-SQL-defined Schemas

Typically, one SQL schema is defined for each non-SQL schema which describes data to be accessed through SQL. The SQL schema definition specifies the name of the segment or database containing the data described by the non-SQL schema.

However, a single non-SQL schema may describe multiple physical databases. In this case, one SQL schema can be used to access any of the physical implementations, or a separate SQL schema can be defined for each. To make the SQL schema independent of the physical implementation, omit the DBNAME specification from the schema definition.

If No DBNAME is Specified

If no DBNAME is specified in the SQL schema definition, only one physical instance of the non-SQL-defined database can be accessed within a single SQL transaction. The data accessed at runtime is determined by the database name to which your SQL session is connected. The database name must include the segments containing the data to be accessed.

DBNAME Specification and Access Modules

The way you choose to associate the non-SQL-defined schema with an SQL-defined schema has an impact on access modules:

• If the SQL schema does not contain a DBNAME specification, a single access module can be used against any of the physical databases because the application can specify the appropriate database name on a CONNECT statement (or the user can specify it in a DCUF or SYSIDMS DICTNAME parameter)
• If the SQL schema contains a DBNAME specification, each physical implementation must have its own set of access modules and the application must specify which one to use by issuing a SET ACCESS MODULE statement

Restriction

If two or more non-SQL-defined schemas describe the same physical data, it should be accessed under only one SQL schema within a transaction. For example, if the same physical employee information is described in two non-SQL-defined schemas referenced by the two SQL schemas HR and MFG, the employee records should either be accessed as HR tables or MFG tables, but not both.

Preventing Unpredictable Results

Unpredictable results occur (including possible database corruption) if the above restriction is violated.

To prevent unpredictable results, grant access to only one table for each non-SQL-defined record. In the above example, grant access to either the HR.EMPLOYEE table or the MFG.EMPLOYEE table but not both.

Definition Changes and Access Modules

Changes made to the non-SQL-defined schema do not cause access modules to be automatically recompiled (as is the case for SQL-defined entities). This is because there are no synchronization stamps that CA IDMS can use to detect definition changes for non-SQL-defined records. It is the DBA's responsibility to recompile the access modules when changes are made to the non-SQL-defined schema.

If an automatic recompile of the access module is desired, the RCM must be recreated through a recompile of the program to change the RCM synchronization stamps. Then when the access module is run, an access module recompile is triggered.

Other changes that may necessitate access module recompilation are:

• Changing the dictionary, name, or version number of the non-SQL-defined schema associated with an SQL-defined schema
• Changing the DBNAME parameter associated with the SQL-defined schema

To recompile affected access modules, use the ALTER ACCESS MODULE statement with the REPLACE ALL option. To determine which access modules are affected, query the SYSTEM.AM and SYSTEM.AMDEP tables in the dictionary.

Note: For more information, see ALTER ACCESS MODULE and SYSTEM Tables and SYSCA Views.

Definition Changes and Views

The deletion of records and changes made to the structure of records in a non-SQL-defined schema may necessitate the dropping and recreating of views referencing the non-SQL-defined tables representing those records. The following changes invalidate referencing views:

• Removal of the record from the schema
• Addition of record elements (record elements added to the end of the record structure invalidate only those views based on SELECT *)
• Re-ordering of record elements within the record
• Changing of an element's picture or usage

To determine which views are impacted by such changes, query the SYSCA.VIEWDEP table.

Note: For more information, see SYSTEM Tables and SYSCA Views.