Preface

This manual explains the syntax of SQL statements used to manipulate the databases that you manage with the HiRDB Version 9 Nonstop Database program product.

Intended readers

This manual is intended for users who will be using HiRDB Version 9 (referred to hereafter as HiRDB) to design and create tables or to create and execute UAPs.

It is assumed that readers of this manual have the following:

This manual is based on the following manuals, which we recommend you read before reading this manual:

Organization of this manual

This manual consists of the following chapters and appendixes:

1. Basics
Chapter 1 explains the basics of SQL.
2. Details of Constituent Elements
Chapter 2 explains in detail the constituent elements of SQL.
3. Definition SQL
Chapter 3 explains the syntax and structure of the definition SQL.
4. Data Manipulation SQL
Chapter 4 explains the syntax and structure of the data manipulation SQL.
5. Control SQL
Chapter 5 explains the syntax and structure of the control SQL.
6. Embedded Language Syntax
Chapter 6 explains the syntax and structure of the embedded language.
7. Routine Control SQL
Chapter 7 explains the syntax and structure of the routine control SQL.
A. Reserved Words
Appendix A provides lists of the HiRDB and SQL reserved words.
B. List of SQLs
Appendix B provides a list of the SQL statements.
C. Example Database
Appendix C explains the example database used in this manual.

Related publications

This manual is related to the following manuals, which should be read as required.

HiRDB (for UNIX)
HiRDB (for both Windows and UNIX)

In references to HiRDB Version 9 manuals, this manual omits the phrases for UNIX systems and for Windows systems. Refer to either the UNIX or Windows HiRDB manual, whichever is appropriate for your platform.

For related products

#: This manual has been published in Japanese only; it is not available in English.

Organization of HiRDB manuals

The HiRDB manuals are organized as shown below. For the most efficient use of these manuals, it is suggested that they be read in the order they are shown, going from left to right.

[Figure]

[Figure]

Conventions: Abbreviations for product names

This manual uses the following abbreviations for product names:

