Notices

Preface

1. Overview of HiRDB System Construction

1.1 Overview of behavior when operands are omitted

1.2 System construction procedures

1.2.1 System construction procedure for installing a new HiRDB

1.2.2 Setting up a HiRDB environment

1.2.3 Environment setup for linking to other products

1.3 Organization of HiRDB directories and files

1.3.1 Initial files that are created

1.3.2 Files that consistently increase in size

1.4 Upgrading HiRDB

1.4.1 Before upgrading

1.4.2 Replacing an existing version with the new version

1.4.3 Installing a new version while retaining the old version

1.4.4 Upgrading the HiRDB plug-ins

1.4.5 Using Java stored procedures and functions

1.4.6 In the event of an upgrading error

1.4.7 Restoring an earlier version of HiRDB

1.5 Updating to HiRDB update version

1.5.1 Updating HiRDB

1.5.2 Prerequisites

1.5.3 Update procedure

1.5.4 Update procedure when the system switchover facility is used

1.5.5 Cautions

1.5.6 Operation considerations

1.5.7 Related product limitations and considerations

1.5.8 Operation when an error occurs during installation

1.6 Using JP1/Software Distribution to execute online distribution of HiRDB

1.6.1 Online distribution procedure

1.6.2 Preparations on JP1/Software Distribution Workstation

1.6.3 Notes about online distribution

1.7 Migrating to 64-bit mode HiRDB

1.7.1 Considerations when migrating to 64-bit mode

1.7.2 How to migrate to 64-bit mode

1.7.3 In the event of an SQL object migration error

1.7.4 In the event of a 64-bit-mode migration error (restoring the old version)

2. Installation

2.1 Pre-installation procedure

2.1.1 Checking and modifying OS parameters

2.1.2 Registering the HiRDB administrator

2.1.3 Setting up a HiRDB group

2.1.4 Creating the installation directory

2.1.5 Registering host names

2.2 HiRDB installation procedure

2.2.1 Installing HiRDB

2.2.2 Installing option program product

2.2.3 Installing plug-ins

2.3 Post-installation procedures

2.3.1 Creating the HiRDB directory

2.3.2 Creating a work file output directory

2.3.3 Registering HiRDB and option program products in the operating system

2.3.4 Setting environment variables

2.3.5 Setting a remote shell execution environment

2.3.6 Notes about background execution of HiRDB operation commands

2.3.7 Preparing to create the HiRDB file system area

2.4 Uninstallation of HiRDB

3. Setting Up an Environment Using the Simple Setup Tool

3.1 Overview of the simple setup tool

4. Setting Up an Environment Using Commands

4.1 Overview of environment setup using commands

4.2 Creating the HiRDB system definitions

4.2.1 Creating HiRDB system definitions (HiRDB single server configuration)

4.2.2 Creating HiRDB system definitions (HiRDB parallel server configuration)

4.2.3 Sharing HiRDB system definition files (HiRDB parallel server configuration)

4.2.4 Modifying HiRDB system definitions (excluding UAP environment definitions)

4.2.5 Modifying a UAP environment definition

4.3 Creating HiRDB file system areas

4.3.1 Types of HiRDB file system areas

4.3.2 Using character special files

4.3.3 Creating a large file

4.3.4 Example 1 (creating a HiRDB file system area for RDAREAs)

4.3.5 Example 2 (creating a HiRDB file system area for system files)

4.3.6 Example 3 (creating a HiRDB file system area for work table files)

4.3.7 Example 4 (creating a HiRDB file system area for utilities)

4.3.8 Example 5 (creating a HiRDB file system area for list RDAREAs)

4.4 Creating system files

4.4.1 Creating system log files

4.4.2 Creating synchronization point dump files

4.4.3 Creating status files

4.4.4 Example of system file creation (HiRDB single server configuration)

4.4.5 Example of system file creation (HiRDB parallel server configuration)

4.5 Creating system RDAREAs

