Identifiers

Naming conventions and implicit object name qualifications

The rules for forming a database object name depend on the type of the object designated by the name. A name may consist of a single SQL identifier or it may be qualified with one or more identifiers that more specifically identify the object. A period must separate each identifier.

The syntax diagrams use different terms for different types of names. The following list defines these terms.

alias-name

A schema-qualified name that designates an alias.

attribute-name

An identifier that designates an attribute of a structured data type.

array-type-name

A qualified or unqualified name that designates a user-defined array type. The unqualified form of array-type-name is an SQL identifier. An unqualified array type name in an SQL statement is implicitly qualified. The implicit qualifier is a schema name or a module name, which is determined by the context in which array-type-name appears. The qualified form is a schema-name followed by a period and an SQL identifier or a module-name (which can also be qualified by a schema-name) followed by a period and an SQL identifier. If the array type is defined in a module and used outside of the same module, it must be qualified by the module-name.

authorization-name

An identifier that designates a user, group, or role. For a user or a group:

• Valid characters are: 'A' through 'Z'; 'a' through 'z'; '0' through '9'; '#'; '@'; '$'; '_'; '!'; ' '('; ')'; '{'; '}'; '-'; '.'; and '^'.
• The following characters must be delimited with quotation marks when entered through the command line processor: '!'; ' '('; ')'; '{'; '}'; '-'; '.'; and '^'.
• The name must not begin with the characters 'SYS', 'IBM', or 'SQL'.
• The name must not be: 'ADMINS', 'GUESTS', 'LOCAL', 'PUBLIC', or 'USERS'.
• A delimited authorization ID must not contain lowercase letters.

bufferpool-name

An identifier that designates a buffer pool.

column-name

A qualified or unqualified name that designates a column of a table or view. The qualifier is a table name, a view name, a nickname, or a correlation name.

component-name

An identifier that designates a security label component.

condition-name

A qualified or unqualified name that designates a condition. An unqualified condition name in an SQL statement is implicitly qualified, depending on its context. If the condition is defined in a module and used outside of the same module, it must be qualified by the module-name.

constraint-name

An identifier that designates a referential constraint, primary key constraint, unique constraint, or a table check constraint.

correlation-name

An identifier that designates a result table.

cursor-name

An identifier that designates an SQL cursor. For host compatibility, a hyphen character may be used in the name.

cursor-type-name

A qualified or unqualified name that designates a user-defined cursor type. The unqualified form of cursor-type-name is an SQL identifier. An unqualified cursor-type-name in an SQL statement is implicitly qualified, depending on context. The implicit qualifier is a schema name or a module name, which is determined by the context in which cursor-type-name appears. The qualified form is a schema-name followed by a period and an SQL identifier or a module-name (which can also be qualified by a schema-name) followed by a period and an SQL identifier. If the cursor type is defined in a module and used outside of the same module, it must be qualified by the module-name.

cursor-variable-name

A qualified or unqualified name that designates a global variable, local variable or an SQL parameter of a cursor type. An unqualified cursor variable name in an SQL statement is implicitly qualified, depending on context.

data-source-name

An identifier that designates a data source. This identifier is the first part of a three-part remote object name.

db-partition-group-name

An identifier that designates a database partition group.

descriptor-name

A colon followed by a host identifier that designates an SQL descriptor area (SQLDA). For the description of a host identifier, see References to host variables. Note that a descriptor name never includes an indicator variable.

distinct-type-name

A qualified or unqualified name that designates a distinct type. The unqualified form of distinct-type-name is an SQL identifier. An unqualified distinct type name in an SQL statement is implicitly qualified. The implicit qualifier is a schema name or a module name, which is determined by the context in which distinct-type-name appears. The qualified form is a schema-name followed by a period and an SQL identifier or a module-name (which can also be qualified by a schema-name) followed by a period and an SQL identifier. If the distinct type is defined in a module and used outside of the same module, it must be qualified by the module-name.

event-monitor-name

An identifier that designates an event monitor.

function-mapping-name

An identifier that designates a function mapping.

function-name

A qualified or unqualified name that designates a function. The unqualified form of function-name is an SQL identifier. An unqualified function name in an SQL statement is implicitly qualified. The implicit qualifier is a schema name, which is determined by the context in which the function appears. The qualified form could be is a schema-name followed by a period and an SQL identifier or a module-name followed by a period and an SQL identifier. If the function is published in a module and used outside of the same module, it must be qualified by the module-name.

global-variable-name

A qualified or unqualified name that designates a global variable. The unqualified form of global-variable-name is an SQL identifier. An unqualified global variable name in an SQL statement is implicitly qualified. The implicit qualifier is a schema name or a module name, which is determined by the context in which global-variable-name appears. The qualified form is a schema-name followed by a period and an SQL identifier or a module-name (which can also be qualified by a schema-name) followed by a period and an SQL identifier. If the global variable is defined in a module and used outside of the same module, it must be qualified by the module-name.

