DELIMIDENT environment variable

The DELIMIDENT environment variable specifies that strings enclosed between double quotation ( " ) marks are delimited database identifiers.

The DELIMIDENT environment variable is also supported on client systems, where it can be set to y, to n, or to no setting.
  • y specifies that client applications must use single quotation ( ' ) symbols to delimit character strings, and must use double quotation ( " ) symbols only around delimited SQL identifiers, which can support a larger character set than is valid in undelimited identifiers. Letters within delimited strings or delimited identifiers are case-sensitive. This is the default value for OLE DB and .NET.
  • n specifies that client applications can use double quotation ( " ) or single quotation ( ' ) symbols to delimit character strings, but not to delimit SQL identifiers. If the database server encounters a string delimited by double or single quotation symbols in a context where an SQL identifier is required, it issues an error. An owner name that qualifies an SQL identifier can be delimited by single quotation ( ' ) symbols. You must use a pair of the same quotation symbols to delimit a character string.

    This is the default value for ESQL/C, JDBC, and ODBC. APIs that have ESQL/C as an underlying layer, such as the DataBlade® API (LIBDMI), and the C++ API, behave as ESQL/C, and use 'n' as the default if no value for DELIMIDENT is specified on the client system.

  • Specifying the DELIMIDENT environment variable with no value on the client system requires client applications to use the DELIMIDENT setting that is the default for their application programming interface (API).

1  setenv DELIMIDENT

No value is required; DELIMIDENT takes effect if it exists, and it remains in effect while it is on the list of environment variables. Removing DELIMIDENT when it is set at the server level requires restarting the server.

Delimited identifiers can include white space (such as the phrase "Vitamin E") or can be identical to SQL keywords, (such as "TABLE" or "USAGE"). You can also use them to declare database identifiers that contain characters outside the default character set for SQL identifiers (such as "Column #6"). In the default locale, this set consists of letters, digits, and the underscore ( _ ) symbol.

Even if DELIMIDENT is set, you can use single quotation ( ' ) symbols to delimit authorization identifiers as the owner name component of a database object name, as in the following example: 
RENAME COLUMN 'Owner'.table2.collum3 TO column3;
This example is an exception to the general rule that when DELIMIDENT is set, the SQL parser interprets character strings delimited by single quotation symbols as string literals, and interprets character strings delimited by double quotation symbols ( " ) as SQL identifiers.

Database identifiers (also called SQL identifiers) are names for database objects, such as tables and columns. Storage identifiers are names for storage objects, such as dbspaces, blobspaces, and sbspaces. You cannot use DELIMIDENT to declare storage identifiers that contain characters outside the default SQL character set.

Delimited identifiers are case sensitive. To use delimited identifiers, applications in must set DELIMIDENT at compile time and at run time.
Important: If DELIMIDENT is not already set, you should be aware that setting it can cause the failure of existing .sql scripts or client applications that use double ( " ) quotation marks in contexts other than delimiting SQL identifiers, such as delimiters of string literals. You must use single ( ' ) rather than double quotation marks for delimited constructs that are not SQL identifiers if DELIMIDENT is set.
On UNIX™ systems that use the C shell and on which DELIMIDENT has been set, you can disable this feature (which causes anything between double quotation symbols to be interpreted as an SQL identifier) by the command:
unsetenv  DELIMIDENT