IBM Support

New QAQQINI option - SQL_GVAR_BUILD_RULE

News


Abstract

New QAQQINI option - SQL_GVAR_BUILD_RULE

Content

You are in: IBM i Technology Updates > Db2 for i - Technology UpdatesDb2 for i Functional Enhancements > New QAQQINI option - SQL_GVAR_BUILD_RULE

This new QSQQINI option influences whether global variables must exist or not when building SQL routines or executing SQL precompiles.
This option has no affect on dynamic SQL statements.

Supported values:
*DEFAULT - The default value is set to *DEFER

*DEFER - Global variables do not need to exist when an SQL routine is created or the SQL precompiler is run. Since global variables are not required to exist, the create will not fail when an incorrect column name or routine variable is encountered because it won't be known at create time whether the name is a global variable. Incorrect name usage will result in SQL0206 - "Column or global variable &1 not found." failures when the statement is executed.

*EXIST - Global variables referenced by SQL must exist when the SQL routine is created or the SQL precompiler is run. Using this option, an SQL0206 will be issued at create time.
This choice is similar to the behavior of previous releases, where incorrect name usage will fail at create time with either:
SQL0312 - "Variable &1 not defined or not usable." or
SQL5001 - "Column qualifier or table &2 undefined."

Memo To Users detail:

SQL Global Variables
The name of a global variable can be the same as the name of a column in a table or view that is
referenced in an SQL statement, as well as the name of an SQL variable or an SQL parameter in an SQL
routine. When the name is not qualified, or if it is qualified but is still ambiguous, the following rules
describe of precedence of resolution:
* The name is checked if it is the name of a column of any existing table or view referenced in the
statement at the current server.
* If used in an SQL routine, the name is checked to see if it is the name of an SQL variable, SQL
parameter, or transition variable.
* If not found by either of these rules, it is assumed to be a global variable.

In previous releases, when creating SQL procedures and functions, if all the tables referenced in the
routine existed, and an unrecognized variable or column name was encountered, the CREATE ROUTINE
would fail with SQL0312 - "Variable &1 not defined or not usable." or SQL5001 - " Column qualifier or
table &2 undefined." Due to the implementation of SQL Global Variables, in 7.1 the routine is successfully
created because the precedence of resolution rules types the identifier as a global variable. The routine
might fail later with an SQL0206 - "Column or global variable &1 not found." when it is called or
referenced.

[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}}]

Document Information

Modified date:
14 January 2020

UID

ibm11167418