DB2, PHP and Zabbix...

[kpfitzgerald]

So in an effort to mature the db2 backend support of Zabbix, i rollled out a page on implementation of Zabbix on DB2... one problem after about 30 hours of configuring PHP it still is not working... my only assumption is that the versions i am using is unsupported (together). I would be more than happy to wrap this up, since i am incredulously frustrated. MY whole reasoning for looking to zabbix is the db2 backend support. here is my best version of the documentation. please feel free to ask what i have done, ask me for logs whatever you need to help diagnose this... i will be posting more details in later posts to this thread.

my install wikipage... http://zabbix.org/wiki/DB2_Installation_Cookbook

some webpages i have used in trying to get db2/zabbix to work...

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv.php.doc/doc/t0011926.html

http://www.ibm.com/developerworks/data/library/techarticle/dm-0505furlong/

http://php.net/manual/en/ref.pdo-ibm.php

[kpfitzgerald]

this is my stuck point

here is a screenshot of where i am stuck!


zbx_inst_stuck by kpfitzgerald, on Flickr

[kpfitzgerald]

php and db2 configuration information

./configure --prefix=/usr/local/apache2/php --with-config-file-path=/usr/local/apache2/php --with-apxs2=/usr/local/apache2/bin/apxs --with-libxml-dir=/usr/share/doc/packages/libxml2 --with-ibm-db2=/home/db2inst1/sqllib --without-mysql --enable-bcmath --with-curl --with-gd --enable-sockets --enable-mbstring --with-zlib --with-gettext --with-gdbm

nalvsvzabbix01t:/usr/local/src/php-5.3.6 # pecl list
Installed packages, channel pecl.php.net:
=========================================
Package Version State
ibm_db2 1.9.1 stable


nalvsvzabbix01t:/usr/local/src/php-5.3.6/ext/ibm_db2-1.9.1 # ./configure --with-IBM_DB2=/home/db2inst1/sqllib

last 4 lines of config output:
creating libtool
appending configuration tag "CXX" to libtool
configure: creating ./config.status
config.status: creating config.h
config.status: config.h is unchanged

make output last 20 or so lines:
----------------------------------------------------------------------
Libraries have been installed in:
/usr/local/src/php-5.3.6/ext/ibm_db2-1.9.1/modules

