Nonstop Database, HiRDB Version 9 System Operation Guide
13.6.1 Preparations for adding a column
A column cannot be added to a table with the FIX attribute in which data is stored. If it is necessary to add a column to a table with the FIX attribute in which data is stored, you must use the following procedure.
- Procedure
- Use the pdrorg command to unload the table data.
- Use the PURGE TABLE statement to delete the table data.
- Use the ALTER TABLE statement to add a column.
- Use the pdhold command to shut down RDAREAs storing the table.
- Use the pdload command to load the table data.
- Use the pdlogswap -d sys -w command to swap the system log files.
- Use the pdcopy command to make a backup. For details about making backups, see 6. Backup Procedures.
- Use the pdrels command to release the RDAREAs from shutdown status.
- Notes
- Either DAT or binary format can be used for unloading the table's data in step 1. It is preferable to use DAT format because it is a simpler operation.
- If the table contains character data that cannot be converted to DAT format (0x00 and 0x0a), the table data cannot be unloaded in DAT format; binary format must be used in such a case.
- For an example of unloading a table in DAT format, see 13.6.6 Example 5: Adding a column to a table with the FIX attribute (unloading in DAT format).
- For an example of unloading a table in binary format, see 13.6.7 Example 6: Adding a column to a table with the FIX attribute (unloading in binary format).
- Columns cannot be added to falsification prevented tables.
- Adding a column to a table invalidates any stored routines that use that table. If this happens, use the ALTER PROCEDURE or ALTER ROUTINE statement to re-create each stored routine.
- Adding a NOT NULL column to a table specified in a trigger SQL statement invalidates the trigger. If this happens, use the ALTER TRIGGER or ALTER ROUTINE statement to re-create the trigger.
- After the column has been added to the table, execute the optimizing information collection utility (pdgetcst command) if necessary. For details about whether execution of the optimizing information collection utility is required, see the manual HiRDB Version 9 Command Reference.
All Rights Reserved. Copyright (C) 2011, 2015, Hitachi, Ltd.