how to delete large quantity mesaages in en_mail_message

General question about the package, problems, solutions
gloria
Posts: 16
Joined: Mon Aug 17, 2009 6:40 pm

how to delete large quantity mesaages in en_mail_message

Postby gloria » Tue Oct 26, 2010 10:43 am

I have over one thousand failed messages stored in en_mail_message. When try to select a bunch of them and do a bulk delete from CQ client. It crashes CQ Client.
Can you point me an easy way to do a bulk delete to clean out those old messages? Is there a script? Thanks in advance.

Pavel
Posts: 281
Joined: Fri Dec 12, 2008 12:19 am

Re: how to delete large quantity mesaages in en_mail_message

Postby Pavel » Tue Oct 26, 2010 12:18 pm

It is an excellent question!
There was a reason why clean-up script is not provided in the distribution: the most efficient way to clean message queue would require direct access to the backend database in order to remove records from en_email_message table and correspondent history.
It would never be supported by Rational.
On the other hand, removing records using ClearQuest API would be extremely slow.

Nevertheless, you can try using simple cqperl script to remove records.

Code: Select all

#!/usr/bin/perl -w

# the script delete email messages from the queue that are older than date
# provided as a command line parameter in YYYY-MM-DD format
# using ClearQuest API

use strict;
use CQPerlExt;

if(!defined($ARGV[0]) || $ARGV[0] !~ /^\d{4}\-\d{2}\-\d{2}$/){
  print "Usage: $0 <YYYY-MM-DD>\n";
  exit 1;
}

our ($session);

$session = CQPerlExt::CQSession_Build;
$session->UserLogon( <login_name> , <password>, <database>, <dbset>);

my ( $qdef, $fnode, $resultset );
my @dbids;
$qdef = $session->BuildQuery('en_email_message');
$qdef->BuildField('dbid');
$fnode = $qdef->BuildFilterOperator($CQPerlExt::CQ_BOOL_OP_AND);
$fnode->BuildFilter( 'status',      $CQPerlExt::CQ_COMP_OP_EQ, ['Delivered']);
$fnode->BuildFilter( 'action_date', $CQPerlExt::CQ_COMP_OP_LT, [$ARGV[0]]);

# get number of records first
my $count = 0;
my $idx   = 0;
my $del   = 0;
my $sql   = $qdef->GetSQL();
$sql      =~ s/^select\s+(distinct\s+)?([^\s]+)/select $1 count($2)/i;
$resultset = $session->BuildSQLQuery($sql);
$resultset->Execute();
if( $resultset->MoveNext() == $CQPerlExt::CQ_SUCCESS ){
  $count = $resultset->GetColumnValue(1);
}
print "$count records will be deleted\n";

# removing records
$resultset = $session->BuildResultSet($qdef);
$resultset->Execute();

while( $resultset->MoveNext() == $CQPerlExt::CQ_SUCCESS ){
  $idx++;
  print "$idx of $count ...";
  my $dbid   = $resultset->GetColumnValue(1);
  my $entity = $session->GetEntityByDbId('en_email_message', $dbid );
  my $result = 'skipped';
  eval {
    if( $entity->SiteHasMastership() ){
      $session->DeleteEntity($entity, 'Remove');
      $result = 'removed';
      $del++;
    }
  };
 
  print " $result\n";
}
print "$del records out of $count were removed\n";
CQPerlExt::CQSession_Unbuild($session);

exit 0;


Regards,
Pavel

gloria
Posts: 16
Joined: Mon Aug 17, 2009 6:40 pm

Re: how to delete large quantity mesaages in en_mail_message

Postby gloria » Tue Oct 26, 2010 5:18 pm

Thanks, Pavel. This works very well.


Return to “Email Notification Package Q&A”

Who is online

Users browsing this forum: No registered users and 1 guest

cron