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.

Characters of the language are classified as letters, digits, or special characters.

A letter is any of the 26 uppercase (A through Z) and 26 lowercase (a through z) letters of the English alphabet.

A digit is any of the characters 0 through 9.

A special character is any of the characters listed below:

	space or blank	-	minus sign
"	quotation mark or double-quote or double quotation mark	.	period
%	percent	/	slash
&	ampersand	:	colon
'	apostrophe or single quote or single quotation mark	;	semicolon
(	left parenthesis	<	less than
)	right parenthesis	=	equals
*	asterisk	>	greater than
+	plus sign	?	question mark
,	comma	_	underline or underscore
\|	vertical bar	‸	caret
!	exclamation mark	[	left bracket
{	left brace	]	right bracket
}	right brace	¬	not

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

Tokens are classified as ordinary or delimiter tokens:

An ordinary token is a numeric constant, an ordinary identifier, a host identifier, or a keyword.
Examples
```
    1        .1        +2        SELECT        E        3
```
A delimiter token is a string constant, a delimited identifier, an operator symbol, or any of the special characters shown in the syntax diagrams. A question mark (?) is also a delimiter token when it serves as a parameter marker, as explained under prepare.
Examples
```
    ,      'Myst Island'        "fld1"        =        .
```

Spaces:

A space is a sequence of one or more blank characters.

Control Characters:

A control character is a special character that is used for string alignment. The following table contains the control characters that are handled by the database manager:

Table 1. Control Characters
Control Character	EBCDIC Hex Value	Unicode Graphic Hex Value
Tab	05	U+0009
Form Feed	0C	U+000C
Carriage Return	0D	U+000D
New Line	15	U+0085
Line Feed (New line)	25	U+000A
DBCS Space	—	U+3000

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.

The following list defines these terms.

alias-name

A qualified or unqualified name that designates an alias. The qualified form of an alias-name depends on the naming option. For SQL naming, the qualified form is a schema-name followed by a period (.) and an SQL identifier. For system naming, the qualified form is aschema-name followed by a slash (/) followed by an SQL identifier.An alias-name can specify either the name of the alias or the system object name of the alias.

array-type-name

A qualified or unqualified name that designates an array type. The qualified form of a array-type-name depends upon the naming option. For SQL naming, the qualified form is a schema-name followed by a period (.) and an SQL identifier. For system naming, the qualified form is a schema-name followed by a slash (/) followed by an SQL identifier.For system naming, array-type-names cannot be qualified when used in a parameter data type of an SQL routine or in an SQL variable declaration in an SQL procedure.

authorization-name

A system identifier that designates a user or group of users. An authorization-name is a user profile name on the server. It must not be a delimited identifier that includes lowercase letters or special characters.

column-name

A qualified or unqualified name that designates a column of a table or a view. The unqualified form of a column-name is an SQL identifier. The qualified form is a qualifier followed by a period and an SQL identifier. The qualifier is a table name, a view name, or a correlation name.

For system naming, column names can be qualified using the form schema-name/table-name.column-name when the name is used in the COMMENT and LABEL statements. If column names need to be qualified and correlation names are allowed in the statement, a correlation name can be used to qualify the column. The period form of qualification can also be used.

A column-name can specify either the column name or the system column name of a column of a table or view. If a column-name is delimited, the delimiters are considered to be part of the name when determining the length of the name.

constraint-name

A qualified or unqualified name that designates a constraint on a table. The qualified form of a constraint-name depends on the naming option. For SQL naming, the qualified form is a schema-name followed by a period (.) and an SQL identifier. For system naming, the qualified form is a schema-name followed by a slash (/) followed by an SQL identifier.The implicit or explicit qualifier must be the same as the schema name of the table.

correlation-name

An SQL identifier that designates a table, a view, or individual rows of a table or view.

cursor-name

An SQL identifier that designates an SQL cursor.

descriptor-name

A variable name or string constant that designates an SQL descriptor area (SQLDA). A variable that designates an SQL descriptor area must not have an indicator variable. The form :host-variable:indicator-variable is not allowed.

