DB2 Version 9.7 for Linux, UNIX, and Windows

References to SQL parameters, SQL variables, and global variables

SQL parameters, SQL variables, and global variables can be referenced anywhere in an SQL procedure statement where an expression or variable can be specified.

Host variables cannot be specified in SQL routines, SQL triggers or dynamic compound statements. SQL parameters can be referenced anywhere in the routine body, and can be qualified with the routine name. SQL variables can be referenced anywhere in the compound statement in which they are declared, and can be qualified with the label name specified at the beginning of the compound statement. If an SQL parameter or SQL variable has a row data type, fields can be referenced anywhere an SQL parameter or SQL variable can be referenced. Global variables can be referenced within any expression as long as the expression is not required to be deterministic. The following scenarios require deterministic expressions, which preclude the use of global variables:

Check constraints
Definitions of generated columns
Refresh immediate MQTs

All SQL parameters, SQL variables, row variable fields, and global variables are considered nullable. The name of an SQL parameter, SQL variable, row variable field, or global variable in an SQL routine can be the same as the name of a column in a table or view referenced in the routine. The name of an SQL variable or row variable field can also be the same as the name of another SQL variable or row variable field declared in the same routine. This can occur when the two SQL variables are declared in different compound statements. The compound statement that contains the declaration of an SQL variable determines the scope of that variable. For more information, see "Compound SQL (Procedure)".

The name of an SQL variable or SQL parameter in an SQL routine can be the same as the name of an identifier used in certain SQL statements. If the name is not qualified, the following rules describe whether the name refers to the identifier or to the SQL parameter or SQL variable:

In the SET PATH and SET SCHEMA statements, the name is checked as an SQL parameter or SQL variable. If not found as an SQL variable or SQL parameter, it is used as an identifier.
In the CONNECT, DISCONNECT, RELEASE, and SET CONNECTION statements, the name is used as an identifier.

Names that are the same should be explicitly qualified. Qualifying a name clearly indicates whether the name refers to a column, SQL variable, SQL parameter, row variable field, or global variable. If the name is not qualified, or qualified but still ambiguous, the following rules describe whether the name refers to a column, an SQL variable, an SQL parameter, or a global variable:

If the tables and views specified in an SQL routine body exist at the time the routine is created, the name is first checked as a column name. If not found as a column, it is then checked as an SQL variable in the compound statement, then checked as an SQL parameter, and then, finally, checked as a global variable.
If the referenced tables or views do not exist at the time the routine is created, the name is first checked as an SQL variable in the compound statement, then as an SQL parameter, and then as a global variable. The variable can be declared within the compound statement that contains the reference, or within a compound statement in which that compound statement is nested. If two SQL variables are within the same scope and have the same name, which can happen if they are declared in different compound statements, the SQL variable that is declared in the innermost compound statement is used. If not found, it is assumed to be a column.