4.5.1 Basics

4.5.2 Example 1 (HiRDB single server configuration)

4.5.3 Example 2 (HiRDB parallel server configuration)

4.6 Starting HiRDB for the first time

4.7 Creating user RDAREAs

4.7.1 Basics

4.7.2 Example 1 (HiRDB single server configuration)

4.7.3 Example 2 (HiRDB parallel server configuration)

4.8 Creating user LOB RDAREAs

4.8.1 Basics

4.8.2 Example 1 (HiRDB single server configuration)

4.8.3 Example 2 (HiRDB parallel server configuration)

4.9 Creating data dictionary LOB RDAREAs

4.9.1 Basics

4.9.2 Example 1 (HiRDB single server configuration)

4.9.3 Example 2 (HiRDB parallel server configuration)

4.10 Creating list RDAREAs

4.10.1 Basics

4.10.2 Example 1 (HiRDB single server configuration)

4.10.3 Example 2 (HiRDB parallel server configuration)

5. Setting Up the Plug-in Environment

5.1 Overview of plug-in environment setup

5.1.1 Environment setup procedure

5.1.2 Notes on using plug-ins

5.2 Upgrading plug-ins

5.3 Deleting plug-ins

6. Creating Databases

6.1 Overview of database creation

6.1.1 Preparing for database creation

6.1.2 Database creation procedure

6.1.3 Database update log acquisition mode

6.1.4 Notes on data storage for a table for which an index with the unique attribute has been defined

6.1.5 Loading a large amount of data (data loading with the synchronization point specification)

6.1.6 Loading data into a row-partitioned table (using the parallel loading facility)

6.1.7 Loading data into a row-partitioned table (Creating divided-input data files)

6.1.8 Data loads that use the automatic numbering facility

6.1.9 Input data file UOC

6.1.10 Deleting unneeded RDAREAs

6.2 Creating a row-partitioned table

6.3 Creating a table with a LOB column

6.4 Creating a table containing a plug-in-provided abstract data type

6.4.1 The SGMLTEXT type

6.4.2 The XML type

6.5 Creating a table containing a user-defined abstract data type

6.5.1 Defining an abstract data type

6.5.2 Defining a table

6.5.3 Defining an index

6.5.4 Storing data in a table

6.5.5 Database update log acquisition methods

6.5.6 Checking the data storage status

6.6 Handling errors during batch index creation

6.6.1 When data was loaded in log acquisition mode or pre-update log acquisition mode

6.6.2 When data was loaded in no-log mode

6.7 Handling utility abnormal termination errors during data loading with the synchronization point specification

6.7.1 Overview of error handling procedure

6.7.2 Example

7. Linking to Other Products

7.1 Linking to the replication facility

7.1.1 Linking to HiRDB Datareplicator

7.1.2 Linking to HiRDB Dataextractor

7.2 Linking with an OLTP system

7.2.1 OLTP products supported for linking

7.2.2 HiRDB XA library

7.2.3 Example of HiRDB system configuration with OLTP linkage

7.2.4 Transaction transfer

7.2.5 Registering HiRDB in the transaction manager

7.2.6 Information to be registered in the transaction manager

7.2.7 Example of registering in the transaction manager

7.2.8 Modifying the registration information in the transaction manager

7.2.9 Methods for re-establishing connection between the transaction manager and HiRDB

7.2.10 Monitoring HiRDB using the TP1/Resource Manager Monitor facility

7.2.11 Notes

7.3 Linking to the inner replica facility

7.4 Linking to JP1

7.4.1 Reporting events to JP1/Base

7.4.2 Managing events by JP1/IM

7.4.3 Automatic job execution using JP1/AJS3 linkage

8. Designing a HiRDB single server configuration

8.1 System design for a HiRDB single server configuration

8.1.1 System design

8.1.2 System configuration

8.2 Designing HiRDB file system areas

8.2.1 Designing HiRDB file system areas for RDAREAs

