SQL/MX 2.x Database and Application Migration Guide (G06.23+, H06.04+, J06.03+)

Converting SQL/MP Applications to SQL/MX
Applications
HP NonStop SQL/MX Database and Application Migration Guide—540435-005
10-56
Similarity Checks and Automatic Recompilation of
Embedded SQL/MX Applications
Automatic Recompilation Warnings
NonStop SQL/MP returns a warning to the application if a similarity check fails and
automatic recompilation occurs. Because NonStop SQL/MX is ANSI-compliant, an
automatic recompilation warning is not returned to the application by default. To obtain
the same behavior as in NonStop SQL/MP, set the RECOMPILATION_WARNINGS
default attribute to ON.
Causes of Similarity Check Failure
Unlike NonStop SQL/MP, which determines the indexes to update at run time, index
maintenance in NonStop SQL/MX is part of the query execution plan that is created at
compile time. Creating or dropping an index invalidates all SQL/MX plans that insert,
update, or delete data in an SQL/MX or SQL/MP table.
Check constraints, referential integrity (RI) constraints, and triggers are also part of the
compiled plan. A similarity check always fails when invoked if a constraint or trigger
exists on an SQL/MX or SQL/MP table.
Table 10-15
summarizes the outcome of similarity checks in NonStop SQL/MP and
NonStop SQL/MX when similarity check is on at compilation.
Table 10-15. Comparison of Similarity Checks Between NonStop SQL/MP and
NonStop SQL/MX (page 1 of 3)
Difference Between Compile Time and Run Time
Similarity check
passes in
NonStop
SQL/MP?
Similarity check
passes in
NonStop
SQL/MX?
Name of table Yes Yes
Data in table Yes Yes
Number of partitions Yes for serial
plans
No for parallel
plans
No, unless plan
chooses OLT
optimization
Partition key ranges Yes for serial
plans
No for parallel
plans
Yes
Nonpartitioned table at compile time, partitioned at
run time
Yes No, unless plan
uses OLT
optimization
ESP parallelism used in the plan No No