Full name or meaningAbbreviation
HiRDB Server Version 9HiRDB/Single ServerHiRDB or HiRDB Server
HiRDB/Parallel Server
HiRDB/Developer's Kit Version 9HiRDB/Developer's KitHiRDB Client
HiRDB/Developer's Kit Version 9 (64)
HiRDB/Run Time Version 9HiRDB/Run Time
HiRDB/Run Time Version 9 (64)
HiRDB Advanced High Availability Version 9HiRDB Advanced High Availability
HiRDB Accelerator Version 8HiRDB Accelerator
HiRDB Accelerator Version 9
HiRDB Non Recover Front End Server Version 9HiRDB Non Recover FES
HiRDB Staticizer Option Version 9HiRDB Staticizer Option
HiRDB Disaster Recovery Light Edition Version 9HiRDB Disaster Recovery Light Edition
HiRDB Text Search Plug-in Version 9HiRDB Text Search Plug-in
HiRDB XML Extension Version 9HiRDB XML Extension
HiRDB Datareplicator Version 8HiRDB Datareplicator
HiRDB Dataextractor Version 8HiRDB Dataextractor
HiRDB Adapter for XML - Standard EditionHiRDB Adapter for XML
HiRDB Adapter for XML - Enterprise Edition
HiRDB Control ManagerHiRDB CM
HiRDB Control Manager AgentHiRDB CM Agent
Hitachi TrueCopyTrueCopy
Hitachi TrueCopy basic
TrueCopy
TrueCopy remote replicator
JP1/Automatic Job Management System 3JP1/AJS3
JP1/Automatic Job Management System 2
JP1/Automatic Job Management System 2 - Scenario OperationJP1/AJS2-SO
JP1/Cm2/Extensible SNMP AgentJP1/ESA
JP1/Cm2/Extensible SNMP Agent for Mib Runtime
JP1/Cm2/Network Node ManagerJP1/NNM
JP1/Integrated Management - ManagerJP1/Integrated Management or JP1/IM
JP1/Integrated Management - View
JP1/Magnetic Tape AccessEasyMT
EasyMT
JP1/Magnetic Tape LibraryMTguide
JP1/NETM/Audit - ManagerJP1/NETM/Audit
JP1/NETM/DMJP1/NETM/DM
JP1/NETM/DM Manager
JP1/Performance ManagementJP1/PFM
JP1/Performance Management - Agent Option for HiRDBJP1/PFM-Agent for HiRDB
JP1/Performance Management - Agent Option for PlatformJP1/PFM-Agent for Platform
JP1/Performance Management/SNMP System ObserverJP1/SSO
JP1/VERITAS NetBackup BS v4.5NetBackup
JP1/VERITAS NetBackup v4.5
JP1/VERITAS NetBackup BS V4.5 Agent for HiRDB LicenseJP1/VERITAS NetBackup Agent for HiRDB License
JP1/VERITAS NetBackup V4.5 Agent for HiRDB License
JP1/VERITAS NetBackup 5 Agent for HiRDB License
OpenTP1/Server Base Enterprise OptionTP1/EE
Virtual-storage Operating System 3/Forefront System ProductVOS3/FSVOS3
Virtual-storage Operating System 3/Leading System ProductVOS3/LS
Virtual-storage Operating System 3/Unific System ProductVOS3/US
Extensible Data Manager/Base Extended Version 2
XDM Basic Program XDM/BASE E2
XDM/BASE E2
XDM/Data Communication and Control Manager 3
XDM Data Communication Management System XDM/DCCM3
XDM/DCCM3
XDM/Relational Database
Relational Database System XDM/RD
XDM/RDXDM/RD
XDM/Relational Database Extended Version 2
Relational Database System XDM/RD E2
XDM/RD E2
VOS3 Database Connection ServerDB Connection Server
Oracle WebLogic ServerWebLogic Server
DB2 Universal Database for OS/390 Version 6DB2
DNCWARE ClusterPerfect (Linux Edition)ClusterPerfect
JavaTMJava
Microsoft(R) Office ExcelMicrosoft Excel or Excel
Microsoft(R) Visual C++(R)Visual C++ or C++ language
PowerHA for AIX, V5.5PowerHA
PowerHA SystemMirror V6.1
HP-UX 11i V2 (IPF)HP-UX or HP-UX (IPF)
HP-UX 11i V3 (IPF)
AIX 5L V5.2AIX 5LAIX
AIX 5L V5.3
AIX V6.1AIX V6.1
Linux(R)Linux
Red Hat Enterprise Linux AS 4 (AMD64 & Intel EM64T)Linux AS 4Linux
Red Hat Enterprise Linux AS 4 (x86)
Red Hat Enterprise Linux ES 4 (AMD64 & Intel EM64T)Linux ES 4
Red Hat Enterprise Linux ES 4 (x86)
Red Hat Enterprise Linux 5.1 Advanced Platform (x86)Linux 5.1
Red Hat Enterprise Linux 5.1 (x86)
Red Hat Enterprise Linux 5.1 Advanced Platform (AMD/Intel 64)
Red Hat Enterprise Linux 5.1 (AMD/Intel 64)
Red Hat Enterprise Linux 5.2 Advanced Platform (AMD/Intel 64)Linux 5.2
Red Hat Enterprise Linux 5.2 (AMD/Intel 64)
Red Hat Enterprise Linux 5.3 Advanced Platform (AMD/Intel 64)Linux 5.3
Red Hat Enterprise Linux 5.3 (AMD/Intel 64)
Red Hat Enterprise Linux 5.4 Advanced Platform (AMD/Intel 64)Linux 5.4
Red Hat Enterprise Linux 5.4 (AMD/Intel 64)
Red Hat Enterprise Linux AS 4 (AMD64 & Intel EM64T)Linux (EM64T)
Red Hat Enterprise Linux ES 4 (AMD64 & Intel EM64T)
Red Hat Enterprise Linux 5.1 Advanced Platform (AMD/Intel 64)
Red Hat Enterprise Linux 5.1 (AMD/Intel 64)
Red Hat Enterprise Linux 5.2 Advanced Platform (AMD/Intel 64)
Red Hat Enterprise Linux 5.2 (AMD/Intel 64)
Red Hat Enterprise Linux 5.3 Advanced Platform (AMD/Intel 64)
Red Hat Enterprise Linux 5.3 (AMD/Intel 64)
Red Hat Enterprise Linux 5.4 Advanced Platform (AMD/Intel 64)
Red Hat Enterprise Linux 5.4 (AMD/Intel 64)
Red Hat Enterprise Linux 5.1 Advanced Platform (x86)Linux 5 (x86)Linux 5
Red Hat Enterprise Linux 5.1 (x86)
Red Hat Enterprise Linux 5.1 Advanced Platform (AMD/Intel 64)Linux 5 (AMD/Intel 64)
Red Hat Enterprise Linux 5.1 (AMD/Intel 64)
Red Hat Enterprise Linux 5.2 Advanced Platform (AMD/Intel 64)
Red Hat Enterprise Linux 5.2 (AMD/Intel 64)
Red Hat Enterprise Linux 5.3 Advanced Platform (AMD/Intel 64)
Red Hat Enterprise Linux 5.3 (AMD/Intel 64)
Red Hat Enterprise Linux 5.4 Advanced Platform (AMD/Intel 64)
Red Hat Enterprise Linux 5.4 (AMD/Intel 64)
turbolinux 7 Server for AP8000Linux for AP8000
Microsoft(R) Windows NT(R) Workstation Operating System Version 4.0Windows NT
Microsoft(R) Windows NT(R) Server Network Operating System Version 4.0
Microsoft(R) Windows(R) 2000 Professional Operating SystemWindows 2000
Microsoft(R) Windows(R) 2000 Server Operating System
Microsoft(R) Windows(R) 2000 Datacenter Server Operating System
Microsoft(R) Windows(R) 2000 Advanced Server Operating System
Microsoft(R) Windows(R) 2000 Advanced Server Operating SystemWindows 2000 Advanced Server
Microsoft(R) Windows Server(R) 2003, Standard EditionWindows Server 2003 Standard EditionWindows Server 2003
Microsoft(R) Windows Server(R) 2003, Enterprise EditionWindows Server 2003 Enterprise Edition
Microsoft(R) Windows Server(R) 2003, Standard x64 EditionWindows Server 2003 Standard x64 Edition
Microsoft(R) Windows Server(R) 2003, Enterprise x64 EditionWindows Server 2003 Enterprise x64 Edition
Microsoft(R) Windows Server(R) 2003 R2, Standard EditionWindows Server 2003 R2
Microsoft(R) Windows Server(R) 2003 R2, Enterprise Edition
Microsoft(R) Windows Server(R) 2003 R2, Standard x64 Edition
Microsoft(R) Windows Server(R) 2003 R2, Enterprise x64 Edition
Microsoft(R) Windows Server(R) 2003 R2, Standard x64 EditionWindows Server 2003 R2 x64 Editions
Microsoft(R) Windows Server(R) 2003 R2, Enterprise x64 Edition
Microsoft(R) Windows Server(R) 2003, Enterprise Edition (64-bit version)Windows Server 2003 (IPF)
Microsoft(R) Windows Server(R) 2008 StandardWindows Server 2008 StandardWindows Server 2008
Microsoft(R) Windows Server(R) 2008 EnterpriseWindows Server 2008 Enterprise
Microsoft(R) Windows Server(R) 2008 R2 Standard (x64)Windows Server 2008 R2
Microsoft(R) Windows Server(R) 2008 R2 Enterprise (x64)
Microsoft(R) Windows Server(R) 2008 R2 Datacenter (x64)
Microsoft(R) Windows Server(R) 2008 Standard (x64)Windows Server 2008 (x64)
Microsoft(R) Windows Server(R) 2008 Enterprise (x64)
Microsoft(R) Windows Server(R) 2003, Standard x64 EditionWindows Server 2003 x64 EditionsWindows (x64)
Microsoft(R) Windows Server(R) 2003, Enterprise x64 Edition
Microsoft(R) Windows Server(R) 2003 R2, Standard x64 Edition
Microsoft(R) Windows Server(R) 2003 R2, Enterprise x64 Edition
Microsoft(R) Windows(R) XP Professional x64 EditionWindows XP x64 Edition
Microsoft(R) Windows Server(R) 2003, Enterprise Edition (64-bit version)Windows Server 2003 (IPF)Windows(IPF)
Microsoft(R) Windows(R) XP Professional x64 EditionWindows XP x64 EditionWindows XP
Microsoft(R) Windows(R) XP Professional Operating SystemWindows XP Professional
Microsoft(R) Windows(R) XP Home Edition Operating SystemWindows XP Home Edition
Microsoft(R) Windows Vista(R) Home BasicWindows Vista Home BasicWindows Vista
Microsoft(R) Windows Vista(R) Home PremiumWindows Vista Home Premium
Microsoft(R) Windows Vista(R) UltimateWindows Vista Ultimate
Microsoft(R) Windows Vista(R) BusinessWindows Vista Business
Microsoft(R) Windows Vista(R) EnterpriseWindows Vista Enterprise
Microsoft(R) Windows Vista(R) Home Basic (x64)Windows Vista (x64)
Microsoft(R) Windows Vista(R) Home Premium (x64)
Microsoft(R) Windows Vista(R) Ultimate (x64)
Microsoft(R) Windows Vista(R) Business (x64)
Microsoft(R) Windows Vista(R) Enterprise (x64)
Microsoft(R) Windows(R) 7 Home PremiumWindows 7
Microsoft(R) Windows(R) 7 Professional
Microsoft(R) Windows(R) 7 Enterprise
Microsoft(R) Windows(R) 7 Ultimate
Microsoft(R) Windows(R) 7 Home Premium (x64)Windows 7 (x64)
Microsoft(R) Windows(R) 7 Professional (x64)
Microsoft(R) Windows(R) 7 Enterprise (x64)
Microsoft(R) Windows(R) 7 Ultimate (x64)
Single serverSDS
System managerMGR
Front-end serverFES
Dictionary serverDS
Back-end serverBES

