How do you clean up the SESSION_HISTORY table?

The SESSION_HISTORY table uses 500GB of disk space in our production environment. What is the best way to clean it up?

You would want to make appropriate backups and test this thoroughly, but this is something that can be done via the Platform Maintenance Scheduler (formerly Cleanup Agent) in the IBM TRIRIGA Admin Console. You can develop SQL to do the cleanups as they meet your business requirements, add that as a new cleanup command at the bottom section of the window, and then add a new Cleanup Schedule event in the top section of the window to have it run periodically.

This may be something that you want to reach out to a business partner for assistance with, depending on your skill and comfort level in implementing such a change. If so, you can search the IBM PartnerWorld portal.

[Admin: To see other related posts, use the Sessions tag or History tag.]

What happened to the performance monitors in the Admin Console?

In the IBM TRIRIGA Administrator Console, under Performance Monitor > Monitor a Single Value, several monitors are no longer listed.

The following changes were made in the Administrator Console, under Performance Monitor > Monitor a Single Value:

• CRYSTAL_QUEUE_AGENT_RUNNING is renamed to REPORT_QUEUE_AGENT.
• CLEANUP_AGENT_RUNNING is renamed to PLATFORM_MAINTENANCE_SCHEDULER.
• KEYWORD_PARSER_AGENT_RUNNING and WAREHOUSE_AGENT_RUNNING were deprecated in a previous release and are now removed from the Performance Monitor.

Since the CRYSTAL_QUEUE_AGENT_RUNNING and CLEANUP_AGENT_RUNNING monitors are used for integration, only the performance monitor names are changed, not the values or URLs.

[Admin: This post is related to the 07.14.15 post about renaming CleanupAgent (Cleanup Agent) to PlatformMaintenanceScheduler (Platform Maintenance Scheduler). To see other related posts, use the Monitoring tag.]

Why aren’t servers in the “Active Servers” table in Admin Console?

We are seeing servers dropping out of the “Active Servers” table in the Admin Console > Agent Manager page. We are running multiple environments, each with platform version 3.5.2.2 with two UI and two process servers, and have experienced this across multiple environments. Current observations:

• Both UI and process servers can drop out, and it’s not the same server every time.
• Servers can be accessed and logged into, even if they are gone from the “Active Servers” table.
• A restart of the server will make the servers appear again.

Our current approach is to monitor daily and when a server drops, take a look in the logs for that day. Any other ideas? What controls when servers are listed or not in the “Active Servers” table in the Admin Console > Agent Manager page?

[Admin: To see other related posts, use the Admin Console tag.]

Why is the extended formula queue backing up?

We have quite a lot of rows in the extended formula queue that started to back up since Aug 29. At one point, we had over 900K in the queue and the process server is processing it pretty slowly. As of Sep 8, the system is still processing the queue. We are adding daily on average over 125K rows to the EF_QUEUE table.

We tried starting and stopping the Extended Formula Agent and Formula Recalc Agent and tried rebooting the process servers. We swapped the agents to run on different servers. We also ran the segment shrink on the EF_QUEUE table and gathered statistics. It is still extremely slow. We still have over 500K in the queue.

In parallel, we did open the platform logging on the extended formula activity and cache building, and we are working to identify the processes that can be fixed to reduce the multiple calls to the Extended Formula Agent queue. Do you have any recommendations to speed up the process to clear the extended formula queue?

Review the following two APARs. They may need to be applied to your environment to address this issue: (1) Platform: APAR IV87030. (2) Application: APAR IV87104.

[Admin: This post is related to the 07.28.16 post and 05.04.16 post about the Extended Formula (EF) Agent not processing and backing up the queue. To see other related posts, use the Extended Formula tag.]

Can you get the Cleanup Agent to remove record data in chunks?

In one of our environments, we have a large amount of records that have been transitioned to the null state. When the Cleanup Agent runs, it runs out of DB2 transaction log space executing the following:

