Problem with monitoring DB2 with dbforbix

[zaicnupagadi]

Hi all,

We are struggling to force working our dbforbix with DB2 database (TSM 6)

We are receiving a strange error in dbforbix.log:

2012-12-05 00:53:37,767 [main] ERROR DBforBIX - Error on Configurator for database DB2DB5 -->DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: END-OF-STATEMENT;SELECT SYSTEM_USER;<table_expr>

It seemes that dbforbix has the initiate the first contact with the database. As we have searched throught the Internet that kidn of error is for - as arror says itself - wrongly terminated line (at least the first SQLERRMC says that), all we try to ask for tests is:

QueryList=test
test.Query=SELECT buff_hit_ratio FROM TSMDB1.DB

I have read that the problem may occur when there are some ")" in query, but ours is as easy as pie, it should pass with no problem.

I would like to add that Oracle database is monitored properly - dbforbix is connecting to it easily.

[handerson]

error

Hi,
we´re testing DBForbix to monitor a DB2 Database, but in dbforbix.log we are receiving this error on log:

2013-08-16 09:56:12,477 [main] ERROR DBforBIX - Error on Configurator for database server197 -->DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=END-OF-STATEMENT;SELECT SYSTEM_USER;<table_expr>, DRIVER=3.53.95


This is the code of db2Query.props:
#DefaultQueryPeriod=2
QueryList=tbsutilization
#statistic of database
tbsutilization.Query=select substr(tbsp_name,1,10) "Name", tbsp_utilization_percent "Used%", tbsp_free_pages "FreePages", tbsp_usable_Pages "UsablePages", tbsp_used_pages "UsedPages", tbsp_page_top "HWM", tbsp_auto_resize_Enabled "AutoResize 1=Yes", tbsp_increase_size "Increase Size", tbsp_max_size "MaxSizeBytes -1=ulimited", tbsp_last_resize_failed "ResizeFailed 1=Yes", tbsp_page_size "PgSize Bytes" \
from SYSIBMADM.TBSP_UTILIZATION \
where tbsp_type = 'DMS'
tbsutilization.NoDataFound=non

Could you help us?

Thanks in advance!

[johansen]

was anybody able to solve this? Have exactly the same problem:

error:

 2014-02-28 16:12:28,379 [main] INFO  DBforBIX - Starting DBforBIX Version 0.6.1
 2014-02-28 16:12:28,389 [main] INFO  DBforBIX - DBforBIX started with pid:18868
 2014-02-28 16:12:28,390 [main] INFO  DBforBIX - PidFile -> ./logs/dbforbix.pid
 2014-02-28 16:12:28,936 [main] ERROR DBforBIX - Error on Configurator for database DB2DB1 -->DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=END-OF-STATEMENT;SELECT SYSTEM_USER;<table_expr>, DRIVER=3.63.75
 2014-02-28 16:12:28,936 [main] INFO  DBforBIX - This Database DB2DB1 removed
 2014-02-28 16:12:28,937 [main] ERROR DBforBIX - ERROR on main - Connections is empty

config:

###DB2
#define here your connection string for each database
#on the following example we are treating DB2
DB2DB1.Url=jdbc:db2://hostname.domain:50000/databasename
DB2DB1.User=zabbix
DB2DB1.Password=passwordofzabbixuser
DB2DB1.DatabaseType=db2
DB2DB1.QueryListFile=./conf/db2query.props

[s4chin]

Db4bix

Hi Frnds,

I have zabbix server on 192.168.0.162 (CentOS 6.5). Zabbix version is 2.2.1.
My Oracle server (10.2.0.5) is on 192.168.0.244. So i installed orabbix 1.2.3 on oracle server in /opt directory and i think it is working ok. I imported oracle template "Orabbix_export_full.xml" from orabbix template folder in zabbix server and added host also...but there is no data on zabbix server related to oracle template

My files config are :
1 ) config.props

