ODBC Connection Strings for IBM i Db2
As 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.
|
1 |
Driver={IBM i Access ODBC Driver};System=127.0.0.1;AlwaysCalculateResultLength=1;CCSID=1208 |
If the string doesn’t contain a semicolon, we may not need the curly braces. Thus, the minimal viable connection string would be:
|
1 |
Driver=IBM i Access ODBC Driver;System=127.0.0.1 |
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:
|
1 |
odbc:{Driver=IBM i Access ODBC Driver};System=127.0.0.1 |
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:
|
1 2 3 4 5 |
[*LOCAL] Description = Default IBM i local database Driver = IBM i Access ODBC Driver System = localhost UserID = *CURRENT |
with each parameter designating the following items:
- The INI-style heading,
[*LOCAL], provides the Data Source Name (DSN) Descriptionis just a note for the sake of the readerDriveris the driver name (the most important keyword)- The first driver-specific option is
System, in this case pointing tolocalhost(the same IBM i server) UserIDand 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:
|
1 2 |
Example 1: DSN=*LOCAL;CCSID=1208 Example 2: DSN=my_dsn_name_here;CCSID=1208;NAM=1;TRIMCHAR=1 |
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.
- NAM=1 enables system naming (i.e. a slash in
- 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
- Example of starting the list with a comma to set library list:
- 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:
- ODBC Connection String Keywords for IBM i (IBM)
- How to Query IBM i Data with PHP and PDO_ODBC (Seiden Group)
- ODBC Q&A (Seiden Group)
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.
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?
While posting, multiple spaces have been converted into a single space.
The real output is:
“RIFFO”:”__________”
(where _ are spaces)
It worked upon http server restart & disabling PDO persistent connections:
setAttribute(PDO::ATTR_PERSISTENT, false);
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
No max length that we know of, but how long is your library list? Are you sure the last library is valid and that the user has permission to use it?
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
Sorry for the late response. You want the Db2 for i driver; you can install the “ibm-iaccess” driver from Yum on i, or grab it from IBM for other platforms: http://ibm.biz/ibmi-odbc-download
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.
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
Port number is 8471/TLS 9471: https://www.ibm.com/support/pages/tcpip-ports-required-ibm-i-access-and-related-functions
I didn’t notice any in the connection string documentation. It might be worth trying something like SYSTEM=hostname:port and see if that works