Resolution:
Message Logging Database Usage Overview:

Message logging is described in the TIBCO Documentation.  This note provides additional runtime information to help you understand how the message logging database is used and help you identify configuration parameters.  

Message logging stores two "types" of data:  message properties and message content (i.e., the SOAP message).  Message properties are always stored in the database.  You may optionally log the message content and it may be stored in files or the database.  Again, message logging always uses a database.
When displaying messages in the Policy Manager UI (i.e. Message Log tab), the UI queries the appropriate agent(s) for the messages.  The agent retrieves the message(s) and returns the information to the Policy Manager UI.

As mentioned in the documentation, TIBCO recommends that an Enterprise database be used in a production environment.  Do not use the default embedded database in a production environment.

1) Content in Files or Content in Database:

The Logging Policy's "Message Storage Location" setting applies only to the actual message content.    Since individual messages may be large, most of your storage requirements will be from any logged content.

Choosing to store message content in the database may adversely affect database functioning, such as performance, backup time, and disk storage requirements.  If your database logs deletion of the large text objects (e.g., a CLOB), then you need to concern yourself with your database logging resources.

Message logging involves database INSERT and DELETE statements only.  New messages and message properties are inserted.  Old messages and message properties are purged with DELETE statements. We do not UPDATE rows.  

2) Overview of the Agent Message Logging Process:

When messages to be logged are handled by the Policy Agent, the messages are immediately written to disk within message logging bundles in the Policy Agent storage directory.  A message logging bundle consists of one data file (.log) and two index files (.idx1, .idx2).  This "write to file" step is true even if you have selected "Content in Database".  This immediate write is done to limit the agent’s logging overhead during the actual message request/response handling.   There may be one or more message logging bundles, where each bundle may contain multiple messages.

Within the agent, another thread known as the "indexer" then reads these messages and processes them.   For messages to be logged, it extracts properties as needed and inserts the properties into the database.  If you’ve specified "Content in Database", the message content is also inserted into the database.    For "Content in Files", the message remains in the file.

Another step is to clean up the intermediate message logging bundles when you have chosen to store "Content in Database".   When all messages within a bundle have been processed:

- If you have "Content in Database" or do not save the Content, the processed bundles files are deleted
- If you have "Content in Files", then the bundle files are retained.

Lastly, at regular intervals, the indexer will purge old messages based on the logging policy’s time limit.  This includes the data within the database and (if you "Content if Files") the message bundle files.

If your database is unavailable the messages will continue to be written to the intermediate files.  No messages will be lost.   Be aware that these files will continue to grow (forever and regardless of the logging policy’s time limit) and consume disk space until the indexer thread can process the messages.  Querying and viewing messages will also not be possible whenever the database is unavailable.

Lastly, you may delete the message logging bundles in the Policy Agent storage directory.  If the messages within the bundle have not yet been processed then the messages are lost.  If the bundles have been retained because you have chosen "Content if Files", then the Policy Manager UI will give the message "Content not available."

3) Purging old Messages (Logging Policy Time Limit):

Every so often the indexer thread checks old messages for their age being older than the logging policy's time limit.

"Every so often" is based on the agent's CURRENT.SetupData file, element indexerSetupData/cleanDatabaseCheckInterval; the default is 1800 seconds.  As this is done within the indexer thread, there is no promise as to exactly when it is done.  

At every check, only a certain amount of old messages may be purged, based on the SetupData file’s element indexerSetupData/maxDataCleanedPerInteration; default is 10800 seconds.   We will delete all message data where the message’s time is less than the value of (actual oldest time in the database + maxDataCleanedPerInteration).

This delete is done within a single database transaction, so ensure your database logging (e.g., redo Oracle log files) can handle it.  It is up to the customer to estimate how large this may be for their environment.

The SetupData changes may be done through the Policy Manager console.  Should you change these values, also change indexerSetupData/agentOverrideAndApply to true.  (If false, changes will only occur after an agent restart).

The purging is first done based on when the indexer thread starts.  The thread starts during agent startup or when the SetupData is configured with agentOverrideAndApply set to true.

Alternately, you can manually delete the messages from the tables.  The advantage of this is you can control exactly when it will be done.


4) Removing a Logging Policy:

Removing a Logging Policy will delete all message data at the time the log policy is applied to the agent.  This delete is done within a single database transaction.  If the delete fails, we will reduce the attempted transaction size and try again.  And we will continue to use smaller transactions until it succeeds.  The usual causes for failure are locking deadlocks or insufficient database logging space.  Alternately, you may consider manually deleting the data before removing the policy.

5) How many Agent Databases?

If you have multiple agents, each agent may have its own database, all agents may all share a single database, or somewhere in between.  (Note: for the purposes of this article, an agent "database" refers to one database schema instance.)

A real possible issue with sharing a single database is locking contention.  Each Agent has single indexer thread that writes and deletes data.  A very real possibility is that one or more busy agents will be inserting data while another will be deleting old data.  The deleting agent eventually tries to escalate its lock to table level (most databases have configurable "lock escalation" parameters) but cannot get the lock because of other agents.  This eventually causes the delete to fail, causing the purging process to fall behind.  The only way to completely avoid this conflict is to give each agent its own database (database schema).  

Also remember that only the agent that writes the data will read that data and delete that data.  Sharing a database might result in multiple agents having their data on the same physical database page.  This might slow both writing and reading and consume additional lock resources while deleting.
In general, an agent handling high message rates should have its own database (database schema instance).   For low message rates, you may wish to share a database for ease of administration.

6) Manually Deleting Message Data

Old messages are purged at regular intervals as described above.  If you wish to control the exact time old messages are trimmed you should consider manually deleted the data from the tables.  For example, if you system is extremely busy from 8AM to 8PM, you can reduce the load on your database process and agent  indexer thread during that busy time by manually deleting the data during the middle of the night.
To manually delete, the following tables contain message data:
    -AmbPt_string_properties
    -AmbPt_numeric_properties
    -AmbPt_log_metadata
    -AmbPt_exchange
    -AmbPt_summaries
    -AmbPt_messages
    
The relevant column is "time" for all tables except AmbPt_exchange.  Table AmbPt_exchange contains info on "paired" messages - one for the request (column request_arrtime) and another for the response(column response_arrtime).  All time values are in milliseconds since January 1, 1970, 00:00:00 GMT.
If you plan to delete messages while the agent(s) are active, delete data from AmbPt_exchange first.  This will effectively make the other tables’ data "invisible" to queries run from the UI.

Message content is in table AmbPt_messages if you choose to write the message content to the database.  If you just delete from this table, your message queries will work but you'll see that the "request/response is unavailable" for the actual messages you deleted.

Lastly, adjust your message logging retention time and the indexer properties so that no messages will be purged by the indexer process. To turn it off completely set cleanDatabaseCheckInterval to -1.

Caution: Do not delete from AmbPt_prop_defs, AmbPt_prop_guid, AmbPt_cursor_metadata, AmbPt_indexer_windows, and AmbPt_indexer_bundle_info.  But if you mistakenly do, immediately restart all agent(s) as each agent caches the data in these tables.

Disclaimer: The table names and/or structure disclosed in this article are provided solely to offer enough information for sizing, monitoring, and management of the database supporting our product. We strongly recommend against altering the schema, or performing any otherwise intrusive operations against the databases content. TIBCO reserves the right to change the database schema in future releases of the product without prior advance notice. Should such change arise, we request our customer to adapt their operational procedures and/or monitoring tools. If you have any additional questions, please open a Service Request with your detailed questions via http://support.tibco.com .