Identifiers

An identifier is a token that is used to form a name. An identifier in an SQL statement is either an SQL identifier or a host identifier.

  • SQL identifiers

    There are two types of SQL identifiers: ordinary and delimited.

    • An ordinary identifier is an uppercase letter followed by zero or more characters, each of which is an uppercase letter, a digit, or the underscore character. Note that lowercase letters can be used when specifying an ordinary identifier, but they are converted to uppercase when processed. An ordinary identifier should not be a reserved word.
      Examples
         WKLYSAL     WKLY_SAL
    • A delimited identifier is a sequence of one or more characters enclosed by double quotation marks. Leading blanks in the sequence are significant. Trailing blanks in the sequence are not significant, although they are stored with the identifier. Two consecutive quotation marks are used to represent one quotation mark within the delimited identifier. A delimited identifier can be used when the sequence of characters does not qualify as an ordinary identifier. In this way an identifier can include lowercase letters.
      Examples
         "WKLY_SAL"     "WKLY SAL"     "UNION"     "wkly_sal"

    Character conversion of identifiers created on a double-byte code page, but used by an application or database on a multi-byte code page, may require special consideration: After conversion, such identifiers may exceed the length limit for an identifier.

  • Host identifiers

    A host identifier is a name declared in the host program. The rules for forming a host identifier are the rules of the host language. A host identifier should not be greater than 255 bytes in length and should not begin with SQL or DB2 (in uppercase or lowercase characters).

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.

The effect of using an alias in an SQL statement is similar to that of text substitution. The alias, which must be defined by the time that the SQL statement is compiled, is replaced at statement compilation time by the qualified object name. For example, if PBIRD.SALES is an alias for DSPN014.DIST4_SALES_148, then at compilation time:
   SELECT * FROM PBIRD.SALES
effectively becomes
   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.

Authorization IDs are used by the database manager to provide:
  • Authorization checking of SQL statements
  • A default value for the QUALIFIER precompile/bind option and the CURRENT SCHEMA special register. The authorization ID is also included in the default CURRENT PATH special register and the FUNCPATH precompile/bind option.
An authorization ID applies to every SQL statement. The authorization ID that applies to a static SQL statement is the authorization ID that is used during program binding. The authorization ID that applies to a dynamic SQL statement is based on the DYNAMICRULES option supplied at bind time, and on the current runtime environment for the package issuing the dynamic SQL statement:
  • In a package that has bind behavior, the authorization ID used is the authorization ID of the package owner.
  • In a package that has define behavior, the authorization ID used is the authorization ID of the corresponding routine's definer.
  • In a package that has run behavior, the authorization ID used is the current authorization ID of the user executing the package.
  • In a package that has invoke behavior, the authorization ID used is the authorization ID currently in effect when the routine is invoked. This is called the runtime authorization ID.
For more information, see Dynamic SQL characteristics at run time.

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.

Table 1. How DYNAMICRULES and the runtime environment determine dynamic SQL statement behavior
DYNAMICRULES value Behavior of dynamic SQL statements in a stand-alone program environment Behavior of dynamic SQL statements in a routine environment
BIND Bind behavior Bind behavior
RUN Run behavior Run behavior
DEFINEBIND Bind behavior Define behavior
DEFINERUN Run behavior Define behavior
INVOKEBIND Bind behavior Invoke behavior
INVOKERUN Run behavior Invoke behavior
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.
Invoking Environment ID Used
any static SQL implicit or explicit value of the OWNER of the package the SQL invoking the routine came from
used in definition of view or trigger definer of the view or trigger
dynamic SQL from a bind behavior package implicit or explicit value of the OWNER of the package the SQL invoking the routine came from
dynamic SQL from a run behavior package ID used to make the initial connection to the database
dynamic SQL from a define behavior package definer of the routine that uses the package that the SQL invoking the routine came from
dynamic SQL from an invoke behavior package the current authorization ID invoking the routine
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

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. 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.
  • Temporarily rename a column, as in the correlation-clause of a table-reference in a FROM clause.

Qualified column names

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

Whether a column name may be qualified depends on its context:
  • Depending on the form of the COMMENT ON statement, a single column name may need to be qualified. Multiple column names must be unqualified.
  • Where the column name specifies values of the column, it may be qualified at the user's option.
  • In the assignment-clause of an UPDATE statement, it may be qualified at the user's option.
  • In all other contexts, a column name must not be qualified.

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

