SQL Schema Considerations

SQL Schemas and Network-defined Schemas
SQL Schemas and Network-defined Schemas
Typically, one SQL schema is defined for each network schema that 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 network schema.
However, a single network 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 network-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 network-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
If two or more network-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 network-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.
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 network-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 network-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/DB can use to detect definition changes for network-defined records. It is the DBA's responsibility to recompile the access modules when changes are made to the network-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 network-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.
Definition Changes and Views
The deletion of records and changes made to the structure of records in a network-defined schema may necessitate the dropping and recreating of views referencing the network-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.
For more information see ALTER ACCESS MODULE, and SYSTEM Tables and SYSCA Views.