Nonstop Database HiRDB Version 9

For UNIX Systems

Nonstop Database

HiRDB Version 9

Command Reference

3000-6-455-50(E)

Contents

Notices

Preface

1. Overview of Commands

1.1 Overview

1.2 List of operation commands

1.3 List of utilities

1.4 Utility I/O files

1.4.1 Files and volumes

1.4.2 File modes usable by utilities

1.5 Notes on command execution

1.5.1 Forced termination by interruption of command execution

1.5.2 Specification of RDAREAs in operation commands and utilities

1.5.3 Rules for output of command execution results in DAT format

1.5.4 Specifying the LANG environment variable

1.5.5 Limitations when using the facility for simple authentication of OS login users

1.6 Functions that become available when optional HiRDB program products are installed

2. Operation Commands

2.1 pdacunlck (Release consecutive certification failure account lock state)

2.2 pdadmvr (Acquire HiRDB product information)

2.3 pdaudatld (Control the facility for automatically loading audit trail table data)

2.4 pdaudbegin (Start acquisition of audit trails)

2.5 pdaudend (Stop acquisition of audit trails)

2.6 pdaudrm (Delete audit trail files in shutdown status)

2.7 pdaudswap (Swap audit trail files)

2.8 pdbkupls (Display backup file information)

2.9 pdbufls (Display global buffer information)

2.10 pdbufmod (Change global buffer dynamically)

2.11 pdcancel (Forcibly terminate UAP or utility processing)

2.12 pdcat (Display messages in file)

2.13 pdcat [-d sts specified]

2.14 pdcat [-d option omitted]

2.15 pdchgconf (Reconfigure the system)

2.16 pdchprc (Change number of startup server processes)

2.17 pdclibsync (Manipulate C library files)

2.18 pdclose (Close RDAREAs)

2.19 pdclttrc (Acquire SQL trace dynamically)

2.20 pdcmt (Commit transactions)

2.21 pdconfchk (Check system definitions)

2.22 pdcspool (Delete troubleshooting information)

2.23 pddbchg (Change the replica status of replica RDAREAs)

2.24 pddbfrz (Place a full HiRDB file in a user LOB RDAREA in frozen update status)

2.25 pddbls (Display status of RDAREAs)

2.26 pddefrev (Generate definition SQL)

2.27 pddivinfgt (Acquire and output table partitioning conditions)

2.28 pdextfunc (Define or delete the extended system-defined scalar function)

2.29 pdfbkup (Back up HiRDB file system)

2.30 pdfchfs (Modify management information for a HiRDB file system area)

2.31 pdffsck ( (Check and repair the integrity of a HiRDB file system area)

2.32 pdfgt (Terminate transaction forcibly)

2.33 pdfls (Display HiRDB file system area information)

2.34 pdfmkfs (Initialize a HiRDB file system area)

2.35 pdfrm (Delete HiRDB file)

2.36 pdfrstr (Restore HiRDB file system)

2.37 pdfstatfs (Display status of HiRDB file system area)

2.38 pdfzeroinit (Initialize used area in a HiRDB file system area)

2.39 pdgeter (Acquire error information)

2.40 pdhold (Shut down RDAREAs)

2.41 pdinfoget (Acquire error information and estimate its volume)

2.42 pditvstop (Stop periodic acquisition of HiRDB status)

2.43 pditvtrc (Periodically acquire the HiRDB status)

2.44 pdjarsync (Manipulate JAR files)

2.45 pdlistls (Display list definition information)

2.46 pdlodsv (Reduce the size of the installation directory)

2.47 pdlogadpf (Allocate log file)

2.48 pdlogatul (Control the automatic log unloading facility)

2.49 pdlogchg (Change status of log file)

2.50 pdlogcls (Close open log file)

2.51 pdloginit (Initialize log file)

2.52 pdlogls (Display log-related file information)

2.53 pdlogls [-d spd] (Display synchronization point dump file information)

2.54 pdlogls [-d sys] (Display system log file information)

2.55 pdlogopen (Open log file)

2.56 pdlogrm (Delete log file)

2.57 pdlogswap (Swap log files)

2.58 pdlogsync (Collect a synchronization point dump)

2.59 pdlogucat (Display unload log file information)

2.60 pdlogunld (Unload log file)

2.61 pdls (Display HiRDB system status)

2.62 pdls [-d act] (Display user status)

2.63 pdls [-d aud] (Display the status of audit trail files)

2.64 pdls [-d ha] (Display HiRDB system status)

2.65 pdls [-d lck] (Display server lock control status)

2.66 pdls [-d mem] (Display server shared memory status)

2.67 pdls [-d org] (Display progress of reflection processing for online reorganization)

2.68 pdls [-d prc] (Display server process status)

2.69 pdls [-d ris] (Display log application processing execution status)

2.70 pdls [-d rpc] (Display server communication control information)

2.71 pdls [-d rpl] (Display HiRDB Datareplicator linkage status)

2.72 pdls [-d scd] (Display server schedule status)

2.73 pdls [-d stj] (Display whether statistical data output type by unit and server is specified)

2.74 pdls [-d sts] (Display status file status)

2.75 pdls [-d svr] (Display status by unit and server)

2.76 pdls [-d trn] (Display server transaction status)

2.77 pdls [-d ust] (Display unit activity status)

2.78 pdmemdb (In-memory data processing operations)

2.79 pdmemsv (Save memory space)

2.80 pdobils (Display SQL object buffer statistics)

2.81 pdobjconv (Convert SQL objects for 64-bit-mode HiRDB)

2.82 pdopen (Open RDAREAs)

2.83 pdopsetup (Set up add-on PP)

2.84 pdorbegin (Commit a database for online reorganization)

2.85 pdorcheck (Check the application conditions for online reorganization)

2.86 pdorchg (Change the current RDAREAs for online reorganization)

2.87 pdorcreate (Create an environment for reflection processing for online reorganization)

2.88 pdorend (Execute reflection processing for online reorganization)

2.89 pdparaload (Perform parallel loading)

2.90 pdpfresh (Refresh server process)

2.91 pdplgrgst (Register and delete plug-ins)

2.92 pdplgset (Set up a plug-in)

2.93 pdprfed (Edit and output performance trace information)

2.94 pdprflevel (Display or change the performance trace collection level)

2.95 pdprgcopy (Copy a HiRDB update version)

2.96 pdprgrenew (Update a HiRDB update version)

2.97 pdrbk (Roll back transactions)

2.98 pdrdrefls (Display information about related RDAREAs)

2.99 pdrels (Release RDAREAs from shutdown status)

2.100 pdrisechk (Check the configuration of Real Time SAN Replication)

2.101 pdrisedbto (Inherit a database in Real Time SAN Replication)

2.102 pdriseset (Set the site status in Real Time SAN Replication)

2.103 pdrpause (Restart process server process)

2.104 pdrplstart (Start HiRDB Datareplicator linkage)

2.105 pdrplstop (Stop HiRDB Datareplicator linkage)

2.106 pdsetenv (Set up an environment for a HiRDB unit)

2.107 pdsetup (Register or delete HiRDB system in OS)

2.108 pdstart (Start HiRDB system, unit, or server)

2.109 pdstbegin (Start output of statistical information)

2.110 pdstend (Stop output of statistical information)

2.111 pdstjswap (Swap statistics log files)

2.112 pdstjsync (Apply statistics log buffer to statistics log file)

2.113 pdstop (Terminate HiRDB system, unit, or server)

2.114 pdstscls (Close open status file)

2.115 pdstsinit (Initialize status file)

2.116 pdstsopen (Open status file)

2.117 pdstsrm (Delete status file)

2.118 pdstsswap (Swap status files)

2.119 pdsvhostname (Display server's host name)

2.120 pdtrndec (Settle unsettled transactions forcibly and automatically)

2.121 pdtrnqing (Start or release the transaction queuing facility)

2.121.1 When executing the pdtrnqing command in a HiRDB single server configuration or on a front-end server unit

2.121.2 When executing the pdtrnqing command while using the standby-less system switchover (effects distributed) facility

2.122 pdvrup (Upgrade HiRDB version)

3. Database Initialization Utility (pdinit)

3.3 Control statement

3.3.1 define system statement

3.3.2 create rdarea statement

3.3.3 Notes

3.4 Rules and notes

3.5 Examples

4. Database Definition Utility (pddef)

4.3 Handling of continuation lines

4.4 Rules and notes

4.5 Examples

5. Database Load Utility (pdload)

5.1 Function

5.1.1 What is data loading?

5.1.2 Loading data to a table with LOB columns

5.1.3 Loading data to a table with abstract data type columns (LOB attribute) provided by a plug-in

5.1.4 Data loading to a table for which a character set has been defined

5.1.5 Data loading using the automatic numbering facility

5.1.6 Data loading using a utility special unit

5.1.7 Data loading with the synchronization point specification

5.1.8 Data migration between platforms that use different endians

5.1.9 RDAREAs containing a table subject to data loading

5.1.10 Log acquisition mode during execution of pdload

5.1.11 Executor

5.2 Typical examples of data loading

5.2.1 Loading data to a table with indexes defined

5.2.2 Loading data to a table with LOB columns

5.2.3 Loading data to a table with abstract data type columns provided by a plug-in

5.3 List of references by purpose

5.3.1 Required information

5.3.2 Information to be specified depending on the attribute of the table subject to data loading

5.3.3 Information to be specified depending on the type of input data file

5.3.4 Information to be specified depending on the data loading method

5.4 Command format

5.4.1 Format

5.4.2 Options

5.4.3 mtguide and emtdef statements (specification of MT information)

5.4.4 source statement (specification of input data file information)

5.4.5 index statement (specification of index information file information)

5.4.6 idxwork statement (specification of index information file directory)

5.4.7 sort statement (specification of sort work directory information)

5.4.8 lobdata statement (specification of LOB input file information)

5.4.9 lobcolumn statement (specification of LOB column input file information)

5.4.10 lobmid statement (specification of LOB middle file information)

5.4.11 srcuoc statement (specification of UOC storage library information)

5.4.12 array statement (specification of an array data format for a table containing repetition columns)

5.4.13 extdat statement (specification of information in the extended DAT format)

5.4.14 src_work statement (specification of the output destination of divided-input data files)

5.4.15 constraint statement (specification of check pending status)

5.4.16 option statement (specification of data processing information)

5.5 Input data file

5.5.1 DAT format

5.5.2 Binary format

5.5.3 Fixed-size data format

5.5.4 Arrayed data format for tables containing repetition columns

5.5.5 LOB column input file

5.5.6 pdrorg-output binary files

5.6 Error information file

5.6.1 Contents of error information file

5.6.2 Output example of error information file

5.6.3 Notes about referencing error information

5.6.4 Rules for output when the errdata operand is specified in a source statement

5.7 Column structure information file

5.7.1 column-name statement

5.7.2 skipdata statement

5.7.3 Add data statement

5.7.4 Specification examples

5.8 Null value/function information file

5.8.1 Column number statement

5.9 Processing performance information file

5.10 Null and default values during data loading

5.11 Using a UOC to load a table

5.11.1 Overview

5.11.2 UOC interface

5.11.3 Examples of UOCs

5.11.4 Creating a shared library

5.12 Rules and notes

5.13 Database status in the event of an error and recovery methods

5.14 Examples

5.14.1 HiRDB single server configuration

5.14.2 HiRDB parallel server configuration

6. Interactive SQL Execution Utility (pdsql)

6.1 Overview

6.1.1 Function

7. Database Structure Modification Utility (pdmod)

7.1 Functions of the database structure modification utility

7.3.3 Control statement (create rdarea statement)

7.3.4 Notes

7.4 Expanding an RDAREA

7.4.1 Function

7.4.2 Examples

7.4.3 Control statement (expand rdarea statement)

7.4.4 Notes

7.5 Reinitializing an RDAREA

7.5.1 Function

7.5.2 Examples

7.5.3 Control statement (initialize rdarea statement)

7.5.4 Table and index status after reinitialization

7.5.5 Output of execution results listing

7.5.6 Notes

7.6 Deleting an RDAREA

7.6.1 Function

7.6.2 Example

7.6.3 Control statement (remove rdarea statement)

7.7.3 Control statement (move rdarea statement)

7.7.4 Output of execution results listing

7.7.5 Notes

7.8 Modifying data dictionary table attribute definitions

7.8.1 Function

7.8.2 Example

7.8.3 Control statement (alter system statement)

7.8.4 Note

7.9 Modifying RDAREA attributes

7.9.1 Function

7.9.2 Example

7.9.3 Control statement (alter rdarea statement)

7.10 Changing an RDAREA from a HiRDB single server configuration to a HiRDB parallel server configuration

7.10.1 Function

7.10.2 Example

7.10.3 Control statement (alter HiRDB mode to parallel statement)

7.10.4 Notes

7.11 Registering generation information for a HiRDB file system area (inner replica facility)

7.11.1 Function

7.11.2 Example

7.11.3 Control statement (create generation statement)

7.11.4 Notes

7.12 Deleting generation information for a HiRDB file system area (inner replica facility)

7.12.1 Function

7.12.2 Example

7.12.3 Control statement (remove generation statement)

7.12.4 Notes

7.13 RDAREA replica definition (inner replica facility)

7.13.1 Function

7.13.2 Example

7.13.3 Control statement (replicate rdarea statement)

7.13.4 Notes

7.14 Copying RDAREA configuration information (inner replica facility)

7.14.1 Function

7.14.2 Example

7.14.3 Control statement (define copy rdarea statement)

7.14.4 Notes

7.15 Integrating RDAREAs (inner replica facility)

7.15.1 Function

7.15.2 Example

7.15.3 Control statement (recast rdarea statement)

7.15.4 Notes

7.16 Registering an auditor (security audit facility)

7.16.1 Function

7.16.2 Example

7.16.3 Control statement (create auditor statement)

7.16.4 Notes

7.17 Creating an audit trail table (security audit facility)

7.17.1 Function

7.17.2 Example

7.17.3 Control statement (create audit table statement)

7.18 Rules and notes

8. Database Reorganization Utility (pdrorg)

8.1 Overview

8.1.1 Functions of pdrorg

8.1.2 Reorganizing a table

8.1.3 Unloading a table

8.1.4 Reloading to a table

8.1.5 Reorganizing a dictionary table

8.1.6 Creating indexes in batch mode

8.1.7 Re-creating an index

8.1.8 Reorganizing an index

8.1.9 Log acquisition mode during execution of pdrorg

8.1.10 RDAREAs containing a table and indexes being processed

8.1.11 Whether execution of pdrorg is permitted on a table containing columns of an abstract data type

8.1.12 Executor

8.2 Reorganizing a table

8.2.1 Examples

8.2.2 Cross-reference by purpose

8.3 Unloading a table

8.3.1 Examples

8.3.2 Cross-reference by purpose

8.3.3 Format of database load utility input files

8.4 Reloading to a table

8.4.1 Examples

8.4.2 Cross-reference by purpose

8.4.3 Whether reloading is permitted when the table definitions of unload table and reload table do not match

8.4.4 Handling of a reloading error during table reorganization

8.5 Reorganizing data dictionary tables

8.5.1 Example

8.5.2 Cross-reference by purpose

8.6 Creating indexes in batch mode

8.6.1 Examples

8.6.2 Cross-reference by purpose

8.7 Re-creating indexes

8.7.1 Examples

8.7.2 Cross-reference by purpose

8.8 Reorganizing indexes

8.8.1 Example

8.8.2 Cross-reference by purpose

8.9 Command format

8.9.1 Format

8.9.2 Options

8.9.3 mtguide and emtdef statements (specification of MT information)

8.9.4 unload statement (specification of unload data file)

8.9.5 index statement (specification of index information file information)

8.9.6 idxname statement (specification of index information)

8.9.7 idxwork statement (specification of directory for index information file)

8.9.8 sort statement (specification of work directory for sorting)

8.9.9 lobunld statement (specification of LOB data unload file)

8.9.10 unlduoc statement (specification of UOC storage library information)

8.9.11 tblname statement (re-registration of table data to another table)

8.9.12 array statement (specification of row data output format for repetition columns)

8.9.13 unld_func statement (specification of constructor parameter reverse creation function)

8.9.14 reld_func statement (specification of constructor function)

8.9.15 constraint statement (specification of check pending status)

8.9.16 option statement (specification of data processing information)

8.9.17 blobtovarchar statement (specification of column subject to data conversion)

8.9.18 fixtext_option statement (specification of output data during creation of input data file in fixed-size data format)

8.10 Processing performance information file

8.11 Reorganization using a UOC

8.11.1 How to use a UOC during reorganization

8.11.2 Relationships between options and control statements

8.11.3 Overview of UOC processing

8.11.4 UOC interface

8.11.5 Example of a UOC

8.12 Notes on executing pdrorg on special tables

8.12.1 Table with an abstract data type

8.12.2 Falsification prevented table

8.13 Rules and notes

8.14 Database status in the event of an error and recovery method

8.14.1 Error when unloading

8.14.2 Error when reloading

8.14.3 Error when creating an index in batch mode or re-creating an index

8.14.4 Error when reorganizing an index

8.14.5 Error when unloading LOB data

8.14.6 Error when reloading LOB data

9. Dictionary Import/Export Utility (pdexp)

9.1 Overview

9.1.1 Table definition information import/export

9.1.2 Trigger definition information import/export

9.1.3 Stored procedure information import/export

9.1.4 Generation of a definition SQL

10. Rebalancing Utility (pdrbal)

10.1 Overview

10.1.1 Functions of the rebalancing utility

10.1.2 Operation modes of the rebalancing utility

10.1.3 Execution environment

10.3.3 index statement (specification of index information file information)

10.3.4 idxwork statement (specification of directory for index information files)

10.3.5 sort statement (specification of work directory for sorting)

10.3.6 execstop statement (specification of pdrbal execution time)

10.3.7 unld_func statement (specification of constructor parameter reverse creation function)

10.3.8 reld_func statement (specification of constructor function)

10.3.9 report statement (specification of a process results file)

10.3.10 option statement (specification of optional functions)

10.4 Notes about the execution of SQL on FIX hash-partitioned tables during a rebalance operation

10.5 Whether or not SQL is executable during a rebalance operation

10.6 Notes

10.7 Error handling procedures

11. Free Page Release Utility (pdreclaim)

11.1 Overview

11.1.1 About free pages

11.1.2 Overview of pdreclaim

11.1.3 Functions of pdreclaim

11.1.4 Difference in functions from pdrorg

11.1.5 Execution environment

11.1.6 Executor

11.1.7 Concurrent execution of pdreclaim and other functions

11.3.3 idxname statement (specification of index information)

11.3.4 option statement (specification of optional information)

11.4 pdreclaim processing results

11.5 Notes

12. Global Buffer Residence Utility (pdpgbfon)

12.1 Overview

12.1.1 Overview of pdpgbfon

12.1.2 Functions of pdpgbfon

12.1.3 Execution environment

12.1.4 Executor

12.1.5 Concurrent execution of pdpgbfon and other functions

12.3.3 idxname statement (specification of index information)

12.3.4 option statement (specification of optional information)

12.4 pdpgbfon processing results

12.5 Notes

13. Integrity Check Utility (pdconstck)

13.1 Overview

13.2 Functions of pdconstck

13.2.1 Integrity check facility

13.2.2 Facility for changing check pending status forcibly

13.5 pdconstck processing results

13.6 Notes

14. Statistics Analysis Utility (pdstedit)

14.2.3 Notes on option specifications

14.3 Details about statistical information

14.3.1 Output formats of statistical information

14.3.2 System activity statistical information

14.3.3 UAP statistical information

14.3.4 SQL statistical information

14.3.5 Global buffer pool statistical information

14.3.6 Statistical information about HiRDB files for database manipulation

14.3.7 Deferred write processing statistical information

14.3.8 Index statistical information

14.4 Output of statistical information to a DAT-format file

14.4.1 Data storage format of a DAT-format file

14.4.2 DAT-format file unit

14.4.3 Record formats of DAT-format files

14.5 Notes

14.6 Examples

15. Database Condition Analysis Utility (pddbst)

15.1 Overview

15.1.1 Database condition analysis facility

15.1.2 Condition analysis result accumulation facility

15.1.3 Facility for predicting reorganization time

15.2 Command format

15.2.1 Format

15.2.2 Options

15.2.3 predict statement

15.2.4 workdir statement

15.2.5 option statement

15.3 Output format of the database condition analysis facility

15.3.1 Condition analysis by RDAREA (logical analysis)

15.3.2 Condition analysis by RDAREA (physical analysis)

15.3.3 Condition analysis by table or index

15.3.4 Cluster key and clustering data page storage condition analysis

15.4 Execution results of the facility for predicting reorganization time

15.4.1 Output format of the execution results

15.4.2 Output format in CSV format

15.5 Standard value definition file (facility for predicting reorganization time)

15.6 Executing the condition analysis result accumulation facility in prediction level 2

15.6.1 Interval analysis

15.6.2 Merge analysis

15.6.3 Selection criteria

15.7 Rules and notes

15.8 Examples

16. Optimizing Information Collection Utility (pdgetcst)

16.1 Overview

16.1.1 Collecting optimization information by retrieval

16.1.2 Registering optimization information from an optimization information parameter file

16.1.3 Saving optimization information in an optimization information parameter file

16.1.4 Maximum number of concurrently executable utilities

16.2 Command format

16.2.1 Format

16.2.2 Options

16.3 Optimizing information

16.3.1 Whether optimization information is acquired

16.3.2 Optimization information to be collected

16.3.3 Optimizing information collection levels

16.4 Optimization information parameter file

16.4.1 Items in the optimization information parameter file

16.4.2 Specifications in the optimization information parameter file

16.4.3 Items output to the optimization information parameter file

16.4.4 Example of the optimization information parameter file

16.4.5 Specification rules for the optimization information parameter file

16.5 Rules and notes

16.6 Output format

16.7 Examples

17. Access Path Display Utility (pdvwopt)

17.3 Execution procedure

17.4 Rules and notes

17.5 Output format

17.5.1 Set operation information

17.5.2 Query processing information

17.5.3 Join processing information

17.5.4 Base table retrieval processing information

17.5.5 Types of join methods

17.5.6 Types of retrieval methods

17.5.7 Types of transfer methods

17.5.8 Types of access paths

17.5.9 Types of grouping processing methods

17.5.10 Types of plug-in-provided functions

17.5.11 Search conditions

17.5.12 Key conditions

17.5.13 Types of ORDER BY processing methods

17.6 Concept of tuning

17.6.1 Examples of tuning procedure

17.6.2 Notes about index definitions

17.6.3 Notes about index retrieval

17.6.4 Notes about join retrieval

18. Database Copy Utility (pdcopy)

18.1 Functions of the database copy utility

18.2 Information required for making backup copies

18.3 Examples

18.3.1 Backing up in units of systems

18.3.2 Backing up in units of RDAREAs

18.3.3 Backing up in units of servers

18.3.4 Backing up to files on DAT

18.3.5 Backing up using the inner replica facility and specifying an RDAREA generation

18.3.6 Backing up using the differential backup facility

18.3.7 Backing up using NetBackup

18.6 Database copy utility process results listing

18.7 Backup file format

19. Database Recovery Utility (pdrstr)

19.1 Functions of the database recovery utility

19.1.1 Database recovery

19.1.2 Re-creating a log point information file

19.2 Information required for recovering databases

19.3 Examples

19.3.1 Restoring in units of systems

19.3.2 Restoring in units of RDAREAs

19.3.3 Restoring in units of servers

19.3.4 Restoring from a backup using the differential backup facility

19.3.5 Restoring a specified RDAREA generation when the inner replica facility is used

19.3.6 Restoring RDAREAs from their backup using NetBackup

19.3.7 Re-creating the log point information file

19.6 Database recovery utility process results listing

20. Registry Facility Initialization Utility (pdreginit)

20.3 Control statements

20.3.1 create rdarea statement

20.3.2 Notes

20.4 Rules and notes

20.5 Examples

21. Audit Log Output Utility for JP1/NETM/Audit (pdaudput)

21.1 Overview

Appendixes

A. List of Commands

B. Lock Mode During Command Execution

B.1 Lock mode for operation commands

B.2 Lock mode for utilities

C. RDAREA Status During Command Execution

C.1 RDAREA status transitions

C.2 Availability of utility or UAP execution depending on RDAREA status

C.3 Whether or not the check pending status can be set

D. Maximum Number of Concurrently Executable Utilities

E. Creation of Input Data Files for the Database Load Utility

E.1 Creating an input file on fixed-length blocked tape

E.2 Creating an input file on variable-length blocked tape

E.3 Creating an input file on EasyMT tape

F. Creation of a UOC for Use by pdload and pdrorg

F.1 Creating a shared library

F.2 Notes on UOC creation

G. Number of Concurrent Command Connections

H. List of Command Return Codes

I. List of Files Output by Commands

J. Options and Control Statements Whose Default Value Has Changed or that Do Not Need To Be Specified Any Longer After Upgrading

Index