Language elements

This section defines the basic syntax of SQL and language elements that are common to many SQL statements.

Characters
The basic symbols of keywords and operators in the SQL language are single-byte characters that are part of all character sets supported by the IBM relational database products.
Tokens
The basic syntactical units of the language are called tokens. A token consists of one or more characters, excluding blanks, control characters, and characters within a string constant or delimited identifier. (These terms are defined later.)
Identifiers
An identifier is a token used to form a name. An identifier in an SQL statement is an SQL identifier, a system identifier, or a host identifier.
Naming conventions
The rules for forming a name depend on the type of the object designated by the name and the naming option (*SQL or *SYS). The naming option is specified on the CRTSQLxxx, RUNSQLSTM, and STRSQL commands. The SET OPTION statement can be used to specify the naming option within the source of a program containing embedded SQL. The syntax diagrams use different terms for different types of names.
Aliases
An alias can be thought of as an alternative name for a table, partition of a table, view, or member of a database file. A table or view in an SQL statement can be referenced by its name or by an alias. An alias can refer to a table, partition of a table, view, or database file member within the same or a remote relational database.
Authorization IDs and authorization names
An authorization ID is a character string that is obtained by the database manager when a connection is established between the database manager and either an application process or a program preparation process. It designates a set of privileges. It may also designate a user or a group of users, but this property is not controlled by the database manager.
Procedure resolution
Given a procedure invocation, DB2 must decide which of the possible procedures with the same name to execute.
Data types
The smallest unit of data that can be manipulated in SQL is called a value.
Promotion of data types
Data types can be classified into groups of related data types. Within such groups, an order of precedence exists where one data type is considered to precede another data type. This precedence enables the database manager to support the promotion of one data type to another data type that appears later in the precedence ordering. For example, the data type CHAR can be promoted to VARCHAR; INTEGER can be promoted to DOUBLE PRECISION; but CLOB is NOT promotable VARCHAR.
Casting between data types
There are many occasions when a value with a given data type needs to be cast (changed) to a different data type or to the same data type with a different length, precision, or scale.
Assignments and comparisons
The basic operations of SQL are assignment and comparison. Assignment operations are performed during the execution of CALL, INSERT, UPDATE, FETCH, SELECT, SET variable, and VALUES INTO statements. Comparison operations are performed during the execution of statements that include predicates and other language elements such as MAX, MIN, DISTINCT, GROUP BY, and ORDER BY.
Rules for result data types
The data types of a result are determined by rules which are applied to the operands in an operation. This section explains those rules.
Conversion rules for operations that combine strings
The operations that combine strings are concatenation, UNION, UNION ALL, EXCEPT, and INTERSECT. (These rules also apply to the MAX, MIN, VALUE, COALESCE, IFNULL, and CONCAT scalar functions and CASE expressions.) In each case, the CCSID of the result is determined at bind time, and the execution of the operation may involve conversion of strings to the coded character set identified by that CCSID.
Constants
A constant (also called a literal) specifies a value. Constants are classified as string constants or numeric constants. String constants are further classified as character or graphic. Numeric constants are further classified as integer, floating point, or decimal.
Special registers
A special register is a storage area that is defined for an application process by database manager and is used to store information that can be referenced in SQL statements. A reference to a special register is a reference to a value provided by the current server. If the value is a string, its CCSID is a default CCSID.
Column names
The meaning of a column name depends on its context.
Variables
A variable in an SQL statement specifies a value that can be changed when the SQL statement is executed.
Functions
A function is an operation denoted by a function name followed by one or more operands that are enclosed in parentheses. It represents a relationship between a set of input values and a set of result values. The input values to a function are called arguments. For example, a function can be passed two input arguments that have date and time data types and return a value with a timestamp data type as the result.
Expressions
An expression specifies a value.
Predicates
A predicate specifies a condition that is true, false, or unknown about a given value, row, or group.
Search conditions
A search condition specifies a condition that is true, false, or unknown about a given row or group.

Parent topic: Db2 for i SQL reference