When you check the database of PI/PO system, you can see different table names and you may be wondering what message related data they keep and how to control the size of these tables. Here I will simplely introduce these tables and the related background jobs for you.
- BC_MSG
BC_MSG (XI_AF_MSG for PI 7.0x and lower) is the master table for persistence of messages on the Java side, one record per each persistent asynchronous message. BC_MSG keeps the last (or only) message version created via staging – just for asynchronous messages.
To analyze the overall number of messages based on their status, you could use the following SQL statement:
SELECT COUNT(*), STATUS FROM <Java Schema name>.BC_MSG GROUP BY STATUS;
Only messages with final status (DLVD, FAIL) can be deleted. Messages with status NDLV should either be retried (manually or via Restart Job) or cancelled (manually or via Cancel Job) to go into a final state – status Delivered or Cancelled. In case these messages are with Quality of Service Exactly Once In Order (EOIO), they also block the other messages from the same sequences (EOIO queues) in Holding state. That’s why you should check the messages in System Error state and decide whether you need to process them (and then restart them) or if it’s better to cancel them (in case they are obsolete). In the latter case you should also check if there are EOIO messages in Holding state with the same Serialization Context and similarly cancel all obsolete messages in Holding state or restart the first one (with the smallest serial number) to trigger the rest in the same sequence.
The default persist time for messages in the Adapter Framework is 1 day from 7.1 onwards (30 days for older releases). This means that messages will be kept in the database for 1 day until the deletion job removes them.The retention period can be configured as the following two properties (value is in milliseconds) in NWA -> Configuration -> Infrastructure -> Java System Properties -> Services -> "XPI Adapter: XI".
"xiadapter.outbound.persistDuration.default" and "xiadapter.inbound.persistDuration.default"
- BC_MSG_VERSION
The entries in table BC_MSG_VERSION are the message versions created when message is edited with Message Editor or in case staging was configured. Versions created via staging -- these are created only for asynchronous messages and are stored in BC_MSG_VERSION and BC_MSG tables. By default, this version is created after Receiver and Interface determination step (stage MS).
In case the original message is split (e.g. to several receivers or interfaces at Receiver or Interface determination step or with multi-mapping during Mapping step), the system will implicitly store one version for each of the split child messages right after this step (stage MS or AM).
You can define additional steps at which the system will create staging versions per scenario (for example, in the "Advanced setting" tab of the Integrated Configuration Object in the Integration Directory) or globally, using the property "xiadapter.stage.conf" in NWA -> Configuration -> Infrastructure -> Java System Properties -> Services -> "XPI Adapter: XI service". For more information how to configure persistence of additional staging version, you can find here:
Saving Message Versions in the AAE (Local Message Processing)
- BC_MSG_LOG and BC_MSG_LOG_STAT
The entries in table BC_MSG_LOG and BC_MSG_LOG_STAT are created in case message logging was configured. These can be created for both asynchronous and synchronous messages.
BC_MSG_LOG – one record per each logged version.
BC_MSG_LOG_STAT keeps a single record per logged message.
These versions are used only for monitoring purposes – you cannot restart an asynchronous message form one of these versions.
By default the system will create such only for erroneous synchronous messages. The persistence of audit logs for successfully processed synchronous messages may be required for business reasons or performance analysis. To persist the audit logs for all synchronous messages to the database, you can change the value of property "messaging.auditLog.memoryCache" from the default 'true' to 'false' in NWA -> Configuration -> Infrastructure -> Java System Properties -> Services -> "XPI Service : Messaging System". Please be aware that audit logs for successful asynchronous messages will also be persisted.
You can configure steps with logging per scenario (for example, in the "Advanced setting" tab of the Integrated Configuration Object in the Integration Directory) or globally, using the property "xiadapter.logger.conf" in NWA -> Configuration -> Infrastructure -> Java System Properties -> Services -> "XPI Adapter: XI service". For more information how to configure Message Logging at Specific Processing Steps, you can find here:
Configuring Message Logging (Within AAE Pipeline)
If logging is not activated, synchronous messages are kept in the memory but not persistent in the database. You can refer to note 1872758 - How XI/PI messages are persisted in Adapter Engine, for detailed explanation.
If logging is activated, synchronous messages are only logged in BC_MSG_LOG and BC_MSG_LOG_STAT without a corresponding entry in BC_MSG or
BC_MSG_VERSION table. Therefore, these entries have their own retention period specified by property "messaging.log.retainTime" in NWA -> Configuration -> Infrastructure -> Java System Properties -> Services -> "XPI Service: Messaging System". The default retention period for logged messages is 7 days.
Retention Period can also be configured per interface as described in note 1960448.
You can check PERSIST_UNTIL field of BC_MSG_LOG_STAT table to see when a message becomes eligible for archiving/deletion.
- BC_MSG_AUDIT
The entries in table BC_MSG_AUDIT (XI_AF_MSG_AUDIT for 7.0x and lower) are the audit log entries visible in the RWB/PIMON. In 7.1 and higher, per default the audit log is only written to database for error messages and the messaging system does not persist the audit logs of successully processed asynchronous messages.
The persistence of audit logs for successfully processed asynchronous messages may be required for business reasons or performance analysis. To persist the audit logs for all asynchronous messages to the database, you can change the value of property "messaging.auditLog.memoryCache" from the default 'true' to 'false' in NWA -> Configuration -> Infrastructure -> Java System Properties -> Services -> "XPI Service : Messaging System".
BC_MSG_AUDIT keeps audit logs for messages with errors during processing and for successful messages in case "messaging.auditLog.memoryCache" is set to false – for asynchronous messages + synchronous logged messages since Note 1982452 . No other table keeps audit log entries.
By default synchronous messages and audit logs are kept in the memory, not in database. If logging is activated, the audit log of error synchronous message will persist in BC_MSG_AUDIT and the audit log of successful synchronous message will persist in BC_MSG_AUDIT only if "messaging.auditLog.memoryCache" is set to false.
The persistence of the audit log entries is directly related to the persistence of messages in BC_MSG table. Therefore, reducing the number of messages in BC_MSG will also reduce the number of messages in the BC_MSG_AUDIT table.
- BC_MSG_DUP_CHECK
For each asynchronous message (EO or EOIO) that enters the PI/PO system, an entry is stored in table BC_MSG_DUP_CHECK and kept for 30 days by default to prevent reprocessing of the same message. This is because the retention period of asynchronous messages is usually too short (1 day by default). And if the same message is re-triggered by the sender, it might get duplicated. The entries in table BC_MSG_DUP_CHECK are not relevant with synchronous messages (BE) even if logging is configured. There are just 3 fields in this table - MSG_ID, DIRECTION and PERSISTED_AT - this is not a version related information, just message identifiers.
The property 'messaging.duplicateCheck.persistTime' specifies the duplicate check persist time. The default value is 30 days. If you would like to delete the entries in table BC_MSG_DUP_CHECK earlier, you can consider to decrease the value of property 'messaging.duplicateCheck.persistTime'. You can modify this property online under NWA > Configuration > Infrastructure > Java System Properties > Service "XPI Service: Messaging System". It takes effect on both the old and new entries in table BC_MSG_DUP_CHECK. The entries out of retention period will be deleted by Default Delete job under PIMON > Adapter Engine > Background Job Processing Monitor > Adapter Engine Jobs.
===
Summary
===
By default "messaging.auditLog.memoryCache" is set to true and the audit log is only written to database for error messages.
If "messaging.auditLog.memoryCache" is set to false, audit log of asynchronous messages + synchronous logged messages will be persisted in database – table BC_MSG_AUDIT.
- Background jobs on Adapter Engine
For asynchronous messages, the logged or staged message versions in the table BC_MSG_VERSION, BC_MSG_LOG and BC_MSG_LOG_STAT will be deleted automatically when the message from BC_MSG table will be deleted.
The logged versions of synchronous messages will also be deleted by the standard PI message deletion job and no additional job has to be scheduled.
The audit log in BC_MSG_AUDIT table will be deleted automatically when the message is deleted.
In case no archiving job is configured in the system, the Delete job is responsible to delete message data from BC_MSG, BC_MSG_VERSION, BC_MSG_LOG, BC_MSG_LOG_STAT, BC_MSG_AUDIT tables related to messages in final state with expired retention period (Persist Until time).
In case there is an active Archive job, then these messages are skipped by delete jobs and the archive job should take care for archiving and deletion of messages which is done in two phases - first all eligible messages are archived and after that all archived messages are deleted.
For more information, you can find here: Background Job Types
******
Related Notes:
SAP Note 790226 - Messages in AdapterEngine/PCK database do not get archived
SAP Note 872388 - Troubleshooting Archiving and Deletion in PI
SAP Note 1314974 - Successful asynchronous messages - audit log persistence
SAP Note 1760915 - FAQ: Staging and Logging in PI 7.3 and higher
SAP Note 1872758 - How XI/PI messages are persisted in Adapter Engine
SAP Note 1982452 - Audit Logs for successful synchronous messages with log versions to be persisted/archived if audit log memory cache is disabled
SAP Note 2137688 - New Feature: Background Job to mass cancel messages
SAP Note 2403587 - Default Delete Job does not work even the custom archive job has been deactivated
SAP Note 2518441 - The tablespace PSAPSR3DB of PI system grows quickly
SAP Note 2538365 - How to enable compression on PI/PO system
SAP Note 2778426 - How to decease the size of table BC_MSG_DUP_CHECK
SAP Note 1960448 - Configure message retention period per interface
SAP Note 2771052 - PI global parameter of retention period for logging messages is not taking effect
What can slow down execution of SAP PI Default Delete Job?
- SAP Managed Tags:
7 Comments
