All Apps and Add-ons

Truncate table with DB connect 2

Path Finder

We are in the middle of migrating from DB connect 1 to DB connect 2.

I can't see an option to truncating or truncate/insert from the UI of dbconnect 2 (I don't want to upsert). I also haven't been able to find any examples or documentation online.

This is the existing search that we are trying to migrate.
| makeresults | dboutput type=sql database=EDW "TRUNCATE TABLE TABLE_NAME IMMEDIATE"

//Non working attempt to migrate. (Don't think you can insert/delete from the select command dbxquery)
| dbxquery query="TRUNCATE TABLE TABLE_NAME IMMEDIATE" connection=EDW wrap=t shortnames=t

//Another non working attempt. (Don't believe you can run queries from dbxoutput, you need to call the job made in the UI)
| makeresults | dbxoutput connection=EDW query="TRUNCATE TABLE TABLE_NAME IMMEDIATE"

Any help would be appreciated! 🙂

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Did you ever get a solution for this?

I believe you are going to have to create a stored procedure that executes the DDL, then returns a nominal result set like a return code.

A similar question was asked today, and we're pursuing an answer for you.

https://answers.splunk.com/answers/584473/how-can-i-run-truncate-table-statement-with-dbconn.html

View solution in original post

SplunkTrust
SplunkTrust

Did you ever get a solution for this?

I believe you are going to have to create a stored procedure that executes the DDL, then returns a nominal result set like a return code.

A similar question was asked today, and we're pursuing an answer for you.

https://answers.splunk.com/answers/584473/how-can-i-run-truncate-table-statement-with-dbconn.html

View solution in original post

Super Champion

I'm not sure if its a good idea to use dbconnect to do Data Definition Language (DDL) operations. Especially truncate being a dangerous action.

Path Finder

Truncating this table shouldn't be an issue. It is a table that I have setup and that only I use for development. So if I accidental truncated everything it wouldn't be the end of the world, I could always recreate the table.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!