ZabbixServerList=ZabbixServer1
ZabbixServer1.Address=192.168.0.162
ZabbixServer1.Port=10051
OrabbixDaemon.PidFile=./logs/orabbix.pid
OrabbixDaemon.Sleep=300
OrabbixDaemon.MaxThreadNumber=2
DatabaseList=DB1
DatabaseList.MaxActive=10
DatabaseList.MaxWait=100
DatabaseList.MaxIdle=1

DB1.Url=jdbcracle:thin:@192.168.0.244:1521rclt est10g
DB1.User=zabbix
DB1.Password=zabbix

DB1.MaxActive=10
DB1.MaxWait=100
DB1.MaxIdle=1
DB1.QueryListFile=./conf/query.props


2) orabbix.log

2014-03-08 10:23:57,935 [main] INFO Orabbix - Starting Orabbix Version 1.2.3
2014-03-08 10:23:57,989 [main] INFO Orabbix - Orabbix started with pid:5099
2014-03-08 10:23:57,989 [main] INFO Orabbix - PidFile -> ./logs/orabbix.pid
2014-03-08 10:23:58,434 [main] INFO Orabbix - DB Pool created: org.apache.commons.dbcp.datasources.SharedPoolData Source@7a3f437c
2014-03-08 10:23:58,434 [main] INFO Orabbix - URL=jdbcracle:thin:@192.168.0.244:1521rcltest1 0g
2014-03-08 10:23:58,434 [main] INFO Orabbix - maxPoolSize=10
2014-03-08 10:23:58,435 [main] INFO Orabbix - maxIdleSize=1
2014-03-08 10:23:58,435 [main] INFO Orabbix - maxIdleTime=1800000ms
2014-03-08 10:23:58,435 [main] INFO Orabbix - poolTimeout=100
2014-03-08 10:23:58,435 [main] INFO Orabbix - timeBetweenEvictionRunsMillis=-1
2014-03-08 10:23:58,435 [main] INFO Orabbix - numTestsPerEvictionRun=3
2014-03-08 10:24:02,369 [main] INFO Orabbix - Connected as ZABBIX
2014-03-08 10:24:02,376 [main] INFO Orabbix - --------- on Database -> orcltest
2014-03-08 10:24:04,576 [pool-1-thread-1] INFO Orabbix - Done with dbJob on database DB1 QueryList elapsed time 1988 ms
2014-03-08 10:25:02,665 [pool-1-thread-2] INFO Orabbix - Done with dbJob on database DB1 QueryList elapsed time 31 ms

Why there is no data on zabbix server

[kunajn]

was anybody able to solve this? Have exactly the same problem:

error:

 2014-02-28 16:12:28,936 [main] ERROR DBforBIX - Error on Configurator for database DB2DB1 -->DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=END-OF-STATEMENT;SELECT SYSTEM_USER;<table_expr>, DRIVER=3.63.75

Hi,

I have the same problem. And it seems to be problem with the source code, in the /com/smartmarmot/common/Constants.java:

public static final String DB2_VALIDATION_QUERY = "SELECT 1";
public static final String DB2_WHOAMI_QUERY = "SELECT SYSTEM_USER";
public static final String DB2_DBNAME_QUERY = "SELECT @@SERVERNAME";

These queries won't work with DB2. It should be something like this:

public static final String DB2_VALIDATION_QUERY = "SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1";
public static final String DB2_WHOAMI_QUERY = "SELECT CURRENT USER FROM SYSIBM.SYSDUMMY1";
public static final String DB2_DBNAME_QUERY = "SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY1";

BTW - I wish Andrea had more time and willingness to develop this great tool...

[kunajn]

Hi,

I have the same problem. And it seems to be problem with the source code, in the /com/smartmarmot/common/Constants.java:

public static final String DB2_VALIDATION_QUERY = "SELECT 1";
public static final String DB2_WHOAMI_QUERY = "SELECT SYSTEM_USER";
public static final String DB2_DBNAME_QUERY = "SELECT @@SERVERNAME";

These queries won't work with DB2. It should be something like this:

