Verificar também o artigo IBM Sterling B2B: Archive e Purge

Archive Info

ARCHIVE_FLAG	Meaning
-1	Not Indexed
0	Indexed, awaiting Archiving
1	Lifespan Exceeded, Ready for Purge
2	Archive Complete, Ready for Purge

Queries

Calcula os registros para archive conforme o ARCHIVE_FLAG

SELECT ARCHIVE_FLAG, count(1) as CNT FROM ARCHIVE_INFO GROUP BY ARCHIVE_FLAG ORDER BY ARCHIVE_FLAG

result:

ARCHIVE_FLAG	CNT
-1	310
0	83910
1	912571
2	4198

SELECT ARCHIVE_FLAG,GROUP_ID,MIN(ARCHIVE_DATE),MAX(ARCHIVE_DATE) FROM ARCHIVE_INFO where ARCHIVE_FLAG >=0 GROUP BY ARCHIVE_FLAG, GROUP_ID

result:

ARCHIVE_FLAG	GROUP_ID	MIN(ARCHIVE_DATE)	MAX(ARCHIVE_DATE)
2	1	2030-10-19 16:59:41.0	2031-04-07 19:11:17.0
1	4	2021-04-19 11:31:44.0	2021-04-30 16:56:05.0
1	2	2021-04-17 07:37:36.0	2021-04-19 07:36:06.0

Messages on Mailboxes by Year

select to_char(mm.CREATED_DATETIME, 'YYYY') as YEAR1, count(1) as CONT
from MBX_MESSAGE mm
group by to_char(mm.CREATED_DATETIME, 'YYYY')
order by to_char(mm.CREATED_DATETIME, 'YYYY')

result:

YEAR1	CNT
2018	9943
2019	14530
2020	265567
2021	4198

Messages on Mailboxes by Year and Extractable Count

select to_char(mm.CREATED_DATETIME, 'YYYY') as YEAR1, mec.EXTRACTABLE_COUNT, count(1) as CONT
from MBX_MESSAGE mm, MBX_EXTRACT_COUNT mec 
WHERE mm.MESSAGE_ID = mec.MESSAGE_ID 
group by to_char(mm.CREATED_DATETIME, 'YYYY'),mec.EXTRACTABLE_COUNT
order by 1,2

Messages on mailboxes by created date

select mm.MESSAGE_ID,mm.CREATED_DATETIME,mm.MESSAGE_SIZE,mm.MAILBOX_ID,mb.PATH,mm.DOC_ID
from MBX_MESSAGE mm, MBX_MAILBOX mb
where mm.MAILBOX_ID = mb.MAILBOX_ID
order by mm.CREATED_DATETIME

Messages on mailboxes group by year with count and sum

select to_char(mm.CREATED_DATETIME, 'YYYY') as ANO, mb.PATH as mbx, count(1) as CNT, SUM(mm.MESSAGE_SIZE) as SUM1
from MBX_MESSAGE mm, MBX_MAILBOX mb
where mm.MAILBOX_ID = mb.MAILBOX_ID
group by to_char(mm.CREATED_DATETIME, 'YYYY'), mb.PATH

Ver também