22.3.2 Customizing the sample database

You can customize the batch files for creating the sample database to define tables and load data to the tables. To customize batch files, you must be familiar with SQL and the database load utility (pdload). For details about SQL, see the manual HiRDB Version 9 SQL Reference. For details about the database load utility (pdload), see the manual HiRDB Version 9 Command Reference.

Note
At the point when HiRDB has been installed and has just started, the registered authorization identifier and password are both root. The root user has the DBA privilege. Therefore, root is the authorization identifier and password in the client environment definitions (hirdb.ini) during the sample database setup.
Organization of this subsection
(1) Adding a new user
(2) Defining tables and indexes
(3) Loading data to the tables
(4) Creating a batch file for executing commands under aliases

(1) Adding a new user

The following shows how to customize the batch files to add a new user:

To customize the batch file:
  1. Modify the contents of sampleDB1.bat. Change the line executing the table definition to a comment statement so that only the privilege definition is executed:
    pddef<%PDDIR%\sample\sampleDB\tblecreate
    [Figure]
    rem pddef<%PDDIR%\sample\sampleDB\tblecreate
  2. Modify the contents of the sample privilege definition %PDDIR%\sample\sampleDB\gr_USER1 as follows:
    [Figure]To add a new user, define the CONNECT privilege with the GRANT statement.
    [Figure]Define the schema definition privilege with the GRANT statement, as required.
  3. When you have finished modifying the file, execute sampleDB1.bat from the HiRDB command prompt. When a message is displayed on the screen indicating normal termination of the database definition utility (pddef), the addition of a new user and the privilege definition is completed.
    Note that sampleDB3.bat adds USER1.

(2) Defining tables and indexes

The following shows how to customize the batch files to define tables and indexes:

To customize the batch file:
  1. To add a new user and use this user to define schemas, modify the authorization identifier and password in the client environment definitions (hirdb.ini). (Change the value of the PDUSER environment variable in the hirdb.ini file, which is located immediately below the windir environment variable, from root to the added authorization identifier and password.)
  2. Modify the contents of sampleDB1.bat. Change the line executing the privilege definition to a comment statement. If the table definition part is set as a comment statement, uncomment it.
  3. Modify the contents of %PDDIR%\sample\sampleDB\tblecreate, which is provided as a sample table definition. Modify the schemas and tables to be defined.
  4. When you have finished modifying the file, execute sampleDB1.bat from the HiRDB command prompt. Customization is completed if a message is displayed on the screen indicating normal termination of the database definition utility (pddef). If you have defined multiple tables and there is an error in any of the tables, modify the contents of tblecreate, then reexecute.

(3) Loading data to the tables

The following shows how to customize the batch files to load data to tables:

To customize the batch file:
  1. The authorization identifier and password are set to root in sampleDB2.bat. Change them to the actual table owner's authorization identifier and password as follows:
    set PDUSER="root"/"root"
    [Figure]
    set PDUSER="authorization-identifier"/"password"
  2. The sample batch file provided loads data to four tables (CUSTOM, GOODS, VENDOR, and STOCK). Modify or change an applicable line to a comment statement as required.
  3. @echo source%PDDIR%\sample\sampleDB\loaddata\GOODS.CSV>%PDDIR%\TMP\LOD specifies control information in the LOD control information file. Modify the specified control information as required.
  4. %PDDIR%\bin\pdload -i s -e GOODS %PDDIR%\TMP\LOD executes the database load utility (pdload). Modify information such as table name, GOODS, if necessary.
  5. %PDDIR%\sample\sampleDB\loaddata\GOODS.csv is the input file. Create input data in this file. The sample data provided uses DAT-format GOODS.CSV. Use this as an example to create input data.
  6. When you have finished creating the input data, execute sampleDB2.bat from the HiRDB command prompt. Check the message indicating the execution status of the database load utility (pdload).
    sampleDB4.bat assumes that USER1 executes the data load operation.

(4) Creating a batch file for executing commands under aliases

It may not be possible to execute a HiRDB operation command because it has the same name as an OS command or a command provided by another program. In this case, the following actions can be taken:

If neither of these actions can be taken, there is a way to execute a HiRDB operation command under a user-defined name. HiRDB provides a sample batch file for this purpose.

(a) Name of sample batch file provided by HiRDB

The table below lists and describes the sample batch file provided by HiRDB. This file is stored in the following directory:

(b) Procedure for creating an alias for a command

To create an alias for an operation command:

  1. Copy the sample batch file into a desired directory. To create aliases for multiple commands, copy the file once for each of the commands. Do not copy it into the bin or lib directory under the HiRDB directory.
  2. Set the copy target directory for the sample file in the PATH environment variable or path as the search path.
  3. Rename the file copied in step 1 to the alias of the HiRDB operation command. For example, command name pdmod might be changed to hirmod.
  4. Open the copied sample file and change the part shown in the following figure as cc...cc to the name of the HiRDB command you want to execute as an alias.

    Figure 22-2 Sample batch file

    [Figure]

This procedure enables a HiRDB operation command to be executed under any desired name. Options can be specified in the alias command in the same manner as with the normal HiRDB operation command.

(c) Notes
  1. A name other than the HiRDB operation command name must be assigned to a copy of the sample file.
  2. It is possible that the %PDDIR%\bin and %PDDIR%\lib directories under the HiRDB directory may be deleted in their entirety during uninstallation. For this reason, sample files must not be copied into these directories.
  3. To call a sample batch file from within another batch file, use call. Otherwise, the system may not be able to return control to the initial batch file.
  4. The contents of the sample files must not be changed, except for setting a HiRDB operation command name.
  5. To cancel execution of a created alias command during command processing, the HiRDB command process must be terminated at an extension of the alias process. Terminating the alias process does not automatically terminate the HiRDB command process.
  6. If a created alias command is executed and another process is terminated while the HiRDB command is waiting for a response to be entered, HiRDB command execution may result in an error or the response entry wait status may still be in effect. If the response entry wait status is still in effect, the HiRDB command process must be terminated.