This manual explains the syntax of SQL, a language that is used for performing operations on databases of the HiRDB, Version 8, Scalable Database Server program products.
Intended readers
This manual is intended for users who will be using HiRDB Version 8 (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 should be read before reading this manual:
Organization of this manual
This manual is organized as follows:
Related publications
This manual is related to the following manuals, which should be read as required.
* This manual has been published in Japanese only; it is not available in English.
You must use the UNIX or the Windows manuals, as appropriate to the platform you are using.
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
Unless otherwise required, this manual uses the following abbreviations for product and other names.
Name of product or other entity | Representation | |
---|---|---|
HiRDB/Single Server Version 8 | HiRDB/Single Server | HiRDB or HiRDB Server |
HiRDB/Single Server Version 8(64) | ||
HiRDB/Parallel Server Version 8 | HiRDB/Parallel Server | |
HiRDB/Parallel Server Version 8(64) | ||
HiRDB/Developer's Kit Version 8 | HiRDB/Developer's Kit | HiRDB Client |
HiRDB/Developer's Kit Version 8(64) | ||
HiRDB/Run Time Version 8 | HiRDB/Run Time | |
HiRDB/Run Time Version 8(64) | ||
HiRDB Datareplicator Version 8 | HiRDB Datareplicator | |
HiRDB Dataextractor Version 8 | HiRDB Dataextractor | |
HiRDB Text Search Plug-in Version 7 | HiRDB Text Search Plug-in | |
HiRDB Spatial Search Plug-in Version 3 | HiRDB Spatial Search Plug-in | |
HiRDB Staticizer Option Version 8 | HiRDB Staticizer Option | |
HiRDB LDAP Option Version 8 | HiRDB LDAP Option | |
HiRDB Advanced Partitioning Option Version 8 | HiRDB Advanced Partitioning Option | |
HiRDB Advanced High Availability Version 8 | HiRDB Advanced High Availability | |
HiRDB Non Recover Front End Server Version 8 | HiRDB Non Recover FES | |
HiRDB Disaster Recovery Light Edition Version 8 | HiRDB Disaster Recovery Light Edition | |
HiRDB External Data Access Version 8 | HiRDB External Data Access | |
HiRDB External Data Access Adapter Version 8 | HiRDB External Data Access Adapter | |
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 2 | JP1/AJS2 | |
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/DM | JP1/NETM/DM | |
JP1/NETM/DM Manager | ||
JP1/Performance Management | JP1/PFM | |
JP1/Performance Management Agent for HiRDB | JP1/PFM-Agent for HiRDB | |
JP1/Performance Management - Agent 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 | |
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 control XDM/DCCM3 | XDM/DCCM3 | |
XDM/Relational Database XDM/RD | XDM/RD | XDM/RD |
XDM/Relational Database Extended Version 2 XDM/RD E2 | XDM/RD E2 | |
VOS3 Database Connection Server | DB Connection Server | |
DB2 Universal Database for OS/390 Version 6 | DB2 | |
DNCWARE ClusterPerfect (Linux Version) | ClusterPerfect | |
Microsoft(R) Excel | Microsoft Excel or Excel | |
Microsoft(R) Visual C++(R) | Visual C++ or C++ | |
Oracle 8i | ORACLE | |
Oracle 9i | ||
Oracle 10g | ||
Sun JavaTM System Directory Server | Sun Java System Directory Server or Directory Server | |
HP-UX 11i V2 (IPF) | HP-UX or HP-UX (IPF) | |
Red Hat Linux | Linux | |
Red Hat Enterprise Linux | ||
Red Hat Enterprise Linux AS 3 (IPF) | Linux (IPF) | Linux |
Red Hat Enterprise Linux AS 4 (IPF) | ||
Red Hat Enterprise Linux AS 3(AMD64 & Intel EM64T) | Linux (EM64T) | |
Red Hat Enterprise Linux AS 4(AMD64 & Intel EM64T) | ||
Red Hat Enterprise Linux ES 4(AMD64 & Intel EM64T) | ||
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 | Windows 2000 or Windows 2000 Advanced Server | |
Microsoft(R) Windows ServerTM 2003, Standard Edition | Windows Server 2003 | |
Microsoft(R) Windows ServerTM 2003, Enterprise Edition | ||
Microsoft(R) Windows ServerTM 2003 R2, Standard Edition | Windows Server 2003 R2 or Windows Server 2003 | |
Microsoft(R) Windows ServerTM 2003 R2, Enterprise Edition | ||
64 bit Version Microsoft(R) Windows ServerTM 2003, Enterprise Edition (IPF) | Windows Server 2003 (IPF) or Windows Server 2003 | |
Microsoft(R) Windows ServerTM 2003, Standard x64 Edition | Windows Server 2003, Windows Server 2003 R2 or Windows Server 2003 x64 Editions | Windows (x64) |
Microsoft(R) Windows ServerTM 2003, Enterprise x64 Edition | ||
Microsoft(R) Windows ServerTM 2003 R2, Standard x64 Edition | Windows Server 2003, Windows Server 2003 R2 or Windows Server 2003 x64 Editions | |
Microsoft(R) Windows ServerTM 2003 R2, Enterprise x64 Edition | ||
Microsoft(R) Windows(R) XP Professional x64 Edition | Windows XP or Windows XP x64 Edition | |
Microsoft(R) Windows(R) XP Professional Operating System | Windows XP Professional | Windows XP |
Microsoft(R) Windows(R) XP Home Edition Operating System | Windows XP Home Edition | |
Single server | SDS | |
System manager | MGR | |
Front-end server | FES | |
Dictionary server | DS | |
Back-end server | BES |
This manual also uses the following abbreviations:
Abbreviation | 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 |
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 Taperecorder |
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 |
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 |
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 |
XFIT | Extended File Transmission program |
XML | Extensible Markup Language |
Path name representations
Windows version: %PDDIR%\CLIENT\UTL\
UNIX version: $PDDIR/client/lib/
Log representations
OS | File |
---|---|
HP-UX | /var/adm/syslog/syslog.log |
Solaris | /var/adm/messages or /var/log/syslog |
AIX 5L | /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
Font conventions are divided into:
These are described next.
General font conventions
copy source-file target-file
The password is incorrect.
Font conventions in syntax explanations and examples
Syntax definitions appear as follows:
StoreDatabase {:embedded-variable|?-parameter} [temp|perm] (database-name ...)
In such a syntax definition:
table-name ::= [authorization-identifier.]table-identifier
Code examples and messages appear as follows (though there may be some exceptions, such as when the code is part of a diagram):
MakeDatabase
...
StoreDatabase temp DB32
In code examples, ellipses (...) indicate that some code has been removed for the sake of clarity.
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.
Sources of HiRDB relational database language
The HiRDB relational database language described in this manual was developed by adding Hitachi's unique interpretations and specifications to the following standards. Hitachi expresses its appreciation to the developers and acknowledges the sources of these specifications.
JIS | X3005-1997 | Database Language SQL |
IS | ISO9075-1992 | Information processing systems - Database Language SQL |
ANS | X3.135-1986 | Information system - Database Language SQL |
Relationships to ANSI standard
The specifications for the HiRDB relational database language have been developed by adding Hitachi's unique interpretations to the specifications of ANS X3.135-1986 information systemsdatabase language SQL.
Hitachi has been granted ANSI's permission for the creation of this manual; however, ANSI is not responsible for this product or the contents of this manual.
Acknowledgments
The COBOL language specifications were developed by CODASYL. The following statement acknowledges Hitachi's indebtedness to the developers, as requested by CODASYL. This acknowledgement restates a portion of the acknowledgement provided in the original specifications of COBOL, CODASYL COBOL JOURNAL OF DEVELOPMENT, 1984.
Any organization interested in reproducing the COBOL report and specifications in whole or in part, using ideas from this report as the basis for an instruction manual or for any other purpose, is free to do so. However, all such organizations are requested to reproduce the following acknowledgement paragraphs in their entirety as part of the preface to any such publication. Any organization using a short passage from this document, such as in a book review, is requested to mention "COBOL" in acknowledgment of the source, but need not quote the acknowledgment.
COBOL is an industry language and is not the property of any company or group of companies, or of any organization or group of organizations.
No warranty, expressed or implied, is made by any contributor or by the CODASYL COBOL Committee as to the accuracy and functioning of the programming system and language. Moreover, no responsibility is assumed by any contributor, or by the committee, in connection therewith.
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: