Example 4: Migrating a table containing binary data to a system that has a different endian format

Nonstop Database, HiRDB Version 9 System Operation Guide

12.1.8 Example 4: Migrating a table containing binary data to a system that has a different endian format

This example migrates a table (TABLE1) from HiRDB system A (big endian) to HiRDB system B (little endian).

A LOB column is defined for TABLE1.
An index (INDEX1) is defined for TABLE1.

It is assumed that RDAREAs with the same names (RDAREA1 to RDAREA6) have already been created in the target system.

[Figure]

Organization of this subsection: (1) Enter the pdhold command to shut down RDAREA1 to RDAREA6; (2) Create a control statements file for the pdrorg command; (3) Use the pdrorg command to unload data for TABLE1; (4) Enter the pdrels command to release RDAREA1 to RDAREA6 from shutdown status; (5) Use the pddefrev command to create a definition SQL statement; (6) Store the unload data file and export file on a medium such as CMT; (7) Store the unload data file and export file in the target system; (8) Use the pddef command to define a schema for the user who owns TABLE1; (9) Swap the system log files; (10) Use the pdcopy command to back up the data; (11) Use the pddef command to execute the definition SQL statement; (12) Use the pdhold command to shut down RDAREA1 to RDAREA6; (13) Create a control statements file for the pdload command; (14) Use the pdload command to load the TABLE1 data; (15) Swap the system log files; (16) Enter the pdcopy command to back up data; (17) Use the pdrels command to release RDAREA1 to RDAREA6 from shutdown status

(1) Enter the pdhold command to shut down RDAREA1 to RDAREA6

 
pdhold -r RDAREA1,RDAREA2,RDAREA3,RDAREA4,RDAREA5,RDAREA6

(2) Create a control statements file for the pdrorg command

The following are the contents of the control statements file (/pdrorg/rorg01).

(a) HiRDB single server configuration

 
unload /pdrorg/unfile1

Explanation: Specifies the name of the unload data file.

(b) HiRDB parallel server configuration

 
unload bes1:/pdrorg/unfile1

Explanation: Specifies the name of the unload data file (for bes1).

(3) Use the pdrorg command to unload data for TABLE1

 
pdrorg -k unld -g -W bin -t TABLE1 /pdrorg/rorg01

Explanation

-k: Specifies unld to unload data.

-g: Specifies that TABLE1 is a row-partitioned table on a server in a HiRDB parallel server configuration. A single unload data file is created.

-W: Specifies that an input data file for pdload is to be created. In this case, bin (binary format) is specified since TABLE1 contains a binary format column.

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

/pdrorg/rorg01: Specifies the name of the control statements file for the pdrorg command created in step (2).

(4) Enter the pdrels command to release RDAREA1 to RDAREA6 from shutdown status

 
pdrels -r RDAREA1,RDAREA2,RDAREA3,RDAREA4,RDAREA5,RDAREA6

(5) Use the pddefrev command to create a definition SQL statement

 
pddefrev -f ctrl1 -o /pddefrev/expfile1

Explanation: -f: Specifies the control statement file that specifies the table or stored procedure for which the definition SQL statement is being created.; -o: Specifies the name of the file to which the created definition SQL statement is to be output.

(6) Store the unload data file and export file on a medium such as CMT

The unload data file created in step (3) and the file created in step (5) are stored on a medium such as CMT.

The operations at the source system (HiRDB system A) have now been completed.

(7) Store the unload data file and export file in the target system

The operations from this point on are performed at the target system (HiRDB system B).

The unload data file and export file stored on a medium such as CMT are copied to the target system.

(8) Use the pddef command to define a schema for the user who owns TABLE1

 
pddef
   CREATE SCHEMA AUTHORIZATION USR01;

(9) Swap the system log files

Before you acquire a backup, use the pdlogswap command to swap the system log files.

 
pdlogswap -d sys -w

Explanation: In the case of a HiRDB parallel server configuration, specify the -s option and execute the pdlogswap command for each server to be backed up.

(10) Use the pdcopy command to back up the data

 
pdcopy -m /rdarea/mast/mast01 -M r
-r RDMAST,RDDIR,RDDIC,RDAREA1,RDAREA2,RDAREA3,RDAREA4,RDAREA5,RDAREA6
-b /pdcopy/backup01 -p /pdcopy/list01

