Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005
    Posts
    8

    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.

    tia, Blaise

    version info follows:
    OS = Windows Server 2003
    ================================================== ========
    About DB2 Administration Tools Environment
    ================================================== ========
    DB2 administration tools level:
    Product identifier SQL08020
    Level identifier 03010106
    Level DB2 v8.1.7.445
    Build level s040812
    PTF WR21342
    ================================================== ========
    Java development kit (JDK):
    Level IBM Corporation 1.4.1
    ================================================== ========

  2. #2
    Join Date
    Mar 2005
    Posts
    8

    another alternative

    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.

    Blaise

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Other options:
    - 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.

  4. #4
    Join Date
    Mar 2005
    Posts
    8

    thanks

    I like your second option, I will give that a try. thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •