SQL/MX 3.2 Programming Manual for C and COBOL (H06.25+, J06.14+)

Name Resolution, Similarity Checks, and Automatic
Recompilation
HP NonStop SQL/MX Release 3.2 Programming Manual for C and COBOL—663854-002
8-12
Similarity Check

Both tables must have the same number of columns. The similarity check fails if
the total number of columns is different, even if the referenced columns in the
statement are the same for both tables.

All corresponding columns in both tables must have the same:

Name

Data type, length, precision, and scale

NULL attribute (Both columns must be either NULL or NOT NULL.) (NOT
NULL clauses in SQL/MP and SQL/MX native tables do not disable similarity
checks. If a table contains a NOT NULL clause, the SHOWDDL command
displays it as part of the CHECK clause. However, it is not handled as a check
constraint clause when the similarity information is generated.)

DEFAULT clause (For user-specified values, columns must have the same
default value. For SQL/MP floating-point columns, the same default value at
run time might not always match the compile-time value, causing the similarity
check to fail.)

Both tables must have the same number of key columns. Corresponding key
columns must have the same:

Position, offset, and column number

Column attributes

If a DML statement uses an index, the run-time table must have an index that has
the same attributes as the index that was used by the compile-time table. For an
index on the run-time table to be considered similar to the compile-time index:

Both must be either unique or nonunique.

Both must have the same keytag value (SQL/MP indexes). (See the CREATE
INDEX statement in the SQL/MP Reference Manual for details about the
keytag specification of an index. If the index is created without specifying the
keytag, for the indexes of old and new tables to match, they must have been
created in the same sequence.)

Both must have the same number of key columns.

Both must have the same key column attributes.

Both tables must have the same partitioning scheme (hash or range partitioned),
the same number of partitions, and the same partitioning keys if the plan does not
use OLT optimization.

Both tables must not have any check constraints or referential integrity constraints.

The query must not use compound statements.
Note. If the plan uses OLT optimization, the number of partitions does not affect the
similarity check. For more information on OLT optimization, see the SQL/MX Query Guide.