Explanation

Back up the RDAREAs as a safeguard against any errors that might occur during database migration. The following RDAREAs must be backed up:

Master dictionary RDAREA
Data directory RDAREA
Data dictionary RDAREA
User RDAREAs and user LOB RDAREAs for storing the imported table (RDAREA1 to RDAREA6)

-m: Specifies the name of the first HiRDB file in the master directory RDAREA.

-M: Specifies the backup acquisition mode.

-r: Specifies the names of the RDAREAs to be backed up.

-b: Specifies the name of the backup file.

-p: Specifies the output destination for the pdcopy command's processing results listing.

For details about how to back up data, see 6. Backup Procedures.

(11) Use the pddef command to execute the definition SQL statement

 
pddef < expfile1

Explanation: Specifies the input file that describes the definition SQL statement created in step (5).

(12) Use the pdhold command to shut down RDAREA1 to RDAREA6

 
pdhold -r RDAREA1,RDAREA2,RDAREA3,RDAREA4,RDAREA5,RDAREA6

(13) Create a control statements file for the pdload command

The following are the contents of the control statements file (/pdload/load01).

(a) HiRDB single server configuration

 
source /pdrorg/unfile1                           1
option srcendian=big                             2
idxwork /pdrorg/idxwork                          3
sort /sortwork                                   4

Explanation

Specifies the name of the unload data file.
Specifies the endian format of the unload data file specified in step 1.
Specifies the name of the directory in which an index information file is to be created. The index information file is created under this directory.
Specifies the name of the work directory for sorting.

(b) HiRDB parallel server configuration

 
unload bes1:/pdrorg/unfile1              1
option srcendian=big                     2
idxwork bes1 /pdrorg/idxwork             3
sort bes1 /sortwork                      4
idxwork bes2 /pdrorg/idxwork             5
sort bes2 /sortwork                      6

Explanation

Specifies the name of the unload file (for bes1).
Specifies the endian format of the unload data file specified in step 1.
Specifies the name of the directory in which an index information file is to be created (for bes1). The index information file is created under this directory.
Specifies the name of the work directory for sorting (for bes1).
Specifies the name of the directory in which an index information file is to be created (for bes2). The index information file is created under this directory.
Specifies the name of the work directory for sorting (for bes2).

(14) Use the pdload command to load the TABLE1 data

 
pdload -b -W TABLE1 /pdload/load01

Explanation: Because an index (INDEX1) is re-created simultaneously, the -i option is omitted and indexes are created in batch index creation mode.; -b: Specifies -W to use as the input data file the file that was output in binary format when the -W option was specified for the pdrorg command.; /pdload/load01: Specifies the name of the control statements file for the pdload command created in step (13).

(15) Swap the system log files

Before acquiring a backup, use the pdlogswap command to swap the system log files.

 
pdlogswap -d sys -w

Explanation: In the case of a HiRDB parallel server configuration, specify the -s option and execute the pdlogswap command for each server to be backed up.

(16) Enter the pdcopy command to back up data

 
pdcopy -m /rdarea/mast/mast01 -M r
-r RDMAST,RDDIR,RDDIC,RDAREA1,RDAREA2,RDAREA3,RDAREA4,RDAREA5,RDAREA6
-b /pdcopy/backup02 -p /pdcopy/list02

Explanation

The following RDAREAs must be backed up:

Master dictionary RDAREA
Data directory RDAREA
Data dictionary RDAREA
User RDAREAs and user LOB RDAREAs for importing the table (RDAREA1 to RDAREA6)

-m: Specifies the name of the first HiRDB file in the master directory RDAREA.

-M: Specifies the backup acquisition mode.

-r: Specifies the names of the RDAREAs to be backed up.

-b: Specifies the name of the backup file.

-p: Specifies the output destination for the pdcopy command's processing results listing.

For details about how to back up data, see 6. Backup Procedures.

(17) Use the pdrels command to release RDAREA1 to RDAREA6 from shutdown status

 
pdrels -r RDAREA1,RDAREA2,RDAREA3,RDAREA4,RDAREA5,RDAREA6

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