Results 1 to 8 of 8

Thread: Truncate Table

  1. #1
    Join Date
    Oct 2004
    Posts
    268

    Unanswered: Truncate Table

    I am using the below statement to truncate tables in Linux 6x, DB2 V9.5 FP8 servers but this is a linux command. Is there a command that I can use for both Windows and Linux to truncate tables without changing them ?. don't want to keep 2 version of the script.

    Thanks.

    IMPORT FROM /dev/null OF DEL REPLACE INTO schema1.EMPLOYEES;
    COMMIT WORK;

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    oops. wrong link.
    Last edited by db2mor; 08-02-12 at 18:10. Reason: removed irrelevant information

  3. #3
    Join Date
    Oct 2004
    Posts
    268
    Quote Originally Posted by db2mor View Post
    I am sorry I guess I did not make it clear. I am using this to delete all rows from the table.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The link above is for Truncate scalar function. I think he meant to link to the Truncate Table command, but not sure if is was available in 9.5 (definitely in 9.7).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    TRUNCATE TABLE is not available in V9.5.

    You could do something like:

    Code:
    ALTER TABLE schema1.EMPLOYEES ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE
    Be careful with this though, if it fails, your table is toast.

    Andy

  6. #6
    Join Date
    Oct 2004
    Posts
    268
    Quote Originally Posted by ARWinner View Post
    TRUNCATE TABLE is not available in V9.5.

    Be careful with this though, if it fails, your table is toast.

    Andy

    Can you Elaborate ?. What Would be the results if it fails ?

    Thank you.

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Straight from the manual:

    ACTIVATE NOT LOGGED INITIALLY
    Activates the NOT LOGGED INITIALLY attribute of the table for this current unit of work.

    Any changes made to the table by an INSERT, DELETE, UPDATE, CREATE INDEX, DROP INDEX, or ALTER TABLE in the same unit of work after the table is altered by this statement are not logged. Any changes made to the system catalog by the ALTER statement in which the NOT LOGGED INITIALLY attribute is activated are logged. Any subsequent changes made in the same unit of work to the system catalog information are logged.

    At the completion of the current unit of work, the NOT LOGGED INITIALLY attribute is deactivated and all operations that are done on the table in subsequent units of work are logged.

    If using this feature to avoid locks on the catalog tables while inserting data, it is important that only this clause be specified on the ALTER TABLE statement. Use of any other clause in the ALTER TABLE statement will result in catalog locks. If no other clauses are specified for the ALTER TABLE statement, then only a SHARE lock will be acquired on the system catalog tables. This can greatly reduce the possibility of concurrency conflicts for the duration of time between when this statement is executed and when the unit of work in which it was executed is ended.

    If the table is a typed table, this option is only supported on the root table of the typed table hierarchy (SQLSTATE 428DR).

    For more information about the NOT LOGGED INITIALLY attribute, see the description of this attribute in "CREATE TABLE".
    Note: If non-logged activity occurs against a table that has the NOT LOGGED INITIALLY attribute activated, and if a statement fails (causing a rollback), or a ROLLBACK TO SAVEPOINT is executed, the entire unit of work is rolled back (SQL1476N). Furthermore, the table for which the NOT LOGGED INITIALLY attribute was activated is marked inaccessible after the rollback has occurred and can only be dropped. Therefore, the opportunity for errors within the unit of work in which the NOT LOGGED INITIALLY attribute is activated should be minimized.

    WITH EMPTY TABLE
    Causes all data currently in table to be removed. Once the data has been removed, it cannot be recovered except through use of the RESTORE facility. If the unit of work in which this alter statement was issued is rolled back, the table data will not be returned to its original state.

    When this action is requested, no DELETE triggers defined on the affected table are fired. The index data is also deleted for all indexes that exist on the table.

    A partitioned table with attached data partitions cannot be emptied (SQLSTATE 42928).

  8. #8
    Join Date
    Oct 2004
    Posts
    268
    Thanks for the info.......Below is all we care about. This is what we do to initialize the database to make it ready for production. It is only a couple of tables we run this on.

    "At the completion of the current unit of work, the NOT LOGGED INITIALLY attribute is deactivated and all operations that are done on the table in subsequent units of work are logged."

Posting Permissions

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