group-name

An unqualified identifier that designates a transform group defined for a structured type.

host-variable

A sequence of tokens that designates a host variable. A host variable includes at least one host identifier, explained in References to host variables.

index-name

A schema-qualified name that designates an index or an index specification.

label

An identifier that designates a label in an SQL procedure.

method-name

An identifier that designates a method. The schema context for a method is determined by the schema of the subject type (or a supertype of the subject type) of the method.

module-name

A qualified or unqualified name that designates a module. An unqualified module-name in an SQL statement is implicitly qualified. The implicit qualifier is a schema name, which is determined by the context in which the module-name appears. The qualified form is a schema-name followed by a period and an SQL identifier.

nickname

A schema-qualified name that designates a federated server reference to a table or a view.

package-name

A qualified or unqualified name that designates a package.

parameter-name

An identifier that designates a parameter that can be referenced in a procedure, user-defined function, method, or index extension.

partition-name

An identifier that designates a data partition in a partitioned table.

period-name

An identifier that designates a period. SYSTEM_TIME and BUSINESS_TIME are the only supported period names.

procedure-name

A qualified or unqualified name that designates a procedure. The unqualified form of procedure-name is an SQL identifier. An unqualified procedure name in an SQL statement is implicitly qualified. The implicit qualifier is a schema name, which is determined by the context in which the procedure appears. The qualified form is a schema-name followed by a period and an SQL identifier or a module-name followed by a period and an SQL identifier. If the procedure is defined in a module and used outside of the same module, it must be qualified by the module-name.

remote-authorization-name

An identifier that designates a data source user. The rules for authorization names vary from data source to data source.

remote-function-name

A name that designates a function registered to a data source database.

remote-object-name

A three-part name that designates a data source table or view, and that identifies the data source in which the table or view resides. The parts of this name are data-source-name, remote-schema-name, and remote-table-name.

remote-schema-name

A name that designates the schema to which a data source table or view belongs. This name is the second part of a three-part remote object name.

remote-table-name

A name that designates a table or view at a data source. This name is the third part of a three-part remote object name.

remote-type-name

A data type supported by a data source database. Do not use the long form for built-in types (use CHAR instead of CHARACTER, for example).

role-name

An identifier that designates a role.

row-type-name

A qualified or unqualified name that designates a user-defined row type. The unqualified form of row-type-name is an SQL identifier. An unqualified row-type-name in an SQL statement is implicitly qualified. The implicit qualifier is a schema name or a module name, which is determined by the context in which the row-type-name appears. The qualified form is a schema-name followed by a period and an SQL identifier or a module-name (which can also be qualified by a schema-name) followed by a period and an SQL identifier. If the row type is defined in a module and used outside of the same module, it must be qualified by the module-name.

savepoint-name

An identifier that designates a savepoint.

schema-name

An identifier that provides a logical grouping for SQL objects. A schema name used as a qualifier for the name of an object may be implicitly determined:

• from the value of the CURRENT SCHEMA special register
• from the value of the QUALIFIER precompile/bind option
• on the basis of a resolution algorithm that uses the CURRENT PATH special register
• on the basis of the schema name for another object in the same SQL statement.

To avoid complications, it is recommended that the name SESSION not be used as a schema, except as the schema for declared global temporary tables (which must use the schema name SESSION).

security-label-name

A qualified or unqualified name that designates a security label. An unqualified security label name in an SQL statement is implicitly qualified by the applicable security-policy-name, when one applies. If no security-policy-name is implicitly applicable, the name must be qualified.

security-policy-name

An identifier that designates a security policy.

sequence-name

An identifier that designates a sequence.

server-name

An identifier that designates an application server. In a federated system, the server name also designates the local name of a data source.

specific-name

A qualified or unqualified name that designates a specific name. An unqualified specific name in an SQL statement is implicitly qualified, depending on context.

SQL-variable-name

The name of a local variable in an SQL procedure statement. SQL variable names can be used in other SQL statements where a host variable name is allowed. The name can be qualified by the label of the compound statement that declared the SQL variable.

statement-name

An identifier that designates a prepared SQL statement.

storagegroup-name

An identifier that designates a storage group.

supertype-name

A qualified or unqualified name that designates the supertype of a type. An unqualified supertype name in an SQL statement is implicitly qualified, depending on context.

table-name

A schema-qualified name that designates a table.

table-reference

A qualified or unqualified name that designates a table. An unqualified table reference in a common table expression is implicitly qualified by the default schema.

tablespace-name

An identifier that designates a table space.

trigger-name

A schema-qualified name that designates a trigger.

type-mapping-name

An identifier that designates a data type mapping.

type-name

A qualified or unqualified name that designates a type. An unqualified type name in an SQL statement is implicitly qualified, depending on context.