A correlation name can be defined in the FROM clause of a query and in the first clause of an UPDATE or DELETE statement. For example, the clause FROM X.MYTABLE Z establishes Z as a correlation name for X.MYTABLE.
   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:

Example
                                                                                          
   FROM EMPLOYEE E                                        
     WHERE EMPLOYEE.PROJECT='ABC'      * incorrect*             
The qualified reference to PROJECT should instead use the correlation name, E, as shown in the following example:
   FROM EMPLOYEE E
     WHERE E.PROJECT='ABC'
Names specified in a FROM clause are either exposed or non-exposed. A table, view, nickname, or alias name is said to be exposed in the FROM clause if a correlation name is not specified. A correlation name is always an exposed name. For example, in the following FROM clause, a correlation name is specified for EMPLOYEE but not for DEPARTMENT, so DEPARTMENT is an exposed name, and EMPLOYEE is not:
   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 first two FROM clauses shown in the following list are correct, because each one contains no more than one reference to EMPLOYEE that is exposed:
  1. Given the FROM clause:
       FROM EMPLOYEE E1, EMPLOYEE

    a qualified reference such as EMPLOYEE.PROJECT denotes a column of the second instance of EMPLOYEE in the FROM clause. A qualified reference to the first instance of EMPLOYEE must use the correlation name E1 (E1.PROJECT).

  2. Given the FROM clause:
       FROM EMPLOYEE, EMPLOYEE E2

    a qualified reference such as EMPLOYEE.PROJECT denotes a column of the first instance of EMPLOYEE in the FROM clause. A qualified reference to the second instance of EMPLOYEE must use the correlation name E2 (E2.PROJECT).

  3. Given the FROM clause:
       FROM EMPLOYEE, EMPLOYEE

    the two exposed table names included in this clause (EMPLOYEE and EMPLOYEE) are the same. This is allowed, but references to specific column names would be ambiguous (SQLSTATE 42702).

  4. Given the following statement:
       SELECT *                                               
         FROM EMPLOYEE E1, EMPLOYEE E2             * incorrect *
         WHERE EMPLOYEE.PROJECT = 'ABC'

    the qualified reference EMPLOYEE.PROJECT is incorrect, because both instances of EMPLOYEE in the FROM clause have correlation names. Instead, references to PROJECT must be qualified with either correlation name (E1.PROJECT or E2.PROJECT).

  5. Given the FROM clause:
       FROM EMPLOYEE, X.EMPLOYEE

    a reference to a column in the second instance of EMPLOYEE must use X.EMPLOYEE (X.EMPLOYEE.PROJECT). If X is the CURRENT SCHEMA special register value in dynamic SQL or the QUALIFIER precompile/bind option in static SQL, then the columns cannot be referenced since any such reference would be ambiguous.

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

A qualifier that designates a specific object table is called a table designator. The clause that identifies the object tables also establishes the table designators for them. For example, the object tables of an expression in a SELECT clause are named in the FROM clause that follows it:
   SELECT CORZ.COLA, OWNY.MYTABLE.COLA
     FROM OWNX.MYTABLE CORZ, OWNY.MYTABLE
Table designators in the FROM clause are established as follows:
  • A name that follows a table, view, nickname, alias, nested table expression or table function is both a correlation name and a table designator. Thus, CORZ is a table designator. CORZ is used to qualify the first column name in the select list.
  • An exposed table, view name, nickname or alias is a table designator. Thus, OWNY.MYTABLE is a table designator. OWNY.MYTABLE is used to qualify the second column name in the select list.

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.

For example, assume that the current schema is CORPDATA.
SELECT CORPDATA.EMPLOYEE.WORKDEPT FROM EMPLOYEE
is valid because the EMPLOYEE table referenced in the FROM clause fully qualifies to CORPDATA.EMPLOYEE, which matches the qualifier for the WORKDEPT column.
SELECT EMPLOYEE.WORKDEPT, REGEMP.WORKDEPT
  FROM CORPDATA.EMPLOYEE, REGION.EMPLOYEE REGEMP
is also valid, because the first select list column references the unqualified exposed table designator CORPDATA.EMPLOYEE, which is in the FROM clause, and the second select list column references the correlation name REGEMP of the table object REGION.EMPLOYEE, which is also in the FROM clause.
Now assume that the current schema is REGION.
SELECT CORPDATA.EMPLOYEE.WORKDEPT FROM EMPLOYEE
is not valid because the EMPLOYEE table referenced in the FROM clause fully qualifies to REGION.EMPLOYEE, and the qualifier for the WORKDEPT column represents the CORPDATA.EMPLOYEE table.

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

