Hitachi

Hitachi Advanced Database SQL Reference

Preface

This manual describes the SQL syntax used for manipulating databases in Hitachi Advanced Database.

Note that, in this manual, and in the information output by the product (messages, command output results, and so on), HADB is often used in place of Hitachi Advanced Database.

Organization of this preface

■ Intended readers

This manual is intended for:

Readers of this manual must have:

To Page Top

■ Organization of this manual

This manual is organized into the following parts, chapters, and appendixes:

1. SELECT Statement Examples

Chapter 1 explains, using examples, how to write SELECT statements with constructs such as predicates, set functions, GROUP BY clauses, and HAVING clauses. Read this chapter to understand how to write basic SELECT statements.

2. List of SQL Statements

Chapter 2 provides a list of SQL statements that are supported by HADB, and explains how to read the SQL syntax specification format used in this manual.

3. Definition SQL

Chapter 3 describes the functions, specification formats, and rules of definition SQL statements.

4. Data Manipulation SQL

Chapter 4 describes the functions, specification formats, and rules of data manipulation SQL statements.

5. Control SQL

Chapter 5 describes the functions, specification formats, and rules of control SQL statements.

6. SQL Basics

Chapter 6 describes the basic elements of SQL, including rules for writing SQL statements, data types, and literals.

7. Constituent Elements

Chapter 7 describes query expressions, query specifications, predicates, value expressions, set functions, and other elements that comprise SQL.

8. Scalar Functions

Chapter 8 describes the functions, specification formats, and rules of scalar functions.

A. SQL Reverse Lookup Reference

Appendix A explains SQL syntax organized by where it is used. This appendix provides a reverse lookup reference by which you can determine the SQL construct to use based on what you want to achieve.

B. List of Functions

Appendix B lists the functions supported by HADB and where each is used.

To Page Top

■ Related publications

This manual is part of a related set of manuals. The manuals in the set are listed below (with the manual numbers):

In references to Hitachi Advanced Database manuals, this manual uses HADB in place of Hitachi Advanced Database.

Example: HADB Setup and Operation Guide

In references to the HA Monitor manual, this manual uses HA Monitor for Linux(R) (x86) in place of HA Monitor Cluster Software Guide (for Linux(R) (x86) Systems).

Example: HA Monitor for Linux(R) (x86)

In references to the Job Management Partner 1/Automatic Job Management System 3 manual, this manual uses Job Management Partner 1/Automatic Job Management System 3 System Design (Work Tasks) Guide in place of Job Management Partner 1 Version 10 Job Management Partner 1/Automatic Job Management System 3 System Design (Work Tasks) Guide.

Example: Job Management Partner 1/Automatic Job Management System 3 System Design (Work Tasks) Guide

In references to the JP1/Base manual, this manual uses JP1/Base User's Guide in place of JP1 Version 11 JP1/Base User's Guide.

Example: JP1/Base User's Guide

To Page Top

■ Conventions: Abbreviations for product names

This manual uses the following abbreviations for product names:

Abbreviation

Full name or meaning

HADB

HADB server

Hitachi Advanced Database

HADB client

Hitachi Advanced Database Client

Linux

Linux

Linux(R)

Red Hat Enterprise Linux Server 6

Red Hat(R) Enterprise Linux(R) Server 6 (64-bit x86_64)

Red Hat Enterprise Linux Server 6 (64-bit x86_64)

Red Hat Enterprise Linux Server 7

Red Hat(R) Enterprise Linux(R) Server 7 (64-bit x86_64)

Red Hat Enterprise Linux Server 7 (64-bit x86_64)

HDLM

Hitachi Dynamic Link Manager Software

JP1/AJS3

Job Management Partner 1/Automatic Job Management System 3

JP1/Audit

JP1/Audit Management - Manager

Red Hat Enterprise Linux Server 6 (64-bit x86_64)

Red Hat(R) Enterprise Linux(R) Server 6 (64-bit x86_64)

Red Hat Enterprise Linux Server 7 (64-bit x86_64)

Red Hat(R) Enterprise Linux(R) Server 7 (64-bit x86_64)

To Page Top

■ Conventions: Acronyms

This manual also uses the following acronyms:

Acronym

Full name or meaning

APD

Application Parameter Descriptor

API

Application Programming Interface

ARD

Application Row Descriptor

BI

Business Intelligence

BLOB

Binary Large Object

BNF

Backus-Naur Form

BOM

Byte Order Mark

CLI

Call Level Interface

CLOB

Character Large Object

CPU

Central Processing Unit

CSV

Character-Separated Values

DB

Database

DBMS

Database Management System

DMMP

Device Mapper Multipath

DNS

Domain Name System

ER

Entity Relationship

HBA

Host Bus Adapter

ID

Identification number

IEF

Integrity Enhancement Facility

IP

Internet Protocol

IPD

Implementation Parameter Descriptor

IRD

Implementation Row Descriptor

JAR

Java Archive File

JDBC

Java Database Connectivity

JDK

Java Developer's Kit

JNDI