distinct-type-name

A qualified or unqualified name that designates a distinct type. The qualified form of a distinct-type-name depends upon the naming option. For SQL naming, the qualified form is a schema-name followed by a period (.) and an SQL identifier. For system naming, the qualified form is a schema-name followed by a slash (/) followed by an SQL identifier.

external-program-name

A qualified name, unqualified name, or a character string that designates an external program. The qualified form of an external-program-name depends on the naming option. For SQL naming, the qualified form is a system-schema-name followed by a period (.) and a system identifier. For system naming, the qualified form is a system-schema-name followed by a slash (/) followed by a system identifier.
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.

An alias can be used wherever a table or view name can be used, except:
- Do not use an alias name where a new table or view name is expected, such as in the CREATE TABLE or CREATE VIEW statements. For example, if an alias name of PERSONNEL is created, then a subsequent statement such as CREATE TABLE PERSONNEL will cause an error.
- An alias that refers to an individual partition of a table or member of a database file can only be used in a select statement, CREATE INDEX, DELETE, INSERT, MERGE, SELECT INTO, SET variable, UPDATE, or VALUES INTO statement.
Aliases can also help avoid using file overrides. Not only does an alias perform better than an override, but an alias is also a permanent object that only need be created once.

An alias can be created even though the object that the alias refers to does not exist. However, the object must exist when a statement that references the alias is executed. A warning is returned if the object does not exist when the alias is created. An alias cannot refer to another alias.
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.

After a connection has been established, the authorization ID may be changed using the SET SESSION AUTHORIZATION statement.

Authorization ID's are used by the database manager to provide authorization checking of SQL statements.

An authorization ID applies to every SQL statement. The authorization ID that is used for authorization checking for a static SQL statement depends on the USRPRF value specified on the precompiler command:
- If USRPRF(*OWNER) is specified, or if USRPRF(*NAMING) is specified and SQL naming mode is used, the authorization ID of the statement is the owner of the non-distributed SQL program. For distributed SQL programs, it is the owner of the SQL package.
- If USRPRF(*USER) is specified, or if USRPRF(*NAMING) is specified and system naming mode is used, the authorization ID of the statement is the authorization ID of the user running the non-distributed SQL program. For distributed SQL programs, it is the authorization ID of the user at the current server.
The authorization ID that is used for authorization checking for a dynamic SQL statement also depends on where and how the statement is executed:
- If the statement is prepared and executed from a non-distributed program:
  - If the USRPRF value is *USER and the DYNUSRPRF value is *USER for the program, the authorization ID that applies is the ID of the user running the non-distributed program. This is called the run-time authorization ID.
  - If the USRPRF value is *OWNER and the DYNUSRPRF value is *USER for the program, the authorization ID that applies is the ID of the user running the non-distributed program.
  - If the USRPRF value is *OWNER and the DYNUSRPRF value is *OWNER for the program, the authorization ID that applies is the ID of the owner of the non-distributed program.
- If the statement is prepared and executed from a distributed program:
  - If the USRPRF value is *USER and the DYNUSRPRF value is *USER for the SQL package, the authorization ID that applies is the ID of the user running the SQL package at the current server. This is also called the run-time authorization ID.
  - If the USRPRF value is *OWNER and the DYNUSRPRF value is *USER for the SQL package, the authorization ID that applies is the ID of the user running the SQL package at the current server.
  - If the USRPRF value is *OWNER and the DYNUSRPRF value is *OWNER for the SQL package, the authorization ID that applies is the ID of the owner of the SQL package at the current server.
- If the statement is issued interactively, the authorization ID that applies is the ID of the user that issued the Start SQL (STRSQL) command.
- If the statement is executed from the RUNSQLSTM command, the authorization ID that applies is the ID of the user that issued the RUNSQLSTM command.
- If the statement is executed from REXX, the authorization ID that applies is the ID of the user that issued the STRREXPRC command.
On the IBM® i operating system, the run-time authorization ID is the user profile of the job.

