SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Name Resolution, Similarity Checks, and Automatic
Recompilation
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
8-10
Similarity Check
Controlling the Similarity Check
By default, the similarity check is enabled for all DML statements. To disable the
similarity check and force recompilation of an SQL/MX statement when a class MAP
DEFINE value or table timestamp changes, use the CONTROL QUERY DEFAULT or
CONTROL TABLE statement with the SIMILARITY_CHECK option. For example, this
statement disables the similarity check for all tables in subsequent DML statements:
CONTROL QUERY DEFAULT SIMILARITY_CHECK 'OFF';
This statement disables the similarity check for a specific table:
CONTROL TABLE samdbcat.persnl.job SIMILARITY_CHECK 'OFF';
If you need to reenable the similarity check in the program, use the CONTROL QUERY
DEFAULT or CONTROL TABLE statement with the SIMILARITY_CHECK option set to
ON. For more information on coding CONTROL statements, see Using CONTROL
Statements on page 2-12 and the SQL/MX Reference Manual.
Similarity Check Criteria
During a similarity check, the SQL/MX executor compares the compile-time version of
a table with its run-time version. For the similarity check to pass:
•
Both tables must have the same table type (key-sequenced or entry-sequenced).
•
Both tables must be either audited or nonaudited.
•
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
Note. Although the SIMILARITY_CHECK option is enabled by default in NonStop SQL/MX, a
DML statement that refers to an SQL/MP table does not undergo a similarity check in NonStop
SQL/MX if the SQL/MP table was created with the SIMILARITY CHECK option disabled. To
determine if the similarity check is enabled for an SQL/MP table, check the
SIMILARITYCHECK column for the table in the TABLES table of the SQL/MP catalog. For
more information, see the SQL/MP Reference Manual.