Time to clean up your Workload Automation Event Log table? Here are some hints

This article will explain how to remove old records (tuples) from Workload Automation Event Log table.
 
If you configured your Workload Automation batch scheduling environment with event-based scheduling, defining actions in response to events that occur in your environment, then it’s recommended to clean up your Event Log table on a timely basis depending on your log retention requirement.

​Else, when the Event Log list grows, it takes a long time to load all events from the Event Log table and you might face sluggishness while loading the events in the Dynamic Workload Console through the “Monitor events” widget.
 
To delete the Event records (tuples) from the Event Log table you need to login to the DB2 server as db2user and connect to the Workload Automation database.
 
You could remove the old events from the Event Log table using multiple filters like date and time stamp, event name etc.
 
Below are few examples of db2 queries you could run to housekeep your Event Log table.


DB2 SELECT Queries against Event Log table

1.   DB2 query to display the total number of event records (tuples) in Event Log table.db2 "select count(*) from LOG.LLRC_LOG_RECORDS"

​2.   DB2 query to display the total number of event records (tuples) in Event Log table which are triggered in your event processor after 1st Jan 2019.db2 "select count(*) from LOG.LLRC_LOG_RECORDS where LLRC_TIMESTAMP>='2019-01-01 00:00:00'" 

3.   DB2 query to display the event record (tuples) names which are triggered in your event processor older than 1st Jan 2019.
db2 "select LLRC_TIMESTAMP, LLRC_TEXT_1  from LOG.LLRC_LOG_RECORDS where LLRC_TIMESTAMP<='2018-12-31 23:59:59'"

4. DB2 query to display the event records (tuples) with specific event name 'MONITOR_JOB_FAILURE' in Event Log table.
db2 "select LLRC_TIMESTAMP, LLRC_TEXT_1 from LOG.LLRC_LOG_RECORDS where LLRC_TEXT_1 = 'MONITOR_JOB_FAILURE'" 

5. DB2 query to display the event records (tuples)s with specific event name 'MONITOR_JOB_FAILURE' which are triggered between two dates.
db2 "select LLRC_TIMESTAMP, LLRC_TEXT_1 from LOG.LLRC_LOG_RECORDS where LLRC_TEXT_1 = 'MONITOR_JOB_FAILURE ' AND LLRC_TIMESTAMP between date('2019-01-01 00:00:00') and date('2019-01-31 23:59:59')"

DB2 DELETE Queries against Event Log table

IMPORTANT – Be very cautious while running the DELETE query against the tables. If you wrongly execute the DELETE query, there are high chances that you might lose all your events. Before running any DELETE query, run the SELECT query, make sure you got the appropriate query result, which is required to delete from the table, and then execute the DB2 DELETE query.

1.   DB2 query to remove the event records (tuples) from Event Log table which are older than 1st Jan 2019.
db2 "delete from LOG.LLRC_LOG_RECORDS where LLRC_TIMESTAMP<='2018-12-31 23:59:59'" 

2. DB2 query to delete the event records (tuples) with specific event name 'MONITOR_JOB_FAILURE' in Event Log table.
db2 "delete from LOG.LLRC_LOG_RECORDS where LLRC_TEXT_1 = 'MONITOR_JOB_FAILURE'" 

3. DB2 query to delete the event records (tuples) with specific event name 'MONITOR_JOB_FAILURE' which are triggered between two dates.
db2 "delete from LOG.LLRC_LOG_RECORDS where LLRC_TEXT_1 = 'MONITOR_JOB_FAILURE' AND LLRC_TIMESTAMP between date('2019-01-01 00:00:00') and date('2019-01-31 23:59:59')" 
You could use wild character “%” in DB2 queries
 
DB2 query to display all records (tuples) in Event Log table which starts with event name “MONITOR_JOB*”
 db2 "select LLRC_TIMESTAMP, LLRC_TEXT_1 from LOG.LLRC_LOG_RECORDS where LLRC_TEXT_1 like 'MONITOR_JOB%'"


Here is the shell script that you could run as a batch job to delete the event record(tuple) from the Event Log table periodically as db2user.

You could customize the script as per your requirement.

#/bin/bash
#
#This script removes the old event record(tuple) from event LOG table.
#
#Usage  sciptname.sh -db TWSDBNAME  -retain NUMBER_OF_DAYS
#Example   ./event_record_housekeep.sh -db TWS -retain 80
 
 
#Exporting the DB2 environment variable
export PATH=$PATH:/opt/ibm/db2/V10.5/bin/
 
#Argument Initialization
TWSDB="$2"
Days="$4"
 
#Variable Initialization
Year=$(date --date="$Days days ago" +"%Y")
Month=$(date --date="$Days days ago" +"%m")
Date=$(date --date="$Days days ago" +"%d")
Time="23:59:59"
TillDate="$Year-$Month-$Date $Time"
 
#Connecting to Workload Automation database
db2 connect to $TWSDB
 
#Running query against TWSDB
 
printf "Before deletion - Total number of event record(tuples)available event LOG table \n\n"
db2 "select count(*) from LOG.LLRC_LOG_RECORDS"
 
printf "This query deletes the event record(tuples) from event LOG table till $TillDate \n\n"
db2 "delete from LOG.LLRC_LOG_RECORDS where LLRC_TIMESTAMP>='$TillDate'"
 
printf "After deletion - Total number of event record(tuples)available event LOG table \n\n"
db2 "select count(*) from LOG.LLRC_LOG_RECORDS"
 
Hope you find this article helpful.

For additional information about Event Log table maintenance, you may contact
Madhusudan Raju [email protected].

​

Madhusudan Raju, Senior Technical Specialist

Madhusudan has been working on Workload Automation for 10+ years. He is currently engaged in WA solution on IBM SaaS, focusing mainly on advanced WA solutions through the DevOps delivery model. He is currently associated with HCL Technologies Products and Platforms division as a Senior Technical Specialist.