An authorization-name specified in an SQL statement should not be confused with the authorization ID of the statement. An authorization-name is an identifier that is used in GRANT and REVOKE statements to designate a target of the grant or revoke. The premise of a grant of privileges to X is that X will subsequently be the authorization ID of statements which require those privileges. A group user profile can also be used when checking authority for an SQL statement. For information about group user profiles
Procedure resolution
Given a procedure invocation, DB2 must decide which of the possible procedures with the same name to execute.
- Let A be the number of arguments in a procedure invocation.
- Let P be the number of parameters in a procedure signature.
- Let N be the number of parameters without a default.
Candidate procedures for resolution of a procedure invocation are selected based on the following criteria:
- Each candidate procedure has a matching name and an applicable number of parameters. An applicable number of parameters satisfies the condition N ≤ A ≤ P.
- Each candidate procedure has parameters such that for each named argument in the CALL statement there exists a parameter with a matching name that does not already correspond to a positional (or unnamed) argument.
- Each parameter of a candidate procedure that does not have a corresponding argument in the CALL statement, specified by either position or name, is defined with a default.
- Each candidate procedure from a set of one or more schemas has the EXECUTE privilege associated with the authorization ID of the CALL statement. The authorities of any objects referenced in a default expression are not considered.
In addition, the set of candidate procedures depends on how the procedure name is qualified.
- If the procedure name is unqualified, procedure resolution is done as follows:
  Search all procedures with a schema in the SQL path for candidate procedures. If one or more candidate procedures are found in the schemas of the SQL path, then these candidate procedures are included in the candidate list. If there is a single candidate procedure in the list, resolution is complete. If there are multiple candidate procedures, choose the procedure whose schema is earliest in the SQL path. If there are still multiple candidate procedures, select the candidate procedure with the lowest number of parameters.
  
  If there are no candidate procedures, an error is returned.
- If the procedure name is qualified, procedure resolution is done as follows:
  Search within the schema specified by the qualifier for candidate procedures. If a single candidate procedure exists, resolution is complete. If there are multiple candidate procedures, choose the candidate procedure with the lowest number of parameters and resolution is complete. If the schema does not exist or there are no authorized candidate procedures, an error is returned.
Example 1: There are six FOO procedures, in four different schemas, registered as follows (note that not all required keywords appear):
```
  CREATE PROCEDURE AUGUSTUS.FOO (INT) SPECIFIC FOO_1 ...
  CREATE PROCEDURE AUGUSTUS.FOO (DOUBLE, DECIMAL(15, 3)) SPECIFIC FOO_2 ...
  CREATE PROCEDURE JULIUS.FOO (INT) SPECIFIC FOO_3 ...
  CREATE PROCEDURE JULIUS.FOO (INT, INT, INT) SPECIFIC FOO_4 ...
  CREATE PROCEDURE CAESAR.FOO (INT, INT) SPECIFIC FOO_5 ...
  CREATE PROCEDURE NERO.FOO (INT,INT) SPECIFIC FOO_6 ...
```
The procedure reference is as follows (where I1 and I2 are INTEGER values):
```
  CALL FOO(I1, I2)
```
Assume that the application making this reference has an SQL path established as:
```
 "JULIUS", "AUGUSTUS", "CAESAR"
```
Following through the algorithm, the procedure with specific name FOO_6 is eliminated as a candidate, because the schema "NERO" is not included in the SQL path. FOO_1, FOO_3, and FOO_4 are eliminated as candidates, because they have the wrong number of parameters. The remaining candidates are considered in order, as determined by the SQL path. Note that the types of the arguments and parameters are ignored. The parameters of FOO_5 exactly match the arguments in the CALL, but FOO_2 is chosen because "AUGUSTUS" appears before "CAESAR" in the SQL path.

Data types

The smallest unit of data that can be manipulated in SQL is called a value.

How values are interpreted depends on the attributes of their source, which includes the data type, length, precision, scale, and CCSID. The sources of values are:

