Archive

Splunk DB Connect - DB2 - DBMON TAIL

Communicator

I am trying to setup a dbmon tail from a table running in DB2 ZOS and unsuccessful in getting it work. After enabling debug and looking at the dbx.log, it appears the query the application generates doesn't have schema name. Absence of schema will force the query to use the userid which makes the query. I either need to set the default schema (SET SCHEMA XXXXX and then fire the query) OR the query has to qualify the table name with the schema name. Incidentally looking at the dbquery command string, I see the query is passed with schema name and I could pull the data.

I tried to qualify the table name with schema when setting up Database Inputs (table name field) as DBCDB02.CLM_EVNT. Looking at the logs below, it looks the correct schema is parsed properly, however the query constructed by TailDatabaseMonitor doesn't have the schema.
2014-01-03 17:15:17.984 dbx562:DEBUG:Database - Split table identifier=DBCDB02.CLM_EVNT to schema=DBCDB02 table=CLM_EVNT
2014-01-03 17:15:17.984 dbx562:DEBUG:TailDatabaseMonitor - Constructed SQL query from table name: "SELECT * FROM CLM_EVNT WHERE EVNT_TS > ? ORDER BY EVNT_TS"

Here is the snippet from the dbx.log (U12345 is the userid which is defined to connect to the db)

2014-01-03 17:15:12.984 dbx9210:DEBUG:Scheduler - Scheduling initial execution for input=[dbmon-tail://DBCDB02/DBCDB02.CLM_EVNT]
2014-01-03 17:15:12.984 dbx9210:DEBUG:ExecutionSchedule - State transition from=PENDING to=WAITING
2014-01-03 17:15:12.984 dbx9210:DEBUG:ExecutionContext - Destroying execution context...
2014-01-03 17:15:12.984 dbx9210:INFO:ExecutionContext - Execution finished in duration=94 ms
2014-01-03 17:15:17.984 main:DEBUG:Scheduler - Timer for input=[dbmon-tail://DBCDB02/DBCDB02.CLM_EVNT] nextExecution=5000 state=WAITING expired. Executing it now...
2014-01-03 17:15:17.984 main:DEBUG:ExecutionSchedule - State transition from=WAITING to=QUEUED
2014-01-03 17:15:17.984 dbx562:DEBUG:ExecutionContext - Initializing execution context with app.ctx=dbx and execId=dbx562
2014-01-03 17:15:17.984 dbx562:DEBUG:TailDatabaseMonitor - Running database monitor in own thread.
2014-01-03 17:15:17.984 dbx562:DEBUG:ExecutionSchedule - State transition from=QUEUED to=RUNNING
2014-01-03 17:15:17.984 dbx562:INFO:TailDatabaseMonitor - Executing database monitor=[dbmon-tail://DBCDB02/DBCDB02.CLM_EVNT]
2014-01-03 17:15:17.984 dbx562:DEBUG:XStreamStore - Initialized XStreamStore at C:\Program Files\Splunk\var\lib\splunk\persistentstorage\dbx\dfe3933a37ec54ded02fb7bcf7226e39\state.xml
2014-01-03 17:15:17.984 dbx562:DEBUG:XStreamStore - Loading state from XML file...
**2014-01-03 17:15:17.984 dbx562:DEBUG:Database - Split table identifier=DBCDB02.CLM_EVNT to schema=DBCDB02 table=CLM_EVNT
2014-01-03 17:15:17.984 dbx562:DEBUG:TailDatabaseMonitor - Constructed SQL query from table name: "SELECT * FROM CLM_EVNT WHERE EVNT_TS > ? ORDER BY EVNT_TS"**
2014-01-03 17:15:17.984 dbx562:DEBUG:Database - Fetching connection from factory...
2014-01-03 17:15:17.984 dbx562:DEBUG:PooledConnectionFactory - Fetching connection from pool...
2014-01-03 17:15:17.984 dbx562:DEBUG:PooledConnectionFactory - Activating database connection com.ibm.db2.jcc.b.b@1aecc3a for database=DBCDB02
2014-01-03 17:15:17.984 dbx562:DEBUG:PooledConnectionFactory - Setting transaction isolation...
2014-01-03 17:15:17.984 dbx562:DEBUG:PooledConnectionFactory - Validating database connection for database=DBCDB02
2014-01-03 17:15:17.984 dbx562:DEBUG:Database - Executing database connection specific test query: SELECT CURRENT SQLID FROM SYSIBM.SYSDUMMY1
2014-01-03 17:15:18.046 dbx562:INFO:TailDatabaseMonitor - Applying latest tail.rising.column value=2013-12-18 06:11:30.996225
2014-01-03 17:15:18.093 dbx562:ERROR:TailDatabaseMonitor - Error while executing database monitor: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: U12345.CLM_EVNT
com.ibm.db2.jcc.c.SqlException: DB2 SQL error: **SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: U12345.CLM_EVNT**
    at com.ibm.db2.jcc.c.tf.e(tf.java:1680)
........
    at com.ibm.db2.jcc.c.uf.executeQuery(uf.java:491)
    at com.splunk.dbx.sql.Database.query(Database.java:257)
    at com.splunk.dbx.monitor.impl.TailDatabaseMonitor.performMonitoring(TailDatabaseMonitor.java:113)
0 Karma
1 Solution

Splunk Employee
Splunk Employee

A work-around is to "specify SQL" instead, that will give the correct SQL for tailing.

"""

You can specify the SQL query that is executed against the database yourself. For information on how to specify such a query, see Splunk DB Connect documentation. Example:
SELECT * FROM my_table {{WHERE $rising_column$ > ?}}

"""

View solution in original post

Splunk Employee
Splunk Employee

A work-around is to "specify SQL" instead, that will give the correct SQL for tailing.

"""

You can specify the SQL query that is executed against the database yourself. For information on how to specify such a query, see Splunk DB Connect documentation. Example:
SELECT * FROM my_table {{WHERE $rising_column$ > ?}}

"""

View solution in original post

Splunk Employee
Splunk Employee

to be precise, I tested this against MySQL:

select * from sakila.actor {{where DATE_FORMAT(last_update, '%d %m %Y') > '07 06 2013' and $rising_column$ > ?}}

The $rising_column$ is "keyword", DBConnect takes the field name from the "Rising Column" textfield to populate it.

I also provide a more precise way of doing timestamp comparison.

0 Karma

Communicator

Thanks. I tried that and was not successful. I am guessing it is because of the invalid timestamp format in the rising_column. will give it a shot.

Also, If I want to start pulling the data only after a certain date. Can I specify the SQL like this ?
SELECT * FROM CLM_EVNT {{WHERE EVNT_TS > "12-09-2013.00.00.00.0000" AND $EVNT_TS$ > ?}} ORDER BY EVNT_TS

Where EVNT_TS is the rising_column.

0 Karma

Communicator

Hello Araitz, I already created a support case on this. Thanks

0 Karma

Splunk Employee
Splunk Employee

Can you please open a support case? It would be helpful for us to get a diag in order to investigate further.

0 Karma