Seiden Group Blog

ODBC Connection Strings for IBM i Db2

/12 Comments/in , /by

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)

Developers may define keywords in a centrally located collection, known as a data source. Data sources are identified by their Data Source Name (DSN), which lets you group multiple 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. If you 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), 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: The DBQ keyword allows setting a library list (multiple libraries separated by commas or spaces) or a single default library. If the string starts with a library name, it becomes the single default library. Start the string with a comma to indicate there’s no one default library, i.e. to set a library list.
    • Example of starting the list with a comma to set library list: 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 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:

Help with Connection Strings

If you would benefit from fast, reliable answers to your questions on using connections strings and other technologies in your IBM i environment, ask us about SmartSupport.

12 replies
  1. Scott Schollenberger
    Scott Schollenberger says:

    I recently migrated a server hosting client websites from a POWER9 to POWER10. Once migrated, the websites could not establish a database connection; we received the SQL state -30061 (SQ30061 message) and the message referred to the old server’s RDB directory name S7878xxx. We could not find any .ini files in the IFS that specified that RDB directory name. At this point, we had to restore to deleting the POWER10’s *LOCAL RDB directory record and create on with the old S7878xxx name to get the websites functional.

    I’m wondering if someone could provide some direction to looking for this old S7878xxx entry. Unfortunately, the former employee who created these websites is no longer with the company.

    Thanks.

    Reply
    • David Larsen
      David Larsen says:

      Scott, on a command line type GO CMDRDB. This brings up the Relational Database Commands menu. Option 5. Work with RDB Directory Entry will show all the entries on the system. If the one you need isn’t there, take option 1 on the RDB menu to add an RDB entry. It sounds like you just need to add an entry S7878xxx with Relational Database alias as *NONE, RMTLOCNAME as *LOCAL, and assigned it TYPE *IP. That should work.

      Reply
  2. Giovanni Formentini
    Giovanni Formentini says:

    I’m trying TRIMCHAR=1 but simply doesn’t work.
    My connection string is:
    odbc:DSN=*LOCAL;CCSID=1208;NAM=0;TSFT=1;TRIMCHAR=1;CMT=0
    and this is a sample output of a char(10) field:
    “RIFFO”:” ”
    what am I doing wrong?

    Reply
    • Giovanni Formentini
      Giovanni Formentini says:

      While posting, multiple spaces have been converted into a single space.
      The real output is:
      “RIFFO”:”__________”
      (where _ are spaces)

      Reply
      • Giovanni Formentini
        Giovanni Formentini says:

        It worked upon http server restart & disabling PDO persistent connections:
        setAttribute(PDO::ATTR_PERSISTENT, false);

        Reply
  3. Ha Tran
    Ha Tran says:

    Hi, I recently work on a project back end .net 6.0 in which we use graphql to query data from ibm i/as400. Anyway, in my appsetting.json connection string for db2, I have list of library defined , for some reason it doesnt take in any more library which cause new code to not work but also cause other lib to not data either, I wonder if is there lax length for library list db2.
    Thank you

    Reply
  4. Joachim
    Joachim says:

    Hi Calvin,
    thank you very much for your helpful informations.
    What I’d like to do is using ODBC to connect from IBM i to mysql and other databases using pyodbc. So, I need an appropriate driver, which I was not able to find. Do you have a solution?
    Thanks in forward.
    Best regards,
    Joachim

    Reply
  5. Mustafa
    Mustafa says:

    How do I make IBM i ACS ODBC connection string to prompt for user id and password, I tried SIGNON=1 but it doesn’t have any affect and always get Prompt mode= never in the error message.

    Reply
  6. Oliver Schnell
    Oliver Schnell says:

    Hello Calvin,

    Thanks for the very interesting article. Is there actually an option in the connection string to configure the port? Somehow I didn’t find anything. Do you know, which default port is used, if it is not configurable?

    Thanks and best regards,
    Oliver

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

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

IBM Announces IBM i 7.5 and 7.4 TR6Continuous Integration for PHP Extensions