Example 5: Adding a column to a table with the FIX attribute (unloading in DAT format)

Nonstop Database, HiRDB Version 9 System Operation Guide

13.6.6 Example 5: Adding a column to a table with the FIX attribute (unloading in DAT format)

This example adds a column (C4) to a table with the FIX attribute (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 add 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.; -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 add column C4

 
ALTER TABLE TABLE01 ADD C4 INTEGER WITH DEFAULT;

The WITH DEFAULT operand must be specified in this step. In this case, 0 is stored in column C4.

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

 
C1
C2
C3

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

Note on specifying a column structure information file: When a column structure information file is specified after a column has been added, it is important not to specify the added column. That way, HiRDB will detect that the input data does not contain any data for that column and will store the default value or the NULL value in the added column. Because the NULL value cannot be stored in a table with the FIX attribute, the default value must be stored in such a case by specifying the WITH DEFAULT operand when the column is added with an ALTER TABLE statement.

(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.