SQL/MX Comparison Guide for SQL/MP Users

DML Features
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
3-12
Sort Operations
Rewritten by NonStop SQL/MX
column1 LIKE SUBSTRING(?expr2 FROM 1
FOR (POSITION (?trm in ?expr2) - 1))
column1 LIKE SUBSTRNG(?expr2 FROM 1
FOR (POSITION(?trm IN ?expr2) - 1)) ESCAPE ?esc
Sort Operations
NonStop SQL/MP uses the FastSort sort/merge program for certain operations. These
operations are described in the SQL/MP Installation and Management Guide. You use
the EXPLAIN utility to determine if a sort occurs. The EXPLAIN reports any sort
operations required to execute the query. You also use certain SORT-related DEFINEs
to influence the effectiveness of some SQL operations.
NonStop SQL/MX does not use the FastSort program or the SORT-related DEFINEs.
In addition, a scratch file in NonStop SQL/MX can overflow to another disk. The
operation will not fail if a scratch file becomes full or if the disk becomes full; NonStop
SQL/MX creates an additional scratch file either on the same or a different disk if the
disk becomes full. The maximum number of scratch files that can be used is 2048.
NonStop SQL/MX provides default settings that influence the choice of scratch disks.
SQL/MX parallelism is performed through ESPs only. Parallel sort can be forced
through CONTROL QUERY SHAPE. When the SQL/MX optimizer runs parallel
queries with ESPs, each ESP tries to use a different disk.
For additional information about how scratch disks are selected and influenced, see
the Defaults section of the SQL/MX Query Guide and the SQL/MX Reference Manual.
SQL DML Statements
Most SQL/MP DML statements exist in NonStop SQL/MX. However, a few options and
clauses have been dropped because of SQL:1999 ANSI changes. Table 3-4 lists the
SQL/MP DML statements that differ in NonStop SQL/MX:
Table 3-4. SQL/MX and SQL/MP DML Statements
SQL/MP DML
Statements
Implemented
in NonStop
SQL/MX? Comments
CONTROL EXECUTOR No PARALLEL EXECUTION is ON by default in
NonStop SQL/MX. See Default Settings
on
page 5-7.
CONTROL QUERY Changed See CONTROL QUERY Directive
on page 3-23.
CONTROL TABLE Changed See CONTROL TABLE Directive
on page 3-21;
also see Forcing Query Execution Plans on
page 5-8.