For UNIX Systems

Nonstop Database

HiRDB Version 9

Installation and Design Guide

3000-6-452-50(E)


Contents

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