DELETE FROM IBS_SPEC_ASSIGNMENTS WHERE EXISTS (SELECT ‘X’ FROM IBS_SPEC_CA_DELETE WHERE IBS_SPEC_CA_DELETE.SPEC_ID=IBS_SPEC_ASSIGNMENTS.SPEC_ID)

For workflow instance saves, the Cleanup Agent now seems to remove the data in small chunks (of 1000 rows each). But for the record data cleanup, it still seems to (try to) remove all data in one huge SQL statement/transaction. Is it possible to get the Cleanup Agent to remove record data in chunks, like it does for workflow instance saves?

Otherwise, I’m thinking of writing a small util that would run the statement above, but in smaller chunks, since it seems we still have the list of record IDs that it tries to remove in the IBS_SPEC_CA_DELETE table. Any obvious issues with that?

As of 3.5.3, there have been no changes for the data to be deleted in chunks for IBS_SPEC_ASSIGNMENTS. This sounds like a good PMR. It is rarely recommended to delete records directly from the database, but in this circumstance, you might be okay. However, the IBS_SPEC_CA_DELETE only exists during the time the Cleanup Agent is running. It is dropped when the agent goes back to sleep.

[Admin: To see other related posts, use the Cleanup tag or SQL tag. As a side note, starting with version 3.4, the Cleanup Agent name was changed to Platform Maintenance Scheduler.]

How do you configure TRIRIGA for Tivoli Directory Integrator (TDI)?

You can configure TRIRIGA to use Tivoli Directory Integrator as its ETL runtime engine to run ETLJobItems from within TRIRIGA.

Before you begin

Install Tivoli Directory Integrator, if not already installed, on all the TRIRIGA systems that could run a TDI ETL Job Item.  During the TDI install:

• Make note of the installation directory you enter on the Destination panel. You will enter this value later in TRIRIGAWEB.properties.
• Select either installation type. TRIRIGA requires only the TDI Server component.
• When prompted for the location of the Solution Directory, you can select any option. TRIRIGA specifies its own solution directory at runtime.  However selecting the option “Use Install Directory” may simplify troubleshooting.
• Make note of the value you enter in the Server Port field on the Server Port Values Panel. You will enter this value later in TRIRIGAWEB.properties.
• Clear the “Start the Configuration Editor” check box on the Install Complete panel.
• Note: This step is very important for TDI/TRIRIGA integration to work. After you have installed Tivoli Directory Integrator, update it with the recommended fix packs (per TRIRIGA support matrix). TDI must be at least at FP04 (7.1.1.4) or it  will not automatically start the TririgiaETLDispatch.xml assembly line which will result in ETL job items failing to run successfully.

Procedure

1. Edit TRIRIGAWEB.properties file to enable TRIRIGA to manage TDI server.  Set the following properties…
2. Install a JDBC driver library so that Tivoli Directory Integrator can use it to access TRIRIGA database…
3. Edit TDI global.properties file to allow TRIRIGA to check and stop the TDI server from localhost without requiring authentication and authorization certificates. Set the api.remote.ssl.on property to false to tell TDI to trust requests from localhost…
4. Start Tivoli Directory Integrator Agent from TRIRIGA Admin Console and verify that it starts successfully…

[Admin: This post is related to the 08.03.16 post about installing, upgrading, or uninstalling TRIRIGA TDI, and the 05.01.16 post about documentation on developing TDI with TRIRIGA. To see other related posts, use the TDI tag.]

Is there a way to set up the Reserve SMTP Agent for Secure SMTP?

For inbound SMTP messages to the TRIRIGA “ReserveSMTPAgent”, is there any way to configure it for Secure SMTP (SMTPS)? I know the port can change, but can the agent use the SMTPS protocol instead of SMTP, just by changing the port? You have clients using IBM TRIRIGA in the IBM Cloud and they cannot implement your Reservation Management product because of this major gap in the product.

