Is there any option on the UNLOAD utitlity where I can specify to delete the rows from the table which I am unloading like a desctructive read in an MQ.
The delete statement which I am using to delete the rows is taking longer time because of the volume of data(3 million deletes), so I was looking for any option in the unload utility itself. Our process unloads the data from the table with the key and then feed to a module whcich reads the key field from the file and delete the data and load with the new set of data. We do a commit point every 500 records and the process takes around 3 to 4 hours to delete 3 million records. Trying to speed up the process.
TRUNCATE has a different function here in DB2, not like the same as in oracle,
TRUNCATE or TRUNC
The TRUNCATE function returns the first argument, truncated as specified. Truncation is to the number of places to the right or left of the decimal point this is specified by the second argument.
New facts emerging....better to follow full disclosure (supply ALL relevant facts ) when asking for help.
For a table with 100-million rows, consider using range-partitioning. Simply detach (and later drop) the unwanted partitions when data is no longer needed.
If the table is already range-partitioned, but the partitioning columns don't match the deletion criteria, then you are stuck with the DELETE statement.
I notice you failed to specify basic facts: your db2 version+fixpack, operating-system-platform etc, which also influence possible solutions.
Depending on the criticality of the data and ease of recovery of the table, 'not logged initially' is possible but not recommended for production.