Delete unused Mailboxes in SB2BI/SFG

This is a question I have received from customers and that I have looked into. The typical case is that a user of Sterling B2B Integrator offers Mailboxes to their customers where files can be exchanged. This can be hundreds or thousands of mailboxes. Now and then customers are leaving the service. Typically you then need to do some cleaning, but that is often not done since it is a manual job. And some times the one responsible for Sterling B2B Integrator don’t get the information that a customer is not any longer active. Thus you end up with unused mailboxes.

A similar issue is “dead” files residing on mailboxes. Typically files uploaded for a customer to download, but where the customer is just doing a get to the mailbox, without a delete, and uses another logic to not download old files the next time. This last issue with “dead” data residing on a mailbox is easily fixed with standard Mailbox logic, where you can have a Routing Rule checking all mailboxes, and triggering a delete Business Process for all files older than a given number of days. But the same logic don’t exist for the mailboxes itself. So that is what I will give a simple example on in this Blog Post.

What I am going to do is to use a Map to query the database for when a mailbox is last updated. This map will then based on the query create a XML file to use with an XAPI Service, that uses standard API’s for deleting the mailboxes.

I will do this just as a very simple example to show the theory. For a production use of this, some more considerations needs to be taken. I will come back to those considerations in the end.

Database table to query

To be able to find when a mailbox was last updated, I need to find the right Database table storing this kind of data. The Sterling B2B Integrator Database is consisting of a lot of tables, and is not always easy to navigate around in. That is also why one never should do any SQL to the database that updates, inserts, deletes or in any other way alters the database. In this case we are however going to run SQL towards the Database, but only a select. So no altering of the Database in other words. The altering of the Database will be done through official API’s that takes care of Business Logiq.

The Last Updated field is stored in the column LAST_MODIFICATION in the Table MBX_MAILBOX.

But this LAST_MODIFICATION field does by default not update when a mailbox is updated by a get, put or delete. The field is by default only having the Modification Date from when it was created or updated through the Sterling B2B Integrator GUI. E.g. when changing permissions etc.

To enable this field to update when the Mailbox is in fact used the last time, the configuration has to be changed.

To do this, update the customer_overrides.properties.in file, and add the following value to the file: mailbox.updateMailboxLastModification=true

After updating the customer_overrides.properties.in file, shut down Sterling B2B Integrator, run setupFiles.sh to populate the customer_overrides.properties file, and start Sterling B2B Integrator again to make the changes apply.

SQL to XAPI Map

To delete the unused mailboxes, I will use XAPI. XAPI is a set of API’s for Sterling B2B Integrator and Sterling File Gateway to enable actions on the solution based on API’s instead of working in the GUI. Typically very usable for Batch creation of Mailboxes, creation of Accounts from a third party solution etc. The XAPI is used through a XAPI Service in a Business Process, and takes a standardized XML file as input.

I will create a simple Map that does a query towards the database to find what mailboxes that have not been used in a spesified number of days. And then map the result to a XML file on the format the XAPI Service expects.

The deleteMailbox XAPI is a very simple XML file like below.

<?xml version="1.0" encoding="UTF-8" ?>
<Mailbox Force="Required" Path="Required" />

This file shows that the API only is able to delete one mailbox at a time, and not several files. To handle that I have to do a little modification on the XML file to handle multiple Mailboxes, and then do a split and a loop in the later Business Process.

The XML created from the map will have one more level. I have added a level called <Mailboxes> that loops, and thus can have multiple <Mailbox> tags.

<?xml version="1.0" encoding="UTF-8" ?>
<Mailboxes>
	<Mailbox Force="Required" Path="Required" />
	<Mailbox Force="Required" Path="Required" />
	<Mailbox Force="Required" Path="Required" />
</Mailboxes>

The Map looks like below as an overview.

On the Input side there is a Query/Command node that has a query to the database. Then there is one record with the MBX_MAILBOX_PATH. This record can loop 10 times in my map (can of course be set higher). That means that the SELECT Node will output the results to the MBX_MAILBOX_PATH record. One record for each result. To see details on creating a SQL Map, read this Blog Post.

On the Output side I have the XML that will be the basis for the XAPI Service. This XML is imported from the XAPI schemas (they are available in the Sterling B2B Integrator Installation folder under
<SB2BI Install Dir>/install/xapidocs/api_javadocs/XSD), and edited with the extra Mailboxes Root Element to enable looping.

In the SELECT Node I have the following SQL Query:

SELECT PATH
FROM MBX_MAILBOX WHERE
LAST_MODIFICATION < Current Date - 90 Days;<span id="mce_marker" data-mce-type="bookmark">​</span>

This is a very simple Query that just selects all Mailboxes where the LAST_MODIFICATION value is less than today’s date minus 90 days. It is not a query that will be solid enough for a production environment, and I will mention more about the reasons for that in the last chapter.

Business Process

All this is connected together in a Business Process. The BP starts by running the Map I created. Then I use the output from the Translation in a Document Extraction where I use the same Map, and search for the Root Tag Mailbox. Then I use a For Each Document to loop through each Mailbox document and uses that as input for the XAPI Service. In that way I will run the XAPI Service for each Mailbox Path that was selected in the SQL Query in the Map.

