Recent Blog Entries

Thursday, 26 July 2012

AIA Tips & Tricks Series: Purging ESB Resequencer Tables in AIA 2.5 (Example: Comms PIPs)

AIA 2.5 Comms PIPs use the ESB 10g Resequencer for the following ESB Services:

  • CommunicationsCustomerPartyEBSV2Resequencer
  • SyncAcctSiebelAggrEventConsumer_RS
  • ProcessSalesOrderFulfillmentSiebelCommsSequencer
  • Consume_UPDSO_RS

While processing extensive volumes, the number of rows can grow quite rapidly in the ESB Resequencer tables (ESB_MESSAGES, ESB_MESSAGE_MAP, ESB_GROUP_STATUS). It is therefore critical to include these tables in the regular purging procedure – the standard ESB purge scripts will not touch them.

For ESB, the ESB Resequencer Purge Script is made available though Patch 9309170 – “Purge script for ESB scripts resequencer table”.
It adds a file  purge_resequencer_by_timestamp_serviceGUID.sql in SOA_HOME>/integration/esb/sql/other
Running this in sqlplus on schema ORAESB creates a stored procedure

res_purge_by_date_sguid(date d, varchar guid)

For purging all services using resequencer older than 10 days you can execute for example:

   CURSOR reseq_services is
   select guid from wf_events where name in ('CommunicationsCustomerPartyEBSV2Resequencer', 'SyncAcctSiebelAggrEventConsumer_RS', 'ProcessSalesOrderFulfillmentSiebelCommsSequencer', 'Consume_UPDSO_RS');

   FOR service IN reseq_services LOOP
        res_purge_by_date_sguid(sysdate-10, service.guid);

If you want to determine all ESB services which use Resequencer dynamically you can use

SELECT GUID from WF_EVENTS where ENDPOINT_PROPERTIES like '%ResequencerType%'

So the sequence for purging all AIA data is:

  1. Purge Resequencer data with  res_purge_by_date_sguid  (Example above)
  2. Purge ESB runtime data with purge_by_date_package.Purge_by_Date(date d)
    Example: CALL purge_by_date_package.Purge_by_Date(sysdate – 10)
  3. Purge BPEL runtime data
    Example: CALL SINGLE_THREADED_LOOPED_PURGE.purge_instances_loop(date d, integer rows, integer seconds);
  4. Check ESB queue tables
  5. Check AIA PIP Queue Tables (in JMSUSER)

Additional resources:

A useful query of the number of messages in ESB_GROUP_STATUS to check the purge is

select error, count(1), name from esb_group_status e, wf_events w where e.service_guid = w.guid  group by error, name;

0    126   Consume_UPDSO_RS
1    53    ProcessSalesOrderFulfillmentSiebelCommsSequencer
0    136   CommunicationsCustomerPartyEBSV2Resequencer
1    1     CommunicationsCustomerPartyEBSV2Resequencer
0    219   ProcessSalesOrderFulfillmentSiebelCommsSequencer
1    1     Consume_UPDSO_RS

Useful script for checking all BPEL and ESB tables:

select 'attachment', count(1) from orabpel.attachment UNION
select 'audit_details', count(1) from orabpel.audit_details UNION
select 'audit_trail', count(1) from orabpel.audit_trail UNION
select 'cube_instance', count(1) from orabpel.cube_instance UNION
select 'cube_scope', count(1) from orabpel.cube_scope UNION
select 'dlv_message', count(1) from orabpel.dlv_message UNION
select 'dlv_subscription', count(1) from orabpel.dlv_subscription UNION
select 'invoke_message', count(1) from orabpel.invoke_message UNION
select 'native_correlation', count(1) from orabpel.native_correlation UNION
select 'work_item', count(1) from orabpel.work_item UNION
select 'xml_document', count(1) from ORABPEL.xml_document UNION
select 'esb_control', count(1) from ORAESB.esb_control UNION
select 'esb_error', count(1) from ORAESB.esb_error UNION
select 'esb_error_retry', count(1) from ORAESB.esb_error_retry UNION
select 'esb_faulted_instance', count(1) from ORAESB.esb_faulted_instance UNION
select 'esb_group_status', count(1) from ORAESB.esb_group_status UNION
select 'esb_java_deferred', count(1) from ORAESB.esb_java_deferred UNION
select 'esb_messages', count(1) from ORAESB.esb_messages UNION
select 'esb_message_map', count(1) from ORAESB.esb_message_map UNION
select 'esb_monitor', count(1) from ORAESB.esb_monitor UNION
select 'esb_relation_xml', count(1) from ORAESB.esb_relation_xml UNION
select 'esb_relation_xml', count(1) from ORAESB.esb_relation_xml UNION
select 'esb_transaction_status', count(1) from ORAESB.esb_transaction_status;

Purging ESB Queue Tables (example for ESB_MONITOR and ESB_CONTROL):

po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(queue_table => 'ESB_MONITOR', purge_condition => NULL, purge_options => po);

po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(queue_table => 'ESB_CONTROL', purge_condition => NULL, purge_options => po);


  • New BPEL 10g Purge Scripts From MLR#2 [ID 1110833.1]
  • ESB Purge Instances scripts in and [ID 788285.1]

No comments:

Post a Comment