SQL/MX Comparison Guide for SQL/MP Users

Utility Differences
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
7-2
Partition Management: The MODIFY Utility
For more information, see “SQL/MX Utilities” in the SQL/MX Reference Manual.
Partition Management: The MODIFY Utility
In both NonStop SQL/MX and NonStop SQL/MP, you create partitioned tables and
indexes with CREATE TABLE and CREATE INDEX.
In NonStop SQL/MX you use the MODIFY utility to manage partitions. You can:
•
Reuse an existing partition of a range-partitioned table
•
Add, drop, or move partitions of range-partitioned tables and indexes
•
Add, drop, or move partitions of hash-partitioned tables and indexes
•
Move partitions of system-clustered objects
In NonStop SQL/MP, you use the PARTONLY MOVE clause of ALTER TABLE (for a
key-sequenced file) to break the table into partitions or to break a partition into
additional partitions, or the ADD PARTITION clause of ALTER TABLE to add a partition
to the end of an entry-sequenced or relative table. To create a partitioned index, use
CREATE INDEX or the PARTONLY MOVE clause of ALTER INDEX.
For a description of the MODIFY command, see “SQL/MX Utilities” in the SQL/MX
Reference Manual.
POPULATE INDEX Command
NonStop SQL/MX includes a CREATE INDEX command, but it includes a NO
POPULATE option you can use if you prefer to populate the index in a separate
operation from creating it. For instance, if you are creating an index on a large table
that is already populated, you should use the NO POPULATE option and then run the
POPULATE INDEX utility to load the index. Because CREATE INDEX executes in a
single TMF transaction, it could experience TMF limitations such as a transaction
timeout if there is a large amount of data to be moved.
The SQL/MX statements CREATE INDEX NO POPULATE followed by POPULATE
INDEX are equivalent to the SQL/MP CREATE INDEX statement.
For more information, see the SQL/MX Reference Manual.
SHOWDDL Command
SQL/MX’s SHOWDDL command displays a DDL statement that would create a table,
view, or stored procedure as it exists in metadata, including the object’s dependent
GOAWAY Similar to SQL/MP’s GOAWAY command
INFO Displays ANSI names and versions of Guardian files used by an object
VERIFY Similar to SQL/MP’s VERIFY command