12.2.2 Example

This example migrates a stored procedure (routine identifier PROC1) and a table (TABLE1) used by the PROC1 stored procedure from HiRDB system A to HiRDB system B.

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 the control statements file for the pdrorg command
(3) Use the pdrorg command to unload data for TABLE1
(4) Use the pdrels command to release RDAREA1 to RDAREA6 from shutdown status
(5) Create the control statements file for the pdexp command
(6) Use the pdexp command to export table definition information for TABLE1
(7) Create the control statements file for the pdexp command
(8) Use the pdexp command to export the stored procedure
(9) Store the unload data file and export files on a medium such as CMT
(10) Store the unload data file and export files in target system
(11) Use the pddef command to define a schema for the user who owns PROC1 and TABLE1
(12) Use the pdcopy command to back up data
(13) Use the pdexp command to import table definition information for TABLE1
(14) Use the pdhold command to shut down RDAREA1 to RDAREA6
(15) Create the control statements file for the pdrorg command
(16) Use the pdrorg command to reload data for TABLE1
(17) Use the pdcopy command to back up the data
(18) Use the pdrels command to release RDAREA1 to RDAREA6 from shutdown status
(19) Use the pdexp command to import the stored procedure

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

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

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

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

(a) HiRDB/Single Server

unload /pdrorg/unfile1

Explanation
Specifies the name of the unload data file.
(b) HiRDB/Parallel Server

unload bes1:/pdrorg/unfile1              1
unload bes2:/pdrorg/unfile2              2

Explanation
  1. Specifies the name of the unload data file at a back-end server (bes1).
  2. Specifies the name of the unload data file at a back-end server (bes2).

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

pdrorg -k unld -j -t TABLE1 /pdrorg/rorg01

Explanation
-k: Specifies unld in order to unload data.
-j: Specifies that a table containing a LOB column is to be unloaded.
-t: Specifies the name of the table to be unloaded.
/pdrorg/rorg01: Specifies the name of the control statements file for the pdrorg command created in step (2).

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

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

(5) Create the control statements file for the pdexp command

The following are the contents of the control statements file (/pdexp/exp01):

-t USR01.TABLE1

Explanation
USR01: Name of the schema that owns TABLE1
TABLE1: Name of the table to be exported

(6) Use the pdexp command to export table definition information for TABLE1

pdexp -e /pdexp/expfile1 -f /pdexp/exp01

Explanation
-e: Specifies the name of the file to be exported.
-f: Specifies the name of the control statements file for the pdexp command created in step (5).

(7) Create the control statements file for the pdexp command

The following are the contents of the control statements file (/pdexp/exp02):

-p USR01.PROC1

Explanation
USR01: Name of the schema that owns PROC1
PROC1: Name of the stored procedure to be exported

(8) Use the pdexp command to export the stored procedure

pdexp -e /pdexp/expfile2 -f /pdexp/exp02

Explanation
-e: Specifies the name of the file to be exported.
-f: Specifies the name of the control statements file for the pdexp command created in step (7).

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

The unload data file created in step (3) and the export files created in steps (6) and (8) are stored on a medium such as CMT.

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

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

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

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

(11) Use the pddef command to define a schema for the user who owns PROC1 and TABLE1

pddef
  CREATE SCHEMA AUTHORIZATION USR01;

(12) Use the pdcopy command to back up data

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

Explanation
The RDAREAs are backed up as a safeguard in the event of errors during database migration. The following RDAREAs must be backed up:
  • Master directory RDAREA
  • Data directory RDAREA
  • Data dictionary RDAREAs
  • Data dictionary LOB RDAREAs
  • 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.

(13) Use the pdexp command to import table definition information for TABLE1

pdexp -i /pdexp/expfile1

Explanation
-i: Specifies the name of the file to be imported.

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

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

(15) Create the control statements file for the pdrorg command

The following are the contents of the control statements file (/pdrorg/rorg02):

(a) HiRDB/Single Server

unload /pdrorg/unfile1                           1
idxwork /pdrorg/idxwork                          2
sort /sortwork,8192                              3

Explanation
  1. Specifies the name of the unload data file.
  2. 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.
  3. Specifies the name of the work directory for sorting.
(b) HiRDB/Parallel Server

unload bes1:/pdrorg/unfile1                     1
idxwork bes1 /pdrorg/idxwork                    2
sort bes1 /sortwork,8192                        3
unload bes2:/pdrorg/unfile2                     4
idxwork bes2 /pdrorg/idxwork                    5
sort bes2 /sortwork,8192                        6

Explanation
  1. Specifies the name of the unload data file (for bes1).
  2. 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.
  3. Specifies the name of the work directory for sorting (for bes1).
  4. Specifies the name of the unload data file (for bes2).
  5. 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.
  6. Specifies the name of the work directory for sorting (for bes2).

(16) Use the pdrorg command to reload data for TABLE1

pdrorg -k reld -j -t TABLE1 /pdrorg/rorg01

Explanation
To re-create the index (INDEX1) at the same time, the -i option is omitted and the index batch creation mode is used.
-k: Specifies reld in order to reload data.
-j: Specifies that a table containing a LOB column is to be reloaded.
-t: Specifies the name of the table to be reloaded.
/pdrorg/rorg02: Specifies the name of the control statements file for the pdrorg command created in step (15).

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

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

Explanation
The following RDAREAs must be backed up:
  • Master directory RDAREA
  • Data directory RDAREA
  • Data dictionary RDAREAs
  • Data dictionary LOB RDAREAs
  • 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 of the pdcopy command's processing results listing.

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

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

(19) Use the pdexp command to import the stored procedure

pdexp -i /pdexp/expfile2

Explanation
-i: Specifies the name of the file to be imported.

It is recommended that after the command has executed you check whether or not the execution results are correct. For details on how to check command execution results, see the manual HiRDB Version 8 Command Reference.