IBM Sterling B2B: Queries for Archive

From Wiki

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

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 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