SQL/MP Version Management Guide

Version Compatibility for SQL Programs
Compaq NonStop™ SQL/MP Version Management Guide—429833-001
7-12
Migrating to a Newer Version
°
References to SQLDA fields that use new data types
°
Checks for NULLP in SQLDA
For more detailed information on the changes between version 1 and version 2 of SQL,
see information about release compatibilities in the NonStop SQL/MP Installation and
Management Manual.
For a summary of the changes between versions 1 and 300, versions 2 and 300, versions
300 and 310, and versions 310 and 315, see Appendix A, Summary of Feature, Catalog,
and Data Structure Changes.
Effect of Newer Version on Query Execution Plans
Query execution plans developed for version 300 or newer SQL compilers are not
always used during compilation of older version programs. These are situations in which
a newer execution plan is not selected:
•
During recompilation of an older version SQL program, the SQL compiler uses the
latest query execution plans only if the plans produce optimal code.
•
In a mixed version network, where not all nodes have the same version of SQL/MP
software, the SQL compiler omits certain query execution plans, such as plans for
parallel execution, to ensure that the compiled program can execute successfully.
Features introduced in versions 300 and newer that affect query execution plans are:
•
Collation expressions
•
MCSS character types
•
CAST AS function
•
Hash joins
Query execution plans that depend on these features are not used by version 300 or
newer SQL compilers during recompilation of older version SQL programs. Also, if
partitions exists on version 2 or older systems, query execution plans cannot use hash
grouping in parallel or fast expressions for disk process access.
Query execution plans for SQL programs that query objects on nodes running older
versions of SQL/MP software or that include plans for parallel execution in a mixed
version network cannot always be implemented. In these cases, the SQL compiler
selects a plan that enables correct execution rather than more efficient execution.
An SQL compiler cannot always choose the best query execution plan if the query
accesses a table on a node with older version software or accesses a table with one or
more partitions on a node with older version software.
The following examples illustrate situations in which a version 300 or newer SQL
compiler cannot use a preferred query execution plan. In each situation, the query
execution plan uses features not supported by older versions of the software.
•
A query that compares a column to a constant by using a nonbinary collating
sequence from a table on a node running version 2 SQL software. Because the disk
process on the node running version 2 software does not recognize nonbinary
collating sequences, the version 300 or newer SQL compiler generates a plan by
which the comparison is performed by the version 300 or newer SQL executor.