Automic Workload Automation

 View Only
Expand all | Collapse all

Huge number of records in RT Table - Not getting deleted by Maintenance jobs

  • 1.  Huge number of records in RT Table - Not getting deleted by Maintenance jobs

    Posted Sep 10, 2020 02:37 AM

    Hello Team,

    Our RT Table records are almost 440 million at the moment and the record count is not decreasing even after running the Maintenance jobs on daily basis.

    Below is the summery of job executions on an average:

    Number of executions: 100K (For the last 9 months) and it's around 700k in the last 3 days due to a new release
    RT Table size: Around 40 GB with 420 million records
    RH Table: 15 million records.

    We've modified the DB Archive & Reorg settings to keep only last 30 days statistics and We've disabled the option to keep last 30 versions and re-ran the Maintenance jobs again but there is no improvement in the record size and the records in RH table are not getting flagged to delete.

    Attached are the config files and the latest logs of the Archive, reorg and unload jobs.

    I've also verfied the previous questions from the community but they are of no help to our situation. :(




    ------------------------------
    Bharath B.
    ------------------------------

    Attachment(s)

    txt
    ucybdbun.ini.txt   7 KB 1 version
    txt
    ucybdbre.ini.txt   6 KB 1 version
    txt
    ucybdbun.log.txt   97 KB 1 version
    txt
    ucybdbre.log.txt   23 KB 1 version
    txt
    ucybdbar.ini.txt   9 KB 1 version
    txt
    ucybdbar.log.txt   31 KB 1 version


  • 2.  RE: Huge number of records in RT Table - Not getting deleted by Maintenance jobs

    Posted Sep 10, 2020 05:09 AM
    Hi Bharath,

    please check the archive/reorg/unload logs for client 0 as well.

    regards,
    Peter


  • 3.  RE: Huge number of records in RT Table - Not getting deleted by Maintenance jobs

    Posted Sep 10, 2020 05:43 AM
    Hi Peter,

    We are performing archive/reorg/unload on Client 0 as well.

    I've just ran a query and observed that there are close to 7 million records are there in the RH Table for the last 15-30 days. So, I am thinking probably all the records are valid in the RT Table as well.

    Any thoughts?

    ------------------------------
    Bharath B.
    ------------------------------



  • 4.  RE: Huge number of records in RT Table - Not getting deleted by Maintenance jobs

    Posted Sep 10, 2020 10:28 AM
    Hi Bharath,

    do you know what kind of information is stored in R* tables, i.e. have you run a sql query like this:

    select RH_Client, RH_Type, count(1)
    from RH
    group by RH_Client, RH_Type
    order by RH_Client, RH_Type

    Also interesting to know: how many executions happen per day?
    Did you ever run a health check?
    What about long term statistics of R-tables: did the growth happen recently or can you see a trend in it?

    regards,
    Peter


  • 5.  RE: Huge number of records in RT Table - Not getting deleted by Maintenance jobs

    Posted Sep 10, 2020 11:13 AM
    Edited by Pete Wirfs Sep 10, 2020 11:18 AM
    Make sure your UCYBDBun utility is functioning as desired.

    This is how we run it;
    UCYBDBun -BREORG​

    And this is from our UCYBDBun reports;

    UCUDB: ===> 'delete from RH where RH_AH_idnr in (select DIVDB_PK from DIVDB)'


    If this is running properly, then I would question weather or not you are correctly running the utilities that set the contents of the RH_DeleteFlag and RH_ArchiveFlag values.

    EDIT:********************
    I just looked at your attached UCYBDBun log file, and it looks like you are running it with -B without the "REORG"?


    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 6.  RE: Huge number of records in RT Table - Not getting deleted by Maintenance jobs

    Posted Sep 13, 2020 01:55 AM
    Hi Pete,

    All the records that are available in the tables are from less than a month ago and we are running ucybdbun with -BREORG as the parameter.



    ------------------------------
    Bharath B.
    ------------------------------



  • 7.  RE: Huge number of records in RT Table - Not getting deleted by Maintenance jobs

    Posted Sep 13, 2020 01:51 AM
    Hi Peter,

    I know a bit about the RH & RT Tables.

    We used to run 150k jobs per day until last week. We had a new release and we are running almost 700k jobs per day now.

    I tried to find out more kept "TRUE 28 FALSE 28" for AH & RH and now there are only 193 records in RH which are older than 31 days.

    ------------------------------
    Bharath B.
    ------------------------------



  • 8.  RE: Huge number of records in RT Table - Not getting deleted by Maintenance jobs

    Posted Sep 12, 2020 08:20 PM
    Hi Bharath,

    the log file you've uploaded as DB.Unload is wrong. If I click on the file it gives me the output of Reorg:

    User account (user/domain) <AUTOMIC/>
    Startup parameter <-B -S0500 >
    Application name <ucybdbre>
    Launch from </automic/Utility_1221/bin/>

    regards,
    Peter

    ------------------------------
    Capture Europe
    ------------------------------



  • 9.  RE: Huge number of records in RT Table - Not getting deleted by Maintenance jobs

    Posted Sep 13, 2020 02:13 AM
    My bad, I've uploaded the correct & latest log file now.

    ------------------------------
    Bharath B.
    ------------------------------



  • 10.  RE: Huge number of records in RT Table - Not getting deleted by Maintenance jobs

    Posted Oct 05, 2020 02:26 AM
    Update: We figured out that we are using many PRINT and :PSET statements in our jobs which might be adding up more number of records in the RT Table. We've started taking out the unnecessary :PRINT and :PSET and the number of records are reduced by 50 million in the last 4 days.

    However, I could see that the WP, CP, QUEUE and AGENT exeuction records are not getting deleted even after the specified amount of days from the Archive and Reorg settings. Is there anything else that we need to check to clear these records?

    ------------------------------
    Bharath B.
    ------------------------------