uCosminexus Application Server, Application Development Guide
![[Contents]](FIGURE/CONTENT.GIF)
![[Glossary]](FIGURE/GLOSS.GIF)
![[Index]](FIGURE/INDEX.GIF)
![[Back]](FIGURE/FRONT.GIF)
Appendix B.3 Setting up an embedded database
An embedded database is built using the commands provided by Developer, and HiRDB SQL Executer. To operate the embedded database that you have built, also use HiRDB SQL Executer.
The flow of building and implementing an embedded database is as follows:
- Installing HiRDB SQL Executer
Install HiRDB SQL Executer. For details on how to install HiRDB SQL Executer, see 3.2 Installing HiRDB SQL Executer.
- Setting up an embedded database
Build an embedded database using commands and HiRDB SQL Executer. To set up the embedded database use commands, and to define users and tables, use HiRDB SQL Executer. For details on how to build the embedded database, see (1) Procedure for building an embedded database.
- Add an RD area
Add an RD area to the database according to the contents of embedded database to be used. For details on how to add an RD area, see 3.4.1 Adding an RD Area.
- Setting up operation control of the embedded database
Specify the settings for controlling the operation of the embedded database according to the contents of the embedded database to be used. For details on how to set up operation control, see 3.5 Setting operational control of the embedded database.
- Operating an embedded database
Operate the embedded database that you have built using HiRDB SQL Executer. For details on how to operate the embedded database, see 3.3 Operating an embedded database.
This section describes the procedure for building an embedded database using HiRDB SQL Executer.
To delete the embedded database that you have built, see (3) Deleting an embedded database. For details on the procedure to start and stop the embedded database that you have built, see 6.2 Starting and stopping an embedded database.
- Organization of this subsection
- (1) Procedure for building an embedded database
- (2) Settings of the setup file for an embedded database
- (3) Deleting an embedded database
An embedded database is built with either of the following methods:
- Method in which the users and tables are defined after building the database
- Method in which the database is built after defining the users and tables
This subsection describes each of the above-mentioned methods to build an embedded database.
For details on using an embedded database to develop a J2EE application that accesses the database, see the following manuals in addition to the procedure described here:
- HiRDB Command Reference
- HiRDB UAP Development Guide
- HiRDB SQL Reference
- HiRDB Messages
- Note
- When executing HiRDB commands after building an embedded database, input the commands in the dedicated DB command prompt that you can start from Cosminexus- Database console in the Start menu of Windows.
- The embedded database is built in started condition.
- The embedded database does not support unloading and loading of BINARY type and BLOB type columns. Transfer these data types to the respective applications.
- The maximum number of concurrent connections to an embedded database is eight.
In the property definition, specify a value up to eight for the minimum and maximum number of connections of a resource adapter used for connecting. The default value of the minimum and maximum number of connections of the resource adapter is ten. If you start the resource adapter when a value more than eight is specified, the KDJE49513-W message is output during the start-up.
Note that when you do not use the warming-up function of a connection pool, the KDJE49513-W message is not output during the start-up even if a value greater than eight is specified.
To define users and tables after building a database:
- To build a database and then define users and tables: Copy the following directory to any location:
Developer-installation-directory\ADP\EmbeddedDB\
This procedure assumes that the DB directory is copied to the following directory:
C:\Setup_Input\DB
- Add the code to the setup file of the embedded database.
Specify the following items in the C:\Setup_Input\DB\bats\cdsetupconfig.bat file:
- Authorized identifier of the table owner (USRID)
- Password of the table owner (PSWD)
- Size of the database (DB_SIZE)
- Directory in which the RD area and system files are to be created (RDAREA_DIR)
- Port number to be used in the embedded database (DB_PORT)
- Path of the directory copied in step 1. (INPUT_FILE_DIR)
With this method, you need not code the following items:
The coding example of the setup file is as follows:
@rem Environment settings file used by HiRDB Embedded Server
@rem Please do not use double quotation marks (") in the file path of this file.
@rem Table owner's authorization identifier
set USRID=
@rem Table owner's password
set PSWD=
@rem Area size of the database to be constructed (required)
set DB_SIZE=S
@rem Directories where the RD area and system files are to be created (required)
set RDAREA_DIR=C:\Program Files\Hitachi\Cosminexus\ADP\EmbeddedDB\area
@rem TCP/IP port number used by HiRDB Embedded Server
set DB_PORT=22200
@rem Directory where the input files used for HiRDB Embedded Server execution exist
set INPUT_FILE_DIR=C:\Setup_Input\DB |
Code the setup file without using double quotation marks (") even if the path includes a space.
For details on each setting, see (2) Settings of the setup file for an embedded database.
- Delete the user definition file and table definition file.
Either delete the grantuser file and tablecreate file that exist in the following directory or rename the files.
C:\Setup_Input\DB\bats\
- Execute a batch file to build an embedded database.
Change the current directory of the command prompt to C:\Setup_Input\DB\bats\ and execute the following command:
If an error occurs, a message indicating a failure in building the database is displayed on the prompt where you have executed the batch file. In this case, take appropriate action based on the following information:
- Identify the cause of the error by referencing Developer-installation-directory\DB\PDISTUP\spool\pdi_err.txt.
- Remove the cause of the error, and rebuild the embedded database.
Before rebuilding an embedded database, delete the embedded database. For details on how to delete the embedded database, see (3) Deleting an embedded database.
- Start HiRDB SQL Executer, and connect to the database.
Enter the following information in the CONNECT dialog box of HiRDB SQL Executer:
- Authorized identifier: root
- Password: root
- Host name: localhost
- Port number: 22200 (value specified in DB_PORT of the setup file)
# Include the double quotation marks (") when specifying the authorized identifier and password.
- Define the users.
After connecting to the database, define the users that access the database. The following is an example of defining users assuming that the authorized identifier is USER1 and password is PSWD.
GRANT DBA TO USER1 IDENTIFIED BY PSWD; |
- Connect to the HiRDB SQL Executer with the defined user and define the table.
Define the table with the CREATE SCHEMA and CREATE TABLE statement. You can also load the data with the insert statement. For details on the syntax of SQL statements, see the manual HiRDB SQL Reference.
The following is an example of loading the table definition and data:
CREATE SCHEMA;
CREATE FIX TABLE CUSTOM
(
Customer CD CHAR(5) ,
Customer name CHAR(30),
TELNO CHAR(12),
ZIPCD CHAR(3) ,
Address CHAR(30))
IN RDDATA10 PCTFREE=(10,0);
CREATE INDEX CUSTOMX ON CUSTOM
(
Customer name ASC)
IN RDINDX10 PCTFREE=10;
insert into CUSTOM values('TK001', 'Mikida Denki', '011-434-1998', '091', 'Totsuka');
insert into CUSTOM values('TK002', 'Shinko Department Store', '012-402-5055', '082', 'Yokohama');
insert into CUSTOM values('TK003', 'Kisetsuya', '014-252-1555', '043', 'Kanazawa');
|
To load the data, use the pdload command in addition to the SQL statements. For details on the pdload command, see the manual HiRDB Command Reference.
To build a database after defining the users and tables:
- To build a database and then define users and tables: Copy the following directory to any location:
Developer-installation-directory\ADP\EmbeddedDB\
This procedure assumes that the DB directory is copied to the following directory:
C:\Setup_Input\DB
- Add the code to the setup file of the embedded database.
Specify the following items in the C:\Setup_Input\DB\bats\cdsetupconfig.bat file:
- Authorized identifier of the table owner (USRID)
- Password of the table owner (PSWD)
- Size of the database (DB_SIZE)
- Directory in which the RD area and system files are to be created (RDAREA_DIR)
- Port number to be used in the embedded database (DB_PORT)
- Path of the directory copied in step 1. (INPUT_FILE_DIR)
The coding example of the setup file is as follows:
@rem Environment settings file used by HiRDB Embedded Server
@rem Please do not use double quotation marks (") in the file path of this file.
@rem Table owner's authorization identifier
set USRID=USER1
@rem Table owner's password
set PSWD=PSWD
@rem Area size of the database to be constructed (required)
set DB_SIZE=S
@rem Directories where the RD area and system files are to be created (required)
set RDAREA_DIR=C:\Program Files\Hitachi\Cosminexus\ADP\EmbeddedDB\area
@rem TCP/IP port number used by HiRDB Embedded Server
set DB_PORT=22200
@rem Directory where the input files used for HiRDB Embedded Server execution exist
set INPUT_FILE_DIR=C:\Setup_Input\DB |
Code the setup file without using double quotation marks (") even if the path includes a space.
In the coding example, consider the authorized identifier as USER1 and password as PSWD.
For details on each setting, see (2) Settings of the setup file for an embedded database.
- Create a user definition file.
The grantuser file of the user definition file stored in C:\Setup_Input\DB\bats\ is a text file coded with an SQL GRANT statement. The rules to create the file are as follows:
- The file name is fixed as grantuser.
- Create this file in C:\Setup_Input\DB\bats\.
- Specify such that the authorized identifier and password to be specified in the grantuser file are of the value same as that of the USRID parameter and PSWD parameter specified in the C:\Setup_Input\DB\bats\cdsetupconfig.bat file.
- Do not define users when there is no user definition file. When a table is created without defining the users, the authorized identifier and password of the table owner becomes the root.
- Create a table definition file.
The tablecreate file of a table definition file stored in the C:\Setup_Input\DB\bats\ is a text file coded with the defined node SQL (DDL statement) of the CREATE SCHEMA statement and CREATE TABLE statement. The rules to create the file are as follows:
- Create this file in C:\Setup_Input\DB\bats\.
- The table definition file name is fixed as tablecreate.
- Do not define the tables when there is no table definition file.
For details on the syntax of SQL statements, see the manual HiRDB SQL Reference.
To load the data into a table, use the pdload command after building the database. For details on the pdload command, see the manual HiRDB Command Reference.
- Execute a batch file to build an embedded database.
Change the current directory of the command prompt to C:\Setup_Input\DB\bats\ and execute the following command:
If an error occurs, a message indicating a failure in building the database is displayed on the prompt where you have executed the batch file. In this case, take appropriate action based on the following contents:
- Identify the cause of the error by referencing Developer-installation-directory\DB\PDISTUP\spool\pdi_err.txt.
- Remove the cause of the error, and rebuild the embedded database.
Before rebuilding an embedded database, delete the embedded database. For details on how to delete the embedded database, see (3) Deleting an embedded database.
This subsection describes the settings of the setup file (cdsetupconfig.bat) of the following embedded database:
@rem ALL RIGHTS RESERVED, COPYRIGHT (C) 2004, HITACHI, LTD.
@rem LICENSED MATERIAL OF HITACHI, LTD.
@title HiRDB Embedded Server
@echo off
@rem Environment settings file used by HiRDB Embedded Server
@rem Please do not use double quotation marks (") in the file path of this file.
@rem Table owner's authorization identifier
set USRID=USER1 ...(1)
@rem Table owner's password
set PSWD=PSWD ...(2)
@rem Area size of the database to be constructed (required)
set DB_SIZE=S ...(3)
@rem Directories where the RD area and system files are to be created (required)
set RDAREA_DIR=C:\Program Files\Hitachi\Cosminexus\ADP\EmbeddedDB\area ...(4)
@rem TCP/IP port number used by HiRDB Embedded Server
set DB_PORT=22200 ...(5)
@rem Directory where the input files used for HiRDB Embedded Server execution exist
set INPUT_FILE_DIR=C:\Program Files\Hitachi\Cosminexus\ADP\EmbeddedDB\ ...(6) |
Note the following when adding the code to a setup file:
- Do not use double quotation marks (") even if the path includes a space.
- A space is also recognized as a character. Do not input a space after equal (=).
Each setting from (1) to (6) is described below:
- USRID (authorized identifier)
Specify the authorized identifier of the table owner. You need to specify this setting when you define a system, user, and table concurrently using the cddbconstruct.bat command. Specify the same authorized identifier as that of specified in the grantuser file.
- PSWD (password)
Specify the password of the table owner. You need to specify this setting when you define a system, user, and table concurrently using the cddbconstruct.bat command. Specify the same password as that of specified in the grantuser file.
- DB_SIZE (database size)
Specify the size of the database to be built. This setting is mandatory. The database size of the provided template is the total size of system files and RD area. Specify S, M, or L for size.
- RDAREA_DIR (directory in which RD area is to be created)
Specify an absolute path for the directories in which the RD area and system files are created. This setting is mandatory. The format to specify the directory is as follows:
RDAREA_DIR=rdarea_directory[,systemA_directory[,systemB_directory]]
- rdarea_directory
Specify the directory in which the RD area is created.
- systemA_directory
Specify the directory in which the A node system file is created.
- systemB_directory
Specify the directory in which the B node system file is created.
When specifying, note the following:
- Specify single byte alphanumeric characters up to 48 bytes for the directory specified in RDAREA_DIR.
- If you omit systemA_directory, system files are created in rdarea_directory.
- If you omit systemB_directory, the B node system file is created in systemA_directory.
- When you specify systemB_directory, you need to divide the area where the system file, specified in the initial value file, is to be created into node A and node B.
- If you execute commands when the same file system area is used, an error occurs.
- When you specify a directory that does not exist, a new directory is created.
- If the disk space of the specified directory is smaller than the size of the file to be set, an error occurs.
- DB_PORT (port number)
Specify the TCP/IP port number to be used in the embedded database (corresponding to pd_name_port). When specifying, note the following:
- Specify the specification value in the range of 5001 to 65535.
- Assume 22200 when omitting the port number.
- If you specify a port number that already exists, an error occurs. The existing port number means the port number coded in the services file. Note that duplication check for other HiRDB port numbers is not performed.
- INPUT_FILE_DIR z (directory of the input file)
Specify an absolute path for the directory containing the input file. The input file indicates the default file and system-defined input file. When specifying, note the following:
- You need to create a directory below conf and ini in the structure same as the directory structure provided as a template below the specified directory.
- If there is no specification, the environment variable COSMINEXUS_HOME\DB\PDISTUP directory is assumed.
The following figure shows the structure of the directory provided as a template, a directory that is created randomly, and the directory that is read when nothing is specified:
Figure B-2 Directory configuration of the input file of embedded database
![[Figure]](FIGURE/ZU0B1300.GIF)
To delete an embedded database, execute the following batch file:
If an error occurs, a message indicating a failure in building the database is displayed on the prompt where you have executed the batch file. In this case, remove the cause of the error by referencing pdirst_err.txt and re-execute cddbdelete.bat.
All Rights Reserved. Copyright (C) 2012, 2013, Hitachi, Ltd.