Results 1 to 9 of 9
  1. #1
    Join Date
    May 2006
    Posts
    20

    Unanswered: Delete All Rows From a table

    Hi all,

    Can anyone give me a query to delete all the rows from a table?

    Or just in simple I want to Empty the Table.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    delete from table-name

    Since there is no where clause, then all rows are affected.

    If there are a large number of rows, your transaction log may fill up, since all the deletes are in a single unit of work. There are ways around this such as:

    1. Alter table to not logged initially (search this forum for details).

    2. Load table from /dev/null file with replace option.

    3. Increase log file size, and number of primary and secondary logs
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2006
    Posts
    20
    Hi,

    I am working in Mainframe and when I try to delete all the rows first I got -904 and now its went to COPY PENDING

    Can any one help me what to do if it went to copy pending state?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Run the image copy (backup) utility on the tablespace.
    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
    May 2006
    Posts
    20
    Can you tell me how to run the image copy, Does it require any DBA authority?

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    For details, see the COPY utility in the Utility Reference Guide.

    Authorization required for COPY utility: To execute this utility, the privilege set of the process must include one of the following:

    - IMAGCOPY privilege for the database
    - DBADM, DBCTRL, or DBMAINT authority for the database
    - SYSCTRL or SYSADM authority
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jul 2005
    Posts
    47
    Alter table Schemaname.Tablename activate not logged initially with empty table;
    OR
    Load from empty.del of del messages msg.out replace into Schemaname.Tablename nonrecoverable;
    OR
    Alter table Schemaname.Tablename activate not logged initially ;
    delete from Schemaname.Tablename ;

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by venky5436
    Alter table Schemaname.Tablename activate not logged initially with empty table;
    OR
    Load from empty.del of del messages msg.out replace into Schemaname.Tablename nonrecoverable;
    OR
    Alter table Schemaname.Tablename activate not logged initially ;
    delete from Schemaname.Tablename ;
    You missed the part where the OP said it is on the mainframe.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jul 2005
    Posts
    47
    I am sorry I really missed that part. Thanks for the correction Marcus.Yes it is for DB2 on LUW I do not know about mainframes.

Posting Permissions

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