The Reserve SMTP agent does not currently support encrypted TLS communication. Please create a request for enhancement (RFE) to request that capability be added to the Reserve SMTP Agent.

[Admin: To see other related posts, use the SMTP tag or TLS tag.]

IV95382: Agent performance monitors not working properly

In the Admin Console, Performance Monitor, when monitoring a single value, it returns bad results. Returns wrong agent’s status. Returns “false” for an agent status even if the agent is running. Returns “0” for the number of agents running even if multiple agents are running.

We needed to update the calls to various platform status APIs. Moving forward, we updated the Admin Console, Performance Monitor, Single Value to correctly obtain the status of the active agents. This means the AGENTS_RUNNING_COUNT, OBJECT_PUBLISH_AGENT_RUNNING, SCHEDULER_AGENT_RUNNING, and all other agent-related monitors will now correctly report the agent status and count. We also removed the calls to KEYWORD_PARSER_AGENT_RUNNING and WAREHOUSE_AGENT_RUNNING as these no longer exist.

[Admin: This post is related to the 04.08.15 post about performance monitoring tools, and the 02.10.17 post about performance best practices.]

IV95312: Incoming Mail Agent does not properly process Apple emails

When emails are sent to TRIRIGA using Apple’s Mail client, they are not always being interpreted properly on the TRIRIGA end. Some problems include:

• When a rich-text email is sent using Apple’s Mail client and has an attachment, the attachment isn’t seen if the email history is present as part of the email. If the email history is removed, the attachment is seen.
• When a plain text email is sent using Apple’s Mail client and a PNG or JPG attachment is present, the attachment isn’t seen, but the email body has a marker that makes it look like an embedded image should be present (but isn’t).
• When a plain text email is sent using Apple’s Mail client and text exists in the email before the attachment, the text that exists in the email before the attachment isn’t seen. Any text that exists after the attachment is seen.

Apple mail formats the attachments and forwards differently than other mail clients. Moving forward, an issue that cause attachments to be missing and email bodies to lose content when the mail client puts attachments inline, has been resolved.

[Admin: To see other related posts, use the Attachment tag.]

How do you avoid the tree error after deleting hierarchy records?

I’m loading data via the Data Integrator into a Classifications business object. In the first load, my data is successfully loaded. However, I notice some data mapping issues. So I delete the records from a query, then I clear cache. In the second load, my data is successfully loaded. I go into the Classifications hierarchy form and get the dreaded message:

“Please contact your system administrator. The tree control reported this error while trying to draw itself: There was an error in the database or the query definition.”

When this happens, I tell myself that I deleted the records too quickly and didn’t allow the system to reset in time. The solution is the dreaded wait time for the Cleanup Agent to process records that takes 12 hours, 1 day, 3 days, or sometimes 1 week, before all records with a TRIRECORDSTATESY is null, are removed from the database. The only workaround seems to be to increase the Cleanup Agent time. However, is there a sequence of steps I need to follow before I delete records from a hierarchy form, so that I don’t get the dreaded message each time?

Regarding your scenario of loading hierarchy records, deleting them, then reloading the same records to cause the tree control to fail, that should be considered a platform defect. I would advise you to enter a PMR, so Support can look into this issue. The tree control should never fail to render as you describe it.

To help with your issue, there is an unsupported platform feature that allows the Cleanup Agent to delete data immediately. If you add the following property to your TRIRIGAWEB.properties file and set CLEANUP_AGENT_RECORD_DATA_AGE=2, the Cleanup Agent when run will delete records that are 2 minutes old. This allows you to immediately delete a bad data load, and allows you to run it cleanly again a second time without conflicts from that data already existing in a null state.

[Admin: This post is related to the 08.11.16 post about the Organization hierarchy tree not being displayed, the 08.04.16 post about unretiring and returning records to null, and the 02.24.16 post about executing the Cleanup Agent (a.k.a. Platform Maintenance Scheduler) after retiring a record.]