SQL/MX 2.x Installation and Management Guide (H06.04+)

HP NonStop SQL/MX Installation and Management Guide—540436-001
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, POPULATE INDEX, 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-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-35.
•
Use PURGEDATA to delete data from a table or index. See Using PURGEDATA to
Delete Data From Tables on page 10-37.
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 will not drop any more objects until
more space is made available. To prevent this from happening, periodically check
(once a week, for example) the DDL directory and purge all DDL files for tables you