typed-table-name

A schema-qualified name that designates a typed table.

typed-view-name

A schema-qualified name that designates a typed view.

usage-list-name

A schema-qualified name that designates a usage list.

user-defined-type-name

A qualified or unqualified name that designates a user-defined data type. The unqualified form of user-defined-type-name is an SQL identifier. An unqualified user-defined-type-name in an SQL statement is implicitly qualified. The implicit qualifier is a schema name or a module name, which is determined by the context in which user-defined-type-name appears. The qualified form is a schema-name followed by a period and an SQL identifier or a module-name (which can also be qualified by a schema-name) followed by a period and an SQL identifier. If the user-defined data type is defined in a module and used outside of the same module, it must be qualified by the module-name.

view-name

A schema-qualified name that designates a view.

wrapper-name

An identifier that designates a wrapper.

XML-schema-name

A qualified or unqualified name that designates an XML schema.

xsrobject-name

A qualified or unqualified name that designates an object in the XML schema repository.

Aliases for database objects

An alias can be thought of as an alternative name for an SQL object. An SQL object, therefore, can be referred to in an SQL statement by its name or by an alias.

A public alias is an alias which can always be referenced without qualifying its name with a schema name. The implicit qualifier of a public alias is SYSPUBLIC, which can also be specified explicitly.

Aliases are also known as synonyms.

An alias can be used wherever the object it is based on can be used. An alias can be created even if the object does not exist (although it must exist by the time a statement referring to it is compiled). It can refer to another alias if no circular or repetitive references are made along the chain of aliases. An alias can only refer to a module, nickname, sequence, table, view, or another alias within the same database. An alias name cannot be used where a new object name is expected, such as in the CREATE TABLE or CREATE VIEW statements; for example, if the table alias name PERSONNEL has been created, subsequent statements such as CREATE TABLE PERSONNEL... will return an error.

The option of referring to an object by an alias is not explicitly shown in the syntax diagrams, or mentioned in the descriptions of SQL statements.

A new unqualified alias of a given object type, say for a sequence, cannot have the same fully-qualified name as an existing object of that object type. For example, a sequence alias named ORDERID cannot be defined in the KANDIL schema for the sequence named KANDIL.ORDERID.

   SELECT * FROM PBIRD.SALES

   SELECT * FROM DSPN014.DIST4_SALES_148

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.

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 within various SQL statements. An authorization name is used in the CREATE SCHEMA statement to designate the owner of the schema. An authorization name is used in the GRANT and REVOKE statements to designate a target of the grant or revoke operation. Granting privileges to X means that X (or a member of the group or role X) will subsequently be the authorization ID of statements that require those privileges.

Examples

• Assume that SMITH is the user ID and the authorization ID that the database manager obtained when a connection was established with the application process. The following statement is executed interactively:

     GRANT SELECT ON TDEPT TO KEENE

  SMITH is the authorization ID of the statement. Therefore, in a dynamic SQL statement, the default value of the CURRENT SCHEMA special register is SMITH, and in static SQL, the default value of the QUALIFIER precompile/bind option is SMITH. The authority to execute the statement is checked against SMITH, and SMITH is the table-name implicit qualifier based on qualification rules described in Naming conventions and implicit object name qualifications.

  KEENE is an authorization name specified in the statement. KEENE is given the SELECT privilege on SMITH.TDEPT.

• Assume that SMITH has administrative authority and is the authorization ID of the following dynamic SQL statements, with no SET SCHEMA statement issued during the session:

     DROP TABLE TDEPT

  Removes the SMITH.TDEPT table.

     DROP TABLE SMITH.TDEPT

  Removes the SMITH.TDEPT table.

     DROP TABLE KEENE.TDEPT

  Removes the KEENE.TDEPT table. Note that KEENE.TDEPT and SMITH.TDEPT are different tables.

     CREATE SCHEMA PAYROLL AUTHORIZATION KEENE

  KEENE is the authorization name specified in the statement that creates a schema called PAYROLL. KEENE is the owner of the schema PAYROLL and is given CREATEIN, ALTERIN, and DROPIN privileges, with the ability to grant them to others.

Dynamic SQL characteristics at run time

The BIND option DYNAMICRULES determines the authorization ID that is used for checking authorization when dynamic SQL statements are processed. In addition, the option also controls other dynamic SQL attributes, such as the implicit qualifier that is used for unqualified object references, and whether certain SQL statements can be invoked dynamically.

The set of values for the authorization ID and other dynamic SQL attributes is called the dynamic SQL statement behavior. The four possible behaviors are run, bind, define, and invoke. As the following table shows, the combination of the value of the DYNAMICRULES BIND option and the runtime environment determines which of the behaviors is used. DYNAMICRULES RUN, which implies run behavior, is the default.

Run behavior

