SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

NonStop SQL/MX and TMF absolutely guarantee the integrity of the database following a CPU
failure, application failure, or other component failure.
Moving Programs From Development to Production
The structure of the database may differ on development and production systems. The execution
plans generated on the development system may be inefficient or invalid on the production system.
It may be desirable or required to retain the existing plans if care is taken to ensure that the
development and production databases are always structurally similar.
SQL/MX provides three approaches to moving an application and its compiled modules to another
system:
• Moving a program and recompiling the modules – This may cause changes to plans which
may need to be reassessed for acceptability in the new environment. This is especially true
where statements may have been fine tuned on the development system.
• Moving a program and reusing its compiled modules – Depending on circumstances, plans
may be retained, re-compiled or may cause an error. Additional costs are incurred during
every program start due to Similarity Checks and possible automatic recompilations.
• Moving a program and reprocessing its modules – Valid plans are retained during reprocessing.
Invalid plans that result in Similarity Check and automatic recompilation can be identified.
Reprocessing helps to avoid Similarity Check costs during program start. This is applicable
only to programs that use SQL/MX native tables and views. This approach cannot be used
with programs that access SQL/MP tables through SQL/MX.
HP recommends that you recompile the SQL plans after moving to the production system.
Recompiling SQL plans after moving to the production system might result in plans that are different
from those on the development system. Careful management of the two environments is required
to ensure that the compiled modules from the development system execute properly on the production
systems. When you execute a statement, a Similarity Check is invoked on it. If the statement fails
the Similarity Check, it is automatically recompiled unless the option to recompile is disabled. If
the option to recompile is disabled, an error is generated. Recompilation might change the plan.
Similarity Check and any resulting automatic recompilation can be expensive operations. They
are repeated when each process is created. See “Reasons Not to Move a Program and Reuse Its
Compiled Modules” (page 214).
Starting with SQL/MX Release 3.2, the mxrpm tool is included. The mxrpm utility reprocesses the
compiled module files and invokes Similarity Checks on the statements in the module file, thereby
allowing plans that pass the Similarity Check to be retained, without the costs associated with
repeated Similarity Checks upon subsequent process creations. For more information, see the
“Moving a Program and Reprocessing Modules Using the mxrpm Tool” (page 217).
HP 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
and Recompiling Modules” (page 215).
Reasons Not to Move a Program and Reuse Its 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 runtime definition timestamp of the tables on the production
system.
214 Managing Database Applications