Case sensitivity and the correct use of quotation marks

When you specify option values and objects in Db2® SQL statements, you need to know when quotation marks are required, which type to use, and how they affect cases sensitivity.

How you name objects when you first create them affects the case of the characters in the object name and determines how you specify object names and option values in commands. For example, if, when you create a nickname, you do not enclose the name in double quotation marks, the system catalog stores the nickname in uppercase characters, regardless of the case of the characters that you use to name the object. If you use double quotation marks when you create the nickname, the catalog stores the characters of the object name in exactly the case that you specify. Then, when you use the object name as an option value, you must specify exactly that case. For example, the FOREIGN_KEY column option that the Script, Web services, and XML wrappers support requires that you specify the nickname for the foreign key column as the option value. When you enter the option value, you must use the same case that the federated server catalog uses to store the nickname.

The following table describes the correct use of case and quotation marks when you specify option values and objects in Db2 SQL statements.
Table 1. Correct use of case and quotation marks
Identifier Case and quotation mark use Examples
Option value Use the case that the option value requires, and enclose the option value in single quotation marks. This statement creates a data source table named remote_schema.remote_table (all lowercase):
CREATE TABLE newton.my_nick
(c1 int)
OPTIONS
(remote_server 'MY_SERVER'
remote_schema 'remote_schema',
remote_tabname 'remote_table');
This statement creates a data source table named REMOTE_SCHEMA.REMOTE_TABLE (all uppercase):
CREATE TABLE newton.my_nick
(c1 int) 
OPTIONS 
(remote_server'MY_SERVER'
remote_schema 'REMOTE_SCHEMA',
remote_tabname 'REMOTE_TABLE');
Object that contains only lowercase characters Use all lowercase characters, and enclose in the identifier double quotation marks. This statement creates a nickname on a data source table named infx_user.remote_table (all lowercase):
CREATE NICKNAME my_nick
FOR 
infx_server.
"infx_user"."remote_table";
Note: Some data sources such as Informix® and Teradata use lowercase names by default.
Object that contains only uppercase characters, numbers, and underscore characters (_) There are two choices:
  • Use all uppercase characters, and enclose the identifier in double quotation marks.
  • Use any case and do not enclose the identifier in double quotation marks.
Each of these statements creates the nickname MY_NICK (all uppercase):
CREATE NICKNAME my_nick
FOR infx_server.
"infx_user"."remote_table";
CREATE NICKNAME "MY_NICK"
FOR infx_server.
"infx_user"."remote_table";
For data source authorization IDs and passwords, you can also use the server options FOLD_ID and FOLD_PW to convert the ID and password to the correct case.

From a UNIX operating system command prompt

If you enclose a case-sensitive value in quotation marks at the UNIX command prompt on the federated server, you must ensure that the quotation marks are parsed correctly:
SQL statements that contain double quotation marks, but that do not contain single quotation marks
If the SQL statement contains double quotation marks but does not contain single quotation marks, enclose the entire statement in single quotation marks.
For example, if you want to issue this SQL statement: 
CREATE NICKNAME my_nickname FOR my_server."owner"."my_table"

You enter the following text at the UNIX command prompt:

db2 'CREATE NICKNAME my_nickname FOR my_server."owner"."my_table"'
SQL statements that contain single quotation marks, but that do not contain double quotation marks
If the SQL statement contains single quotation marks but does not contain double quotation marks, enclose the entire statement in double quotation marks.
For example, if you want to issue this SQL statement: 
CREATE USER MAPPING FOR USER SERVER my_server 
    OPTIONS(REMOTE_AUTHID 'my_id', REMOTE_PASSWORD 'my_password')

You enter the following text at the UNIX command prompt:

db2 "CREATE USER MAPPING FOR USER SERVER my_server 
    OPTIONS(REMOTE_AUTHID 'my_id', REMOTE_PASSWORD 'my_password')"
SQL statements that contain both double and single quotation marks
If the SQL statement contains both single and double quotation marks:
  • Enclose the entire statement in double quotation marks
  • Precede each double quotation mark in the statement with a backward slash.
For example, to issue this SQL statement: 
CREATE USER MAPPING FOR "local_id" SERVER my_server 
    OPTIONS(REMOTE_AUTHID 'my_id', REMOTE_PASSWORD 'my_password')
You enter the following text at the UNIX command prompt:
db2 "CREATE USER MAPPING FOR \"local_id\" SERVER my_server 
    OPTIONS(REMOTE_AUTHID 'my_id',  REMOTE_PASSWORD 'my_password')"

The above examples assume that you enter the SQL statements from the UNIX command prompt and pass the statement to the Db2 command, without using the -f option. To use the Db2 command with the -f option to enter the SQL statements from a file, enter the statements as shown in the first occurrence of each example.

From a Windows operating system command prompt

To preserve case-sensitive values when you enter commands from a Microsoft Windows command prompt on the federated server, precede each double quotation mark with a backward slash. For example, you want to create the nickname nick1 for the Microsoft SQL Server table weekly_salary. The table resides in the NORBASE database. The local schema is my_schema.

At the Windows command prompt on the federated server, you type:
db2 CREATE NICKNAME nick1 
    FOR NORBASE.\"my_schema\".\"weekly_salary\"

From the Db2 command line or from an application

When you specify a value from the Db2 command line or from an application, you can preserve case-sensitive values by enclosing the values in the proper quotation marks.

For example, you want to create a user mapping for the user ID local_id. The remote user ID my_id and the remote password is my_password. You want all three of these values to be preserved in lowercase. At the Db2 command prompt you type:
CREATE USER MAPPING FOR "local_id" SERVER my_server 
    OPTIONS(REMOTE_AUTHID 'my_id', REMOTE_PASSWORD 'my_password')