Truncate Table


I need to clean a table with 15 million records and repopulate it once a month or so.

My problem is with the cleaning part. I was considering the use of the "Truncate Table" statement, but the hubruntime user doesn't have sufficient permissions to execute it.

Has anyone ever needed to do something like this? What do you recommend/how did you do it at the time?

Best regards,

Paulo Tavares
Hi Paulo,

Here is an example on how you can manage a cleaning process with a timer.

Assuming that you want to clean a LOG table with a structure similar to [ID | Instant | UserID | Operation | Details] and you want to remove all records with more that 30 days, you can create a timer that will delete records by parts.

The timer is scheduled to run everyday at 2am. It will run until there are no more old records to delete. We are deleting 100 records at a time to avoid SQL Timeout locking the table for too long.
if the process takes more than 10 minutes, we automatically wake the timer again in order to avoid reaching the Timeout (of 20 minutes) set in the timer.

You could add more controls to make sure that the timer will only run at night to avoid problems when your application has more users accessing it.

Hope this helps.

Best regards,
PS - I think I've forgot to put the minus (-) when setting the OldDate variable, but you get the idea.

Thanks for the reply, that is actually a nice approach - it would not stall the system during work hours. The problem was that my records were not necessarily deleted after a determined period of time. The users needed to import 15 million records from a file, and whenever they were going to import them, the old data was to be replaced by the new one.

Since I was short on time, I had to manage to do what I wanted earlier on with an extension. The extension created a new SQL connection, with integrated authentication, and executed the truncate statement flawlessly. It is pretty instantaneous, and the only other thing I had to do was to execute the calling eSpace with a "Run As..." login with permissions over the table to be truncated.

Thanks for the tip, nonetheless. I have a task in hands which might get me to follow a similar approach as the one you described!

Best regards,

Paulo Tavares
I'm having a similar issue right now - I need to truncate / reload a table nightly, and the "DELETE FROM {TABLE}" that was initially implemented now times out due to size of the table. Truncate is the right solution to this - is an extension still the only way how to implement this?

Is there an example out there somewhere, so that I don't have to re-invent it? :)

The fastest way is to give permissions to hubruntime and use the TRUNCATE statement, rather then try to create an alternate method of doing it.

I really never understood the reason why hubruntime doesn't have permission to execute DDL statements by default. Are you concerned with SQL injection or some kind of nefarious attack?

Best regards,
Hi Stefan,

I would prefer to have that truncate running on my database maitenance plan instead of giving that type of permissions to my hubruntime user.

Thanks for the suggestions, Ricardo & Pedro!

Both solutions require additional external configurations that I'll need to maintain. If I need to bite that bullet I'd probably go with Ricardo's suggestion and not mess with privileges ...

But if possible I'd rather work within the means of the platform. I know that in an extension I can get a database connection - is it possible to get a privileged (HUBADMIN) connection this way?

Is it possible in 9 to truncate tables the easy way?

As long as you don't have FKs you can use an advanced query to truncate the table.
You actually can't because like Paulo said runtime user is not supposed to have enough permissions in the database to do that. So, with the correct database permissions it is not possible.

As of Platform 9 the previous alternatives still stand.

I will just add another option: if you have admin access to your database you can create a storedprocedure that runs with different privileges and grant the platform runtime user permissions to run the stored procedure.

João Rosado
meh, don't like that 1 bit

but thanks