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:

  1. 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.
  2. 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.
  3. Add a new line 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 line.
  4. Repeat step 3 to add additional connection options to the data source.
  5. Save the odbc.ini file.
Figure 1. An example of a odbc.ini file DSN entry
[DSN]
Description = Example DSN
Driver = IBM i Access ODBC Driver
System = HostName
UserID = UserName
Password = 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:
  1. 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.
  2. After manually editing the registry you can use ODBCConfig to configure your data source.
  3. Options specified by the application in the connection string override any options specified in the odbc.ini file.