Columns
Constants
Expressions
Functions
Special registers
Variables (such as host variables, SQL variables, global variables, parameter markers and parameters of routines)

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.

The database manager considers the promotion of data types when:

performing function resolution
casting distinct types
assigning distinct types to built-in data types

For each data type, Table 1 shows the precedence list (in order) that the database manager uses to determine the data types to which each data type can be promoted. The table indicates that the best choice is the same data type and not promotion to another data type. Note that the table also shows data types that are considered equivalent during the promotion process. For example, CHARACTER and GRAPHIC are considered to be equivalent data types.

Table 1. Data Type Precedence Table

Data Type

Data Type Precedence List (in best-to-worst order)

SMALLINT

SMALLINT, INTEGER, BIGINT, decimal, real, double, DECFLOAT

INTEGER

INTEGER, BIGINT, decimal, real, double, DECFLOAT

BIGINT

BIGINT, decimal, real, double, DECFLOAT

decimal

decimal, real, double, DECFLOAT

real

real, double, DECFLOAT

double

double, DECFLOAT

DECFLOAT

CHAR or GRAPHIC

CHAR or GRAPHIC, VARCHAR or VARGRAPHIC, CLOB or DBCLOB

VARCHAR or VARGRAPHIC

VARCHAR or VARGRAPHIC, CLOB or DBCLOB

CLOB or DBCLOB

CHAR FOR BIT DATA

CHAR, VARCHAR, CLOB, BINARY, VARBINARY, BLOB

VARCHAR FOR BIT DATA

VARCHAR, CLOB, VARBINARY, BLOB

BINARY

BINARY, VARBINARY, BLOB, CHAR FOR BIT DATA, VARCHAR FOR BIT DATA

VARBINARY

VARBINARY, BLOB, VARCHAR FOR BIT DATA

BLOB

DATE

TIME

TIMESTAMP

DATALINK

ROWID

XML

ARRAY

udt

same udt

Note:

The lower case types above are defined as follows:

decimal: = DECIMAL(p,s) or NUMERIC(p,s)
real: = REAL or FLOAT(n) where n is a specification for single precision floating point
double: = DOUBLE, DOUBLE PRECISION, FLOAT or FLOAT(n) where n is a specification for double precision floating point
udt: = a user-defined type

Shorter and longer form synonyms of the data types listed are considered to be the same as the synonym listed.

Character and graphic strings are only compatible for Unicode data.

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.

Data type promotion, as described in Promotion of data types, is one example of when a value with one data type needs to be cast to a new data type. A data type that can be changed to another data type is castable from the source data type to the target data type.

The casting of one data type to another can occur implicitly or explicitly. The cast functions or CAST specification can be used to explicitly change a data type. The database manager might implicitly cast data types during assignments that involve a distinct type . In addition, when you create a sourced user-defined function, the data types of the parameters of the source function must be castable to the data types of the function that you are creating.

If truncation occurs when a character or graphic string is cast to another data type, a warning occurs if any non-blank characters are truncated. This truncation behavior is similar to retrieval assignment of character or graphic strings

If truncation occurs when a binary string is cast to another data type, a warning occurs. This truncation behavior is similar to retrieval assignment of binary strings.

For casts that involve an array type, the source and target data type must both be the same array type.

For casts that involve a distinct type as either the data type to be cast to or from, Table 1 shows the supported casts. For casts between built-in data types, Table 1 shows the supported casts.

Table 1. Supported Casts When a Distinct Type is Involved
Data Type ...	Is Castable to Data Type ...
Distinct type DT	Source data type of distinct type DT
Source data type of distinct type DT	Distinct type DT
Distinct type DT	Distinct type DT
Data type A	Distinct type DT where A is promotable to the source data type of distinct type DT (see Promotion of data types)
INTEGER	Distinct type DT if DT's source type is SMALLINT
DOUBLE	Distinct type DT if DT's source data type is REAL
VARCHAR	Distinct type DT if DT's source data type is CHAR or GRAPHIC
VARGRAPHIC	Distinct type DT if DT's source data type is GRAPHIC or CHAR
VARBINARY	Distinct type DT if DT's source data type is BINARY

