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:
Related publications
This manual is related to the following manuals, which should be read as required.
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.
#: 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.
Conventions: Abbreviations for product names
This manual uses the following abbreviations for product names:
Full name or meaning | Abbreviation | |
---|---|---|
HiRDB Server Version 9 | HiRDB/Single Server | HiRDB or HiRDB Server |
HiRDB/Parallel Server | ||
HiRDB/Developer's Kit Version 9 | HiRDB/Developer's Kit | HiRDB Client |
HiRDB/Developer's Kit Version 9 (64) | ||
HiRDB/Run Time Version 9 | HiRDB/Run Time | |
HiRDB/Run Time Version 9 (64) | ||
HiRDB Advanced High Availability Version 9 | HiRDB Advanced High Availability | |
HiRDB Accelerator Version 8 | HiRDB Accelerator | |
HiRDB Accelerator Version 9 | ||
HiRDB Non Recover Front End Server Version 9 | HiRDB Non Recover FES | |
HiRDB Staticizer Option Version 9 | HiRDB Staticizer Option | |
HiRDB Disaster Recovery Light Edition Version 9 | HiRDB Disaster Recovery Light Edition | |
HiRDB Text Search Plug-in Version 9 | HiRDB Text Search Plug-in | |
HiRDB XML Extension Version 9 | HiRDB XML Extension | |
HiRDB Datareplicator Version 8 | HiRDB Datareplicator | |
HiRDB Dataextractor Version 8 | HiRDB Dataextractor | |
HiRDB Adapter for XML - Standard Edition | HiRDB Adapter for XML | |
HiRDB Adapter for XML - Enterprise Edition | ||
HiRDB Control Manager | HiRDB CM | |
HiRDB Control Manager Agent | HiRDB CM Agent | |
Hitachi TrueCopy | TrueCopy | |
Hitachi TrueCopy basic | ||
TrueCopy | ||
TrueCopy remote replicator | ||
JP1/Automatic Job Management System 3 | JP1/AJS3 | |
JP1/Automatic Job Management System 2 | ||
JP1/Automatic Job Management System 2 - Scenario Operation | JP1/AJS2-SO | |
JP1/Cm2/Extensible SNMP Agent | JP1/ESA | |
JP1/Cm2/Extensible SNMP Agent for Mib Runtime | ||
JP1/Cm2/Network Node Manager | JP1/NNM | |
JP1/Integrated Management - Manager | JP1/Integrated Management or JP1/IM | |
JP1/Integrated Management - View | ||
JP1/Magnetic Tape Access | EasyMT | |
EasyMT | ||
JP1/Magnetic Tape Library | MTguide | |
JP1/NETM/Audit - Manager | JP1/NETM/Audit | |
JP1/NETM/DM | JP1/NETM/DM | |
JP1/NETM/DM Manager | ||
JP1/Performance Management | JP1/PFM | |
JP1/Performance Management - Agent Option for HiRDB | JP1/PFM-Agent for HiRDB | |
JP1/Performance Management - Agent Option for Platform | JP1/PFM-Agent for Platform | |
JP1/Performance Management/SNMP System Observer | JP1/SSO | |
JP1/VERITAS NetBackup BS v4.5 | NetBackup | |
JP1/VERITAS NetBackup v4.5 | ||
JP1/VERITAS NetBackup BS V4.5 Agent for HiRDB License | JP1/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 Option | TP1/EE | |
Virtual-storage Operating System 3/Forefront System Product | VOS3/FS | VOS3 |
Virtual-storage Operating System 3/Leading System Product | VOS3/LS | |
Virtual-storage Operating System 3/Unific System Product | VOS3/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/RD | XDM/RD |
XDM/Relational Database Extended Version 2 Relational Database System XDM/RD E2 | XDM/RD E2 | |
VOS3 Database Connection Server | DB Connection Server | |
Oracle WebLogic Server | WebLogic Server | |
DB2 Universal Database for OS/390 Version 6 | DB2 | |
DNCWARE ClusterPerfect (Linux Edition) | ClusterPerfect | |
JavaTM | Java | |
Microsoft(R) Office Excel | Microsoft Excel or Excel | |
Microsoft(R) Visual C++(R) | Visual C++ or C++ language | |
PowerHA for AIX, V5.5 | PowerHA | |
PowerHA SystemMirror V6.1 | ||
HP-UX 11i V2 (IPF) | HP-UX or HP-UX (IPF) | |
HP-UX 11i V3 (IPF) | ||
AIX 5L V5.2 | AIX 5L | AIX |
AIX 5L V5.3 | ||
AIX V6.1 | AIX V6.1 | |
Linux(R) | Linux | |
Red Hat Enterprise Linux AS 4 (AMD64 & Intel EM64T) | Linux AS 4 | Linux |
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 AP8000 | Linux for AP8000 | |
Microsoft(R) Windows NT(R) Workstation Operating System Version 4.0 | Windows NT | |
Microsoft(R) Windows NT(R) Server Network Operating System Version 4.0 | ||
Microsoft(R) Windows(R) 2000 Professional Operating System | Windows 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 System | Windows 2000 Advanced Server | |
Microsoft(R) Windows Server(R) 2003, Standard Edition | Windows Server 2003 Standard Edition | Windows Server 2003 |
Microsoft(R) Windows Server(R) 2003, Enterprise Edition | Windows Server 2003 Enterprise Edition | |
Microsoft(R) Windows Server(R) 2003, Standard x64 Edition | Windows Server 2003 Standard x64 Edition | |
Microsoft(R) Windows Server(R) 2003, Enterprise x64 Edition | Windows Server 2003 Enterprise x64 Edition | |
Microsoft(R) Windows Server(R) 2003 R2, Standard Edition | Windows 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 Edition | Windows 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 Standard | Windows Server 2008 Standard | Windows Server 2008 |
Microsoft(R) Windows Server(R) 2008 Enterprise | Windows 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 Edition | Windows Server 2003 x64 Editions | Windows (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 Edition | Windows 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 Edition | Windows XP x64 Edition | Windows XP |
Microsoft(R) Windows(R) XP Professional Operating System | Windows XP Professional | |
Microsoft(R) Windows(R) XP Home Edition Operating System | Windows XP Home Edition | |
Microsoft(R) Windows Vista(R) Home Basic | Windows Vista Home Basic | Windows Vista |
Microsoft(R) Windows Vista(R) Home Premium | Windows Vista Home Premium | |
Microsoft(R) Windows Vista(R) Ultimate | Windows Vista Ultimate | |
Microsoft(R) Windows Vista(R) Business | Windows Vista Business | |
Microsoft(R) Windows Vista(R) Enterprise | Windows 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 Premium | Windows 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 server | SDS | |
System manager | MGR | |
Front-end server | FES | |
Dictionary server | DS | |
Back-end server | BES |
This manual also uses the following acronyms:
Acronym | Full name or meaning |
---|---|
ACK | Acknowledgement |
ADM | Adaptable Data Manager |
ADO | ActiveX Data Objects |
ADT | Abstract Data Type |
AP | Application Program |
API | Application Programming Interface |
ASN.1 | Abstract Syntax Notation One |
BES | Back End Server |
BLOB | Binary Large Object |
BMP | Basic Multilingual Plane |
BOM | Byte Order Mark |
CD-ROM | Compact Disc - Read Only Memory |
CGI | Common Gateway Interface |
CLOB | Character Large Object |
CMT | Cassette Magnetic Tape |
COBOL | Common Business Oriented Language |
CORBA(R) | Common ORB Architecture |
CPU | Central Processing Unit |
CSV | Comma Separated Values |
DAO | Data Access Object |
DAT | Digital Audio Tape |
DB | Database |
DBM | Database Module |
DBMS | Database Management System |
DDL | Data Definition Language |
DF for Windows NT | Distributing Facility for Windows NT |
DF/UX | Distributing Facility/for UNIX |
DIC | Dictionary Server |
DLT | Digital Linear Tape |
DML | Data Manipulate Language |
DNS | Domain Name System |
DOM | Document Object Model |
DS | Dictionary Server |
DTD | Document Type Definition |
DTP | Distributed Transaction Processing |
DWH | Data Warehouse |
EUC | Extended UNIX Code |
EX | Exclusive |
FAT | File Allocation Table |
FD | Floppy Disk |
FES | Front End Server |
FQDN | Fully Qualified Domain Name |
FTP | File Transfer Protocol |
GUI | Graphical User Interface |
HBA | Host Bus Adapter |
HD | Hard Disk |
HTML | Hyper Text Markup Language |
ID | Identification number |
IP | Internet Protocol |
IPF | Itanium(R) Processor Family |
JAR | Java Archive File |
Java VM | Java Virtual Machine |
JDBC | Java Database Connectivity |
JDK | Java Developer's Kit |
JFS | Journaled File System |
JFS2 | Enhanced Journaled File System |
JIS | Japanese Industrial Standard code |
JP1 | Job Management Partner 1 |
JRE | Java Runtime Environment |
JTA | Java Transaction API |
JTS | Java Transaction Service |
KEIS | Kanji processing Extended Information System |
LAN | Local Area Network |
LDAP | Lightweight Directory Access Protocol |
LIP | Loop Initialization Process |
LOB | Large Object |
LRU | Least Recently Used |
LTO | Linear Tape-Open |
LU | Logical Unit |
LUN | Logical Unit Number |
LVM | Logical Volume Manager |
MGR | System Manager |
MIB | Management Information Base |
MRCF | Multiple RAID Coupling Feature |
MSCS | Microsoft Cluster Server |
MSFC | Microsoft Failover Cluster |
NAFO | Network Adapter Fail Over |
NAPT | Network Address Port Translation |
NAT | Network Address Translation |
NIC | Network Interface Card |
NIS | Network Information Service |
NTFS | New Technology File System |
ODBC | Open Database Connectivity |
OLAP | Online Analytical Processing |
OLE | Object Linking and Embedding |
OLTP | On-Line Transaction Processing |
OOCOBOL | Object Oriented COBOL |
ORB | Object Request Broker |
OS | Operating System |
OSI | Open Systems Interconnection |
OTS | Object Transaction Service |
PC | Personal Computer |
PDM II E2 | Practical Data Manager II Extended Version 2 |
PIC | Plug-in Code |
PNM | Public Network Management |
POSIX | Portable Operating System Interface for UNIX |
PP | Program Product |
PR | Protected Retrieve |
PU | Protected Update |
RAID | Redundant Arrays of Inexpensive Disk |
RD | Relational Database |
RDB | Relational Database |
RDB1 | Relational Database Manager 1 |
RDB1 E2 | Relational Database Manager 1 Extended Version 2 |
RDO | Remote Data Objects |
RiSe | Real time SAN replication |
RM | Resource Manager |
RMM | Resource Manager Monitor |
RPC | Remote Procedure Call |
SAX | Simple API for XML |
SDS | Single Database Server |
SGML | Standard Generalized Markup Language |
SJIS | Shift JIS |
SNMP | Simple Network Management Protocol |
SNTP | Simple Network Time Protocol |
SQL | Structured Query Language |
SQL/K | Structured Query Language / VOS K` |
SR | Shared Retrieve |
SU | Shared Update |
TCP/IP | Transmission Control Protocol / Internet Protocol |
TM | Transaction Manager |
TMS-4V/SP | Transaction Management System - 4V / System Product |
UAP | User Application Program |
UOC | User Own Coding |
VOS K | Virtual-storage Operating System Kindness |
VOS1 | Virtual-storage Operating System 1 |
VOS3 | Virtual-storage Operating System 3 |
WS | Workstation |
WWW | World Wide Web |
XDM/BASE E2 | Extensible Data Manager / Base Extended Version 2 |
XDM/DF | Extensible Data Manager / Distributing Facility |
XDM/DS | Extensible Data Manager / Data Spreader |
XDM/RD E2 | Extensible Data Manager / Relational Database Extended Version 2 |
XDM/SD E2 | Extensible Data Manager / Structured Database Extended Version 2 |
XDM/XT | Extensible Data Manager / Data Extract |
XDS | Extended Data Server |
XFIT | Extended File Transmission program |
XML | Extensible Markup Language |
Path name representations
Windows edition: %PDDIR%\CLIENT\UTL\
UNIX edition: $PDDIR/client/lib/
Log representations
OS | File |
---|---|
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:
Symbols used in text
In addition to those used in figures, this manual uses the following symbols in text:
Organization of SQL syntax explanations
This manual explains the syntax of SQL expressions in the following general format:
Conventions: Fonts and symbols
The following table explains the fonts used in this manual:
Font | Convention |
---|---|
Bold | Bold 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:
|
Italics | Italics are used to indicate a placeholder for some actual text to be provided by the user or system. For example:
|
Code font | A code font indicates text that the user enters without change, or text (such as messages) output by the system. For example:
|
SD | Bold code-font characters indicate the abbreviation for a command. |
perm | Underlined characters indicate the default value. |
The following table explains the symbols used in this table:
Symbol | Convention |
---|---|
| | 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. |
![]() | A single delta symbol indicates one space. For example *DC![]() |
![]() | Two delta symbols indicate one or more spaces. For example: WHERE ![]() |
::= | 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 element | Meaning |
---|---|
<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>1 | Numeric values (0 to 9) and the period (.), numeric value (0-9) |
<identifier>2 | Alphanumeric 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>3 | In 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: