SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

HP NonStop SQL/MX Installation and Management Guide—544536-007
10-1
10
Reorganizing SQL/MX Tables and
Maintaining Data
Sometimes you might need to restructure the data in an SQL/MX table by reloading or
reorganizing the table. NonStop SQL/MX offers several tools to aid the restructuring
task:
•
Purge DDL files for dropped tables from the directory at
/usr/tandem/sqlmx/ddl to provide more space for DROP operations. See
Purging Dropped Tables From the DDL Directory on page 10-1.
•
Choose a method for reorganizing the data. See Using FUP RELOAD to
Reorganize Tables on page 10-2.
•
Use FUP RELOAD to reload data to increase the data-block free space, reducing
block splits during insertions and updates. See Using FUP RELOAD to Reorganize
Tables on page 10-2.
•
Understand how NonStop SQL/MX supports DDL locks for some utility commands.
See DDL Lock Considerations for MODIFY, import, POPULATE INDEX, DUP,
FASTCOPY, and PURGEDATA on page 10-5.
•
Use MODIFY to add, move, drop, or split table and index partitions. See Using
MODIFY to Manage Table and Index Partitions on page 10-10.
•
Use import to move data from an ASCII or UCS2 file into an existing table or to
append data to tables or partitions. See Using import to Load SQL/MX Tables on
page 10-22.
•
Use DUP to copy data from one table to another table. See Using DUP to Copy
Tables Into Tables on page 10-40.
•
Use FASTCOPY to copy source table and its indexes into an existing equivalent
target table and indexes. See Using FASTCOPY to Copy Tables into Tables
on
page 10-42.
•
Use PURGEDATA to delete data from a table or index. See Using PURGEDATA to
Delete Data From Tables on page 10-45.
For information about reorganizing tables and maintaining data in an SQL/MP
database, see the SQL/MP Installation and Management Guide.
Purging Dropped Tables From the DDL
Directory
When a table is dropped, NonStop SQL/MX automatically saves the DDL needed to
re-create the table in the OSS directory /usr/tandem/sqlmx/ddl.