public static final String DB2_VALIDATION_QUERY = "SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1";
public static final String DB2_WHOAMI_QUERY = "SELECT CURRENT USER FROM SYSIBM.SYSDUMMY1";
public static final String DB2_DBNAME_QUERY = "SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY1";

BTW - I wish Andrea had more time and willingness to develop this great tool...

FYI - I changed these queries, recompiled dbforbix and it's working with DB2 now...

 2014-07-21 16:53:37,626 [main] INFO  DBforBIX - DBforBIX started with pid:32067
 2014-07-21 16:53:37,626 [main] INFO  DBforBIX - PidFile -> ./logs/orabbix.pid
 2014-07-21 16:53:37,751 [main] ERROR DBforBIX - Error on Configurator for database DB2DB5 -->DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: END-OF-STATEMENT;SELECT SYSTEM_USER;<table_expr>
 2014-07-21 16:53:37,751 [main] INFO  DBforBIX - This Database DB2DB5 removed
 2014-07-21 16:53:37,751 [main] ERROR DBforBIX - ERROR on main - Connections is empty
 2014-07-21 16:56:07,451 [main] INFO  DBforBIX - Starting DBforBIX Version 0.6.1
 2014-07-21 16:56:07,457 [main] INFO  DBforBIX - DBforBIX started with pid:15045
 2014-07-21 16:56:07,457 [main] INFO  DBforBIX - PidFile -> ./logs/orabbix.pid
 2014-07-21 16:56:07,618 [main] INFO  DBforBIX - Connected as ZABBIX
 2014-07-21 16:56:07,619 [main] INFO  DBforBIX - --------- on Database -> SAMPLE
 2014-07-21 16:56:07,721 [pool-1-thread-1] INFO  DBforBIX - Done with dbJob on database DB2DB5 elapsed time 48 ms
 2014-07-21 16:57:07,676 [pool-1-thread-2] INFO  DBforBIX - Done with dbJob on database DB2DB5 elapsed time 2 ms
 2014-07-21 16:58:07,679 [pool-1-thread-3] INFO  DBforBIX - Done with dbJob on database DB2DB5 elapsed time 4 ms

[rajans]

Trying to setup DB2 monitor

Hi,
I am trying dbforbix for db2, could you please share the code. Thanks.

[rajans]

db2bix error, please help

Prop Conf:
SAMPLE.Url=jdbc:db2://192.168.4.40:50001/SAMPLE
SAMPLE.User=zabuser
SAMPLE.Password=ZABBIX
SAMPLE.MaxActive=10
SAMPLE.MaxWait=100
SAMPLE.MaxIdle=1
SAMPLE.DatabaseType=db2
QueryListFile=/opt/db2bix/conf/query.props
DB2bixDaemon.PidFile=./logs/db2bix.pid
DB2bixDaemon.MaxThreadNumber=100


LOG:
2015-07-28 23:33:48,410 [main] INFO DB2bix - Starting DB2bix Version 1.2.0 RC1
2015-07-28 23:33:48,442 [main] INFO DB2bix - DB2bix started with pid:3803
2015-07-28 23:33:48,442 [main] INFO DB2bix - PidFile -> ./logs/db2bix.pid
2015-07-28 23:33:48,443 [main] DEBUG DB2bix - getConnection for database SAMPLE
2015-07-28 23:33:49,518 [main] ERROR DB2bix - Error on Configurator for database SAMPLE -->Could not retrieve connection info from pool
2015-07-28 23:33:49,518 [main] INFO DB2bix - This Database SAMPLE removed
2015-07-28 23:33:49,518 [main] ERROR DB2bix - ERROR on main - Connections is empty or null
2015-07-28 23:33:49,535 [main] INFO DB2bix - New database founded! SAMPLE
2015-07-28 23:33:49,535 [main] DEBUG DB2bix - getConnection for database SAMPLE
2015-07-28 23:33:50,111 [main] ERROR DB2bix - Error on Configurator for database SAMPLE -->Could not retrieve connection info from pool