topic DB Connect Batch Mode in All Apps and Add-ons

DB Connect Batch Mode

jugalkinariwala — Tue, 07 Jul 2020 08:12:22 GMT

Hi Splunkers,

I have enabled the batch mode for a date field with below query in DB Connect :

SELECT *

FROM SCHEMANAME.TABLENAME

WHERE Termination_date >= from_unixtime(unix_timestamp()-1*60*60*24, 'yyyy-MM-dd')

ORDER BY Termination_date DESC;

The Table doesnt have any primary key and hence making using of batch mode in db connect to retrieve all the data from the table when comparing with one of the date fileds in the table "Termination_date".

The table generates 5000 rows in a day. Hence I have given a condition to schedule the script every 300 seconds and retrieve 300 rows .

My Question::

Will it retrieve last 300 rows of the day or it will keep on ingesting first 300 rows from the table into splunk (I have given DESC in the sql query).

Is there any other solution to get the data by using the same date field as there is no primary key.

Thanks in advance.

Re: DB Connect Batch Mode

richgalloway — Tue, 07 Jul 2020 13:44:21 GMT

DB Connect batch mode will re-read the same data every time.
To retrieve the most recent rows, use a rising column. The table does not need a primary key - it just needs a column that has a contuously-increasing value that Splunk can use to mark its place in the table.

Re: DB Connect Batch Mode

jugalkinariwala — Sun, 12 Jul 2020 16:07:23 GMT

Rising column is having some issues

If table generates 1000 rows every 10 mins and as I am using Batch mode , shall I go with ASC or DESC to get the recently generated rows from the table ?

Re: DB Connect Batch Mode

richgalloway — Sun, 12 Jul 2020 17:15:20 GMT

Don't use batch mode to get recent rows because batch mode reads the entire table.
If the values of the rising column increase continually (as most do) then you want ASC.