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 behaviorDYNAMICRULES 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
- DB2® uses the authorization
ID of the user (the ID that initially connected to DB2) executing the package as the value to be
used for authorization checking of dynamic SQL statements and for
the initial value used for implicit qualification of unqualified object
references within dynamic SQL statements.
- Bind behavior
- At run time, DB2 uses all
the rules that apply to static SQL for authorization and qualification.
It takes the authorization ID of the package owner as the value to
be used for authorization checking of dynamic SQL statements, and
the package default qualifier 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. DB2 uses the authorization ID of
the routine definer (not the routine's package binder) as the value
to be used for authorization checking of dynamic SQL statements, and
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. DB2 uses the statement authorization
ID in effect when the routine is invoked as the value to be used for
authorization checking of dynamic SQL, and 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 DB2 |
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:
- 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).
- 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).
- 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).
- 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).
- 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.
- If the authorization ID of the statement is CORPDATA:
SELECT CORPDATA.EMPLOYEE.WORKDEPT
FROM EMPLOYEE
is a valid statement.
- 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:
>>-:host-identifier--+---------------------------------+-------><
| .-INDICATOR-. |
'-+-----------+--: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. When accessing a database
with a client version earlier than DB2 Universal Database™ Version
5, HV2 will be -1 for arithmetic exceptions. 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.