8.2.2 Designing HiRDB file system areas for system files

8.2.3 Designing HiRDB file system areas for work table files

8.2.4 Designing HiRDB file system areas for utilities

8.2.5 Designing HiRDB file system areas for list RDAREAs

8.2.6 Maximum sizes of HiRDB file system areas

8.3 Designing system files

8.3.1 Designing system log files

8.3.2 Designing synchronization point dump files

8.3.3 Designing status files

8.4 Placing RDAREAs

8.4.1 Placing system RDAREAs

8.4.2 Placing data dictionary LOB RDAREAs

8.4.3 Placing user RDAREAs

8.4.4 Placing user LOB RDAREAs

8.4.5 Placing list RDAREAs

9. Designing a HiRDB parallel server configuration

9.1 System design for a HiRDB parallel server configuration

9.1.1 System design

9.1.2 System configuration of a HiRDB parallel server configuration

9.1.3 Setting up multiple front-end servers

9.1.4 Recovery-unnecessary front-end server

9.2 Designing HiRDB file system areas

9.2.1 Designing HiRDB file system areas for RDAREAs

9.2.2 Designing HiRDB file system areas for system files

9.2.3 Designing HiRDB file system areas for work table files

9.2.4 Designing HiRDB file system areas for utilities

9.2.5 Designing HiRDB file system areas for list RDAREAs

9.2.6 Maximum sizes of HiRDB file system areas

9.3 Designing system files

9.3.1 Designing system log files

9.3.2 Designing synchronization point dump files

9.3.3 Designing status files

9.4 Placing RDAREAs

9.4.1 Placing system RDAREAs

9.4.2 Placing data dictionary LOB RDAREAs

9.4.3 Placing user RDAREAs

9.4.4 Placing user LOB RDAREAs

9.4.5 Placing list RDAREAs

9.5 Considerations that apply to building a system with many units or servers

9.5.1 Considerations that apply to configuring systems

9.5.2 Considerations for system operation

9.5.3 Corrective actions to take in response to errors that occur when commands are executed

10. Designing a Multi-HiRDB

10.1 System design for a multi-HiRDB

10.1.1 Installing a multi-HiRDB

10.1.2 Setting the environment for a multi-HiRDB

10.2 Notes about upgrading

11. Designing Global Buffers and Local Buffers

11.1 Allocating global buffers

11.1.1 Allocating index global buffers

11.1.2 Allocating data global buffers

11.1.3 Allocating LOB global buffers

11.1.4 Global buffer allocation procedures

11.2 Setting the number of global buffer sectors

11.3 Specifying the prefetch facility

11.4 Specifying the asynchronous READ facility

11.5 Specifying deferred write processing

11.6 Specifying the facility for parallel writes in deferred write processing

11.7 Setting the commit-time reflection processing

11.8 Global buffer LRU management

11.8.1 LRU management methods

11.8.2 LRU management suppression settings for a UAP

11.8.3 Setting suppression of LRU management of binary data accessed by UAPs

11.9 Page access using the snapshot method

11.10 Global buffer pre-writing

11.11 Local buffers

11.11.1 Allocating index local buffers

11.11.2 Allocating data local buffers

11.11.3 Allocating local buffers

11.11.4 Considerations about local buffers

12. Designing Tables

12.1 Items to be examined during table design

12.2 Normalizing a table

12.3 Table row partitioning

12.3.1 Table row partitioning

12.3.2 Types of table row partitioning

12.3.3 Forms of table row partitioning

12.3.4 Effects of table row partitioning

12.3.5 Design considerations

12.3.6 Notes on table row partitioning

12.4 Table matrix partitioning

12.5 Defining a trigger

12.5.1 Application standards

12.5.2 Defining a trigger

12.5.3 Trigger considerations

12.5.4 Trigger management

12.5.5 Error recovery

12.6 Creating a view table

12.7 Specifying the FIX attribute

12.8 Specifying a primary key

