Identifier Properties in Database Connections
When you create most relational database connections, you must configure database identifier properties. The identifier properties determine whether the Data Integration Service encloses identifiers within delimited characters when the service generates SQL queries to access the database.
A database identifier is a database object name. Tables, views, columns, indexes, triggers, procedures, constraints, and rules can have identifiers. You use the identifier to reference the object in SQL queries. A database can have regular identifiers or delimited identifiers that must be enclosed within delimited characters.
Regular Identifiers
Regular identifiers comply with the format rules for identifiers. Regular identifiers do not require delimited characters when they are used in SQL queries.
For example, the following SQL statement uses the regular identifiers MYTABLE and MYCOLUMN:
SELECT * FROM MYTABLE
WHERE MYCOLUMN = 10
Delimited Identifiers
Delimited identifiers must be enclosed within delimited characters because they do not comply with the format rules for identifiers.
Databases can use the following types of delimited identifiers:
- Identifiers that use reserved keywords
If an identifier uses a reserved keyword, you must enclose the identifier within delimited characters in an SQL query. For example, the following SQL statement accesses a table named ORDER:
SELECT * FROM “ORDER”
WHERE MYCOLUMN = 10
- Identifiers that use special characters
If an identifier uses special characters, you must enclose the identifier within delimited characters in an SQL query. For example, the following SQL statement accesses a table named MYTABLE$@:
SELECT * FROM “MYTABLE$@”
WHERE MYCOLUMN = 10
- Case-sensitive identifiers
By default, identifiers in IBM DB2, Microsoft SQL Server, and Oracle databases are not case sensitive. Database object names are stored in uppercase, but SQL queries can use any case to refer to them. For example, the following SQL statements access the table named MYTABLE:
SELECT * FROM mytable
SELECT * FROM MyTable
SELECT * FROM MYTABLE
To use case-sensitive identifiers, you must enclose the identifier within delimited characters in an SQL query. For example, the following SQL statement accesses a table named MyTable:
SELECT * FROM “MyTable”
WHERE MYCOLUMN = 10
Identifier Properties
When you create most database connections, you must configure database identifier properties. The identifier properties that you configure depend on whether the database uses regular identifiers, uses keywords or special characters in identifiers, or uses case-sensitive identifiers.
Configure the following identifier properties in a database connection:
- SQL Identifier Character
Type of character that the database uses to enclose delimited identifiers in SQL queries. The available characters depend on the database type.
Select (None) if the database uses regular identifiers. When the Data Integration Service generates SQL queries, the service does not place delimited characters around any identifiers.
Select a character if the database uses delimited identifiers. When the Data Integration Service generates SQL queries, the service encloses delimited identifiers within this character.
- Support Mixed-case Identifiers
Enable if the database uses case-sensitive identifiers. When enabled, the Data Integration Service encloses all identifiers within the character selected for the SQL Identifier Character property.
In the Informatica client tools, you must refer to the identifiers with the correct case. For example, when you create the database connection, you must enter the database user name with the correct case.
When the SQL Identifier Character property is set to none, the Support Mixed-case Identifiers property is disabled.
Example: Database Uses Regular Identifiers
In this example, the database uses regular identifiers. No identifiers contain a reserved keyword or a special character. The database uses identifiers that are not case sensitive.
In the database connection, set the SQL Identifier Character property to (None). When SQL Identifier Character is set to none, the Support Mixed-case Identifiers property is disabled.
When the Data Integration Service generates SQL queries, the service does not place delimited characters around any identifiers.
Example: Database Uses Keywords or Special Characters in Identifiers
In this example, the database uses keywords or special characters in some identifiers. The database uses identifiers that are not case sensitive.
In the database connection, configure the identifier properties as follows:
- 1. Set the SQL Identifier Character property to the character that the database uses for delimited identifiers.
This example sets the property to "" (quotes).
- 2. Clear the Support Mixed-case Identifiers property.
When the Data Integration Service generates SQL queries, the service places the selected character around identifiers that use a reserved keyword or that use a special character. For example, the Data Integration Service generates the following query:
SELECT * FROM "MYTABLE$@" /* identifier with special characters enclosed within delimited
character */
WHERE MYCOLUMN = 10 /* regular identifier not enclosed within delimited character */
Example: Database Uses Case-Sensitive Identifiers
In this example, the database uses case-sensitive identifiers. The database might use keywords or special characters in some identifiers, or it might not.
In the database connection, configure the identifier properties as follows:
- 1. Set the SQL Identifier Character property to the character that the database uses for delimited identifiers.
This example sets the property to "" (quotes).
- 2. Select the Support Mixed-case Identifiers property.
When the Data Integration Service generates SQL queries, the service places the selected character around all identifiers. For example, the Data Integration Service generates the following query:
SELECT * FROM "MyTable" /* case-sensitive identifier enclosed within delimited character */
WHERE "MYCOLUMN" = 10 /* regular identifier enclosed within delimited character */