unixODBC supports a wide and almost complete list of databases. Most of the following widely diffused databases are supported:

Now, if you have previously installed unixODBC via the package manager, you can follow the same procedure, for example, on Red Hat with the following command:

$ yum install mysql-connector-odbc

Otherwise, they are also available as a packet; you only need to download the package, for example, mysql-connector-odbc-5.1.13-linux-glibc2.5-x86-64bit.tar.gz.

Then, decompress the package and copy the contents in the /usr/lib/odbc and /usr/lib64/odbc/ directories as follows:

$ tar xzf mysql-connector-odbc-5.1.13-linux-glibc2.5-x86-64bit.tar.gz
$ mkdir /usr/lib64/odbc/
$ cp /usr/src/ mysql-connector-odbc-5.1.13-linux-glibc2.5-x86-64bit/lib/* /usr/lib64/odbc/

Now you can check whether all the needed libraries are present on your system using the ldd command.

This can be done on a 32-bit system with the following command:

$ ldd /usr/lib /libmyodbc5.so

This can be done on a 64-bit system using the following command:

$ ldd /usr/lib64 /libmyodbc5.so

If nothing is marked as Not Found, this means that all the needed libraries are found and you can go ahead; otherwise, you need to check what is missing and fix it.

All the installed ODBC database drivers are listed in /etc/obcinst.ini; this file, for MySQL 5, should contain the following:

[mysql]
Description = ODBC for MySQL
Driver      = /usr/lib /libmyodbc5.so 
Setup       = /usr/lib/libodbcmyS.so

A 64-bit system should contain the following:

[mysql]
Description = ODBC for MySQL
Driver64        = /usr/lib64/libmyodbc5.so
Setup64         = /usr/lib64/libodbcmyS.so 

Data sources are defined in the odnc.ini file. You need to create a file with the following content:

[mysql-test]
# This is the driver name as specified on odbcinst.ini
Driver = MySQL5 
Description = Connector ODBC MySQL5
Database = <db-name-here>
USER= <user-name-here>
Password = <database-password-here>
SERVER = <ip-address-here>
PORT = 3306

In order to access a PostgreSQL database via ODBC, you need to install the appropriate drivers. They will be used by the Zabbix server to send the queries to any PostgreSQL database via the ODBC protocol.

The official ODBC drivers for PostgreSQL are available at http://www.postgresql.org/ftp/odbc/versions/src/.

Perform the following steps to work with the PostgreSQL database:

Oracle is another widely used database and provides an ODBC driver as well. The following is a description of how to install Oracle's ODBC because at http://www.unixodbc.org, there isn't much information about it.

Now, you are enabled to connect to most of the common databases. To check the connection, you can test it using isql, as follows:

This enables the verbose mode, which is very useful to debug a connection.

A good thing to know is that /etc/obcinst.ini is a common file, hence you'll have all your unixODBC entries there.

Now if you connect to the target database that is to be monitored, it is time to compile the Zabbix server with ODBC support by performing the following steps:

This means that all went fine.

Now it is time to use the Zabbix ODBC functionality. In order to do so, you need to create an item of the Database monitor type, as shown in the following screenshot:

The item where the retrieved value will be stored is identified by the item key as follows:

db.odbc.select[<unique short description>]

<unique short description> is a string that must be unique and can be whatever you want. An example is as follows:

db.odbc.select[web_user_connected_on_myapp]

Inside the Additional parameters field, you need to specify the following:

DSN=<database source name>
user=<user name>
password=<password>
sql=<query>

Where the DSN should exist in /etc/odbc.ini and whether the username and password are stored in the DSN definition or not can be specified here. In the last line, you need to specify the SQL query.

The following are some restrictions to the use of, and things to consider about, a SQL query:

As you can see, there are only some limitations that you can accept. In particular, you can't call a function if that function returns only one value. You can't execute a stored procedure; you can only select the data. Also, the query can't contain any line breaks, so long and complex queries will not be easily readable.

The following are some other points to consider:

If you consider a database that will be under heavy stress, don't have a pool introduced for an overhead that is not necessary. Also, in this case, it is possible that just to have a connection, you need to wait for more than 5 seconds.

The 5 seconds mentioned previously is not a random value; indeed, the timeout of a connection is defined when you open a connection. During the initialization of that, you need to define your expected timeout before considering the connection impossible.

Zabbix defines this timeout in the following command:

src/libs/zbxdbhigh/odbc.c

On line 130 of the file, we have the definition of the connection timeout for Zabbix as follows:

SQLSetConnectAttr(pdbh->hdbc, (SQLINTEGER)SQL_LOGIN_TIMEOUT,
  (SQLPOINTER)5, (SQLINTEGER)0);

This (SQLPOINTER)5 sets SQL_LOGIN_TIMEOUT to 5 seconds. If your database doesn't respond in 5 seconds, you will get the following error inside the log file:

[ODBC 3.51 Driver]Can't connect to MySQL server on 'XXX.XXX.XXX.XXX' (4)] (2003).

SQLSetConnectAttr(pdbh->hdbc,(SQLINTEGER)SQL_LOGIN_TIMEOUT, (SQLPOINTER)15,(SQLINTEGER)0);