To test this, I created 4 new Mailboxes, and then I altered the LAST_MODIFICATION to be a long time ago. The first map created then a file like below in ProcessData:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<Mailboxes>
	<Mailbox Force="true" Path="/esmines/mailbox1" ></Mailbox>
	<Mailbox Force="true" Path="/esmines/mailbox2" ></Mailbox>
	<Mailbox Force="true" Path="/esmines/mailbox3" ></Mailbox>
	<Mailbox Force="true" Path="/esmines/mailbox4" ></Mailbox>
</Mailboxes><span id="mce_marker" data-mce-type="bookmark">​</span>

This file shows that 4 mailboxes is detected as a Mailbox to delete. The Document Extract Service will split this file based on the Mailbox tag, and the process will then loop through each of the Extracted documents and through the XAPI Service delete the Mailboxes.

The BPM-Code for the Business Process is below.

<process name = "ES_MBX_Delete"> 
  <rule name="MoreMailboxes">
    <condition>Mailboxes.DONE = 0</condition>
  </rule>

  <sequence>
    <operation name="Translation MBX_MAILBOX TO XAPI">
      <participant name="Translation"/>
      <output message="TranslationTypeInputMessage">
        <assign to="map_name">ES_MBX_MAILBOX_TABLE_TO_MBX_deleteMailbox_XAPI</assign>
        <assign to="output_report_to_process_data">YES</assign>
        <assign to="." from="*"></assign>
      </output>
      <input message="inmsg">
        <assign to="." from="*"></assign>
      </input>
    </operation>

    <operation name="Document Extraction - Extract XAPI XML">
      <participant name="DocumentExtractionService"/>
      <output message="DocumentExtractionTypeInputMessage">
        <assign to="." from="*"></assign>
        <assign to="BatchLikeDocuments">NO</assign>
        <assign to="DocExtractMapList">ES_MBX_MAILBOX_TABLE_TO_MBX_deleteMailbox_XAPI</assign>
        <assign to="PDToProcessData">NO</assign>
        <assign to="XMLInput">YES</assign>
        <assign to="XMLRootTag">Mailbox</assign>
      </output>
      <input message="inmsg">
        <assign to="." from="*"></assign>
      </input>
    </operation>

    <sequence name="MailboxLoop">
      <operation name="For Each Document">
        <participant name="ForEachDocument"/>
        <output message="ForEachDocumentTypeInputMessage">
          <assign to="." from="*"></assign>
          <assign to="DOCUMENT_NAME_PREFIX">DOC-SPLIT-</assign>
          <assign to="ITERATOR_NAME">Mailboxes</assign>
        </output>
        <input message="inmsg">
          <assign to="." from="*"></assign>
        </input>
      </operation>

      <choice name="MoreMailboxesLeft">
        <select>
          <case ref="MoreMailboxes" activity="Mailbox Delete Start"/>
        </select>

        <sequence name="Mailbox Delete Start">
          <assign to="PrimaryDocument" from="/ProcessData/PrimaryDocument/@SCIObjectID"></assign>
          <operation name="XAPI Service">
            <participant name="XAPIService"/>
            <output message="XAPIServiceTypeInputMessage">
              <assign to="." from="*"></assign>
              <assign to="api">deleteMailbox</assign>
              <assign to="UserId">admin</assign>
            </output>
            <input message="inmsg">
              <assign to="." from="*"></assign>
            </input>
          </operation>

          <repeat name="Repeat" ref="MailboxLoop"/>

        </sequence>
      </choice>
    </sequence>
  </sequence>
</process>

Considerations with this APPROACH

As mentioned before, there are some considerations with this approach.

• Database Queries to the Sterling B2B Integrator database should always be done with great care. Never do updates, inserts, deletes or any other altering of the database through SQL. There are a great deal of relations between tables in the database, so changing something in one table, might affect other tables as well since relations could be broken. In worst case the database might be corrupted. There is no “law” against doing it, but a customer getting in problems from querying the database directly will have some explanation to do when contacting support…:)
• Most production systems at a customer site is sized based on the traffic and the transactions made by Sterling B2B Integrator towards the database. Doing other queries to the same database, might affect the performance in a production environment.
• The change in the customer_overrides.properties.in increases the total number of updates to the database since the LAST_MODIFICATION field is updated every time a file is uploaded, downloaded, deleted etc. This might also affect the over all performance, and should be done with care and consideration of the impact. With a lot of users on the Mailbox solution, this will obviously create a lot of transactions to the database that is not present in system default setup.
• The SQL Query to find the last modification date should be a lot more advanced in a production environment. My query is just a simple example to show how the basics work. But usually in a production environment there is nested Mailboxes. If a file is uploaded to a sub-Mailbox, the parent Mailbox LAST_MODIFICATION value is not updated. That means that a simple query like mine in this example would delete the Parent Mailbox, even though the sub-mailbox is in use. So a recursive query is needed to make this work. But my DB2 Skills is not quite up for that task…:)
• I have not included User Accounts in this example. There should also be a XAPI Service cleaning up in the User Accounts as well since a unused mailbox, also would indicate an unused User Account.