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

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 expensive in terms of performance. You
can overcome this problem by persisting the results of Similarity Check and Late name resolution
in the module files using the mxrpm tool. See “Moving a Program and Reprocessing Modules Using
the mxrpm Tool” (page 217)
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. For more
information, see 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.
• If you fail to properly set up all DEFINEs for SQL/MP tables and the development and
production systems are on the same Expand network, the wrong tables might be accessed.
Moving a Program and Recompiling Modules
When you move a program from a development system to a production system, you should always
compile module definitions (either embedded module definitions or module definition files) on the
production system.
Considerations
Before choosing this approach for moving the program files, consider:
• “Query Execution Plan” (page 215)
• “Default Settings” (page 215)
• “Name Resolution” (page 216)
Query Execution Plan
The SQL/MX compiler might produce plans on the production system that differ significantly from
those on the development system. Possible causes include:
• The SQL statements in the program refer to database objects on the development system that
differ in table structure (for example, indexes and partitions) and distribution of data from
those on the production system.
• Differences in the data contained in the same tables on the development and production
systems cause differences in the statistics that the SQL/MX compiler uses to optimize the plan.
NOTE: Unless care is taken to ensure differences do not exist in the table contents as
represented by its statistics, plans generated in the development environment may not be the
most efficient plan in the production environment.
Default Settings
If the SQL/MX compiler uses different defaults on the production system than on the development
system, program performance could be affected.
Moving Programs From Development to Production 215