Data Import from IBM DB2

stevie-sy · September 6, 2019, 9:50am

Dear community,

since our director workshop we know, that’s also easy to import data directly from a SQL-database instead of csv/txt files. We have our own CMDB where we manage our infrastructure. The database system is IBM DB2.

Situation now:
Now is we get the data with a perl script and the IBM DB2-ODBC driver from our CMDB. The Installation of the driver was at the beginning not easy, but after some setbacks I could handle it (for people who are interested in: With this documentaiton it was very easy to install the driver for the way I needed it with perl: https://www.froebe.net/blog/2017/10/06/howto-ibm-db2-odbc-and-linux-nix-configuration/).
We created a cron job which runs the script to produce the csv file for the import into the director. Everything works fine. But not every of my colleagues can maintain this, if something changes.

Future:
I saw in the source code of Icingaweb2 that a data source could also be IBM DB2, if the PHP extensions are installed and loaded. So my idea now is don’t use the intermediate step with the script and use the director feature to get the data directly from the database. Now I’m testing this …

First of all with the ODBC-DB2-driver this doesn’t work. You’ll need the “big” Driver packet from IBM. Here are, besides the driver, some extensions for programming langues included (also the Java, Python, Perl, PHP etc. extensions). So I got this from our database-admin.
For interested people: This documentation is very good for the installation https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.swg.im.dbclient.php.doc/doc/t0011926.html.
After the experience with the odbc-driver and the documentation the installation was not so difficult. Only a few tricks were needed, maybe because of our configuration. You can use the PHP extensions from the driver packet or get the latest Version from PECL (https://pecl.php.net/package/PDO_IBM and https://pecl.php.net/package/PDO_IBM). I used the latest version from PECL, because IBM wrote in the docs it’s recommended.

After Installation, PHP loaded the extension successfully and we created a new database ressource at the configuration menue in icingaweb2. Now there is the possibility to choose a new database type “IBM (DB2)”.

.
After input the connection data to our DB2 icingaweb2 can connect to it.

So far, everything is successful!

Problem now:
Now I want to insert the first SQL Statement in the director to get the data.

If I want to save this, I got following error message:

And I don’t know where it comes from to debug this. I also don’t found the message in the source code of icingaweb2. Maybe somewhere are some further options needed.
Because the connections with my perl script works perfect for us. Here I use the dbi package. And here I had to use some options for connect-command (LongTruncOk => 1, LongReadLen=> 20000). I guess something like this is missing for the connection with PHP.
Maybe someone has ideas or can give me some hints.

Thank you
Stefan

dnsmichi · September 6, 2019, 10:09am

That originates from php.ini max allowed memory size. But the tried to allocate thingy looks like a memory overflow.

Before digging into deep here, create a small standalone PHP script which incorporates the PDO calls to DB2 and analyse how the general flow works. I could imagine that due to some version or binary mismatch, you run into memory leaks. I’ve seen such in the past with other PDO extensions.

Cheers,
Michael

stevie-sy · September 6, 2019, 10:59am

thank you for the hint. I’ll try it
Stefan

stevie-sy · September 9, 2019, 3:01pm

Today I tested it with a small test php script. Of course it works . So now I have to dig deeper …

dnsmichi · September 12, 2019, 9:26am

Then I’d go deep into the PHP implementation parts for DB2, and use a heck of var_dump() and die() in there with triggering the connection and queries.

stevie-sy · September 13, 2019, 1:59pm

I tested it the last days with PHP-FPM 7.1 and 7.2. The PHP extensions from the Driver package don’t work. So I have to download and compile it from pecl. In our environment I had to compile the source for each PHP-FPM version. The modules are correctly loaded by PHP-FPM. The Shell commands “ldd” and “ldconfig” also phpinfo() shows the same. And my simply script can access DB2 and get data. So from this side everyhting works fine. So far so good…

But insert a SQL query in the form from the directory I get the error about the allocated memory. Since I enabled the log from PHP-FPM I know the exception came from “/usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Pdo/Abstract.php on line 293”. So the error comes from this function:

But why a function “quote” produce this error I don’t know. And I also don’t know which function calls the quote-function. That’s why I have no idea where to add the suggested php functions var_dump() and die().
Maybe there is something wrong with the inclued Zend framework?
I tired some options in the php.ini like “memory_limit” or some from the php extensions “ibm_db2”. But it doesn’t help.

For me it looks like there is a incompatibility between the zend framework, db2-driver and/or php-modules/php-fpm versions.
For now my php skills are not very good. The last time i did something with this language is about 15 years ago (PHP 4). So I have to refresh it befor I can do more here. Especially I have to understand what everything is doing in the php file from the zend Framework, icingaweb2 and the director.

Another solution could be the PHP extension PDO_ODBC. In combination with a installed unixODBC-package for Linux, we would be more flexible. Because every database driver would be configured there and icingaweb2 would send the request to ODBC. With this, everybody would be more flexible and nobody have to add more (exotic) db options to the icingaweb2 ressource form.
If I find time to refresh my php skills and I understand the source code from icingaweb2 and director I could try to add options for ODBC. Because I found the commit where the db2-options is added to icingaweb2 and I would try the same.

If somebody else has experience with DB2 and icingaweb2/director it would be nice to share. Until I know how everything works we will still use my perl scripts, where everything works fine. I also will test this if IBM will release new drivers and/or new PHP extensions.

twidhalm · March 9, 2020, 1:18pm

Hi all.

@mfrosch just pointed me to this PR from @kobmaki : https://github.com/Icinga/icingaweb2/pull/2931

Shouldn’t this help with your problem @stevie-sy ? I know, the PR is older than your tries but maybe it’s just a small thing that needs to be changed so you can get it to work?

stevie-sy · March 9, 2020, 1:40pm

Hi @twidhalm,
this PR was the base for my tries. But in our environment this doesn’t work. But every hint is welcome

kobmaki · March 9, 2020, 9:54pm

You find a simple test program for the quotes

https://www.ibm.com/support/pages/node/292497

It is very very old, but should still work.
What shows you the output?
Can you run the simple test? Does it works or do you get an error when calling the db2_escape_string function?

stevie-sy · March 10, 2020, 12:50pm

Hi @kobmaki ,

thanks for the answer.
With our perl and a simple php script everything is working as expected. Only with using the forked Zend framework we have problems. If I put the access data into the form, connections test works. But the SQL statement don’t work. We get the message from above “allowed memory size … bytes exausted” (see the screenshots in my first post).

I tested the old php script from the ibm site, it works. also. The connection string is this:

$conn = new PDO(“ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$ibm_database;HOSTNAME=$ibm_host;PORT=$ibm_port;PROTOCOL=TCPIP;UID=$ibm_user;PWD=$ibm_password”,"","");
Because we are using the “smaller” driver package. That means the odbc_cli version.
With

I did also a simple test script for quering data via PHP. Works also fine.