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 madhusudan_r@hcl.com.
1 Comment
5/7/2019 10:37:18 am
If you really want to be efficient, then you need to understand how to manage your time. In the professional world, many of us have our own workload. In my opinion, workload may differ depending on what job you have. However, it is all the same to me. There is nothing in this world that you cannot make easier if you properly plan for it. Managing your workload is a very simple thing to do, all you need is the discipline.
Reply
Your comment will be posted after it is approved.
Leave a Reply. |
Archives
August 2023
Categories
All
|