This manual also uses the following acronyms:

AcronymFull name or meaning
ACKAcknowledgement
ADMAdaptable Data Manager
ADOActiveX Data Objects
ADTAbstract Data Type
APApplication Program
APIApplication Programming Interface
ASN.1Abstract Syntax Notation One
BESBack End Server
BLOBBinary Large Object
BMPBasic Multilingual Plane
BOMByte Order Mark
CD-ROMCompact Disc - Read Only Memory
CGICommon Gateway Interface
CLOBCharacter Large Object
CMTCassette Magnetic Tape
COBOLCommon Business Oriented Language
CORBA(R)Common ORB Architecture
CPUCentral Processing Unit
CSVComma Separated Values
DAOData Access Object
DATDigital Audio Tape
DBDatabase
DBMDatabase Module
DBMSDatabase Management System
DDLData Definition Language
DF for Windows NTDistributing Facility for Windows NT
DF/UXDistributing Facility/for UNIX
DICDictionary Server
DLTDigital Linear Tape
DMLData Manipulate Language
DNSDomain Name System
DOMDocument Object Model
DSDictionary Server
DTDDocument Type Definition
DTPDistributed Transaction Processing
DWHData Warehouse
EUCExtended UNIX Code
EXExclusive
FATFile Allocation Table
FDFloppy Disk
FESFront End Server
FQDNFully Qualified Domain Name
FTPFile Transfer Protocol
GUIGraphical User Interface
HBAHost Bus Adapter
HDHard Disk
HTMLHyper Text Markup Language
IDIdentification number
IPInternet Protocol
IPFItanium(R) Processor Family
JARJava Archive File
Java VMJava Virtual Machine
JDBCJava Database Connectivity
JDKJava Developer's Kit
JFSJournaled File System
JFS2Enhanced Journaled File System
JISJapanese Industrial Standard code
JP1Job Management Partner 1
JREJava Runtime Environment
JTAJava Transaction API
JTSJava Transaction Service
KEISKanji processing Extended Information System
LANLocal Area Network
LDAPLightweight Directory Access Protocol
LIPLoop Initialization Process
LOBLarge Object
LRULeast Recently Used
LTOLinear Tape-Open
LULogical Unit
LUNLogical Unit Number
LVMLogical Volume Manager
MGRSystem Manager
MIBManagement Information Base
MRCFMultiple RAID Coupling Feature
MSCSMicrosoft Cluster Server
MSFCMicrosoft Failover Cluster
NAFONetwork Adapter Fail Over
NAPTNetwork Address Port Translation
NATNetwork Address Translation
NICNetwork Interface Card
NISNetwork Information Service
NTFSNew Technology File System
ODBCOpen Database Connectivity
OLAPOnline Analytical Processing
OLEObject Linking and Embedding
OLTPOn-Line Transaction Processing
OOCOBOLObject Oriented COBOL
ORBObject Request Broker
OSOperating System
OSIOpen Systems Interconnection
OTSObject Transaction Service
PCPersonal Computer
PDM II E2Practical Data Manager II Extended Version 2
PICPlug-in Code
PNMPublic Network Management
POSIXPortable Operating System Interface for UNIX
PPProgram Product
PRProtected Retrieve
PUProtected Update
RAIDRedundant Arrays of Inexpensive Disk
RDRelational Database
RDBRelational Database
RDB1Relational Database Manager 1
RDB1 E2Relational Database Manager 1 Extended Version 2
RDORemote Data Objects
RiSeReal time SAN replication
RMResource Manager
RMMResource Manager Monitor
RPCRemote Procedure Call
SAXSimple API for XML
SDSSingle Database Server
SGMLStandard Generalized Markup Language
SJISShift JIS
SNMPSimple Network Management Protocol
SNTPSimple Network Time Protocol
SQLStructured Query Language
SQL/KStructured Query Language / VOS K`
SRShared Retrieve
SUShared Update
TCP/IPTransmission Control Protocol / Internet Protocol
TMTransaction Manager
TMS-4V/SPTransaction Management System - 4V / System Product
UAPUser Application Program
UOCUser Own Coding
VOS KVirtual-storage Operating System Kindness
VOS1Virtual-storage Operating System 1
VOS3Virtual-storage Operating System 3
WSWorkstation
WWWWorld Wide Web
XDM/BASE E2Extensible Data Manager / Base Extended Version 2
XDM/DFExtensible Data Manager / Distributing Facility
XDM/DSExtensible Data Manager / Data Spreader
XDM/RD E2Extensible Data Manager / Relational Database Extended Version 2
XDM/SD E2Extensible Data Manager / Structured Database Extended Version 2
XDM/XTExtensible Data Manager / Data Extract
XDSExtended Data Server
XFITExtended File Transmission program
XMLExtensible Markup Language

Path name representations

Windows edition: %PDDIR%\CLIENT\UTL\

UNIX edition: $PDDIR/client/lib/

Log representations

[Figure]Windows edition
The application log that is displayed by Windows Event Viewer is referred to as the event log. The following procedure is used to view the event log.
To view the event log:
1. Choose Start, Programs, Administrative Tools (Common), and then Event Viewer.
2. Choose Log, and then Application.
The application log is displayed. Messages with HiRDBSingleServer or HiRDBParallelServer displayed in the Source column were issued by HiRDB.
If you specified a setup identifier when you installed HiRDB, the specified setup identifier follows HiRDBSingleServer or HiRDBParallelServer.
[Figure]UNIX edition
The OS log is referred to generically as syslogfile. syslogfile is the log output destination specified in /etc/syslog.conf. Typically, the following files are specified as syslogfile.
OSFile
HP-UX/var/adm/syslog/syslog.log
Solaris/var/adm/messages or /var/log/syslog
AIX/var/adm/ras/syslog
Linux/var/log/messages

Symbols used in figures

The following symbols are used in the figures in this manual:

[Figure]

Symbols used in text

In addition to those used in figures, this manual uses the following symbols in text:

[Figure]formula [Figure]
The resulting value is to be rounded up.
[Figure]CI-length / 1000 [Figure]
[Figure]formula [Figure]
The resulting value is to be rounded off.
[Figure]CI-length / 1000 [Figure]

Organization of SQL syntax explanations

This manual explains the syntax of SQL expressions in the following general format:

Function
Explains the function of the SQL expression.
Privileges
Explains the privileges that are required in order to use the SQL expression.
Format
Shows the format of the operands.
Operands
Explains the operands that can be specified, when they should be specified, and their specification conventions.
Rules
Explains rules applicable to the SQL expression.
Notes
Provides helpful notes, such as the relationships between this SQL expression and other SQL expressions.
Examples
Shows examples of the use of the SQL expression.

Conventions: Fonts and symbols

The following table explains the fonts used in this manual:

FontConvention
BoldBold type indicates text on a window, other than the window title. Such text includes menus, menu options, buttons, radio box options, or explanatory labels. For example:
  • From the File menu, choose Open.
  • Click the Cancel button.
  • In the Enter name entry box, type your name.
ItalicsItalics are used to indicate a placeholder for some actual text to be provided by the user or system. For example:
  • Write the command as follows:
    copy source-file target-file
  • The following message appears:
    A file was not found. (file = file-name)
Italics are also used for emphasis. For example:
  • Do not delete the configuration file.
Code fontA code font indicates text that the user enters without change, or text (such as messages) output by the system. For example:
  • At the prompt, enter dir.
  • Use the send command to send mail.
  • The following message is displayed:
    The password is incorrect.
SDBold code-font characters indicate the abbreviation for a command.
permUnderlined characters indicate the default value.

The following table explains the symbols used in this table:

SymbolConvention
|In syntax explanations, a vertical bar separates multiple items, and has the meaning of OR. For example:
A|B|C means A, or B, or C.
{ }In syntax explanations, curly brackets indicate that only one of the enclosed items is to be selected. For example:
A|B|C means A, or B, or C.
[ ]In syntax explanations, square brackets indicate that the enclosed item or items are optional. For example:
[A] means that you can specify A or nothing.
[B|C] means that you can specify B, or C, or nothing.
...In coding, an ellipsis (...) indicates that one or more lines of coding are not shown for purposes of brevity.
In syntax explanations, an ellipsis indicates that the immediately preceding item can be repeated as many times as necessary. For example:
A, B, B, ... means that, after you specify A, B, you can specify B as many times as necessary.
()Parentheses indicate the range of items to which the vertical bar (|) or ellipsis (...) is applicable.
[Figure]A single delta symbol indicates one space. For example *DC[Figure]
[Figure]Two delta symbols indicate one or more spaces. For example:
WHERE[Figure]IPNO=1
::=The item to the left of the ::= notation is specified in terms of the items to the right of the ::= notation. For example:
table-name ::= [authorization-identifier.]table-identifier

Syntax element conventions

The following table explains the syntactical element symbols used in this manual:

Syntax elementMeaning
<alphabetics>The alphabetic characters (A to Z and a to z) and the underscore (_)
<alphabetics and special characters>The alphabetic characters (A to Z and a to z) and the special characters #, @, and \
<alphanumerics>Alphabetic characters and the numeric digits (0 to 9)
<alphanumerics and special characters>Alphabetic characters, special characters, and numeric characters
<unsigned-integer>Numeric values
<unsigned-decimal>1Numeric values (0 to 9) and the period (.), numeric value (0-9)
<identifier>2Alphanumeric character string beginning with an alphabetic character
<character-string>String of any characters
<symbolic-name>Alphanumeric character string beginning with an alphabetic character or a special character
In the UNIX edition, symbolic names cannot include a backslash (\).
<path-name>3In the UNIX edition, path names can include forward slashes (/), alphanumeric characters, periods (.), hash marks (#), and at marks (@).
In the Windows edition, path names can include backslashes (\), alphanumeric characters, periods (.), spaces, parentheses (()), hash marks (#), and at marks (@).

Notes on Windows path names

Example: C:\win32app\hitachi\hirdb_s\spool\tmp

Example: pdinit -d "C:\Program Files(x86)\hitachi\hirdb_s\conf\mkinit"

However, double quotation marks are not necessary when you use the set command in a batch file or at the command prompt to set an environment variable, or when you specify the installation directory. If you do use double quotation marks in such a case, the double quotation marks become part of the value assigned to the environment variable.

Example: set PDCLTPATH=C:\Program Files\hitachi\hirdb_s\spool

Conventions: KB, MB, GB, and TB

This manual uses the following conventions:

Conventions: Version numbers

The version numbers of Hitachi program products are usually written as two sets of two digits each, separated by a hyphen. For example:

The version number might be shown on the spine of a manual as Ver. 2.00, but the same version number would be written in the program as 02-00.

HiRDB database language acknowledgements

The interpretations and specifications developed by Hitachi, Ltd. for the HiRDB database language specifications described in this manual are based on the standards listed below. Along with citing the standards relevant to HiRDB database language specifications, we would like to take this opportunity to express our appreciation to the original developers of these standards.

Note:

JIS: Japanese Industrial Standards

ISO: International Organization for Standardization

IEC: International Electrotechnical Commission

Important notes on this manual

The following facilities are explained, but they are not supported:

The following products and option program products are explained, but they are not supported: