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.
There are two types of SQL identifiers: ordinary and delimited.
WKLYSAL WKLY_SAL
"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.
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).
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.
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).
An alias can be thought of as an alternative name for an SQL object. An SQL object, therefore, can be referred to in an SQL statement by its name or by an alias.
A public alias is an alias which can always be referenced without qualifying its name with a schema name. The implicit qualifier of a public alias is SYSPUBLIC, which can also be specified explicitly.
Aliases are also known as synonyms.
An alias can be used wherever the object it is based on can be used. An alias can be created even if the object does not exist (although it must exist by the time a statement referring to it is compiled). It can refer to another alias if no circular or repetitive references are made along the chain of aliases. An alias can only refer to a module, nickname, sequence, table, view, or another alias within the same database. An alias name cannot be used where a new object name is expected, such as in the CREATE TABLE or CREATE VIEW statements; for example, if the table alias name PERSONNEL has been created, subsequent statements such as CREATE TABLE PERSONNEL... will return an error.
The option of referring to an object by an alias is not explicitly shown in the syntax diagrams, or mentioned in the descriptions of SQL statements.
A new unqualified alias of a given object type, say for a sequence, cannot have the same fully-qualified name as an existing object of that object type. For example, a sequence alias named ORDERID cannot be defined in the KANDIL schema for the sequence named KANDIL.ORDERID.
SELECT * FROM PBIRD.SALES
effectively
becomes SELECT * FROM DSPN014.DIST4_SALES_148
An authorization ID is a character string that is obtained by the database manager when a connection is established between the database manager and either an application process or a program preparation process. It designates a set of privileges. It may also designate a user or a group of users, but this property is not controlled by the database manager.
An authorization name specified in an SQL statement should not be confused with the authorization ID of the statement. An authorization name is an identifier that is used within various SQL statements. An authorization name is used in the CREATE SCHEMA statement to designate the owner of the schema. An authorization name is used in the GRANT and REVOKE statements to designate a target of the grant or revoke operation. Granting privileges to X means that X (or a member of the group or role X) will subsequently be the authorization ID of statements that require those privileges.
Examples:
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.
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.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.
DYNAMICRULES value | Behavior of dynamic SQL statements | |
---|---|---|
Standalone program environment | 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 |
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.
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.
A qualifier for a column name may be a table, view, nickname, alias, or correlation name.
Where a qualifier is optional, it can serve two purposes. They are described under Column name qualifiers to avoid ambiguity and Column name qualifiers in correlated references.
FROM X.MYTABLE Z
With Z defined as a correlation name for X.MYTABLE, only Z can be used to qualify a reference to a column of that instance of X.MYTABLE in that SELECT statement.
A correlation name is associated with a table, view, nickname, alias, nested table expression, table function, or data change table reference only within the context in which it is defined. Hence, the same correlation name can be defined for different purposes in different statements, or in different clauses of the same statement.
As a qualifier, a correlation name can be used to avoid ambiguity or to establish a correlated reference. It can also be used merely as a shorter name for a table reference. In the example, Z might have been used merely to avoid having to enter X.MYTABLE more than once.
If a correlation name is specified for a table, view, nickname, or alias name, any qualified reference to a column of that instance of the table, view, nickname, or alias must use the correlation name, rather than the table, view, nickname, or alias name. For example, the reference to EMPLOYEE.PROJECT in the following example is incorrect, because a correlation name has been specified for EMPLOYEE:
FROM EMPLOYEE E
WHERE EMPLOYEE.PROJECT='ABC' * incorrect*
FROM EMPLOYEE E
WHERE E.PROJECT='ABC'
FROM EMPLOYEE E, DEPARTMENT
A table, view, nickname, or alias name that is exposed in a FROM clause may be the same as any other table name, view name or nickname exposed in that FROM clause or any correlation name in the FROM clause. This may result in ambiguous column name references which returns an error (SQLSTATE 42702).
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).
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).
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).
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).
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.
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.
SELECT CORZ.COLA, OWNY.MYTABLE.COLA
FROM OWNX.MYTABLE CORZ, OWNY.MYTABLE
When qualifying a column with the exposed table name form of a table designator, either the qualified or unqualified form of the exposed table name can be used. If the qualified form is used, the qualifier must be the same as the default qualifier for the exposed table name.
SELECT CORPDATA.EMPLOYEE.WORKDEPT FROM EMPLOYEE
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.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.
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.
SELECT CORPDATA.EMPLOYEE.WORKDEPT
FROM EMPLOYEE
is a valid statement.
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.
A fullselect is a form of a query that may be used as a component of various SQL statements. A fullselect used within a search condition of any statement is called a subquery. A fullselect used to retrieve a single value as an expression within a statement is called a scalar fullselect or scalar subquery. A fullselect used in the FROM clause of a query is called a nested table expression. Subqueries in search conditions, scalar subqueries and nested table expressions are referred to as subqueries through the remainder of this topic.
A subquery may include subqueries of its own, and these may, in turn, include subqueries. Thus an SQL statement may contain a hierarchy of subqueries. Those elements of the hierarchy that contain subqueries are said to be at a higher level than the subqueries they contain.
Every element of the hierarchy contains one or more table designators. A subquery can reference not only the columns of the tables identified at its own level in the hierarchy, but also the columns of the tables identified previously in the hierarchy, back to the highest level of the hierarchy. A reference to a column of a table identified at a higher level is called a correlated reference.
For compatibility with existing standards for SQL, both qualified and unqualified column names are allowed as correlated references. However, it is good practice to qualify all column references used in subqueries; otherwise, identical column names may lead to unintended results. For example, if a table in a hierarchy is altered to contain the same column name as the correlated reference and the statement is prepared again, the reference will apply to the altered table.
When a column name in a subquery is qualified, each level of the hierarchy is searched, starting at the same subquery as the qualified column name appears and continuing to the higher levels of the hierarchy until a table designator that matches the qualifier is found. Once found, it is verified that the table contains the given column. If the table is found at a higher level than the level containing column name, then it is a correlated reference to the level where the table designator was found. A nested table expression must be preceded with the optional TABLE keyword in order to search the hierarchy above the fullselect of the nested table expression.
When the column name in a subquery is not qualified, the tables referenced at each level of the hierarchy are searched, starting at the same subquery where the column name appears and continuing to higher levels of the hierarchy, until a match for the column name is found. If the column is found in a table at a higher level than the level containing column name, then it is a correlated reference to the level where the table containing the column was found. If the column name is found in more than one table at a particular level, the reference is ambiguous and considered an error.
Since the same table, view or nickname can be identified at many levels, unique correlation names are recommended as table designators. If T is used to designate a table at more than one level (T is the table name itself or is a duplicate correlation name), T.C refers to the level where T is used that most directly contains the subquery that includes T.C. If a correlation to a higher level is needed, a unique correlation name must be used.
The correlated reference T.C identifies a value of C in a row or group of T to which two search conditions are being applied: condition 1 in the subquery, and condition 2 at some higher level. If condition 2 is used in a WHERE clause, the subquery is evaluated for each row to which condition 2 is applied. If condition 2 is used in a HAVING clause, the subquery is evaluated for each group to which condition 2 is applied.
SELECT EMPNO, LASTNAME, WORKDEPT
FROM EMPLOYEE X
WHERE SALARY < (SELECT AVG(SALARY)
FROM EMPLOYEE
WHERE WORKDEPT = X.WORKDEPT)
DELETE FROM DEPARTMENT THIS
WHERE NOT EXISTS(SELECT *
FROM EMPLOYEE
WHERE WORKDEPT = THIS.DEPTNO)
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:
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.
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.
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. INSERT INTO DEPARTMENT
VALUES (:HV_DEPTNO, :HV_DEPTNAME, :HV_MGRNO, :HV_ADMRDEPT)
INSERT INTO DEPARTMENT VALUES (?, ?, ?, ?)
INSERT INTO DEPARTMENT
VALUES (:DEPTNO, :DEPTNAME, :MGRNO, :ADMRDEPT)
EXECUTE DYNSTMT
USING :HV_DEPTNO, :HV_DEPTNAME :HV_MGRNO, :HV_ADMRDEPT
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.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 and/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.
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.
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.
Within an application, a file should only be referenced in one file reference variable.
As with all other host variables, a file reference variable may have an associated indicator variable.
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
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';
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);
Structured type variables can be defined in all host languages except FORTRAN, REXX, and Java. Since these are not native data types, SQL extensions are used and the precompilers generate the host language constructs necessary to represent each variable.
As with all other host variables, a structured type variable may have an associated indicator variable. Indicator variables for structured type host variables behave in the same way as indicator variables for other data types. When a null value is returned from the database, the indicator variable is set and the structured type host variable is unchanged.
If using a parameter marker instead of a host variable, the appropriate parameter type characteristics must be specified in the SQLDA. This requires a "doubled" set of SQLVAR structures in the SQLDA, and the SQLDATATYPE_NAME field of the secondary SQLVAR must be filled with the schema and type name of the structured type. If the schema is omitted in the SQLDA structure, an error results (SQLSTATE 07002).
EXEC SQL BEGIN DECLARE SECTION;
static SQL
TYPE IS POLYGON AS BLOB(1M)
hv_poly, hv_point;
EXEC SQL END DECLARE SECTION;
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.
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 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.