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

HP NonStop SQL/MX Installation and Management Guide—523723-004
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 Choosing a Reorganization
Method 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, DUP, 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-9.
•
Use import to move data from an ASCII, UCS2, or binary file into an existing
table or to append data to tables or partitions. See Using import to Load and
Append Input Data Into 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-34.
•
Use PURGEDATA to delete data from a table or index. See Using PURGEDATA
to Delete Data From Tables on page 10-35.
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.
If this directory becomes too full, NonStop SQL/MX does not drop any more objects
until more space is made available. To prevent this, periodically check (once a week,
for example) the DDL directory and purge all DDL files for tables you know will not