Character and graphic strings are only compatible for Unicode data. Character bit data and graphic strings are not compatible.

When a distinct type is involved in a cast, a cast function that was generated when the distinct type was created is used. How the database manager chooses the function depends on whether function notation or the CAST specification syntax is used. For details, see Function resolution, and CAST specification. Function resolution is used for both. However, in a CAST specification, when an unqualified distinct type is specified as the target data type, the database manager resolves the schema name of the distinct type and then uses that schema name to locate the cast function.

The following table describes the supported casts between built-in data types.

Table 2. Supported Casts Between Built-In Data Types
Target Data Type → Source Data Type ↓	SMALLINT INTEGER BIGINT	DECIMAL NUMERIC	REAL DOUBLE	DECFLOAT	CHAR VARCHAR CLOB	GRAPHIC VARGRAPHIC DBCLOB	BINARY VARBINARY BLOB	DATE	TIME	TIMESTAMP	ROWID	DATALINK	XML
SMALLINT	Y	Y	Y	Y	Y	Y¹	—	—	—	—	—	—	—
INTEGER	Y	Y	Y	Y	Y	Y¹	—	—	—	—	—	—	—
BIGINT	Y	Y	Y	Y	Y	Y¹	—	—	—	—	—	—	—
DECIMAL	Y	Y	Y	Y	Y	Y¹	—	—	—	—	—	—	—
NUMERIC	Y	Y	Y	Y	Y	Y¹	—	—	—	—	—	—	—
REAL	Y	Y	Y	Y	Y	Y¹	—	—	—	—	—	—	—
DOUBLE	Y	Y	Y	Y	Y	Y¹	—	—	—	—	—	—	—
DECFLOAT	Y	Y	Y	Y	Y	Y¹	—	—	—	—	—	—	—
CHAR	Y	Y	Y	Y	Y	Y¹	Y	Y	Y	Y	Y	—	—
VARCHAR	Y	Y	Y	Y	Y	Y¹	Y	Y	Y	Y	Y	—	—
CLOB	Y	Y	Y	Y	Y	Y¹	Y	—	—	—	Y	—	—
GRAPHIC	Y¹	Y¹	Y¹	Y¹	Y¹	Y	Y	Y¹	Y¹	Y¹	—	—	—
VARGRAPHIC	Y¹	Y¹	Y¹	Y¹	Y¹	Y	Y	Y¹	Y¹	Y¹	—	—	—
DBCLOB	Y¹	Y¹	Y¹	Y¹	Y¹	Y	Y	—	—	—	—	—	—
BINARY	—	—	—	—	Y	—	Y	—	—	—	—	—	—
VARBINARY	—	—	—	—	Y	—	Y	—	—	—	—	—	—
BLOB	—	—	—	—	Y	—	Y	—	—	—	—	—	—
DATE	—	—	—	—	Y	—	—	Y	—	Y	—	—	—
TIME	—	—	—	—	Y	—	—	—	Y	Y	—	—	—
TIMESTAMP	—	—	—	—	Y	—	—	Y	Y	Y	—	—	—
ROWID	—	—	—	—	Y	—	Y	—	—	—	Y	—	—
DATALINK	—	—	—	—	—	—	—	—	—	—	—	Y	—
XML	—	—	—	—	—	—	—	—	—	—	—	—	Y

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.

The basic rule for both operations is that the data type of the operands involved must be compatible. The compatibility rule also applies to UNION, EXCEPT, INTERSECT, concatenation, CASE expressions, and the CONCAT, VALUE, COALESCE, IFNULL, MIN, and MAX scalar functions.

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 of the current server.

The special registers can be referenced as follows:


