SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Managing Database Applications
HP NonStop SQL/MX Installation and Management Guide—544536-007
11-4
Reasons Not to Move a Program With Compiled
Modules
•
Moving a program without compiled modules
However, there are few circumstances under which you can compile a program’s
modules on a development system and then move the program and modules to a
production system without recompiling the modules there. See Reasons Not to Move a
Program With Compiled Modules on page 11-4.
HP strongly recommends that you always move a program from a development system
to a production system and then compile module definitions on the production system.
See Moving a Program Without Compiled Modules on page 11-5.
Reasons Not to Move a Program With Compiled Modules
You are advised not to move a program and its compiled modules to a production
system because:
•
This approach is likely to cause automatic recompilation of query execution plans.
•
Automatic recompilation imposes a cost on program performance.
When you move a program and its compiled modules to the production system, the
query execution plans undergo a similarity check and possible automatic recompilation
when you execute the program on the production system. The SQL/MX executor
initiates a similarity check because the compile-time definition timestamp of the query
execution plan, which is based on tables on the development system, differs from the
run-time definition timestamp of the tables on the production system.
The similarity check determines if the query execution plan of a statement is still
operable. If the similarity check fails (or is disabled), the SQL/MX executor, by default,
invokes the SQL/MX compiler to automatically recompile the query execution plan. The
automatically compiled plan is not saved for subsequent executions of the same
program or for multiple concurrent executions of the same program. Therefore,
automatic recompilation is costly in terms of performance. For more information, see
automatic recompilation in the SQL/MX Programming Manual for C and COBOL.
To pass the similarity check and avoid automatic recompilation, the production
database must be structurally identical to the development database. The tables on the
production system must have the same index definition, number of partitions, partition
boundaries, table attributes, and column names, numbers, and data types as the
tables on the development system. In addition, the presence of identical unique
indexes in SELECT statements, views, or check or referential integrity constraints in
compared tables causes similarity check failure and automatic recompilation. For more
information, see the similarity check criteria in the SQL/MX Programming Manual for C
and COBOL.
Even if similarity check passes, there are other reasons not to use query execution
plans from a development system on a production system:
•
The cost of similarity check on a table increases with the size and complexity of the
table. It is not always advisable to incur the cost of similarity check at the startup of
every process that uses the plan.