IBM Sterling B2B: Queries for Archive: Difference between revisions

From Wiki
No edit summary
 
(9 intermediate revisions by the same user not shown)
Line 1: Line 1:


Verificar também o artigo [[IBM Sterling B2B: Archive e Purge]]
= Archive Info=
{| class="wikitable"
|-
! 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
  SELECT ARCHIVE_FLAG, count(1) as CNT FROM ARCHIVE_INFO GROUP BY ARCHIVE_FLAG ORDER BY ARCHIVE_FLAG
Line 18: Line 39:
|}
|}


== ==
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:
 
{| class="wikitable"
|-
! 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
select to_char(mm.CREATED_DATETIME, 'YYYY') as YEAR1, count(1) as CONT
  from MBX_MESSAGE mm
  from MBX_MESSAGE mm
  group by to_char(mm.CREATED_DATETIME, 'YYYY')
  group by to_char(mm.CREATED_DATETIME, 'YYYY')
Line 40: Line 76:
|}
|}


== 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==
==Messages on mailboxes by created date==
Line 58: Line 100:


= Ver também =
= Ver também =
* [[IBM Sterling B2B: Archive e Purge]]
* [[IBM Sterling B2B: SELECT a Code List]]
* [[IBM Sterling B2B: SELECT a Code List]]
* [[IBM Sterling B2B: SELECT Queue, Lifespan, PersistentLevel of Business Process]]
* [[IBM Sterling B2B: SELECT Queue, Lifespan, PersistentLevel of Business Process]]

Latest revision as of 15:11, 23 October 2023

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

Retrieved from "https://ebasso.net/wiki/index.php?title=IBM_Sterling_B2B:_Queries_for_Archive&oldid=8485"