>>-+-+-CURRENT CLIENT_ACCTNG-+------------+--------------------><
   | '-CLIENT ACCTNG---------'            |   
   +-+-CURRENT CLIENT_APPLNAME-+----------+   
   | '-CLIENT APPLNAME---------'          |   
   +-+-CURRENT CLIENT_PROGRAMID-+---------+   
   | '-CLIENT PROGRAMID---------'         |   
   +-+-CURRENT CLIENT_USERID-+------------+   
   | '-CLIENT USERID---------'            |   
   +-+-CURRENT CLIENT_WRKSTNNAME-+--------+   
   | '-CLIENT WRKSTNNAME---------'        |   
   +-+-CURRENT DATE-----+-----------------+   
   | |              (1) |                 |   
   | '-CURRENT_DATE-----'                 |   
   +---CURRENT DEBUG MODE-----------------+   
   +---CURRENT DECFLOAT ROUNDING MODE-----+   
   +---CURRENT DEGREE---------------------+   
   +---CURRENT IMPLICIT XMLPARSE OPTION---+   
   +-+-CURRENT PATH----------+------------+   
   | +-CURRENT FUNCTION PATH-+            |   
   | |              (1)      |            |   
   | '-CURRENT_PATH----------'            |   
   +-+-CURRENT SCHEMA-----+---------------+   
   | |                (1) |               |   
   | '-CURRENT_SCHEMA-----'               |   
   +-+-CURRENT SERVER-+-------------------+   
   | '-CURRENT_SERVER-'                   |   
   +-+-CURRENT TIME-----+-----------------+   
   | |              (1) |                 |   
   | '-CURRENT_TIME-----'                 |   
   +-+-CURRENT TIMESTAMP-----+------------+   
   | |                   (1) |            |   
   | '-CURRENT_TIMESTAMP-----'            |   
   +-+-CURRENT TIMEZONE-+-----------------+   
   | '-CURRENT_TIMEZONE-'                 |   
   +-+-SESSION_USER-+---------------------+   
   | |      (1)     |                     |   
   | '-USER---------'                     |   
   '---SYSTEM_USER------------------------'


Column names


The meaning of a column name depends on its context.


A column name can be used to:



Declare the name of a column, as in a CREATE TABLE statement.
Identify a column, as in a CREATE INDEX statement.
Specify values of the column, as in the following contexts:
In an aggregate function, a column name specifies all values of the column in the group or intermediate result table to which the function is applied. Groups and intermediate result tables are explained under Quiries. For example, MAX(SALARY) applies the function MAX to all values of the column SALARY in a group.
In a GROUP BY or ORDER BY clause, a column name specifies all values in the intermediate result table to which the clause is applied. For example, ORDER BY DEPT orders an intermediate result table by the values of the column DEPT.
In an expression, a search condition, or a scalar function, a column name specifies a value for each row or group to which the construct is applied. For example, when the search condition CODE = 20 is applied to some row, the value specified by the column name CODE is the value of the column CODE in that row.


Provide a column name for an expression to temporarily rename a column, as in the correlation-clause of a table-reference in a FROM clause, or in the AS clause in the select-clause.



Variables


A variable in an SQL statement specifies a value that can be changed when the SQL statement is executed.


There are several types of variables used in SQL statements:




 global variable 
Global variables are defined using the CREATE VARIABLE statement. For more information about how to refer to global variables .
host variable
Host variables are defined by statements of a host language. For more information about how to refer to host variables.
transition variable
Transition variables are defined in a trigger and refer to either the old or new values of columns. For more information about how to refer to transition variables .
SQL variable
SQL variables are defined by an SQL compound statement in an SQL function, SQL procedure, or trigger. For more information about SQL variables.
SQL parameter
SQL parameters are defined in an CREATE FUNCTION (SQL Scalar), CREATE FUNCTION (SQL Table), or CREATE PROCEDURE (SQL) statement. For more information about SQL parameters.
parameter marker
Variables cannot be referenced in dynamic SQL statements. Parameter markers are defined in an SQL descriptor and used instead. For more information about parameter markers




