Truncate table with DB connect 2

[jamesmarlowww]

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! 🙂

[DalJeanis · Accepted solution]

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

[koshyk]

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.

[jamesmarlowww]

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.