12.9 Specifying a cluster key

12.10 Specifying the suppress option

12.11 Specifying the no-split option

12.12 Specifying a binary data column

12.12.1 BLOB type

12.12.2 BINARY type

12.12.3 BLOB type and BINARY type usage

12.13 Specifying a character set

12.14 Specifying the WITHOUT ROLLBACK option

12.15 Specifying the falsification prevention facility

12.15.1 Specification

12.15.2 Restrictions

12.15.3 Changing a falsification-unprevented table to a falsification prevented table

12.15.4 Error operation

12.16 Table containing a repetition column

12.17 Table containing an abstract data type

12.18 Shared tables

12.18.1 Effects and criteria

12.18.2 Definition method

12.18.3 Manipulating shared tables

12.18.4 Limitations on shared tables

12.18.5 Rules used to allocate back-end servers that search shared tables

12.18.6 Notes about execution of definition SQL statements, utilities, and operation commands

12.18.7 Using shared tables with a HiRDB single server configuration

12.19 Referential constraints

12.19.1 About referential constraints

12.19.2 Defining referential constraints

12.19.3 Check pending status

12.19.4 Data manipulation and integrity

12.19.5 Procedure for checking table integrity

12.19.6 Referential constraints and triggers

12.19.7 Notes about linkage with related products

12.20 Check constraints

12.20.1 About check constraints

12.20.2 Defining check constraints

12.20.3 Check pending status

12.20.4 Data manipulation and integrity

12.20.5 Procedure for checking table integrity

12.20.6 Notes about linkage with related products

12.20.7 Migrating check constraint tables to 64-bit mode

12.21 Compressed tables

12.21.1 Data compression facility

12.21.2 How data is compressed

12.21.3 How to define a compressed table

12.21.4 How to convert an existing table to a compressed table

12.21.5 How to change the definition of a compressed column (removing the compression specification for a column)

12.21.6 Notes about using compressed tables

12.21.7 How to measure the data compression rate

12.22 Temporary tables

12.22.1 Valid period of data in temporary tables

12.22.2 How to define temporary tables and temporary table indexes

12.22.3 Rules for choosing an RDAREA for storage

12.22.4 Processing when there are no available temporary table RDAREAs

12.22.5 Locking for temporary tables

12.22.6 Limitations on the use of temporary tables

13. Designing Indexes

13.1 Items to be examined during index design

13.2 Index

13.2.1 Creating an index

13.2.2 Index creation taking into account optimizing based on cost

13.2.3 Single-column index vs. multicolumn index

13.2.4 Using multiple indexes

13.2.5 Using an index with an exceptional key value set

13.2.6 Effects on performance of the number of indexes

13.3 Index row partitioning

13.4 Plug-in index

13.5 Plug-in index row partitioning

14. Designing RDAREAs

14.1 Items to be examined during RDAREA design

14.2 Segments

14.2.1 Determining the segment size

14.2.2 Setting the percentage of free pages in a segment

14.2.3 Allocating and releasing segments

14.3 Pages

14.3.1 Determining the page length

14.3.2 Setting the percentage of unused space in a page

14.3.3 Allocating and releasing pages

14.4 Designing list RDAREAs

14.5 Free space reusage facility

14.5.1 Data storage search modes

14.5.2 Free space reusage facility

14.5.3 Effects and applicability

14.5.4 Considerations

14.5.5 Environment settings

14.5.6 Checking execution status

14.5.7 Notes

14.6 Shared RDAREAs (HiRDB parallel server configuration only)

14.7 Temporary table RDAREAs

15. Storage Requirements for HiRDB

15.1 Estimating the memory size required for a HiRDB single server configuration

15.1.1 Memory allocation

15.1.2 Calculation of required memory

15.1.3 Formulas for shared memory used by a unit controller

15.1.4 Formulas for shared memory used by a single server

15.1.5 Formula for size of shared memory used by global buffers

15.1.6 Formulas for size of memory required during SQL execution

