SQL/MX 3.2 Programming Manual for C and COBOL (H06.25+, J06.14+)

Name Resolution, Similarity Checks, and Automatic
Recompilation
HP NonStop SQL/MX Release 3.2 Programming Manual for C and COBOL—663854-002
8-7
Late Name Resolution
Late Name Resolution for Tables Referred by the View
Unlike SQL/MX tables, the tables referred in a view cannot use PROTOTYPE host
variables instead of table names. Late name resolution is a SQL/MX extension that
allows table names referred to in a view definition to differ between compile-time and
run-time.
To resolve SQL/MX table names accessed by the view, a one-to-one mapping of the
tables in compile-time and run-time is performed by the executor. Therefore, the
position in which the tables appear in the view text must be the same at compile-time
and runtime. Similarity check for the view fails if the positions of the tables or
predicates are different, even though both the views are semantically equivalent.
Example
The following example shows how underlying SQL/MX tables in the view are mapped
between compile-time and run-time before performing the similarity check for the view:
Consider the following DML statement:
SELECT * from :viewname PROTOTYPE 'CAT.SCH.TEMP_VIEW';
The view definitions in compile-time and run-time are as follows:
Compile-time view:
CREATE VIEW TEMP_VIEW ENABLE SIMILARITY CHECK AS
SELECT T1.X1, T1.Y1, T2.X2
FROM CAT.SCH.TABLE_ONE T1, CAT.SCH.TABLE_TWO T2;
Run-time view:
CREATE VIEW PRDTEMP_VIEW ENABLE SIMILARITY CHECK AS
SELECT T1.X1, T1.Y1, T2.X2
FROM PRDCAT.PRDSCH_ONE.TABLE_ONE T1, PRDCAT.PRDSCH_ONE.TABLE_TWO
T2;
At compile-time, the query is compiled with the view name, CAT.SCH.TEMP_VIEW in
the PROTOTYPE clause. At run-time, the query is executed with the view name,
PRDCAT.PRDSCH_ONE.PRDTEMP_VIEW passed in the host variable: viewname.
Similarity check compares the views, TEMP_VIEW and PRDTEMP_VIEW and maps the
underlying tables in the views. Similarity check for the view passes if the conditions
described in Similarity Check Criteria for a View on page 8-13 are fulfilled.
CAT.SCH.TABLE_ONE is mapped to PRDCAT.PRDSCH_ONE.TABLE_ONE and
CAT.SCH.TABLE_TWO is mapped to PRDCAT.PRDSCH_ONE.TABLE_TWO. Similarity
check verifies if the mapped tables are equivalent in structure.