Unanswered: Truncate table from a SQL Stored Procedure
I've seen the tip about using the LOAD command to truncate a table and this command works just fine for me from a Command Editor.
load from E:\DB2\emptyfile.txt of del replace into tbl_hours;
Now what I want to do is put that into a stored procedure (language=sql). In SQL Server there is a thing called xp_cmdshell that will let you send commands to the OS. I'm not even sure if it's something like that I need to have or is there just some way I can run this in my SP.
version info follows:
OS = Windows Server 2003
About DB2 Administration Tools Environment
DB2 administration tools level:
Product identifier SQL08020
Level identifier 03010106
Level DB2 v184.108.40.2065
Build level s040812
Java development kit (JDK):
Level IBM Corporation 1.4.1
If it's possible I could run my stored procedures and my LOAD commands from some sort of script. That way the LOAD statements wouldn't have to go into the SP.
Basically I have a bunch of source data that needs to be processed. I have source tables (ST_) interim tables (IT_) used during the processing and finally I have results tables (RT_). So all of the IT_ and RT_ tables need to be cleaned out each time I start up the process.
- use global temporary table; you don't have to clean it - the data will be deleted as soon as you disconnect;
- create your IT_* and RT_* tables with NOT LOGGED INITIALLY and activate that state before cleaning them; the operation won't be logged and therefore won't take as much time and resources.