Example: Deleting a column

Nonstop Database, HiRDB Version 9 System Operation Guide

13.7.1 Example: Deleting a column

This example deletes a column (C4) from a table (TABLE01).

[Figure]

Organization of this subsection: (1) Use the pdhold command to shut down RDAREAs in which TABLE01 is stored; (2) Use the pdrorg command to unload data from TABLE01; (3) Use the pdrels command to release RDAREAs from shutdown status; (4) Use the PURGE TABLE statement to delete data from TABLE01; (5) Use the ALTER TABLE statement to delete column C4; (6) Create the column structure information file (/pdload/column01); (7) Use the pdhold command to shut down RDAREAs in which TABLE01 is stored; (8) Load data into TABLE01; (9) Back up the RDAREAs in which data was loaded; (10) Use the pdrels command to release RDAREAs from shutdown status

(1) Use the pdhold command to shut down RDAREAs in which TABLE01 is stored

 
pdhold -r RDAREA1,RDAREA2,...

(2) Use the pdrorg command to unload data from TABLE01

 
pdrorg -k unld -W dat -t TABLE01 -g /pdrorg/unld01

Explanation

-k: Specifies unld for unloading.

-W dat:: Specifies that the unload data file is to be used as the input file (DAT format) for the pdload command.; If the table contains character data (0x00 and 0x0a) that cannot be converted to DAT format, the table data cannot be unloaded in DAT format. In such a case, the data must be unloaded in binary format (-W bin specified); note that the operations in the steps beginning with step (6) will be different. See 13.6.7 Example 6: Adding a column to a table with the FIX attribute (unloading in binary format).

-t: Specifies the name of the table that is to be unloaded.

-g: Specifies that TABLE01 is row-partitioned between servers in a HiRDB parallel server configuration. Specifying the -g option consolidates the unload data files (into a single file).

/pdrorg/unld01: Specifies the name of the control statements file for the pdrorg command.

(3) Use the pdrels command to release RDAREAs from shutdown status

 
pdrels -r RDAREA1,RDAREA2,...

(4) Use the PURGE TABLE statement to delete data from TABLE01

 
PURGE TABLE TABLE01;

(5) Use the ALTER TABLE statement to delete column C4

 
ALTER TABLE TABLE01 DROP C4;

(6) Create the column structure information file (/pdload/column01)

 
C1
C2
C3
*skipdata*

Because the format of the column structure of the input data file created in step (2) is different from the column structure of TABLE01, a column structure information file is needed for data loading.

(7) Use the pdhold command to shut down RDAREAs in which TABLE01 is stored

 
pdhold -r RDAREA1,RDAREA2,...

(8) Load data into TABLE01

 
pdload -c /pdload/column01 TABLE01 /pdload/load01

Explanation: -c /pdload/column01: Specifies the name of the column structure information file created in step (6).; TABLE01: Specifies the name of the table into which the data is to be loaded.; /pdload/load01: Specifies the name of the control statements file for the pdload command.

(9) Back up the RDAREAs in which data was loaded

Because you loaded data in the pre-update acquisition mode (default), back up the RDAREAs in which data was loaded. For details about backing up RDAREAs, see 6.4.8 Example 8 (backing up specified RDAREAs).

(10) Use the pdrels command to release RDAREAs from shutdown status

 
pdrels -r RDAREA1,RDAREA2,...

We recommend that after the command has executed you check whether the execution results are correct. For details on how to check command execution results, see the manual HiRDB Version 9 Command Reference.