All Apps and Add-ons

Splunk DB Connect - DB2 - DBMON TAIL

Sriram
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

btsay_splunk
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

btsay_splunk
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$ > ?}}

"""

btsay_splunk
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

Sriram
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

Sriram
Communicator

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

0 Karma

araitz
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...