This document describes basic syntactical items, which are referenced within the individual SQL statement descriptions.
This document covers the following topics:
The constants used in the syntactical descriptions of the Natural SQL statements are:
constant
|
The item constant refers to either a
Natural constant or an SQL
datetime constant.
|
integer
|
The item integer always represents
an integer constant.
|
Note:
If the character for decimal point notation (session parameter DC
) is set to a comma (,), any
specified numeric constant must not be followed directly by a comma, but must be separated
from it by a blank character; otherwise an error or wrong results occur.
Invalid Syntax: | Valid Syntax: |
---|---|
|
|
An SQL datetime constant is a character string constant of a particular format that specifies one of the following:
DATE string-constant
|
Specifies an SQL date constant, for example: DATE
'2013-15-01' .
|
TIME string-constant
|
Specifies an SQL time constant, for example: TIME
'10:30:15' .
|
TIMESTAMP
string-constant |
Specifies an SQL time stamp constant, for example:
TIMESTAMP '2014-15-01 10:20:15.123456' .
|
For information on the valid string-constant
formats, refer to IBM's Db2 SQL reference information.
The names used in the syntactical descriptions of the Natural SQL statements are:
The item authorization-identifier
, which is
also called creator name, is used to qualify database tables and views. See also
authorization-identifier
under
table-name below.
The item ddm-name
always refers to the name of
a Natural data definition module (DDM) as created with the Natural
utility SYSDDM
.
The item view-name
always refers to the name of
a Natural view as defined in the DEFINE
DATA
statement.
The item column-name
always refers to the name
of a physical database column.
The item location-name
always denotes the
location of the table. Specification of location-name is optional and belongs to the
SQL Extended Set.
The item table-name
in this section is used to
reference both SQL base tables and SQL viewed tables.
Syntax of item table-name
:
[[location-name.]authorization-identifier.]ddm-name |
Syntax Element Description:
Syntax Element | Description |
---|---|
ddm-name |
A Natural data definition module (DDM) must have been created for a table to be used. The name of such a DDM must be the same as the corresponding database table name or view name. |
location-name |
This optional item specifies the location of the table to be accessed. |
authorization-identifier |
There are two ways of specifying the
authorization-identifier of a
database table or view.
One way corresponds to the standard SQL syntax, in
which the Example: DEFINE DATA LOCAL 01 PERS VIEW OF PERSONNEL 02 NAME 02 AGE END-DEFINE SELECT * INTO VIEW PERS FROM SQL.PERSONNEL ... Alternatively, you can define the
Note: Example: DEFINE DATA LOCAL 01 PERS VIEW OF SQL-PERSONNEL 02 NAME 02 AGE END-DEFINE SELECT * INTO VIEW PERS FROM SQL-PERSONNEL ... If the In addition to being used in Examples: ... DELETE FROM SQL.PERSONNEL WHERE AGE IS NULL ... ... INSERT INTO SQL.PERSONNEL (NAME,AGE) VALUES ('ADKINSON',35) ... ... UPDATE SQL.PERSONNEL SET SALARY = SALARY * 1.1 WHERE AGE > 30 ... |
The item correlation-name
represents an alias
name for a table-name
. It can be used to
qualify column names; it also serves to implicitly qualify fields in a Natural view when
used with the INTO
clause
of the SELECT
statement.
Example:
DEFINE DATA LOCAL 01 PERS-NAME (A20) 01 EMPL-NAME (A20) 01 AGE (I2) END-DEFINE ... SELECT X.NAME , Y.NAME , X.AGE INTO PERS-NAME , EMPL-NAME , AGE FROM SQL-PERSONNEL X , SQL-EMPLOYEES Y WHERE X.AGE = Y.AGE END-SELECT ...
Although in most cases the use of
correlation-names
is not necessary, they may
help to make the statement clearer.
Syntax of item parameter
:
[[:sql-type]:] host-variable [INDICATOR
[: ]
host-variable] [LINDICATOR
[: ]
host-variable]
|
Syntax Element Description:
Syntax Element | Description | ||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sql-type |
An sql-type specifies
the SQL data type of the host-variable
when it is used for Db2 access. The specification of
sql-type is optional as most SQL data
types are implicitly assigned to Natural host-variables. However, for some Natural
host-variables the SQL data type cannot be associated implicitly.
If a |
||||||||||||||||||||
|
|||||||||||||||||||||
See also Natural Formats and SQL Data Types. |
|||||||||||||||||||||
host-variable
|
A host-variable is a Natural
user-defined variable (no system variable) which is referenced in an SQL
statement. It can be either an individual field or defined as part of a Natural
view.
When defined as a receiving field (for example, in the When defined as a sending field (for example, in the See also Natural Formats and SQL Data Types. |
||||||||||||||||||||
[:]
|
Colon:
To comply with SQL standards, a
Example: SELECT NAME INTO :#NAME FROM PERSONNEL WHERE AGE = :VALUE The colon is always required if the variable name is identical to an SQL
reserved word. In a context in which either a
|
||||||||||||||||||||
INDICATOR
|
INDICATOR Clause:
The When specified with a receiving
Example: DEFINE DATA LOCAL 1 NAME (A20) 1 NAMEIND (I2) END-DEFINE SELECT * INTO NAME INDICATOR NAMEIND ... In this example, If a null indicator field has been specified and the column to be retrieved is
null, the value of the null indicator field is negative and the target field is
set to When specified with a sending
Example: DEFINE DATA LOCAL 1 NAME (A20) 1 NAMEIND (I2) UPDATE ... SET NAME = :NAME INDICATOR :NAMEIND WHERE ... In this example, An |
||||||||||||||||||||
LINDICATOR
|
LINDICATOR Clause:
The When specified with a receiving
If the Example DEFINE DATA LOCAL 1 ADDRESSLIND (I2) 1 ADDRESS (A50/1:6) END-DEFINE SELECT * INTO :ADDRESS(*) LINDICATOR :ADDRESSLIND ... In this example, When specified with a sending
Example: DEFINE DATA LOCAL 1 NAMELIND (I2) 1 NAME (A20) 1 AGE (I2) END-DEFINE MOVE 4 TO NAMELIND MOVE 'ABC%' TO NAME SELECT AGE INTO :AGE WHERE NAME LIKE :NAME LINDICATOR :NAMELIND ... A If the If the field is defined as Let a fixed length field be defined with a |
include-columns
This clause belongs to the SQL Extended
Set. It is available in the statements DELETE
, INSERT
, MERGE
and UPDATE
.
Syntax of include-columns
clause:
INCLUDE (column-name
data-type,...)
|
Syntax Element Description:
Syntax Element | Description |
---|---|
INCLUDE |
The keyword INCLUDE introduces a list of columns
that is to be included in the result table of a DELETE ,
INSERT , MERGE or UPDATE statement.
INCLUDE can only be specified when a DELETE ,
INSERT , MERGE or UPDATE statement is
nested in the FROM clause of a SELECT statement.
|
column-name |
Specifies the name of a column of the result table of the
MERGE statement that is not the same name as another include column
or a column in the target table.
|
data-type |
Specifies the data type of the include column. See below. |
built-in-type | ||
distinct-type |
Syntax Element Description:
Syntax Element | Description |
---|---|
built-in-type |
Specifies a built-in data type. See the IBM Db2 for z/OS documentation for a description of built-in types. |
distinct-type |
Specifies a distinct type. |
period-clause
This clause belongs to the SQL Extended
Set. It is available in the statements searched DELETE
and
searched
UPDATE
.
Syntax:
FOR PORTION OF
BUSINESS_TIME
FROM
expr1
TO
expr2 |
Syntax Element Description:
Syntax Element | Description |
---|---|
FOR PORTION OF
BUSINESS_TIME |
Specifies that the DELETE or UPDATE
only applies to row values for the portion of the BUSINESS_TIME
period in the row that is specified by the period clause.
|
FROM
expr1 TO
expr2 |
Specifies that the update applies to rows for the period that
is specified by FROM expr1 TO
expr2 .
If the period that is specified by
the start value and the end value for the If the period that is specified by the start value and the end value for the
|
expr1 and
expr2 |
Specify expressions that return a value of a built-in data
type.
The result of each expression must be comparable to the data type of the
columns of the specified period. Timestamp with |
The Natural data format of a host-variable is converted to an SQL data type according to the following table:
Natural Format/Length | SQL Data Type |
---|---|
An, A DYNAMIC | CHAR (n) , VARCHAR(n), CLOB(n) |
B2 (COMPOPT DB2BIN=OFF )
|
SMALLINT |
B4 (COMPOPT DB2BIN=OFF )
|
INT |
F4 | REAL |
F8 | DOUBLE PRECISION |
I2 | SMALLINT |
I4 | INT |
Nnn.m | NUMERIC (nn+m,m) |
Pnn.m | NUMERIC (nn+m,m) |
T, A8 | TIME |
T (COMPOPT DB2TSTI=ON )
|
TIMESTAMP |
D, A10 | DATE |
A26 | TIMESTAMP |
A19 | TIMESTAMP(0) |
A20+n | TIMESTAMP(n) (1<=n<=12) |
A25 | TIMESTAMP(0) WITH TIMEZONE |
A26+n | TIMESTAMP(n) WITH TIMEZONE (1<=n<=12) |
Gn; for view fields only | GRAPHIC (n) |
Un, U DYNAMIC | GRAPHIC (n) ,VARGRAPHIC(n), DBCLOB(n) CCSID 1200 |
Bn, B DYNAMIC (COMPOPT
DB2BIN=ON )
|
BINARY(n), VARBINARY(n), BLOB(n) |
Bn, B DYNAMIC (COMPOPT
DB2BIN=OFF )
|
CHAR(n), VARCHAR(n), BLOB(n) |
P19.0 | BIGINT |
F8 | DECFLOAT(n) |
A DYNAMIC, B DYNAMIC, U DYNAMIC | XML |
Group structure(I4,I4,I4,A255) prefixed with :BLOBFILE: | BLOB-file-reference |
Group structure(I4,I4,I4,A255) prefixed with :CLOBFILE: | CLOB-file-reference |
Group structure(I4,I4,I4,A255) prefixed with :DBCLOBFILE: | DBCLOB-file-reference |
I4 prefixed with :BLOBLOC: | BLOB-locator |
I4 prefixed with :CLOBLOC: | CLOB-locator |
I4 prefixed with :DBCLOBLOC: | DBCLOB-locator |
Natural does not check whether the converted SQL data type is compatible to the database column. Except for fields of format N, no data conversion is done.
In addition, the following extensions to standard Natural formats are available with Natural SQL:
A one-dimensional array of format A can be used to support alphanumeric columns
longer than 253 bytes. This array must be defined beginning with index 1 and can only
be referenced by using an asterisk (*) as the index. The corresponding SQL data type
is CHAR (n)
, where
n
is the total number of bytes in the
array.
A special host-variable
indicated by the
keyword LINDICATOR
can be used to support variable-length columns. The
corresponding SQL data type is VARCHAR (n)
;
see also the LINDICATOR
clause.
The Natural formats date (D) and time (T) can be used with Natural for Db2. They are
converted to Db2 DATE
and TIME
.
A sending field specified as one-dimensional array without a LINDICATOR
field is converted into the SQL data type VARCHAR
. The length is the total
number of bytes in the array, not taking into account trailing blanks.