SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

8 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” (page 178).
• Choose a method for reorganizing the data. See “Using FUP RELOAD to Reorganize Tables”
(page 178).
• 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” (page 178).
• 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” (page 181).
• Use MODIFY to add, move, drop, or split table and index partitions. See “Using MODIFY to
Manage Table and Index Partitions” (page 184).
• 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 MODIFY to Manage Table and Index Partitions”
(page 184).
• Use DUP to copy data from one table to another table. See “Using import to Load SQL/MX
Tables” (page 194).
• 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” (page 208).
• Use PURGEDATA to delete data from a table or index. See “Using PURGEDATA to Delete
Data From Tables” (page 210).
For more 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 know will not have to be recovered.
The DDL directory contains timestamp information for each DDL file that identifies the time and
date when a table was dropped.
If you do not want NonStop SQL/MX to save DDL when you drop table, you can turn this feature
off by setting the SAVE_DROPPED_TABLE_DDL control query to “OFF”. For more information, see
the SQL/MX Reference Manual.
Using FUP RELOAD to Reorganize Tables
Of all the SQL/MX object types, only tables and indexes contain significant amounts of data.
Views, trigger temporary tables, and stored procedures do not usually need to be reloaded.
Use FUP RELOAD to reload a table when:
• You have confirmed that a table is fragmented by using FUP INFO to read the index_level
value.
• You need to insert slack space into a table file.
178 Reorganizing SQL/MX Tables and Maintaining Data