Job Management Partner 1/Automatic Job Management System 3 Administration Guide

[Contents][Glossary][Index][Back][Next]


10.2.2 Reorganizing a database

This subsection describes how to reorganize a database.

Organization of this subsection
(1) Using the ajsembdbrorg command to reorganize the database
(2) Using a script to reorganize a database

(1) Using the ajsembdbrorg command to reorganize the database

Use the ajsembdbrorg command to reorganize the database. For details about the ajsembdbrorg command, see ajsembdbrorg in 2. Commands in the manual Job Management Partner 1/Automatic Job Management System 3 Command Reference 1.

To reorganize the database manually:

  1. Terminate the JP1/AJS3 scheduler services and agent services.
    Use the jajs_spmd_stop command to terminate the target JP1/AJS3 scheduler services and agent services. Terminate all scheduler services for which information is managed in the embedded database to be reorganized. You can use the ajsembdbidlist command to check the association between scheduler services and embedded database directories. For details about the ajsembdbidlist command, see ajsembdbidlist in 2. Commands Used during Setup in the manual Job Management Partner 1/Automatic Job Management System 3 Command Reference 2.

    Command format (terminating the scheduler service):
    jajs_spmd_stop -n jajs_schd -F scheduler-service-name
    -F scheduler-service-name
    Specify the service name of the scheduler service to be processed.
    Command example: jajs_spmd_stop -n jajs_schd -F AJSROOT2

    Command format (terminating the agent service):
    jajs_spmd_stop -n jajs_agtd
  2. Stop all connected JP1/AJS3 - View and commands.
    Make sure that no JP1/AJS3 - View and scheduler commands are running. Also disconnect the database from JP1/AJS3 Console View.
  3. 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.

    To reclaim empty segments from the scheduler database:
    • On a Windows host:
      JP1/AJS3-Manager-installation-folder\tools\ajsembdbreclaim -m scheduler -F service-name
    • On a UNIX host:
      /opt/jp1ajs2/tools/ajsembdbreclaim -m scheduler -F service-name

    To reorganize the agent management database:
    • On a Windows host:
      JP1/AJS3-Manager-installation-folder\tools\ajsembdbreclaim -m agent
    • On a UNIX host:
      /opt/jp1ajs2/tools/ajsembdbreclaim -m agent
  4. Use the ajsembdbrorg command to reorganize the database.
    For details about the ajsembdbrorg command, see ajsembdbrorg in 2. Commands in the manual Job Management Partner 1/Automatic Job Management System 3 Command Reference 1.

    Command example
    • In Windows:
      ajsembdbrorg -k unld -F service-name -d C:\tmp
      ajsembdbrorg -k reld -F service-name -d C:\tmp
    • In UNIX:
      ajsembdbrorg -k unld -F service-name -d /tmp
      ajsembdbrorg -k reld -F service-name -d /tmp

    For reorganizing the agent management database
    • In Windows:
      ajsembdbrorg -k unld -d C:\tmp -agent
      ajsembdbrorg -k reld -d C:\tmp -agent
    • In UNIX:
      ajsembdbrorg -k unld -d /tmp -agent
      ajsembdbrorg -k reld -d /tmp -agent

    Note
    Make sure that the directory specified in the -d option has enough free space for setting up the embedded database.
    To use the data acquired by -k unld for executing -k reld, specify the same directory in the -d option for both -k unld and -k reld.
    If an error occurs in -k unld, do not use the resulting file for executing -k reld. If you do so, the contents of the database might be corrupted.
  5. Start the JP1/AJS3 scheduler services.
    Use the jajs_spmd commands to start the target JP1/AJS3 scheduler services. Also start related services.
    Command format: jajs_spmd -n jajs_schd -F scheduler-service-name

    -F scheduler-service-name
    Specify the service name of the scheduler service to be processed.
    Command example: jajs_spmd -n jajs_schd -F AJSROOT2

(2) Using a script to reorganize a database

The following describes the procedure for using a script to reorganize the embedded database as a batch operation. Each step in the procedure is followed by a usage example.

Supplementary note
Because the reorganization procedure performs the same operations as described in (1), we recommend that you always use the procedure in (1) to reorganize the 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 10-1 Environment variable that need to be set

    Environment variable Description
    PDDIR Specify the name of the embedded database practical directory.
    PDCONFPATH In Windows, specify embedded-database-practical-directory\conf. In UNIX, specify embedded-database-practical-directory/conf. This is the directory in which definition files for the embedded database are stored.
    PDUXPLDIR In Windows only, specify the name of the work directory for the embedded database.
    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"
    JP1_HOSTNAME Specify the logical host name if you are using the database in a cluster system.
    PATH In Windows, specify embedded-database-practical-directory\bin. In UNIX, specify embedded-database-practical-directory/bin. This is the directory in which commands for the embedded database are stored.
    SHLIB_PATH# In UNIX only, specify embedded-database-practical-directory/lib.

    #
    This is LD_LIBRARY_PATH in Solaris, 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 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"'
    # SHLIB_PATH=$PDDIR/lib
    # PATH=$PATH:$PDDIR/bin
     
    # export PDDIR PDCONFPATH PDUSER SHLIB_PATH PATH
  2. Terminate the JP1/AJS3 scheduler services.
    Use the jajs_spmd_stop commands to stop the target JP1/AJS3 scheduler services. Terminate all scheduler services that use the RD area that will be reorganized.
    Command format: jajs_spmd_stop -n jajs_schd -F scheduler-service-name

    -F scheduler-service-name
    Specify the service name of the scheduler service to be processed.
    Command example: jajs_spmd_stop -n jajs_schd -F AJSROOT2
  3. Stop the connected JP1/AJS3 - View and all commands.
    Make sure that JP1/AJS3 - View and scheduler commands are not running. Also disconnect the database from JP1/AJS3 Console View.
  4. 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.

    On a Windows host:
    JP1/AJS3-Manager-installation-folder\tools\ajsembdbreclaim -m scheduler -F service-name

    On a UNIX host:
    /opt/jp1ajs2/tools/ajsembdbreclaim -m scheduler -F service-name
  5. Execute the following script.
    A sample reorganization script is provided. Create a copy in the work directory and use the copy.

    On a Windows host:
    JP1/AJS3-installation-folder\tools\ajsembdbcond-sample.bat

    On a UNIX host:
    /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.)
  6. Start the JP1/AJS3 scheduler services.
    Use the jajs_spmd command to start the target JP1/AJS3 scheduler services.
    Command format: jajs_spmd -n jajs_schd -F scheduler-service-name

    -F scheduler-service-name
    Specify the service name of the scheduler service to be processed.
    Command example: jajs_spmd -n jajs_schd -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 10-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 10-2 and determine the table for which reloading has failed.

[Contents][Back][Next]


[Trademarks]

Copyright (C) 2009, 2010, Hitachi, Ltd.
Copyright (C) 2009, 2010, Hitachi Solutions, Ltd.