IDM 7.3.1

Case insensitivity for a JDBC repo

The following topic only applies if you have set up a JDBC repository, as described in Select a repository

A DS repository is case-insensitive by default. The supported JDBC repositories are generally case-sensitive by default. Case-sensitivity can cause issues if queries expect results to be returned, regardless of case.

For example, with the default configuration of a MySQL database, a search for an email address of scarter@example.com might return a result, while a search for scarter@EXAMPLE.COM might return an Unable to find account error.

If you need to support case-insensitive queries, you must configure a case-insensitive collation in your JDBC repository, on the specific columns that require it. For example:

  • For a generic managed object mapping in MySQL or MariaDB, change the default collation of the managedobjectproperties.propvalue column to utf8_general_ci. Note that this changes case-sensitivity for all managed object properties. To change case-sensitivity for all the properties of a specific object, specify a different table for the propertiesTable entry in your repo.jdbc.json for that object, and adjust the collation on that table. To change case-sensitivity only for certain properties of an object, use an explicit mapping.

  • For a PostgreSQL repository, use an explicit table structure if you require case-insensitivity. Managing case-insensitivity at scale with generic tables in PostgreSQL is not supported. For more information about object mappings, refer to Mappings with a JDBC repository.

  • For an Oracle DB repository, refer to the corresponding Oracle documentation.

  • For a SQL Server repository, refer to the corresponding Windows documentation.

  • For a DB2 repository, refer to the corresponding DB2 documentation.