Conference utrop1::linkworks_v3

It is a known situation. The subject and message text are 
not deleted when the envelope is deleted. It still is 
possible to view the mailInBox log with subject and message 
text. The same is valid for Events. These are never removed 
from the database. I will post SQL statements in order to 
show/remove events , subject text and message text.

regards,

Joop Visser

Mail cleanup description based on LNX Release 3.08	
Written by Joop Visser in april 1997.

What happens when a user sends mail?

The envelope(s) are created in the cell for new objects. 
(1 envelop per recipient)
Per attached object is 1 related entry in the mailinBox log.
(1 per envelop)
(1 object send to 11 recipients generates 11 envelopes and 11 entries)
Per envelop a subject text is stored in mailinfo (created in senders cell 
for new objects) and optional mailtext (created in senders cell for new 
objects) to store the message text. (1 row per message text line).

If the recipient deletes the envelop, the related mailtext and mailinfo 
remains because the MailInbox and MailOutbox logs use these to display the 
subject and message text.

There is no possibility via GUI to remove MailInbox or MailOutbox log 
entries. 

Via SQL statements it is possible to remove mail log entries by deleting 
those entries which exist longer than a given date.

When the recipient deletes the envelop the mailtext and mailinfo remain 
because these can still be shown in the mailinbox log..
The mailtext and mailinfo do not have a datestamp and have only a reference 
to the envelop. One can do a delete for those entries where there does not 
exist an envelop and a MailInbox or MailOutbox log entry. To test the last 
part most likely two cells are needed to be referenced which is not 
possible in one go. The effect is that in the log you can see an entry not 
having a
subject and message text.

What can be achieved with SQL statements is following. The example scripts 
are based on ORACLE. (Use of spool command for result log purpose)
Delete entries which are older than a given date. 
This can be used for events (ereigbuch), mailinbox log (eingangsbuch) and 
mailoutbox log (ausgangsbuch).
Delete the mailtext and mailinfo entries for which no envelop exist.

The problem with the last action is that references to mails which are 
deleted by the recipient no longer shows the message text and the subject.

Planned by development is to introduce a date stamp in mailinfo and 
mailtext so the update can be done in combination with the mail log remove 
action although these can be located on another cell. (Implemented in 3.2.2 
release)


SQL scripts:
showupdate	shows the selected rows which could be deleted
cleanup		deletes the rows.

Actions to do:
1. change the date in both scripts. (21-JAN-97 to the expirydate of events 
and mail logs.)

2. run  script showupdate and inspect the output to get an overview of the 
amount of data to be removed.
Remark: for oracle login as omdba and set the ORACLE_SID.
Note: Check the spool files
Note: The events (ereigbuch) are not related to the mail tables.
So you can treat events in another update scheme. The sequence of the mail 
table updates should remain.

Warning:
If you have a lot of mails to be deleted you can have problems in deleting 
them al in one transaction. The rollback space can be completely filled.
Always do the change during idle linkworks periods. 
And check that you have a good backup before doing this removal action.


3. run cleanup

connect /

spool /tmp/ausgang.out
select * from ausgangsbuch where
	abgegangen < '01-APR-97'
AND
	obj_id NOT IN
		(select obj_id from objekt where
			obj_id = ausgangsbuch.obj_id 
		AND
			om_id = ausgangsbuch.om_id);
spool off

spool /tmp/eingang.out
select * from eingangsbuch where
	eingelangt < '01-APR-97'
AND
	obj_id NOT IN
		(select obj_id from objekt where
			obj_id = eingangsbuch.obj_id 
		AND
			om_id = eingangsbuch.om_id);
spool off

spool /tmp/events.out
select * from ereigbuch where
	geschickt < '01-APR-97';
spool off

spool /tmp/mailtext.txt
select * from mailtext where
	obj_id NOT IN
	(SELECT obj_id FROM objekt WHERE
		obj_id = mailtext.obj_id AND
		om_id  = mailtext.om_id);
spool off

spool /tmp/mailinfo.txt
select * from mailinfo where
	obj_id NOT IN
	(SELECT obj_id FROM objekt WHERE
		obj_id = mailinfo.obj_id AND
		om_id  = mailinfo.om_id);
spool off


disconnect
exit

connect /

spool /tmp/cleanup.txt
delete  from ausgangsbuch where
	abgegangen < '31-JAN-97'
AND
	obj_id NOT IN
		(select obj_id from objekt where
			obj_id = ausgangsbuch.obj_id 
		AND
			om_id = ausgangsbuch.om_id);
commit work;

delete  from eingangsbuch where
	eingelangt < '31-JAN-97'
AND
	obj_id NOT IN
		(select obj_id from objekt where
			obj_id = eingangsbuch.obj_id 
		AND
			om_id = eingangsbuch.om_id);
commit work;

delete  from ereigbuch where
	geschickt < '31-JAN-97';
commit work;

delete  from mailtext where
	obj_id NOT IN
	(SELECT obj_id FROM objekt WHERE
		obj_id = mailtext.obj_id AND
		om_id  = mailtext.om_id);
commit work;

delete  from mailinfo where
	obj_id NOT IN
	(SELECT obj_id FROM objekt WHERE
		obj_id = mailinfo.obj_id AND
		om_id  = mailinfo.om_id);
commit work;
spool off

disconnect
exit