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-14
Recompiling a Module
•
By default, automatic recompilation is enabled for all database applications. To
disable automatic recompilation, set the AUTOMATIC_RECOMPILATION default
attribute to OFF.
•
By default, the SQL/MX executor does not return a warning message to the
database application when a DML statement is automatically recompiled. However,
NonStop SQL/MX always logs a warning event, SQL/MX message 505, to the
Event Management Service (EMS) log when a statement is automatically
recompiled. To return automatic recompilation warning messages directly to the
program, set the RECOMPILATION_WARNINGS default attribute to ON.
For more information on these default attributes, see the SQL/MX Reference Manual.
For more information on controlling automatic recompilation within database
applications, see the SQL/MX Programming Manual for C and COBOL. For more
information about EMS, see the EMS Manual and the Operator Messages Manual.
If a plan requires automatic recompilation, you should explicitly recompile the module.
See Recompiling a Module on page 11-14.
Recompiling a Module
Periodically, depending on changes to the database or the occurrence of automatic
recompilation, you might need to recompile the modules of an application. Explicitly
recompiling modules generates optimal query execution plans and prevents the
performance cost of automatic recompilation. To compile a module, see the SQL/MX
Programming Manual for C and COBOL.
To automate the process of recompiling modules, consider putting recompilation
commands in a script file.
Migrating SQL/MX Nodes to Obtain Improved Query Plan
Performance
New releases of NonStop SQL/MX sometimes provide enhancements to query
execution plan performance over previous releases. To achieve these performance
enhancements on all the SQL/MX nodes in a distributed database, you must migrate
all the nodes running an earlier release of NonStop SQL/MX to the new release or
install the SPR that provides the new functionality on those nodes.
For more information about the performance features of query execution plans, see the
SQL/MX Query Guide. For detailed information about migrating SQL/MX databases,
see the SQL/MX Database and Application Migration Guide.
Displaying and Analyzing Query Execution Plans
Use the EXPLAIN function to display the query execution plans of a module. For more
information, see the SQL/MX Programming Manual for C and COBOL and the SQL/MX
Reference Manual.