15.1.7 Formula for size of memory required during SQL preprocessing

15.1.8 Formula for size of memory required during BLOB data retrieval or updating (HiRDB single server configuration)

15.1.9 Formula for size of memory required during block transfer or array FETCH

15.1.10 Memory required by in-memory data processing

15.2 Estimating the memory size required for a HiRDB parallel server configuration

15.2.1 Memory allocation

15.2.2 Calculation of required memory

15.2.3 Formulas for shared memory used by a unit controller

15.2.4 Formulas for shared memory used by each server

15.2.5 Formula for size of shared memory used by global buffers

15.2.6 Formulas for size of memory required during SQL execution

15.2.7 Formula for size of memory required during SQL preprocessing

15.2.8 Formula for size of memory required during BLOB data retrieval or updating (front-end server)

15.2.9 Formula for size of memory required during BLOB data retrieval or updating (back-end server or dictionary server)

15.2.10 Formula for size of memory required during block transfer or array FETCH (front-end server)

15.2.11 Memory required by in-memory data processing

16. Determining RDAREA Size

16.1 Determining the size of a user RDAREA

16.1.1 Calculating the size of a user RDAREA

16.1.2 Calculating the number of table storage pages

16.1.3 Calculating the number of index storage pages

16.2 Determining the size of a data dictionary RDAREA

16.2.1 Determining the size of a normal data dictionary RDAREA

16.2.2 Determining the size of a data dictionary RDAREA for storing database state analyzed tables and database management tables

16.3 Determining the size of the master directory RDAREA

16.4 Determining the size of the data directory RDAREA

16.5 Determining the size of a data dictionary LOB RDAREA

16.6 Determining the size of a user LOB RDAREA

16.7 Determining the size of the registry RDAREA

16.8 Determining the size of the registry LOB RDAREA

16.9 Determining the size of the list RDAREA

17. Determining the Size of System Files and Audit Trail Files

17.1 Determining the size of system log files

17.1.1 Total size of system log files

17.1.2 Amount of system log information output during table definition

17.1.3 Amount of system log information output during index definition

17.1.4 Amount of system log information output during table data updating

17.1.5 Amount of system log information output during database creation by a utility

17.1.6 Amount of system log information that is output depending on the SQL manipulation

17.1.7 Amount of system log information that is output during the definition of extended system-defined scalar functions

17.1.8 Amount of system log information that is output during the execution of the RDAREA automatic extension facility

17.1.9 Amount of system log information output when the PURGE TABLE statement is executed

17.1.10 Amount of system log information output when the free page release utility (pdreclaim) is executed

17.1.11 Amount of system log information that is output during execution of the facility for predicting reorganization time

17.1.12 Amount of system log information output during an updatable backup hold

17.2 Determining the size of synchronization point dump files

17.3 Determining the size of status files

17.4 Determining audit trail file capacity

18. Determining Work Table File Size

18.1 Overview of work table files

18.2 Determining the size of a HiRDB file system area (pdfmkfs -n command)

18.2.1 Size of a work table file used by an SQL statement

18.2.2 Size of a work table file used by a utility

18.3 Determining the maximum number of files (pdfmkfs -l command)

18.4 Determining the maximum number of extensions (pdfmkfs -e command)

19. Storage Requirements for Utility Execution

19.1 Determining the file sizes required for utility execution

19.1.1 File sizes required for the execution of the database load utility (pdload)

19.1.2 File sizes required for the execution of the database reorganization utility (pdrorg)

19.1.3 File sizes required for the execution of the statistics analysis utility (pdstedit)

19.1.4 File sizes required for the execution of the database condition analysis utility (pddbst)

19.1.5 File sizes required for the execution of the database copy utility (pdcopy)

19.1.6 File sizes required for the execution of the dictionary import/export utility (pdexp)

19.1.7 File sizes required for the execution of the optimizing information collection utility (pdgetcst)