When a column name refers to values of a column, exactly one object table must include a column with that name. The following situations are considered errors:
  • No object table contains a column with the specified name. The reference is undefined.
  • The column name is qualified by a table designator, but the table designated does not include a column with the specified name. Again the reference is undefined.
  • The name is unqualified, and more than one object table includes a column with that name. The reference is ambiguous.
  • The column name is qualified by a table designator, but the table designated is not unique in the FROM clause and both occurrences of the designated table include the column. The reference is ambiguous.
  • The column name is in a nested table expression which is not preceded by the TABLE keyword or in a table function or nested table expression that is the right operand of a right outer join or a full outer join and the column name does not refer to a column of a table-reference within the nested table expression's fullselect. The reference is undefined.

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.

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 may be used. However, the qualifier used and the table used must be the same after fully qualifying the table name, view name or nickname and the table designator.
  1. If the authorization ID of the statement is CORPDATA:
       SELECT CORPDATA.EMPLOYEE.WORKDEPT
         FROM EMPLOYEE

    is a valid statement.

  2. If the authorization ID of the statement is REGION:
       SELECT CORPDATA.EMPLOYEE.WORKDEPT
         FROM EMPLOYEE                           * incorrect *

    is invalid, because EMPLOYEE represents the table REGION.EMPLOYEE, but the qualifier for WORKDEPT represents a different table, CORPDATA.EMPLOYEE.

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.

In either case, T, used in the following example, refers to the table designator that contains column C. A column name, T.C (where T represents either an implicit or an explicit qualifier), is a correlated reference if, and only if, these conditions are met:
  • T.C is used in an expression of a subquery.
  • T does not designate a table used in the from clause of the subquery.
  • T designates a table used at a higher level of the hierarchy that contains the subquery.

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.

For example, in the following statement, the correlated reference X.WORKDEPT (in the last line) refers to the value of WORKDEPT in table EMPLOYEE at the level of the first FROM clause. (That clause establishes X as a correlation name for EMPLOYEE.) The statement lists employees who make less than the average salary for their department.
   SELECT EMPNO, LASTNAME, WORKDEPT
     FROM EMPLOYEE X
     WHERE SALARY < (SELECT AVG(SALARY)
                       FROM EMPLOYEE
                       WHERE WORKDEPT = X.WORKDEPT)
The next example uses THIS as a correlation name. The statement deletes rows for departments that have no employees.
   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

A host variable is either:
  • A variable in a host language such as a C variable, a C++ variable, a COBOL data item, a FORTRAN variable, or a Java™ variable
or:
  • A host language construct that was generated by an SQL precompiler from a variable declared using SQL extensions

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:

Read syntax diagramSkip visual syntax diagram:host-identifierINDICATOR:host-identifier

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.

The normal indicator variable has the following purposes:
  • Specify a non-null value. A 0 (zero), or positive value of the indicator variable specifies that the associated, first, host-identifier provides the value of this host variable reference.
  • Specify the null value. A negative value of the indicator variable specifies the null value.
  • On output, indicate that a numeric conversion error (such as division by 0 or overflow) has occurred, if the dft_sqlmathwarn database configuration parameter is set to yes (or was set to yes during binding of a static SQL statement). A -2 value of the indicator variable indicates a null result because of either numeric truncation or friendly arithmetic warnings.
  • On output, report the original length of a truncated string (if the source of the value is not a large object type).
  • On output, report the seconds portion of a time if the time is truncated on assignment to a host variable.
Extended indicator variables are limited to the input of host variables. The extended indicator variable has the following purposes:
  • Specify a non-null value. A 0 (zero), or positive value specifies that the associated, first, host-identifier provides the value of this host variable reference.
  • Specify the null value. A -1, -2, -3, -4, or -6 value specifies the null value.
  • Specify the default value. A -5 value specifies the target column for this host variable is to be set to its default value.
  • Specify an unassigned value. A -7 value specifies the target column for this host variable is to be treated as if it had not been specified in the statement.

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

Using the PROJECT table, set the host variable PNAME (VARCHAR(26)) to the project name (PROJNAME), the host variable STAFF (DECIMAL(5,2)) to the mean staffing level (PRSTAFF), and the host variable MAJPROJ (CHAR(6)) to the major project (MAJPROJ) for project (PROJNO) 'IF1000'. Columns PRSTAFF and MAJPROJ may contain null values, so provide indicator variables STAFF_IND (SMALLINT) and MAJPROJ_IND (SMALLINT).
  SELECT PROJNAME, PRSTAFF, MAJPROJ
    INTO :PNAME, :STAFF :STAFF_IND, :MAJPROJ :MAJPROJ_IND
    FROM PROJECT
    WHERE PROJNO = 'IF1000'
MBCS Considerations: Whether multi-byte characters can be used in a host variable name depends on the host language.

Variables in dynamic SQL

In dynamic SQL statements, parameter markers are used instead of host variables. A parameter marker represents a position in a dynamic SQL statement where the application will provide a value; that is, where a host variable would be found if the statement string were a static SQL statement. The following example shows a static SQL statement using host variables:
   INSERT INTO DEPARTMENT
     VALUES (:HV_DEPTNO, :HV_DEPTNAME, :HV_MGRNO, :HV_ADMRDEPT)
This example shows a dynamic SQL statement using unnamed parameter markers:
   INSERT INTO DEPARTMENT VALUES (?, ?, ?, ?)
This example shows a dynamic SQL statement using named parameter markers:
   INSERT INTO DEPARTMENT 
     VALUES (:DEPTNO, :DEPTNAME, :MGRNO, :ADMRDEPT)
Named parameter markers can be used to improve the readability of dynamic statement. Although named parameter markers look like host variables, named parameter markers have no associated value and therefore a value must be provided for the parameter marker when the statement is executed. If the INSERT statement using named parameter markers has been prepared and given the prepared statement name of DYNSTMT, then values can be provided for the parameter markers using the following statement:
   EXECUTE DYNSTMT 
     USING :HV_DEPTNO, :HV_DEPTNAME :HV_MGRNO, :HV_ADMRDEPT
This same EXECUTE statement could be used if the INSERT statement using unnamed parameter markers had been prepared and given the prepared statement name of DYNSTMT.

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.

A file reference variable has the following properties:
Data Type
BLOB, CLOB, or DBCLOB. This property is specified when the variable is declared.
Direction
This must be specified by the application program at run time (as part of the File Options value). The direction is one of:
  • Input (used as a source of data on an EXECUTE statement, an OPEN statement, an UPDATE statement, an INSERT statement, or a DELETE statement).
  • Output (used as the target of data on a FETCH statement or a SELECT INTO statement).
File name
This must be specified by the application program at run time. It is one of:
  • The complete path name of the file (which is advised).
  • A relative file name. If a relative file name is provided, it is appended to the current path of the client process.

Within an application, a file should only be referenced in one file reference variable.

File Name Length
This must be specified by the application program at run time. It is the length of the file name (in bytes).
File Options
An application must assign one of a number of options to a file reference variable before it makes use of that variable. Options are set by an INTEGER value in a field in the file reference variable structure. One of the following values must be specified for each file reference variable:
  • Input (from client to server)
    SQL_FILE_READ
    This is a regular file that can be opened, read and closed. (The option is SQL-FILE-READ in COBOL, sql_file_read in FORTRAN, and READ in REXX.)
  • Output (from server to client)
    SQL_FILE_CREATE
    Create a new file. If the file already exists, an error is returned. (The option is SQL-FILE-CREATE in COBOL, sql_file_create in FORTRAN, and CREATE in REXX.)
    SQL_FILE_OVERWRITE (Overwrite)
    If an existing file with the specified name exists, it is overwritten; otherwise a new file is created. (The option is SQL-FILE-OVERWRITE in COBOL, sql_file_overwrite in FORTRAN, and OVERWRITE in REXX.)
    SQL_FILE_APPEND
    If an existing file with the specified name exists, the output is appended to it; otherwise a new file is created. (The option is SQL-FILE-APPEND in COBOL, sql_file_append in FORTRAN, and APPEND in REXX.)
Data Length
This is unused on input. On output, the implementation sets the data length to the length of the new data written to the file. The length is in bytes.

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)

Given a declare section coded as:
   EXEC SQL BEGIN DECLARE SECTION
      SQL TYPE IS CLOB_FILE  hv_text_file;
      char  hv_patent_title[64];
   EXEC SQL END DECLARE SECTION
Following preprocessing this would be:
   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
Then, the following code can be used to select from a CLOB column in the database into a new file referenced by :hv_text_file.
   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)

