Management

 View Only
  • 1.  What is the recipe to prune historical data in REPORTER database?

    Posted 17 days ago
    Hi,

    My REPORTER (DB2) database from NOI deployment seems to grow larger and larger filling up my disk space. What is the way to prune (and possibly make it an automatic regular task) historical data in REPORTER database and release disk space? Can't find any best practice.

    regards,
    Szymon

    ------------------------------
    Szymon Trocha
    Poznańskie Centrum Superkomputerowo-Sieciowe
    ------------------------------


  • 2.  RE: What is the recipe to prune historical data in REPORTER database?

    Posted 17 days ago
    Edited by john postoyko 17 days ago

    Hi Szymon

    Everyone's use and needs for archiving are different- I would start with asking

    Why you are archiving the events ?

              are there regulatory mandates for you to keep all events for a specific period of time  ?

             are there company rules that state you have to keep events for a period of time ?

             or are you archiving for some other reason ?

    This should enable you to determine the maximum time period you need to keep them for.

    Then figure out how to measure the age of an event - FirstOccurrence or LastOccurrence again that will be relevant to why you are archiving them.

    Next review which events are being sent to the database - each and every event - or just events that are important to you ?

    Are there events that are being archived for which there is never any subsequent query from the database ? if so you may want to configure the gateway not to archive such events .

    When you have defined which events are important and how long you need to keep them you can then put in place a protocol  to delete from the database where for example events are older than say 120 days and you can add to that, filters to act on specific classes of events.

    The mode of the reporter database also influences the size of the database

    Is your reporter database running in Reporter or Audit mode ? if it is Audit Mode review if you absolutely need to run in that mode .

    Are you archiving from collection layers and or aggregation ?

    In addition to event data - journal and details information is also often archived - you may determine that you may or may not need to store this additional information and so may elect to remove it from the database

    Here is a link to a support page that may help

    https://www.ibm.com/support/pages/remove-redundant-data-reporter-schema-database

    Hope that helps

    John



    ------------------------------
    john postoyko
    IBM
    London
    ------------------------------



  • 3.  RE: What is the recipe to prune historical data in REPORTER database?

    Posted 17 days ago
    Hi John,

    Thank you for your reply. I will try to answer your points to provide better picture:

    • The simplest answer for a reason why I'm archiving events is beacuse I'm using Event Analystics feature of NOI so it's required
    • General assumption would be to keep them for the last 12 months
    • All events are sent now to db
    • I don't know if there are events that are being archived for which there is never any query because again it's for analytics so I'm observing all events that happen in the network
    • About the protocol you mention. That's the bit I'm missing. How do I delete old events and release free space? Are there any scripts (run on-demand or cron) provided by NOI or best practicies? I don' see any particular tools or settings to help maintaining REPORTER database. But documentation is huge so maybe I just can't find it.
    • I'm running in Reporting mode as per NOI configuration documentation for Gateway for JDBC
    • It's a single layer rchitecture of Omnibus

    Hope it helps.

    Regards,
    Szymon

    ------------------------------
    Szymon Trocha
    Poznańskie Centrum Superkomputerowo-Sieciowe
    ------------------------------



  • 4.  RE: What is the recipe to prune historical data in REPORTER database?

    Posted 17 days ago
    Edited by john postoyko 16 days ago

    Hi Szymon

    Event Analytics: are you referring to the Impact Based Event Analytics or CNEA ?

    For how to delete :

     BP guideshttps://www.ibm.com/docs/en/noi/1.6.3?topic=developer-best-practices

    Specifically this is taken from the Netcool OMNIbus BestPractice guide

    https://www.ibm.com/docs/en/SSTPTP_1.6.3/noi-pdfs/IBM_Netcool_OMNIbus_8.1_Best_Practices_v1.1-SECOND-EDITION.pdf

    Housekeeping historical event archive data 
Periodically a database administrator will need to purge old historical event data from the historical archive database. The following sample SQL shows how to delete data from the historical event archive that is older than 60 days for both DB2 

    The three key tables are the reporter_status, reporter_journal and reporter_details tables.

    Where the Gateway is running in REPORTER mode, the audit tables that track changes in Severity, Acknowledged state, who the OwnerUID and OwnerGID also need to be cleaned.

    delete from reporter_status where lastoccurrence < CURRENT DATE - 60 DAYS;
    delete from reporter_details where identifier not in (select identifier from reporter_status);
    delete from reporter_journal where chrono < CURRENT DATE - 60 DAYS;
    delete from rep_audit_severity where enddate < CURRENT DATE - 60 DAYS;
    delete from rep_audit_ack where enddate < CURRENT DATE - 60 DAYS;
    delete from rep_audit_ownergid where statechange < CURRENT DATE - 60 DAYS;
    delete from rep_audit_owneruid where statechange < CURRENT DATE - 60 DAYS;

    If you wanted to automate this you chron a job to run a sql script that logged into db2 and issued the above commands

    db2 -td@ -vf  prune.reporter.sql

    then in the file prune.reporter.sql

    CONNECT TO reporter USER db2inst1 USING db2inst1Password @

    delete from reporter_status where lastoccurrence < CURRENT DATE - 60 DAYS@
    delete from reporter_details where identifier not in (select identifier from reporter_status)@
    delete from reporter_journal where chrono < CURRENT DATE - 60 DAYS@
    delete from rep_audit_severity where enddate < CURRENT DATE - 60 DAYS@
    delete from rep_audit_ack where enddate < CURRENT DATE - 60 DAYS@
    delete from rep_audit_ownergid where statechange < CURRENT DATE - 60 DAYS@
    delete from rep_audit_owneruid where statechange < CURRENT DATE - 60 DAYS@

    Personally I would simply connect to db2

    su db2inst1  

    cd

    db2

    connect to reporter

    and then copy and paste the delete commands at db2 mindful that the examples are set for 60 days not 12 months



    ------------------------------
    john postoyko
    IBM
    London
    ------------------------------