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.

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

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?

Run the image copy (backup) utility on the tablespace.

Can you tell me how to run the image copy, Does it require any DBA authority?

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

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.

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.