19.1.8 File sizes required for the execution of the access path display utility (pdvwopt)

19.1.9 File sizes required for execution of the rebalancing utility (pdrbal)

19.1.10 File sizes required for execution of the integrity check utility (pdconstck)

19.1.11 File sizes required for execution of parallel loading (pdparaload)

19.1.12 Buffer size used to determine the size of the work file for sorting

19.2 Determining the memory size required for utility execution

19.2.1 Memory size required for the execution of the database initialization utility (pdinit)

19.2.2 Memory size required for the execution of the database definition utility (pddef)

19.2.3 Memory size required for the execution of the database load utility (pdload)

19.2.4 Memory size required for the execution of the database reorganization utility (pdrorg)

19.2.5 Memory size required for the execution of the database structure modification utility (pdmod)

19.2.6 Memory size required for the execution of the statistics analysis utility (pdstedit)

19.2.7 Memory size required for the execution of the database condition analysis utility (pddbst)

19.2.8 Memory size required for the execution of optimizing the information collection utility (pdgetcst)

19.2.9 Memory size required for the execution of the database copy utility (pdcopy)

19.2.10 Memory size required for the execution of the database recovery utility (pdrstr)

19.2.11 Memory size required for the execution of the dictionary import/export utility (pdexp)

19.2.12 Memory size required for the execution of the access path display utility (pdvwopt)

19.2.13 Memory size required for the execution of the rebalancing utility (pdrbal)

19.2.14 Memory size required for execution of the free page release utility (pdreclaim) and global buffer residence utility (pdpgbfon)

19.2.15 Memory size required for execution of the integrity check utility (pdconstck)

19.2.16 Memory size required for the execution of parallel loading (pdparaload)

20. Specifying OS Parameters

20.1 Estimating HP-UX OS parameter values

20.2 Estimating Solaris OS parameter values

20.3 Estimating AIX OS parameter values

20.4 Estimating Linux kernel parameter values

20.5 Estimating the sizes of message queues and semaphores

20.6 Listen queue specified values

21. Sample Files

21.1 Overview of sample files

21.1.1 Names of sample files

21.2 System configuration and table definition information

21.3 Use of the sample files

21.3.1 Creating the configuration files

21.3.2 HiRDB file system area names and user-created file names used with sample database

22. Communication Between HiRDB Servers and HiRDB Clients

22.1 Connecting HiRDB clients to a HiRDB server

22.1.1 Connection to a HiRDB server with an FQDN specified

22.1.2 Using the multi-connection address facility to connect to a HiRDB server

22.2 Settings for a DNS server to manage IP addresses

22.3 Settings when a firewall and NAT are installed

22.3.1 When a firewall is installed on the HiRDB single server configuration side

22.3.2 When a firewall and NAT are installed on the HiRDB single server configuration side

22.3.3 When a firewall is installed on the HiRDB parallel server configuration side

22.3.4 When a firewall and NAT are installed on the HiRDB parallel server configuration side

22.4 Port numbers used by HiRDB

22.4.1 Estimating the number of ports that a unit will use

22.4.2 Notes

22.4.3 Calculation examples

22.4.4 Ways to avoid a shortage of ports

22.5 Port numbers specified in HiRDB

22.5.1 List of port numbers specified in HiRDB

22.5.2 Specifying port numbers

22.5.3 Notes on port number duplication

22.6 HiRDB reserved port facility

22.6.1 Estimation of the HiRDB reserved port facility

Appendixes

A. HiRDB Maximum and Minimum Values

A.1 Maximum and minimum values for the system configuration

A.2 Maximum and minimum values for databases

A.3 Maximum and minimum values for HiRDB file names

B. Processes Started by HiRDB

B.1 Processes started by a HiRDB single server configuration

B.2 Processes started by a HiRDB parallel server configuration

C. Questions and Answers

Index

[Trademarks]

All Rights Reserved. Copyright (C) 2012, 2015, Hitachi, Ltd.