ODBC Connection Strings for IBM i Db2

ODBC with Db2 for IBM iAs ODBC gains popularity for IBM i database access, developers of web applications and APIs need information on defining ODBC database connections. This article demonstrates tips and techniques for effectively using ODBC connection strings in all programming environments, including popular languages such as PHP, Python, and Node.js.

For an example of how connection strings are used in a program, see How to Query IBM i Data with PHP and PDO_ODBC.

Connection strings give you options

The first option to provide in a connection string is the database driver name. The IBM i Db2 driver, for example, is called IBM i Access ODBC Driver.

Beyond this required driver name, ODBC permits any options that the specified driver allows. ODBC is flexible enough to handle these database-specific options. In the case of Db2 for i, IBM documents all applicable ODBC keywords.

When specifying connection options, several rules must be observed:

  • Connection options are separated by semicolons
  • Options containing semicolons (e.g. a password with a semicolon in it) must be wrapped in curly braces
  • It’s common practice to wrap the driver name in curly braces, but it doesn’t have to be

For example, the string below specifies the driver; System (hostname required by this driver); and two optional keywords, AlwaysCalculateResultLength and CCSID, which we’ll cover later in this article.

If the string doesn’t contain a semicolon, we may not need the curly braces. Thus, the minimal viable connection string would be:

PHP note: If you’re using PDO_ODBC, you need to add the odbc: prefix to indicate that PDO should be using ODBC as the PDO backend, like so:

For brevity, we will omit the “odbc:” from our later examples, but you should add this prefix when using PDO_ODBC.

That’s a still a lot of characters. We can shorten the string even further.

Data Source Name (DSN)

Connection strings can be long if they contain many custom options. Depending on how your application is structured, your connection string might be repeated in multiple scripts, forcing you to edit each script to change the connection string. To solve both problems, developers may define a centrally located connection, known as a data source. Data sources are identified by their Data Source Name (DSN), which lets you define a short name for a bunch of different settings, including driver, server, credentials, and any other options. How these are defined depends on your platform:

  • On Windows, ODBC data sources can be managed through “ODBC Data Sources”.
  • On platforms with unixODBC (including IBM i), data sources are defined in odbc.ini (on IBM i, /QOpenSys/etc/odbc.ini)

The IBM i’s default ODBC data source definition is named *LOCAL:

with each parameter designating the following items:

  • The INI-style heading, [*LOCAL], provides the Data Source Name (DSN)
  • Description is just a note for the sake of the reader
  • Driver is the driver name (the most important keyword)
  • The first driver-specific option is System, in this case pointing to localhost (the same IBM i server)
  • UserID and password can optionally be specified

You can define your own data sources as well. You might do this if you needed to specify many keywords, or wanted to define them centrally. If you do define them centrally in the odbc.ini file, you can group them under an appropriate name (DSN), then specify the DSN in your code’s connection string, as shown below.

Using a DSN in a connection string

To specify a Data Source Name (DSN) in a connection string, use the DSN keyword. Your connection will inherit the DSN’s options, such as the driver name. To add to or override the DSN’s settings, you may specify additional keywords in your connection string. The examples below show how to override DSN options:

Common connection string options for Db2

Here are several connection string options that we have found useful. The keywords below are shown in the capitalized format accepted by connection strings. The naming is different when used in data sources, as indicated in the IBM connection string documentation.

  • NAM: An IBM i-specific option that enables the system naming mode and library lists.
    • NAM=1 enables system naming (i.e. a slash in LIBRARY/FILE) and library list resolution of object names.
  • DBQ: Setting a library list is a common requirement on IBM i. Setting a single default library is less common. DBQ lets you do both! The libraries to use are separated by commas. The first library specified in DBQ becomes the default library. If you want to set the library list rather than the default library, leave the default library blank, so that the list begins with a comma.
    • Example: DBQ=,mylib,mylib2,mylib3
  • CCSID: Overrides the ASCII CCSID to another encoding, such as 1208 (UTF-8). Helpful if the the driver picks the wrong CCSID (i.e. for a legacy 8-bit encoding).
  • ALWAYSCALCLEN: By default, the ODBC driver will avoid calculating the exact size of a column if the calculation would be slow. Some ODBC components, however, require an exact size. If that’s the case, you can make the ODBC driver compatible by setting ALWAYSCALCLEN=1. This setting forces the driver to make those more expensive calculations, at the potential cost of speed.
  • TRIMCHAR: If you retrieve fixed length character columns and don’t want to trim trailing spaces in your code, you can set TRIMCHAR=1 to make the driver do the work for you.
  • XDYNAMIC: This lets you turn off SQL package-based caching for queries. By default, it’s on, and can improve performance, but if you encounter issues with packages, you can set XDYNAMIC=0.

The Power of ODBC Connection Strings

Connection string keywords provide a universal method for making ODBC work the way you want. Those keywords can be grouped in a data source to provide a reusable configuration. A given data source can be added to a connection string via its Data Source Name (DSN).

Whatever programming language or database tool you choose, it pays to learn about ODBC connection strings. Here are our favorite resources:

For more information, get in touch.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.