Manually configure an ODBC data source

Learn how to manually configure data source options that can not be set on the GUI.

The ODBC data source graphical user interface(GUI) contains a subset of the most commonly used connection options. Additional connection options are specified by the connecting application or by editing the .odbc.ini file.

It is strongly recommended that you reference the Connection string keywords topic before manually configuring connection options.

Use the following steps to add connection options to the .odbc.ini file:

Open the .odbc.ini file on your workstation using a text editor.
- For user data sources, this file is in the root directory of the user who created the data source. For example, the file for user "exampleUser" is /home/exampleUser/.odbc.ini.
- For system data sources, .odbc.ini is located in /etc/odbc.ini or /usr/local/etc/odbc.ini depending on how you installed the unixODBC driver manager.
If there are multiple ODBC data sources in the .odbc.ini file, locate the section in the file that contains the data source, that is the target for the additional connection options you are configuring.
Add a new row after the last entry in the data source and enter the new connection option and its value.
- The syntax is keyword = value.
- For example, if you want to change the date format from its default of 5 (yyyy-mm-dd or *ISO), to 1 (mm/dd/yy or *MDY), you would add DFT = 1 in the new row.
Repeat step 3 to add additional connection options to the data source.
Save the .odbc.ini file.

Figure 1. An example of a .odbc.ini file DSN entry

[System i DSN]
Description = System i Access ODBC Driver DSN for i5/OS
Driver = System i Access ODBC Driver
System = SystemiName
UserID =
Password =
Naming = 0
DefaultLibraries = QGPL
Database =
ConnectionType = 0
CommitMode = 2
ExtendedDynamic = 0
DefaultPkgLibrary = QGPL
DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression = 1
LibraryView = 0
AllowUnsupportedChar = 0
ForceTranslation = 0
Trace = 0

Note:

Do not add multiple entries for the same connection option to the same section for a specific data source. This can lead to unpredictable behavior.
After manually editing the registry you can use ODBCConfig to configure your data source.
Note: Older versions of the unixODBC driver manager removed manually added options from the odbc.ini file when ODBCConfig was used to configure the data source. If you experience this problem, a newer version of the unixODBC driver manager is needed.
Options specified by the application in the connection string override any options specified in the .odbc.ini file.