If you ever happen to want to link against installed libraries
in a given directory, LIBDIR, you must either use libtool, and
specify the full pathname of the library, or use the `-LLIBDIR'
flag during linking and do at least one of the following:
- add LIBDIR to the `LD_LIBRARY_PATH' environment variable
during execution
- add LIBDIR to the `LD_RUN_PATH' environment variable
during linking
- use the `-Wl,--rpath -Wl,LIBDIR' linker flag
- have your system administrator add LIBDIR to `/etc/ld.so.conf'

See any operating system documentation about shared libraries for
more information, such as the ld(1) and ld.so(8) manual pages.
----------------------------------------------------------------------

Build complete.
Don't forget to run 'make test'.


nalvsvzabbix01t:/usr/local/src/php-5.3.6/ext/ibm_db2-1.9.1 # make install
Installing shared extensions: /usr/local/lib/php/extensions/no-debug-non-zts-20090626/









nalvsvzabbix01t:/usr/local/src/php-5.3.6 # ./config.status
creating php5.spec
creating main/build-defs.h
creating scripts/phpize
creating scripts/man1/phpize.1
creating scripts/php-config
creating scripts/man1/php-config.1
creating sapi/cli/php.1
creating main/php_config.h
main/php_config.h is unchanged
creating main/internal_functions.c
creating main/internal_functions_cli.c
+--------------------------------------------------------------------+
| License: |
| This software is subject to the PHP License, available in this |
| distribution in the file LICENSE. By continuing this installation |
| process, you are bound by the terms of this license agreement. |
| If you do not agree with the terms of this license, you must abort |
| the installation process at this point. |
+--------------------------------------------------------------------+

Thank you for using PHP.

Notice: Following unknown configure options were used:

--with-pdo-ibm=/home/db2inst1/sqllib

Check './configure --help' for available options

[kpfitzgerald]

FIXED - on SUSE 11SP1 DB2WSE 9.7SP3a

finally got zabbix to recognize the db2 installation on PHP. took me about 12 hours to figure this out. ill update the cookbook.. however... i am getting the following after the installation...



db2_execute(): Statement Execute Failed[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:473]
SQL error [SELECT * FROM nodes WHERE nodetype=1 ORDER BY nodeid] in [[IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "ZABBIX.NODES" is an undefined name. SQLSTATE=42704]
db2_fetch_assoc() expects parameter 1 to be resource, boolean given[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:634]
db2_free_result() expects parameter 1 to be resource, boolean given[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:636]
db2_execute(): Statement Execute Failed[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:473]
SQL error [SELECT u.* FROM users u WHERE u.alias='guest' AND ((u.userid BETWEEN 000000000000000 AND 099999999999999))] in [[IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "ZABBIX.USERS" is an undefined name. SQLSTATE=42704]
db2_fetch_assoc() expects parameter 1 to be resource, boolean given[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:634]
db2_free_result() expects parameter 1 to be resource, boolean given[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:636]
db2_execute(): Statement Execute Failed[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:473]
SQL error [SELECT s.* FROM sessions s WHERE s.sessionid='' AND s.status=0 AND ((s.userid BETWEEN 000000000000000 AND 099999999999999))] in [[IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "ZABBIX.SESSIONS" is an undefined name. SQLSTATE=42704]
db2_fetch_assoc() expects parameter 1 to be resource, boolean given[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:634]
db2_free_result() expects parameter 1 to be resource, boolean given[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:636]
db2_execute(): Statement Execute Failed[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:473]
SQL error [SELECT count(u.userid) as user_cnt FROM users u] in [[IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "ZABBIX.USERS" is an undefined name. SQLSTATE=42704]
db2_fetch_assoc() expects parameter 1 to be resource, boolean given[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:634]
db2_free_result() expects parameter 1 to be resource, boolean given[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:636]
db2_execute(): Statement Execute Failed[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:473]
SQL error [SELECT * FROM config WHERE ((configid BETWEEN 000000000000000 AND 099999999999999))] in [[IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "ZABBIX.CONFIG" is an undefined name. SQLSTATE=42704]
db2_fetch_assoc() expects parameter 1 to be resource, boolean given[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:634]
db2_free_result() expects parameter 1 to be resource, boolean given[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:636]
db2_execute(): Statement Execute Failed[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:473]
SQL error [SELECT * FROM profiles WHERE userid=0 AND ((profileid BETWEEN 000000000000000 AND 099999999999999)) ORDER BY userid ASC, profileid ASC] in [[IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "ZABBIX.PROFILES" is an undefined name. SQLSTATE=42704]
db2_fetch_assoc() expects parameter 1 to be resource, boolean given[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:634]
db2_free_result() expects parameter 1 to be resource, boolean given[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:636]


db2_execute(): Statement Execute Failed[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:473]
SQL error [SELECT nextid FROM ids WHERE nodeid=0 AND table_name='profiles' AND field_name='profileid'] in [[IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "ZABBIX.IDS" is an undefined name. SQLSTATE=42704]
db2_execute(): Statement Execute Failed[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:566]
SQL error [INSERT INTO profiles (profileid, userid, idx, value_str, type, idx2) VALUES (, 0, 'web.menu.login.last', 'index.php', 3, 0)] in [[IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "ZABBIX.PROFILES" is an undefined name. SQLSTATE=42704]
db2_execute(): Statement Execute Failed[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:473]
SQL error [SELECT nextid FROM ids WHERE nodeid=0 AND table_name='profiles' AND field_name='profileid'] in [[IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "ZABBIX.IDS" is an undefined name. SQLSTATE=42704]
db2_execute(): Statement Execute Failed[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:566]
SQL error [INSERT INTO profiles (profileid, userid, idx, value_str, type, idx2) VALUES (, 0, 'web.paging.lastpage', 'index.php', 3, 0)] in [[IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "ZABBIX.PROFILES" is an undefined name. SQLSTATE=42704]


some of it looks to be permissions issues so i will track that down...

[nelsonab]

Do you have a subversion repository or other source management tool for tracking your changes?

[kpfitzgerald]

FIXED - on SUSE 11SP1 DB2WSE 9.7SP3a

Do you have a subversion repository or other source management tool for tracking your changes?

I do have SVN, but have not been using it for this project... i have been tracking and documenting via my wiki.

I know what most of these errors are, the schema owner is the instance owner... i am not sure how or why that occured, sicne the batch was run as db2inst1 via user zabbix.

i am creating aliases for the tables and other objects, to verify that this is a schema ownership issue. this may be a simple fix...I will most likely rewrite the batch job for db2 to use the zabbix schema and not the instance owner. At this time i am just trying to get this working after fighting with it for several days.

[kpfitzgerald]

FIXED - on SUSE 11SP1 DB2WSE 9.7SP3a

OK fixed the issue... was a kind of a pebkac... anyways... new errors and i believe these to be code incompatibilities.

b2_execute(): Statement Execute Failed[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:473]
SQL error [SELECT * FROM (SELECT g.usrgrpid FROM usrgrp g, users_groups ug WHERE ug.userid = 2 AND g.usrgrpid = ug.usrgrpid AND g.users_status = 1) WHERE rownum BETWEEN 0 AND 1] in [[IBM][CLI Driver][DB2/LINUXX8664] SQL0206N "ROWNUM" is not valid in the context where it is used. SQLSTATE=42703]
db2_fetch_assoc() expects parameter 1 to be resource, boolean given[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:634]
db2_free_result() expects parameter 1 to be resource, boolean given[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:636]
db2_execute(): Statement Execute Failed[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:473]
SQL error [SELECT * FROM (SELECT g.usrgrpid FROM usrgrp g, users_groups ug WHERE ug.userid = 2 AND g.usrgrpid = ug.usrgrpid AND g.debug_mode = 1) WHERE rownum BETWEEN 0 AND 1] in [[IBM][CLI Driver][DB2/LINUXX8664] SQL0206N "ROWNUM" is not valid in the context where it is used. SQLSTATE=42703]
db2_fetch_assoc() expects parameter 1 to be resource, boolean given[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:634]
db2_free_result() expects parameter 1 to be resource, boolean given[/srv/www/htdocs/zabbix/frontends/php/include/db.inc.php:636]

[kpfitzgerald]

FIXED - on SUSE 11SP1 DB2WSE 9.7SP3a

ok figured this one out too... rownum is a oracle function...

db2set DB2_COMPATIBILITY_VECTOR=ORA or
db2set DB2_COMPATIBILITY_VECTOR=3 pending version you are using. HOWEVER a db2stop and db2start is required for results to take effect.

Fitz

[kpfitzgerald]

Everythings working now!! Server Side

I removed everything and reinstalled just so i know how to do it. Works Great!!!

just needed the right formula and a few small adjustments to the zabbix docs... ill install a few agents, see how that goes...

I am off to NA-IDUG this weekend and wont be back for a week, so response will be delayed.

[dalle]

I'm following you
let us know when there are news!