Identifiers › Identifying Entities in Schemas

Identifying Entities in Schemas

Access modules, referential constraints, tables, views, table procedures, procedures, and user-defined functions are all associated with schemas. However, when you name one of these entities in an SQL statement, specification of the schema name is optional. The schema CA IDMS uses when processing the statement depends on the following:

• Type of entity
• Presence or absence of the schema name in the entity reference
• Statement where the reference to the entity occurs
• Method you use to submit the statement to CA IDMS (for example, through the online command facility or embedded in an application program)

Resolving References to Entities in Schemas

Interactive and dynamic SQL

When compiling a statement using interactive or dynamic SQL, or when using the EXPLAIN statement to determine the access strategy to be used for an SQL statement, CA IDMS resolves references to entities in schemas as follows:

• For each entity qualified by a schema name, CA IDMS uses the named schema.
• For each unqualified table, view or table procedure in a SELECT, INSERT, UPDATE, and DELETE statement, or one of them in an EXPLAIN statement:
  • CA IDMS looks for the definition of a temporary table created during the current database transaction whose name matches the specified identifier. If a match is found, CA IDMS assumes that the reference is to the temporary table.
  • If a matching temporary table name is not found, CA IDMS uses the current schema in effect for the SQL session.
• For all other unqualified references, CA IDMS uses the current schema in effect for the SQL session

Creating or altering an access module

When creating or altering an access module, the user can change the names of schemas in table, view, table procedure, procedure, and user-defined function references in SQL data manipulation statements. The user does this by specifying one or more schema mapping rules, each of which supplies a replacement for a schema name. This facility allows a single program (and its associated RCM) to reference one set of tables when it is included in one access module, and another set of tables when it is included in another access module with different schema mapping rules.

When compiling an SQL statement during the creation or alteration of an access module, CA IDMS resolves schema names as follows:

1. For qualified references to schema entities in non-data manipulation statements, it uses the schema name specified.
2. For each qualified table, view, table procedure, procedure, or user-defined function reference in a data manipulation statement, CA IDMS uses the replacement schema name as specified in the schema mapping rules. If no replacement has been specified, it uses the schema name specified in the table, view, procedure, or user-defined function reference.
3. For unqualified references to schema entities in data description statements, it uses the schema name of the access module being created or altered.
4. For each unqualified table, view, table procedure, procedure, or user-defined function reference in a data manipulation, CA IDMS uses the replacement schema name for the NULL entry in the schema mapping rules. If no such entry is found, it uses the schema name of the access module being created or altered.
5. For all unqualified references to schema entities, CA IDMS uses the current schema associated with your SQL session.

After resolving the schema names according to the above rules, if CA IDMS cannot find the definition of a table, view, table procedure, procedure, or user-defined function referenced in a data manipulation statement, the statement remains uncompiled and a warning is issued. CA IDMS attempts to recompile the statement at runtime because the table-like object may have been created since compile time.

Automatic access module recreation

CA IDMS automatically recreates an access module at runtime if:

• CA IDMS encounters a statement that was not previously compiled
• The AUTO RECREATE option for the access module is ON and:
  • CA IDMS encounters a statement that refers to a table, view, table procedure, procedure, or user-defined function whose definition has changed since the access module was created or last altered
  • The application program has been recompiled since the access module was created or last altered

When it automatically recreates an access module, CA IDMS resolves references to schemas using the same rules as were used when the access module was created or last altered except in dealing with unqualified table, view, table procedure, procedure or user-defined function references in data manipulation and EXPLAIN statements:

• If the table, view, or table procedure reference is unqualified, CA IDMS first looks for the definition of a temporary table created within the current transaction whose name matches the specified table identifier. If one is found, the reference is assumed to be a reference to that temporary table.
• If no temporary table with the same name is found, then CA IDMS uses the schema mapping rules associated with the access module to resolve table, view, and table procedure references as described above in Creating or altering an access module.

Note: For more information about automatic access module re-creation, see CREATE ACCESS MODULE or the CA IDMS SQL Programming Guide.