Hello, I have a requirement to delete millions of rows from a table
which has billions of rows. Hence I have coded a korn script to delete
rows recursively and with a commit interval of 10000. However I feel
that the delete is taking some time to delete, hence would it be a good
idea to code a stored procedure to implement the same ?

If you have a similar exp can you please share your thoughts.

Here is the korn script which I'm using for reference.

COUNT=`db2 -x "select count(*) FROM TXN_PART WHERE source_system_cd=8
and dwh_efctv_timstm='2006-05-23-20.00.00.000000'  and transaction_id <
25000000 for read only with ur" `
COUNT=`echo $COUNT | cut -d'.' -f1`
echo No of rows present is =$COUNT
while [ $COUNT -gt 0 ]
do
       dec_count=10000
       db2 "delete from (select row_number() over(order by
dwh_efctv_timstm ) as row_id from card.TXN_PART where dwh_efctv_timstm
= '2006-05-23-20.00.00.000000' and source_system_Cd = 8 and
transaction_id < 25000000 ) where row_id < $dec_count"
       db2 "commit"
       COUNT=`expr $COUNT - $dec_count`
    scriptdate=`date +%Y%m%d%H%M%S`
    echo "Time after delete : $scriptdate"
       COUNT=`db2 -x "select count(*) FROM card.TXN_PART WHERE
source_system_cd=8 and dwh_efctv_timstm='2006-05-23-20.00.00.000000'
and  transaction_id < 25000000 for read only with ur" `
        COUNT=`echo $COUNT | cut -d'.' -f1`
       scriptdate=`date +%Y%m%d%H%M%S`
       echo "Time after count : $scriptdate"
       echo No of rows present is =$COUNT
done

Thanks,
Sam.