Given the same declare section as the previous one, the following code can be used to insert the data from a regular file referenced by :hv_text_file into a CLOB column.
   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.

The actual host variable for a structured type is defined as a built-in data type. The built-in data type associated with the structured type must be assignable:
  • from the result of the FROM SQL transform function for the structured type as defined by the specified TRANSFORM GROUP option of the precompile command; and
  • to the parameter of the TO SQL transform function for the structured type as defined by the specified TRANSFORM GROUP option of the precompile command.

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

Define the host variables hv_poly and hv_point (of type POLYGON, using built-in type BLOB(1048576)) in a C program.
   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.

The SQL path used depends on the SQL statement:
  • For static SQL statements (except for a CALL variable statement), the SQL path used is the SQL path specified when the containing package, procedure, function, trigger, or view was created.
  • For dynamic SQL statements (and for a CALL variable statement), the SQL path is the value of the CURRENT PATH special register. CURRENT PATH can be set by the SET PATH statement.
If the SQL path is not explicitly specified, the SQL path is the system path followed by the authorization ID of the statement. .

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

The qualification of data type (both built-in types and distinct types), global variable, module, function, procedure, and specific names depends on the SQL statement in which the unqualified name appears:
  • If an unqualified name is the main object of a CREATE, ALTER, COMMENT, DROP, GRANT, or REVOKE statement, the name is implicitly qualified using the same rules as for qualifying unqualified table names (See Unqualified alias, index, package, sequence, table, trigger, and view names). The main object of an ADD, COMMENT, DROP, or PUBLISH operation of the ALTER MODULE statement must be specified without any qualifier.
  • If the context of the reference is within a module, the database manager searches the module for the object, applying the appropriate resolution for the type of object to find a match. If no match is found, the search continues as specified in the next bullet.
  • Otherwise, the implicit schema name is determined as follows:
    • For distinct type names, the database manager searches the SQL path and selects the first schema in the SQL path such that the data type exists in the schema.
    • For global variables, the database manager searches the SQL path and selects the first schema in the SQL path such that the global variable exists in the schema.
    • For procedure names, the database manager uses the SQL path in conjunction with procedure resolution.
    • For function names, the database manager uses the SQL path in conjunction with function resolution .
    • For specific names specified for sourced functions, see CREATE FUNCTION (Sourced).

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

Objects that are defined in a module that are available for use outside the module must be qualified by the module name. Since a module is a schema object that can also be implicitly qualified, the published module objects can be qualified using an unqualified module name or a schema-qualified module name. When an unqualified module name is used, the reference to the module object appears the same as a schema-qualified object that is not part of a module. Within a specific scope, such as a compound SQL statement, a two-part identifier could also be:
  • a column name qualified by a table name
  • a row field name qualified by a variable name
  • a variable name qualified by a label
  • a routine parameter name qualified by a routine name
These objects are resolved within their scope, before considering either schema objects or module object. The following process is used to resolve objects with two-part identifiers that could be a schema object or a module object.
  • If the context of the reference is within a module and the qualifier matches the module name, the database manager searches the module for the object, applying the appropriate resolution for the type of object to find a match among published and unpublished module objects. If no match is found, the search continues as specified in the next bullets.
  • Assume that the qualifier is a schema name and, if the schema exists, resolve the object in the schema.
  • If the qualifier is not an existing schema or the object is not found in the schema that matches the qualifier and the qualifier did not match the context module name, search for the first module that matches the qualifier in the schemas on the SQL path. If authorized to the matching module, resolve to the object in that module, considering only published module objects.
  • If the qualifier is not found as a module on the SQL path and the qualifier did not match the context module name, check for a module public synonym that matches the qualifier. If found, resolve the object in the module identified by the module public synonym, considering only published module objects.

Reserved package names

A specific set of package names have been explicitly reserved for system use. To avoid collision with system use of these names it is recommended that these package names not be used by any application.

The set of package names that are reserved meet one of the following criteria:
  • The package schema is a reserved schema
  • The package schema is NULLID and the package name matches a reserved package name or contains a prefix that matches a reserved package name prefix.
The following package names are reserved within the NULLID schema:
  • AGGDISC
  • PRINTSG
  • TUPLEWRT
The following package name prefixes are reserved within the NULLID schema:
  • AOT
  • ATS
  • CADM
  • CLI
  • DB2
  • POLY
  • REVA
  • SPIM
  • SPUT
  • SQL
  • SYS
  • TOOL