Using ODBC¶
Now that you have the IBM i Access ODBC Driver installed on your system, you are ready to connect to Db2 on i.
Connection Strings¶
ODBC uses a connection string with keywords to create a database connection.
Keywords are case insensitive, and values passed are separated from the keyword
by an equals sign (“=
”) and end with a semi-colon (“;
”). As long as you are
using an ODBC database connector, you should be able to pass an identical
connection string in language or technology and be confident that it will
correctly connect to Db2 on i. A common connection string may look something like:
DRIVER=IBM i Access ODBC Driver;SYSTEM=my.ibmi.system;UID=foo;PWD=bar;
In the above example, we define the following connection options:
DRIVER: The ODBC driver for Db2 for i that we are using to connect to the database (and that we installed above)
SYSTEM: The location of your IBM i system, which can be its network name, IP address, or similar
UID: The User ID that you want to use on the IBM i system that you are connecting to
PWD: The password of the User ID passed above.
These are only some of the over 70 connection options you can use when connecting to Db2 on i using the IBM i Access ODBC Driver. A complete list of IBM i Access ODBC Driver connection options can be found at the IBM Knowledge Center: Connection string keywords webpage. If passing connections options through the connection string, be sure to use the keyword labeled with Connection String.
DSNs¶
As you add more and more options to your connection string, your connection string can become quite cumbersome. Luckily, ODBC offers another way of defining connection options called a DSN (datasource name). Where you define your DSN will depend on whether you are using Windows ODBC driver manager or unixODBC on Linux or IBM i.
Configuration with UnixODBC (IBM i, Linux, macOS)¶
IBM i, Linux distributions, and macOS use unixODBC and have nearly identical methods of setting up your drivers and your DSNs.
odbc.ini
and .odbc.ini
When using unixODBC, DSNs are defined in odbc.ini
and .odbc.ini
(note the
.
preceding the latter). These two files have the same structure, but have one
important difference:
odbc.ini
defines DSNs that are available to all users on the system. If there are DSNs that should be available to everyone, they can be defined and shared here. Likely, this file is located in the default location, which depends on whether you are on IBM i or Linux:IBM i:
/QOpenSys/etc/odbc.ini
Linux:
/etc/unixODBC/odbc.ini
If you want to make sure, the file can be found by running:
odbcinst -j
.odbc.ini
is found in your home directory (~/
) and defines DSNs that are available only to you. If you are going to define DSNs with your personal username and password, this is the place to do it.
In both odbc.ini
and .odbc.ini
, you name your DSN with []
brackets, then
specify keywords and values below it. An example of a DSN stored in
~/.odbc.ini
used to connect to an IBM i system with private credentials might
look like:
[MYDSN]
Description = My IBM i System
Driver = IBM i Access ODBC Driver
System = my.ibmi.system
UserID = foo
Password = bar
Naming = 0
DefaultLibraries = MYLIB
TrueAutoCommit = 1
(Note: The name of the driver specified in the Driver
keyword must match
the name of a driver defined in odbcinst.ini
. The location of this file can
also be found by running odbcinst -j
in PASE. When you install the IBM i
Access ODBC Driver on your system, it automatically creates a driver entry of
IBM i Access ODBC Driver
in odbcinst.ini
, which you should use for all
IBM i connections).
When installing the IBM i Access ODBC Driver on IBM i, the driver will
automatically create a DSN called [*LOCAL]
in your odbc.ini
:
### IBM provided DSN - do not remove this line ###
[*LOCAL]
Description = Default IBM i local database
Driver = IBM i Access ODBC Driver
System = localhost
UserID = *CURRENT
### Start of DSN customization
### End of DSN customization
### IBM provided DSN - do not remove this line ###
When using this DSN, the user credentials used will be *CURRENT
, which is the
user who is running the process that is trying to connect to the ODBC driver.
Use of this *CURRENT
behavior is dependent on some server PTFs:
7.2: SI68113
7.3: SI69058
7.4: (none, comes with the operating system)
Like connection string keywords, DSN keywords can be found at the IBM Knowledge Center: Connection string keywords webpage. When passing connection options through a DSN, be sure to use the keyword labeled with ODBC.INI.
Configuration on Windows¶
When you have the driver installed on your system, you can now configure your datasource names (DSNs) that allow you to wrap all of your connection settings in one place that can be used by any ODBC application.
In ODBC Data Source Administrator, you can define either User DSNs or System DSNs. A User DSN will be available only to your Windows user, while a System DSN will be available to everyone. Furthermore, System DSNs must be defined per-architecture, while User DSNs are architecture agnostic.
To create a DSN, select either User DSN or System DSN and then select Add
on
the right-hand menu. It will prompt you to select a driver, and you will select
IBM i Access ODBC Driver
. Use the GUI to add configuration options, such as
your username and passwords, threading, default library, and so on.
Using Your DSN¶
Once you have DSNs defined with the connection options you want, you can simply pass a connection string to your ODBC connections that references the DSN:
DSN=MYDSN
This will look through your DSNs for a match, and pull in all connection options defined therein. This helps keep your connection string much more manageable, and also keeps your connections string more secure, as you don’t have to explicitly pass your password in plain text.
Additional options can be added to your connection string even if you use a DSN. In this way, you can extend your DSNs with options that make sense for a given use. To add more options, simply list them as you would any normal connection string:
DSN=MYDSN;DBQ=MYLIB,OTHERLIB;CCSID=1208;
Note that connection keywords specified on the connection string will override a
DSN keyword that has the same functionality (e.g. a CMT
value on the
connection string will override any CommitMode
defined for a DSN used in that
connection string).