Java Naming and Directory Interface

JRE

Java Runtime Environment

JTA

Java Transaction API

LOB

Large Object

LRU

Least Recently Used

LV

Logical Volume

LVM

Logical Volume Manager

MSDN

Microsoft Developer Network

NFS

Network File System

NIC

Network Interface Card

NTP

Network Time Protocol

ODBC

Open Database Connectivity

OS

Operating System

PP

Program Product

RAID

Redundant Array of Independent Disks

RDBMS

Relational Database Management System

TLB

Translation Lookaside Buffer

URL

Uniform Resource Locator

VG

Volume Group

WWN

World Wide Name

To Page Top

■ 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:

  • From the File menu, choose Open.

  • Click the Cancel button.

  • In the Enter name entry box, type your name.

Italics

Italics 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 font

A 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.

The table below shows the symbols used in this manual for explaining commands and operands, such as the operands used in server definitions.

Note that these symbols are used for explanatory purposes only; do not specify them in the actual operand or command.

Symbol

Meaning

Example

|

In syntax explanations, a vertical bar separates multiple items, and has the meaning of OR.

adb_sql_text_out = {Y|N}

In this example, the vertical bar means that you can specify either Y or N.

[ ]

In syntax explanations, square brackets indicate that the enclosed item or items are optional.

adbsql [-V]

In this example, the square brackets mean that you can specify adbsql, or you can specify adbsql -V.

{ }

In syntax explanations, curly brackets indicate that only one of the enclosed items is to be selected.

adbcancel {--ALL|-u connection-ID}

In this example, the curly brackets mean that you can specify either --ALL or -u connection-ID.

...

In syntax explanations, an ellipsis (...) indicates that the immediately preceding item can be repeated as many times as necessary.

adbbuff -n DB-area-name[, DB-area-name] ...

In this example, the ellipsis means that you can specify DB-area-name as many times as necessary.

{{ }}

In syntax explanations, double curly brackets indicate that the enclosed items can be repeated as a single unit.

{{adbinitdbarea -n data-DB-area-name}}

In this example, the double curly brackets mean that you can specify adbinitdbarea -n data-DB-area-name as many times as necessary.

X

(underline)

In syntax explanations, underlined characters indicate a default value.

adb_import_errmsg_lv = {0|1}

In this example, the underline means that the value 0 is assumed by HADB when the operand is omitted.

~

A swung dash indicates that the text following it explains the properties of the specified value.

adb_sys_max_users = maximum-number-of-concurrent-connections

~ <integer> ((1 to 1024)) <<10>>

In this example, the text following the swung dash means that you can specify an integer in the range from 1 to 1024. If the operand is not specified, the value 10 is assumed by HADB.

< >

Single angle brackets explain the data type of the specified value.

(( ))

Double parentheses indicate the scope of the specified value.

<< >>

Double angle brackets indicate a default value.

To Page Top

■ Conventions: Path names

To Page Top

■ Conventions: Symbols used in mathematical formulas

The following table explains special symbols used by this manual in mathematical formulas:

Symbol

Meaning

↑ ↑

Round up the result to the next integer.

Example: The result of ↑34 ÷ 3↑ is 12.

↓ ↓

Discard digits following the decimal point.

Example: The result of ↓34 ÷ 3↓ is 11.

MAX

Select the largest value as the result.

Example: The result of MAX(3 × 6, 4 + 7) is 18.

MIN

Select the smallest value as the result.

Example: The result of MIN(3 × 6, 4 + 7) is 11.

To Page Top

■ Conventions: Syntax elements

Syntax element notation

Meaning

<path name>

The following characters can be used in path names:

  • In Linux

    Alphanumeric characters, hash mark (#), hyphen (-), forward slash (/), at mark (@), and underscore (_)

  • In Windows

    Alphanumeric characters, hash mark (#), hyphen (-), forward slash (/), at mark (@), underscore (_), backslash (\), and colon (:)

Note, however, that the characters that can be used might differ depending on the operating system.

<OS path name>

For an OS path name, all characters that can be used in a path name in the operating system can be used. For details about available characters, see the documentation for the operating system you are using.

<character string>

Any character string can be specified.

<integer suffixed by the unit>

Specify the value in a format consisting of a numeric character (in the range from 0 to 9) followed by a unit (MB (megabyte), GB (gigabyte), or TB (terabyte)). Do not enter a space between the numeric character and the unit.

  • Examples of correct specification

    1024MB

    512GB

    32TB

  • Example of specification that causes an error

    512 GB

To Page Top

■ Conventions: KB, MB, GB, TB, PB, and EB

This manual uses the following conventions:

To Page Top

■ 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.

To Page Top

■ HADB database language acknowledgements

The interpretations and specifications developed by Hitachi, Ltd. for the HADB database language specifications described in this manual are based on the standards listed below. Along with citing the standards relevant to HADB 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 Standard

ISO: International Organization for Standardization

IEC: International Electrotechnical Commission

To Page Top

All Rights Reserved. Copyright (C) 2012, 2020, Hitachi, Ltd.