9.2.3 Reorganizing a database

(a) Reorganization procedure

To reorganize an embedded database:

1. Set the environment variables required to execute commands for the embedded database.

   The following table describes the environment variables you need to set.

   Table 9‒1: Environment variable that need to be set

   Environment variable	Value to be specified
   JP1_HOSTNAME	Specify the logical host name if you are using the database in a cluster system.
   PATH	This is the directory in which commands for the embedded database are stored. In Windows: Specify embedded-database-practical-directory\bin In UNIX: Specify embedded-database-practical-directory/bin
   PDCONFPATH	This is the directory in which definition files for the embedded database are stored. In Windows: Specify embedded-database-practical-directory\conf In UNIX: Specify embedded-database-practical-directory/conf
   PDDIR	Specify the name of the embedded database practical directory.
   PDLANG	Specify ANY.
   PDUSER	Specify the user name and password of the embedded database administrator (the owner of the tables of the scheduler database) in user-name/password format. Example: PDUSER="root"/"root"
   PDUXPLDIR	In Windows only, specify the name of the work directory for the embedded database.
   SHLIB_PATH#	In UNIX only, specify embedded-database-practical-directory/lib.

   #

   This is LD_LIBRARY_PATH in Solaris or Linux, and LIBPATH in AIX.

   Example of environment variable settings in Windows:

   x:\> set PDDIR=c:\Program Files\HITACHI\JP1AJS2\embdb\_JF0
   x:\> set PDCONFPATH=%PDDIR%\conf
   x:\> set PDUXPLDIR=%PDDIR%\uxpldir
    
   x:\> set PDUSER="root"/"root"
   x:\> set PDLANG=ANY
   x:\> set JP1_HOSTNAME=lhost1
   x:\> set PATH=%PATH%;%PDDIR%\bin

   Example of environment variable settings in UNIX (HP-UX):

   # PDDIR=/opt/jp1ajs2/embdb/_JF0
   # PDCONFPATH=$PDDIR/conf
   # PDUSER='"root"/"root"'
   # PDLANG=ANY
   # SHLIB_PATH=$PDDIR/lib
   # PATH=$PATH:$PDDIR/bin
    
   # export PDDIR PDCONFPATH PDUSER PDLANG SHLIB_PATH PATH

2. Terminate the JP1/AJS3 scheduler services.

   Use the jajs_spmd_stop command or ajsstop command to stop the target JP1/AJS3 scheduler services. Terminate all scheduler services that use the RD area that will be reorganized.

   Command format (for jajs_spmd_stop command):

   jajs_spmd_stop [-h logical-host-name] -n jajs_schd -F scheduler-service-name

   Command example:

   jajs_spmd_stop -n jajs_schd -F AJSROOT2

   Command format (for ajsstop command):

   ajsstop -F scheduler-service-name

   Command example:

   ajsstop -F AJSROOT2

3. Terminate the JP1/AJS3 agent management.

   Use the jajs_hstd_stop command to terminate the agent management.

   Command format:

   - In Windows:

   jajs_hstd_stop [-h logical-host-name] -n agentm

   - In UNIX:

   jajs_hstd_stop [-h logical-host-name] -n ajsagtmd

   Command example:

   - In Windows:

   jajs_hstd_stop -n agentm

   - In UNIX:

   jajs_hstd_stop -n ajsagtmd

4. Stop the connected JP1/AJS3 - View and all commands.

   Make sure that JP1/AJS3 - View and scheduler commands are not running.

5. Use the ajsembdbreclaim command to reclaim empty segments.

   If you perform reorganization when there are only a few empty segments, the work area might be insufficient, causing reorganization to fail. To avoid this problem, we recommend that you reclaim empty segments beforehand.

   Command example:

   • In Windows:

     JP1/AJS3-Manager-installation-folder\tools\ajsembdbreclaim -m manager

   • In UNIX:

     /opt/jp1ajs2/tools/ajsembdbreclaim -m manager

6. Execute the following script.

   A sample reorganization script is provided. Create a copy in the work directory and use the copy.

   • In Windows:

     JP1/AJS3-installation-folder\tools\ajsembdbcond-sample.bat

   • In UNIX:

     /opt/jp1ajs2/tools/ajsembdbcond-sample

   For details about the ajsembdbcond script, see (b) Format of the reorganization script below.

   Script example:

   ajsembdbcond

   (When the script is renamed to ajsembdbcond.)

7. Start the JP1/AJS3 agent management.

   Use the jajs_hstd command to start the agent management.

   Command format:

   - In Windows:

   jajs_hstd [-h logical-host-name] -n agentm

   - In UNIX:

   jajs_hstd [-h logical-host-name] -n ajsagtmd

   Command example:

   - In Windows:

   jajs_hstd -n agentm

   - In UNIX:

   jajs_hstd -n ajsagtmd

8. Start the JP1/AJS3 scheduler services.

   Use the jajs_spmd or ajsstart command to start the applicable JP1/AJS3 scheduler services.

   Command format (for jajs_spmd command):

   jajs_spmd [-h logical-host-name] -n jajs_schd -F scheduler-service-name

   Command example:

   jajs_spmd -n jajs_schd -F AJSROOT2

   Command format (for ajsstart command):

   ajsstart -F scheduler-service-name

   Command example:

   ajsstart -F AJSROOT2

(b) Format of the reorganization script

The reorganization script allows you to reorganize the tables and indexes of the embedded database.

Format

ajsembdbcond-sample [-u user-name -p password]

Arguments

-u user-name

Specify the name of the user (the owner of the tables) reorganizing the tables. If you do not specify the user name, the user name specified in the script is used.

-p password

Specify the password of the user reorganizing the tables. If you do not specify the password, the password of the user specified in the script is used.

Supplementary note

Specify the user name and password as follows:

In Windows: "root"

In UNIX: \"root\"

The values enclosed in double quotation marks (") will be treated as case-sensitive.

(c) Customizing the reorganization scripts

The following describes how to customize reorganization scripts. Before customizing the script file, create a copy of the file in the work directory and edit the copy. Use a separate script file for each embedded database environment. The following describes the items you can customize.

CUSTOM_PDDIR=embedded-database-practical-directory

Specify the name of the embedded database practical directory.

If the PDDIR environment variable has been set, it overrides this item.

If you do not specify CUSTOM_PDDIR in the script file and the PDDIR environment variable has not been set, the script does not reorganize tables.

CUSTOM_PDCONFPATH=directory-conatining-the-system-definition-files-for-the-embedded-database

Specify the name of the directory containing the system definition files for the embedded database.

If the PDCONFPATH environment variable has been set, it overrides this item.

If you do not specify CUSTOM_PDCONFPATH in the script file and the PDCONFPATH environment variable has not been set, the script does not reorganize tables.

CUSTOM_PDUXPLDIR=work-directory-for-the-embedded-database

This item needs to be specified only in Windows. Specify the name of the work directory for the embedded database.

If the PDUXPLDIR environment variable has been set, it overrides this item.

If you do not specify PDUXPLDIR in the script file and the PDUXPLDIR environment variable has not been set, the script does not reorganize tables.

CUSTOM_EMBDB_USER=table-owner-name

Specify the name of the user executing the reorganization command.

Specify the owner of the tables.

If a user name is specified for CUSTOM_EMBDB_USER and you specify a user name in the script argument, the value of the argument has priority.

CUSTOM_EMBDB_PASS=table-owner's-password

Specify the password of the user executing the reorganization command.

CUSTOM_TABLE_LIST=table-name

Specify a list of table names to be reorganized separated by a space.

If you do not specify this item, the script does not reorganize tables.

CUSTOM_TABLE_RDAREA=table-storage-RD-area

Specify the RD area containing the tables to be reorganized.

If you do not specify this item, the script does not reorganize tables that are to be reorganized.

CUSTOM_INDEX_RDAREA=index-storage-RD-area

Specify the RD area containing the indexes of the tables to be reorganized.

If you do not specify this item, the script does not reorganize tables.

CUSTOM_TEMP_DIR=temporary-file-directory

Specify the full path name of the folder in which reorganization command control files and data files unloaded from the database will be stored.

If you do not specify this item, C:\TEMP is assumed in Windows, and /tmp is assumed in UNIX.

If multiple embedded database environments or scheduler services have been set up, specify a unique temporary file directory for each environment or service to prevent loss of data from the concurrent execution of scripts.

CUSTOM_LOG_FILE=log-output-file

Specify the full path name of the file to which the script execution results are to be output. Because a maximum size cannot be specified for the log file, if this item is specified, delete the log file as required.

If you do not specify this item, the script execution results are output to the standard output.

Supplementary notes

• Users who can execute the script

  Only the embedded database system administrator can execute the commands used in the script. You must execute the script as an OS user who has administrator permissions for the embedded database system.

• Specify the user name and password as follows:

  In Windows: "root"

  In UNIX: \"root\"

  The values enclosed in double quotation marks (") will be treated as case-sensitive.

• Action to be taken if an error occurs

  If the reloading of unloaded data fails during the reorganization of tables, the tables and indexes in the database might become empty. If the tables and indexes have become empty, you will need to manually reload the unloaded data.

  If an error occurs during reorganization, the RD area remains blocked. After you correct the error, release the RD area from its blocked status.

  The following table describes the status of tables if an error occurs during a reload.

  Table 9‒2: Table status when an error occurs (during a reload), and the recovery methods

  Message output immediately before an error occurs	Status of a table in the RD area	Recovery methods
  None	The status is the same as before reorganization.	Re-execute reorganization of the table.
  KFPL00721-I	The table has been deleted.	Re-execute reloading of the table.
  KFPL00712-I KFPL00732-I	Table creation has not finished.	Re-execute reloading of the table.
  KFPL00714-I KFPL00734-I	Table creation has finished.	Re-execute reloading of the table.
  KFPL00715-I	Index creation has not finished.	Re-execute reloading of the table.
  KFPL00716-I	Index creation has finished.	No action is necessary.

  Example: Re-executing a reload

  If the reloading of data fails, the unloaded data file is stored in the folder specified in the following script parameter:

  CUSTOM_TEMP_DIR

  If no folder is specified, C:\TEMP is assumed in Windows and /tmp is assumed in UNIX.

  The following files are created in the folder:

  rorg_ctrl: Control file required for the reorganization of tables

  For 08-10-01 or an earlier version:

  unldfile: Table data

  For 08-10-02 or a later version:

  unldfile_table-name: Table data

  Use these files to re-execute the reload.

  If the reloading of a UNIT table fails, the command for re-executing reorganization is as follows.

  Command example

  In Windows: pdrorg -k reld -t AJSUNIT C:\tmp\rorg_ctrl

  In UNIX: pdrorg -k reld -t AJSUNIT /tmp/rorg_ctrl

  See Table 9-2 and determine the table for which reloading has failed.