The authorization ID of the user (the ID that initially connected to the database) that executes the package is used as the value for authorization checking of dynamic SQL statements. This authorization ID is also used as the initial value for implicit qualification of unqualified object references within dynamic SQL statements.

Bind behavior

At run time, all the rules that apply to static SQL for authorization and qualification are used. The authorization ID of the package owner is used as the value for authorization checking of dynamic SQL statements. The package default qualifier is used for implicit qualification of unqualified object references within dynamic SQL statements.

Define behavior

Define behavior applies only if the dynamic SQL statement is in a package that is run within a routine context, and the package was bound with DYNAMICRULES DEFINEBIND or DYNAMICRULES DEFINERUN. The authorization ID of the routine definer (not the routine's package binder) is used as the value for authorization checking of dynamic SQL statements. This authorization ID is also used for implicit qualification of unqualified object references within dynamic SQL statements within that routine.

Invoke behavior

Invoke behavior applies only if the dynamic SQL statement is in a package that is run within a routine context, and the package was bound with DYNAMICRULES INVOKEBIND or DYNAMICRULES INVOKERUN. The statement authorization ID in effect when the routine is invoked is used as the value for authorization checking of dynamic SQL. This authorization ID is also used for implicit qualification of unqualified object references within dynamic SQL statements within that routine. This is summarized by the following table.

Restricted statements when run behavior does not apply

When bind, define, or invoke behavior is in effect, you cannot use the following dynamic SQL statements: GRANT, REVOKE, ALTER, CREATE, DROP, COMMENT, RENAME, SET INTEGRITY, SET EVENT MONITOR STATE; or queries that reference a nickname.

Considerations regarding the DYNAMICRULES option

The CURRENT SCHEMA special register cannot be used to qualify unqualified object references within dynamic SQL statements executed from bind, define or invoke behavior packages. This is true even after you issue the SET CURRENT SCHEMA statement to change the CURRENT SCHEMA special register; the register value is changed but not used.

In the event that multiple packages are referenced during a single connection, all dynamic SQL statements prepared by those packages will exhibit the behavior specified by the DYNAMICRULES option for that specific package and the environment in which they are used.

It is important to keep in mind that when a package exhibits bind behavior, the binder of the package should not have any authorities granted that the user of the package should not receive, because a dynamic statement will be using the authorization ID of the package owner. Similarly, when a package exhibits define behavior, the definer of the routine should not have any authorities granted that the user of the package should not receive.

Authorization IDs and statement preparation

If the VALIDATE BIND option is specified at bind time, the privileges required to manipulate tables and views must also exist at bind time. If these privileges or the referenced objects do not exist, and the SQLERROR NOPACKAGE option is in effect, the bind operation will be unsuccessful. If the SQLERROR CONTINUE option is specified, the bind operation will be successful, and any statements in error will be flagged. Any attempt to execute such a statement will result in an error.

If a package is bound with the VALIDATE RUN option, all normal bind processing is completed, but the privileges required to use the tables and views that are referenced in the application need not exist yet. If a required privilege does not exist at bind time, an incremental bind operation is performed whenever the statement is first executed in an application, and all privileges required for the statement must exist. If a required privilege does not exist, execution of the statement is unsuccessful.

Authorization checking at run time is performed using the authorization ID of the package owner.

Column names

Qualified column names

A qualifier for a column name may be a table, view, nickname, alias, or correlation name.

Where a qualifier is optional, it can serve two purposes. They are described under Column name qualifiers to avoid ambiguity and Column name qualifiers in correlated references.

Correlation names

   FROM X.MYTABLE Z

With Z defined as a correlation name for X.MYTABLE, only Z can be used to qualify a reference to a column of that instance of X.MYTABLE in that SELECT statement.

A correlation name is associated with a table, view, nickname, alias, nested table expression, table function, or data change table reference only within the context in which it is defined. Hence, the same correlation name can be defined for different purposes in different statements, or in different clauses of the same statement.

As a qualifier, a correlation name can be used to avoid ambiguity or to establish a correlated reference. It can also be used merely as a shorter name for a table reference. In the example, Z might have been used merely to avoid having to enter X.MYTABLE more than once.

If a correlation name is specified for a table, view, nickname, or alias name, any qualified reference to a column of that instance of the table, view, nickname, or alias must use the correlation name, rather than the table, view, nickname, or alias name. For example, the reference to EMPLOYEE.PROJECT in the following example is incorrect, because a correlation name has been specified for EMPLOYEE:

                                                                                          
   FROM EMPLOYEE E                                        
     WHERE EMPLOYEE.PROJECT='ABC'      * incorrect*             

   FROM EMPLOYEE E
     WHERE E.PROJECT='ABC'

   FROM EMPLOYEE E, DEPARTMENT

A table, view, nickname, or alias name that is exposed in a FROM clause may be the same as any other table name, view name or nickname exposed in that FROM clause or any correlation name in the FROM clause. This may result in ambiguous column name references which returns an error (SQLSTATE 42702).

The use of a correlation name in the FROM clause also allows the option of specifying a list of column names to be associated with the columns of the result table. As with a correlation name, these listed column names become the exposed names of the columns that must be used for references to the columns throughout the query. If a column name list is specified, then the column names of the underlying table become non-exposed.

Given the FROM clause:

   FROM DEPARTMENT D (NUM,NAME,MGR,ANUM,LOC)

a qualified reference such as D.NUM denotes the first column of the DEPARTMENT table that is defined in the table as DEPTNO. A reference to D.DEPTNO using this FROM clause is incorrect since the column name DEPTNO is a non-exposed column name.

Column name qualifiers to avoid ambiguity

In the context of a function, a GROUP BY clause, ORDER BY clause, an expression, or a search condition, a column name refers to values of a column in some table, view, nickname, nested table expression or table function. The tables, views, nicknames, nested table expressions and table functions that might contain the column are called the object tables of the context. Two or more object tables might contain columns with the same name; one reason for qualifying a column name is to designate the table from which the column comes. Qualifiers for column names are also useful in SQL procedures to distinguish column names from SQL variable names used in SQL statements.

A nested table expression or table function will consider table-references that precede it in the FROM clause as object tables. The table-references that follow are not considered as object tables.

Table designators

   SELECT CORZ.COLA, OWNY.MYTABLE.COLA
     FROM OWNX.MYTABLE CORZ, OWNY.MYTABLE

When qualifying a column with the exposed table name form of a table designator, either the qualified or unqualified form of the exposed table name can be used. If the qualified form is used, the qualifier must be the same as the default qualifier for the exposed table name.

SELECT CORPDATA.EMPLOYEE.WORKDEPT FROM EMPLOYEE

SELECT EMPLOYEE.WORKDEPT, REGEMP.WORKDEPT
  FROM CORPDATA.EMPLOYEE, REGION.EMPLOYEE REGEMP

SELECT CORPDATA.EMPLOYEE.WORKDEPT FROM EMPLOYEE

Each table designator should be unique within a particular FROM clause to avoid the possibility of ambiguous references to columns.

Avoiding undefined or ambiguous references

Avoid ambiguous references by qualifying a column name with a uniquely defined table designator. If the column is contained in several object tables with different names, the table names can be used as designators. Ambiguous references can also be avoided without the use of the table designator by giving unique names to the columns of one of the object tables using the column name list following the correlation name.

Column name qualifiers in correlated references

A fullselect is a form of a query that may be used as a component of various SQL statements. A fullselect used within a search condition of any statement is called a subquery. A fullselect used to retrieve a single value as an expression within a statement is called a scalar fullselect or scalar subquery. A fullselect used in the FROM clause of a query is called a nested table expression. Subqueries in search conditions, scalar subqueries and nested table expressions are referred to as subqueries through the remainder of this topic.

A subquery may include subqueries of its own, and these may, in turn, include subqueries. Thus an SQL statement may contain a hierarchy of subqueries. Those elements of the hierarchy that contain subqueries are said to be at a higher level than the subqueries they contain.

Every element of the hierarchy contains one or more table designators. A subquery can reference not only the columns of the tables identified at its own level in the hierarchy, but also the columns of the tables identified previously in the hierarchy, back to the highest level of the hierarchy. A reference to a column of a table identified at a higher level is called a correlated reference.

For compatibility with existing standards for SQL, both qualified and unqualified column names are allowed as correlated references. However, it is good practice to qualify all column references used in subqueries; otherwise, identical column names may lead to unintended results. For example, if a table in a hierarchy is altered to contain the same column name as the correlated reference and the statement is prepared again, the reference will apply to the altered table.

When a column name in a subquery is qualified, each level of the hierarchy is searched, starting at the same subquery as the qualified column name appears and continuing to the higher levels of the hierarchy until a table designator that matches the qualifier is found. Once found, it is verified that the table contains the given column. If the table is found at a higher level than the level containing column name, then it is a correlated reference to the level where the table designator was found. A nested table expression must be preceded with the optional TABLE keyword in order to search the hierarchy above the fullselect of the nested table expression.

When the column name in a subquery is not qualified, the tables referenced at each level of the hierarchy are searched, starting at the same subquery where the column name appears and continuing to higher levels of the hierarchy, until a match for the column name is found. If the column is found in a table at a higher level than the level containing column name, then it is a correlated reference to the level where the table containing the column was found. If the column name is found in more than one table at a particular level, the reference is ambiguous and considered an error.

Since the same table, view or nickname can be identified at many levels, unique correlation names are recommended as table designators. If T is used to designate a table at more than one level (T is the table name itself or is a duplicate correlation name), T.C refers to the level where T is used that most directly contains the subquery that includes T.C. If a correlation to a higher level is needed, a unique correlation name must be used.

The correlated reference T.C identifies a value of C in a row or group of T to which two search conditions are being applied: condition 1 in the subquery, and condition 2 at some higher level. If condition 2 is used in a WHERE clause, the subquery is evaluated for each row to which condition 2 is applied. If condition 2 is used in a HAVING clause, the subquery is evaluated for each group to which condition 2 is applied.

   SELECT EMPNO, LASTNAME, WORKDEPT
     FROM EMPLOYEE X
     WHERE SALARY < (SELECT AVG(SALARY)
                       FROM EMPLOYEE
                       WHERE WORKDEPT = X.WORKDEPT)

   DELETE FROM DEPARTMENT THIS
      WHERE NOT EXISTS(SELECT *
                         FROM EMPLOYEE
                         WHERE WORKDEPT = THIS.DEPTNO)

References to 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:

host variable

Host variables are defined by statements of a host language. For more information about how to refer to host variables, see References 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, see CREATE TRIGGER statement.

SQL variable

SQL variables are defined by an SQL compound statement in an SQL function, SQL method, SQL procedure, trigger, or dynamic SQL statement. For more information about SQL variables, see References to SQL parameters, SQL variables, and global variables.

global variable

Global variables are defined by the CREATE VARIABLE statement. For more information about global variables, see CREATE VARIABLE and References to SQL parameters, SQL variables, and global variables.

module variable

Module variables are defined by the ALTER MODULE statement using the ADD VARIABLE or PUBLISH VARIABLE operation. For more information about module variables, see ALTER MODULE statement.

SQL parameter

SQL parameters are defined by a CREATE FUNCTION, CREATE METHOD, or CREATE PROCEDURE statement. For more information about SQL parameters, see References to SQL parameters, SQL variables, and global variables.

parameter marker

Parameter markers are specified in a dynamic SQL statement where host variables would be specified if the statement were a static SQL statement. An SQL descriptor or parameter binding is used to associate a value with a parameter marker during dynamic SQL statement processing. For more information about parameter markers, see Parameter markers.

References to host variables

that is referenced in an SQL statement. Host variables are either directly defined by statements in the host language or are indirectly defined using SQL extensions.

A host variable in an SQL statement must identify a host variable described in the program according to the rules for declaring host variables.

All host variables used in an SQL statement must be declared in an SQL DECLARE section in all host languages except REXX. No variables may be declared outside an SQL DECLARE section with names identical to variables declared inside an SQL DECLARE section. An SQL DECLARE section begins with BEGIN DECLARE SECTION and ends with END DECLARE SECTION.

The meta-variable host-variable, as used in the syntax diagrams, shows a reference to a host variable. A host-variable as the target variable in a SET variable statement or in the INTO clause of a FETCH, SELECT INTO, or VALUES INTO statement, identifies a host variable to which a value from a column of a row or an expression is assigned. In all other contexts a host-variable specifies a value to be passed to the database manager from the application program.

The meta-variable host-variable in syntax diagrams can generally be expanded to:

Each host-identifier must be declared in the source program. The variable designated by the second host-identifier must have a data type of small integer.

The first host-identifier designates the main variable. Depending on the operation, it either provides a value to the database manager or is provided a value from the database manager. An input host variable provides a value in the runtime application code page. An output host variable is provided a value that, if necessary, is converted to the runtime application code page when the data is copied to the output application variable. A given host variable can serve as both an input and an output variable in the same program.

The second host-identifier designates its indicator variable. Indicator variables appear in two forms; normal indictor variables, and extended indicator variables.

Extended indicator variables are only enabled if requested, and all indicator variables are otherwise normal indicator variables. In comparison to normal indicator variables, extended indicator variables have no additional restrictions for where the values for null and non-null can be used. There are no restrictions against using extended indicator variable values in indicator structures with host structures. Restrictions on where extended indicator variable values default and unassigned are allowed apply uniformly, no matter how they are represented in the host application. The default and unassigned extended indicator variable values may only appear in limited, specified uses. They may appear in expressions containing only a single host variable, or a host variable being explicitly cast (assigned to a column). Output indicator variable values are never extended indicator variables.

When extended indicator variables are enabled, there are no restrictions against use of 0 (zero), or positive indicator variable values. However, negative indicator variable values outside the range -1 through -7 must not be input (SQLSTATE 22010). When enabled, the default and unassigned extended indicator variable values must not appear in contexts in which they are not supported (SQLSTATE 22539).

When extended indicator variables are enabled, rules for data type validation in assignment and comparison are loosened for host variables whose extended indicator values are negative. Data type assignment and comparison validation rules will not be enforced for host variables having the values null, default, or unassigned.

For example, if :HV1:HV2 is used to specify an insert or update value, and if HV2 is negative, the value specified is the null value. If HV2 is not negative the value specified is the value of HV1.

Similarly, if :HV1:HV2 is specified in an INTO clause of a FETCH, SELECT INTO, or VALUES INTO statement, and if the value returned is null, HV1 is not changed, and HV2 is set to a negative value. If the database is configured with dft_sqlmathwarn yes (or was during binding of a static SQL statement), HV2 could be -2. If HV2 is -2, a value for HV1 could not be returned because of an error converting to the numeric type of HV1, or an error evaluating an arithmetic expression that is used to determine the value for HV1. If the value returned is not null, that value is assigned to HV1 and HV2 is set to zero (unless the assignment to HV1 requires string truncation of a non-LOB string; in which case HV2 is set to the original length of the string). If an assignment requires truncation of the seconds part of a time, HV2 is set to the number of seconds.

If the second host identifier is omitted, the host-variable does not have an indicator variable. The value specified by the host-variable reference :HV1 is always the value of HV1, and null values cannot be assigned to the variable. Thus, this form should not be used in an INTO clause unless the corresponding column cannot contain null values. If this form is used and the column contains nulls, the database manager will generate an error at run time.

An SQL statement that references host variables must be within the scope of the declaration of those host variables. For host variables referenced in the SELECT statement of a cursor, that rule applies to the OPEN statement rather than to the DECLARE CURSOR statement.

Example

  SELECT PROJNAME, PRSTAFF, MAJPROJ
    INTO :PNAME, :STAFF :STAFF_IND, :MAJPROJ :MAJPROJ_IND
    FROM PROJECT
    WHERE PROJNO = 'IF1000'

Variables in dynamic SQL

   INSERT INTO DEPARTMENT
     VALUES (:HV_DEPTNO, :HV_DEPTNAME, :HV_MGRNO, :HV_ADMRDEPT)

   INSERT INTO DEPARTMENT VALUES (?, ?, ?, ?)

   INSERT INTO DEPARTMENT 
     VALUES (:DEPTNO, :DEPTNAME, :MGRNO, :ADMRDEPT)

   EXECUTE DYNSTMT 
     USING :HV_DEPTNO, :HV_DEPTNAME :HV_MGRNO, :HV_ADMRDEPT

References to LOB variables

Regular BLOB, CLOB, and DBCLOB variables, LOB locator variables (see References to LOB locator variables), and LOB file reference variables (see References to LOB file reference variables) can be defined in all host languages. Where LOBs are allowed, the term host-variable in a syntax diagram can refer to a regular host variable, a locator variable, or a file reference variable. Since these are not native data types, SQL extensions are used and the precompilers generate the host language constructs necessary to represent each variable. In the case of REXX, LOBs are mapped to strings.

It is sometimes possible to define a large enough variable to hold an entire large object value. If this is true and if there is no performance benefit to be gained by deferred transfer of data from the server, a locator is not needed. However, since host language or space restrictions will often dictate against storing an entire large object in temporary storage at one time or because of performance benefit, a large object may be referenced via a locator and portions of that object may be selected into or updated from host variables that contain only a portion of the large object at one time.

References to LOB locator variables

A locator variable is a host variable that contains the locator representing a LOB value on the application server.

A locator variable in an SQL statement must identify a locator variable described in the program according to the rules for declaring locator variables. This is always indirectly through an SQL statement.

The term locator variable, as used in the syntax diagrams, shows a reference to a locator variable. The meta-variable locator-variable can be expanded to include a host-identifier the same as that for host-variable.

As with all other host variables, a large object locator variable may have an associated indicator variable. Indicator variables for large object locator host variables behave in the same way as indicator variables for other data types. When a null value is returned from the database, the indicator variable is set and the locator host variable is unchanged. This means a locator can never point to a null value.

If a locator-variable that does not currently represent any value is referenced, an error is raised (SQLSTATE 0F001).

At transaction commit, or any transaction termination, all locators acquired by that transaction are released.

References to LOB file reference variables

BLOB, CLOB, and DBCLOB file reference variables are used for direct file input and output for LOBs, and can be defined in all host languages. Since these are not native data types, SQL extensions are used and the precompilers generate the host language constructs necessary to represent each variable. In the case of REXX, LOBs are mapped to strings.

A file reference variable represents (rather than contains) the file, just as a LOB locator represents, rather than contains, the LOB bytes. Database queries, updates and inserts may use file reference variables to store or to retrieve single column values.

As with all other host variables, a file reference variable may have an associated indicator variable.

Example of an output file reference variable (in C)

   EXEC SQL BEGIN DECLARE SECTION
      SQL TYPE IS CLOB_FILE  hv_text_file;
      char  hv_patent_title[64];
   EXEC SQL END DECLARE SECTION

   EXEC SQL BEGIN DECLARE SECTION
      /* SQL TYPE IS CLOB_FILE  hv_text_file; */
      struct {
          unsigned long  name_length; //  File Name Length
          unsigned long  data_length; //  Data Length
          unsigned long  file_options; // File Options
          char           name[255];   // File Name
      } hv_text_file;
      char  hv_patent_title[64];
   EXEC SQL END DECLARE SECTION

   strcpy(hv_text_file.name, "/u/gainer/papers/sigmod.94");
   hv_text_file.name_length = strlen("/u/gainer/papers/sigmod.94");
   hv_text_file.file_options = SQL_FILE_CREATE;

   EXEC SQL SELECT content INTO :hv_text_file from papers
        WHERE TITLE = 'The Relational Theory behind Juggling';

Example of an input file reference variable (in C)

   strcpy(hv_text_file.name, "/u/gainer/patents/chips.13");
   hv_text_file.name_length = strlen("/u/gainer/patents/chips.13");
   hv_text_file.file_options = SQL_FILE_READ:
   strcpy(:hv_patent_title, "A Method for Pipelining Chip Consumption");

   EXEC SQL INSERT INTO patents( title, text )
            VALUES(:hv_patent_title, :hv_text_file);

References to structured type host variables

Structured type variables can be defined in all host languages except FORTRAN, REXX, and Java. Since these are not native data types, SQL extensions are used and the precompilers generate the host language constructs necessary to represent each variable.

As with all other host variables, a structured type variable may have an associated indicator variable. Indicator variables for structured type host variables behave in the same way as indicator variables for other data types. When a null value is returned from the database, the indicator variable is set and the structured type host variable is unchanged.

If using a parameter marker instead of a host variable, the appropriate parameter type characteristics must be specified in the SQLDA. This requires a doubled set of SQLVAR structures in the SQLDA, and the SQLDATATYPE_NAME field of the secondary SQLVAR must be filled with the schema and type name of the structured type. If the schema is omitted in the SQLDA structure, an error results (SQLSTATE 07002).

Example

   EXEC SQL BEGIN DECLARE SECTION;
         static SQL
            TYPE IS POLYGON AS BLOB(1M)
            hv_poly, hv_point;
   EXEC SQL END DECLARE SECTION;

SQL path

The SQL path is an ordered list of schema names. The database manager uses the SQL path to resolve the schema name for unqualified data type names (both built-in types and distinct types), global variable names, module names, function names, and procedure names that appear in any context other than as the main object of a CREATE, DROP, COMMENT, GRANT or REVOKE statement. For details, see Qualification of unqualified object names.

For example, if the SQL path is SYSIBM. SYSFUN, SYSPROC, SYSIBMADM, SMITH, XGRAPHICS2 and an unqualified distinct type name MYTYPE was specified, the database manager looks for MYTYPE first in schema SYSIBM, then SYSFUN, then SYSPROC, then SYSIBMADM, then SMITH, and then XGRAPHICS2.

Qualification of unqualified object names

Unqualified object names are implicitly qualified. The rules for qualifying a name differ depending on the type of object that the name identifies.

Unqualified alias, index, package, sequence, table, trigger, and view names

Unqualified alias, index, package, sequence, table, trigger, and view names are implicitly qualified by the default schema.

For static SQL statements, the default schema is the default schema specified when the containing function, package, procedure, or trigger was created.

For dynamic SQL statements, the default schema is the default schema specified for the application process. The default schema can be specified for the application process by using the SET SCHEMA statement. If the default schema is not explicitly specified, the default schema is the authorization ID of the statement.

Unqualified user-defined type, function, procedure, specific, global variable and module names

New SYSIBM functions override unqualified user-defined functions with the same name

An existing user-defined function or a user-defined procedure might have the same name and signature as a new built-in function or SQL administrative routine. In such cases, an unqualified reference to those functions or routines in a dynamic SQL statement runs the built-in function or SQL administrative routine instead of the user-defined one.

The default SQL path contains the schemas SYSIBM, SYSFUN, SYSPROC, and SYSIBMADM before the schema name that is the value of the USER special register. These system schemas are also included in the SQL path when it is explicitly set with the SET PATH statement or the FUNCPATH bind option. During function resolution and procedure resolution, the built-in functions and SQL administrative routines in the SYSIBM, SYSFUN, SYSPROC, and SYSIBMADM schemas are encountered before user-defined functions and user-defined procedures.

This change does not affect static SQL in packages or SQL objects such as views, triggers, or SQL functions. In these cases, the user-defined function or procedure continues to run until an explicit bind of the package, or drop and create of the SQL object.

To run an unqualified user-defined routine instead of a new SYSIBM function with the same name, rename the user-defined routine or fully qualify the name before you run it. Alternatively, place in the SQL path the schema in which the user-defined routine exists before the schema in which the built-in functions and SQL administrative routines exist. However, promoting the schema in the SQL path increases the resolution time for all built-in functions and SQL administrative routines because the system schemas are considered first.

Resolving qualified object names