A host variable is a COBOL data item, an RPG field, or a PLI, REXX, C++, C, or Java variable that is referenced in an SQL statement. Host variables are defined by statements of the host language.
Variables in dynamic SQL
In dynamic SQL statements, parameter markers are used instead of variables. A parameter marker is a question mark (?) that represents a position in a dynamic SQL statement where the application will provide a value; that is, where a variable would be found if the statement string were a static SQL statement.
References to LOB or XML variables
You can define regular LOB or XML variables, LOB or XML locator variables, and LOB or XML file reference variables.
References to XML variables
XML variables are defined in host languages as a type of LOB variable.
Host Structure
A host structure is a COBOL group, PL/I, C, or C++ structure, or RPG data structure that is referenced in an SQL statement. In Java and REXX, there is no equivalent to a host structure.
Host Array Structure
In PL/I, C++, and C, a host structure array is a structure name having a dimension attribute. In COBOL, it is a one-dimensional table. In RPG, it is an occurrence data structure. In ILE RPG, it can also be a data structure with the keyword DIM.


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 time stamp data type as the result.
                                    Types of functions
                    There are several ways to classify functions.
Function invocation
Each reference to a scalar or aggregate function (either built-in or user-defined) conforms to specific syntax.
Function resolution
A function is invoked by its function name, which is implicitly or explicitly qualified with a schema name, followed by parentheses that enclose the arguments to the function.
Determining the best fit
There might be more than one function with the same name that is a candidate for execution. In that case, the database manager determines which function is the best fit for the invocation by comparing the argument and parameter data types. Note that the data type of the result of the function or the type of function (aggregate, scalar, or table) under consideration does not enter into this determination.
Best fit considerations
Once the function is selected, there are still possible reasons why the use of the function may not be permitted. Each function is defined to return a result with a specific data type. If this result data type is not compatible within the context in which the function is invoked, an error will occur.
Expressions
An expression specifies a value.
Authorization: The use of some of the expressions, such as a scalar-fullselect, sequence-reference, function-invocation, or cast-specificationmay require having the appropriate authorization. For these expressions, the privileges held by the authorization ID of the statement must include the following authorization:
global variable. For information about authorization considerations.
scalar-fullselect. For information about authorization considerations.
sequence-reference. The authorization to reference the sequence. For information about authorization considerations.
function-invocation. The authorization to execute a user-defined function. For information about authorization considerations.
array-element-specification. The authorization to reference an array type. For information about authorization considerations.
cast-specification. The authorization to reference a user-defined type. For information about authorization considerations.


Predicates
A predicate specifies a condition that is true, false, or unknown about a given value, row,or group.


The following rules apply to all types of predicates:



Predicates are evaluated after the expressions that are operands of the predicate.
All values specified in the same predicate must be compatible.
The value of a variable may be null (that is, the variable may have a negative indicator variable).
The CCSID conversion of operands of predicates involving two or more operands are done according to Conversion rules for comparison:.
Use of a Data Link value is limited to the NULL predicate.



Search conditions
A search condition specifies a condition that is true, false, or unknown about a given row or group.


>>-+-----+--+-predicate----------+------------------------------>
   '-NOT-'  '-(search-condition)-'   

   .----------------------------------------------.   
   V                                              |   
>----+------------------------------------------+-+------------><
     '-+-AND-+--+-----+--+-predicate----------+-'     
       '-OR--'  '-NOT-'  '-(search-condition)-'       





The result of a search condition is derived by application of the specified logical operators (AND, OR, NOT) to the result of each specified predicate. If logical operators are not specified, the result of the search condition is the result of the specified predicate.

Source http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzsqlelem.htm

Structure

Programming Program for Information Processing (Language Elements)

Characters

Tokens

Spaces:

Control Characters:

Identifiers

Naming conventions

Aliases

Authorization IDs and authorization names

Procedure resolution

Data types

Promotion of data types

Casting between data types

Assignments and comparisons

Conversion rules for operations that combine strings

Constants

Special registers

Column names

Variables

Walang komento:

Mag-post ng isang Komento