Automic Workload Automation

 View Only
Expand all | Collapse all

RT table increasing

  • 1.  RT table increasing

    Posted Nov 25, 2019 10:38 AM
    ​Hello,

    We are experiencing huge increase in our DB.
    Two months ago we upgraded to version 12.2.
    We keep logs 25 days back. Tho our RT table, increasing insanely (contains more than 280,000,000 rows!!)
    It doesn't make sense and of course, affect our system performance.
    Does anyone knows this issue? Do you know if it is a known bug?

    Many thanks,
    Ella


  • 2.  RE: RT table increasing
    Best Answer

    Posted Nov 25, 2019 10:45 AM
    ​Hi.

    I don't know of any known bug, but RT contains the reports. And they are stored line by line, each line is a record. So if you happen to have a job that writes a lot to stdout, RT can increase quite rapidly. I've had some guy from SAP introduce a job that wrote a few megabytes to stdout. That suddenly caused our RT tables to spike as well.

    You may want to take a look at your RT with SQL, group by RT_AH_IDNR and see if any RT_AH_IDNR comes up with many entries many times (though your RAM or temp partitions may prevent that SQL statement at 280M rows I'm afraid). Then check what object that is (AH to OH relationship). Once you find it, disable the huge report or force it to be written to the agent filesystem.

    Hth,



  • 3.  RE: RT table increasing

    Posted Nov 26, 2019 03:33 AM
    Hello Carsten, 

    Do we have any query to find out which job is creating a lot of load in the RT table ? 

    Thanks and Regards
    Vimalan


  • 4.  RE: RT table increasing

    Posted Dec 01, 2019 12:04 PM
    Hi @Carsten Schmitz,
    First of all thank you for your detailed answer.
    I am familiar with the RT table ​and it's content. 
    your idea of group by RT_AH_IDNR is very useful.
    However because our RT is huge atm (more than 300 milion rows), its hard to run the query on it.
    from what i did manage to see, i see JOBF are writing big logs.
    Since we are working with generic objects, is there a way to decide on a specific run if we wanna keep the log or not?

    Plus, Does anyone of you have the SQL Server queries for DB maintenance? 
    we run DBM on a weekly basis but it seems that lately it doesn't work properly and our DB keeps increasing, which cause us problems.
    We have Many 'Time critical db calls' messages and we're considering to run the DBM directly in the DB.
    Would you recommend it? do you have the SQL queries for each DBM steps?

    Many thanks,
    Ella


  • 5.  RE: RT table increasing

    Posted Dec 02, 2019 03:57 AM
    ​Hi Ella,

    No, sorry, I am not aware of a way to only keep output from specific jobs, decided at runtime (not saying there isn't, maybe it's possible with :PUT_ATT or somesuch, but have never dabbled in this). You could possibly look into more aggressive cleanup settings (with the usual Automic process that marks reports for deletion after x days, then deletes them from the database), or set your job to only store reports on error. But if you are routinely writing large reports, I'd look into somehow truncating them or setting them all to be stored in the Agent machine's filesystem, you can select this in your job object itself.

    I have recently spoken to another client and it seems DB maintenance is very different for customers, depending on which consultant installed it. The other client is calling a stored procedure for his db maintenance. What we do is a job chain consisting of:

    ucybdbar -B -S[clientnumber]
    ucybdbre -B -S[clientnumber]
    ucybdbun -BREORG

    and then we rebuild all the indexes:

    SELECT
        'ALTER INDEX GFD_IS_UC4_O_01.' || index_name || ' REBUILD ONLINE;'
    FROM
        all_indexes
    WHERE
            owner = 'GFD_IS_UC4_O_01'
        AND index_type <> 'LOB'
        AND partitioned = 'NO'
        AND (
                Table_name LIKE 'A%'
            OR  Table_name LIKE 'E%'
            OR  Table_name LIKE 'R%'
            OR  Table_name = 'MELD'
            OR  Table_name = 'OVW'
        );

    and then

    ./ucybdbun -BTRANSPORTALL -C[clientnumber] -X[backup_dir_and_filename] -D

    This marks, reorgs, "unloads", rebuilds indexes and creates a backup by means of transport case respectively.

    This seems to work for us, but I'm not saying this is perfect or even the officially recommended way, you might want to speak to a consultant or the Automic support if in doubt about the "officially recommended" way of db maintenance.

    Hth,


  • 6.  RE: RT table increasing

    Posted Nov 26, 2019 11:13 AM
    hi Ella,

    If your system is using Oracle DB, lower than v12c, you might be impacted by this:

    https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=126369

    Regards,
    Gabor


  • 7.  RE: RT table increasing

    Posted Nov 27, 2019 05:19 AM
    Hi @Gabor Fritz,
    Do you know of a query to check if LOB type is Securefile or Basicfile​ ??

    /Keld.


  • 8.  RE: RT table increasing

    Posted Nov 27, 2019 07:24 AM
    hi Keld,

    this query should give you that info:

    select * from dba_lobs where securefile = 'YES'

    Regards,
    Gabor


  • 9.  RE: RT table increasing

    Posted Dec 02, 2019 11:04 AM

    Please also double check if client 0000 is in the automic reorg definitions. 
    - ucybdbar
    - ucybdbun
    - ucybdbre 

    if you dont need and run archiving of old statistics please change in the ucybdbun.ini and ucybdbar.ini 
    no_Archive_Check=0 to no_Archive_Check=1

    Missing Client 0000 in Reorg or no need to run archiving but forget to change the ini files is often the root cause why A*, R* and MELD Tables are conitously growing.

    If you have allowed SQLI Variables you can define a Variables object what is giving you back automatically all Clients in the System:

    Select OH_Name from OH where OH_OType = 'CLNT' and OH_NAME <> 'CLNT'

    With a for each workflow you can refrence this variables object. It will automaticall publish client to define Jobs for each client defined in your System.

    For example you publish ist as &Automic_Client#

    UCYBDBRE Job flag reords to be delted would look like this:

    uybdbre -B -S&Automic_Client#

    UCYBDBUN Job delting all flaged records:

    ucybdbun -BREORG -C&Automic_Client#

    This workflow you can run on a daily base, to limit the numbers of records deleted in one run. This helps to reduce the load on the db to compare it with a weekly or monthly based execution.

    For sure if the reorg never was running with sucess before, see my comments why this might happen, I would recommend to change the values in the ucybdbre definitions (run ucybdbre or for experts UC_UTILITY_REORG Variables in each client) to higher values (older than days). To reoganice and delete records of a too huge time frame will make a huge load on your DB and might slow down the db repsons time to the Automation Engine. 

    Hope this helps,
    Franz



    ------------------------------
    Managing Consultant Enterprise Studio
    HCL Technologies Austria GmbH
    ------------------------------



  • 10.  RE: RT table increasing

    Posted Dec 02, 2019 04:27 PM
    When I want to know which objects are writing the largest reports to RT, I use this (SQLServer) query.

    Note that I also use ah_timestamp1 as a filter to limit how far back in time it will search.  I use this to speed up the query.

    select oh_name as job_name
         --, ah_timestamp1 as activation_time
         , dateadd(hour, datediff(hour, getutcdate(), getdate()), ah_TimeStamp2) as Start_time
         --, ah_runtime as runtime
         , CONVERT(varchar, DATEADD(ms, ah_runtime * 1000, 0), 114) as runtime
         , count(*) as report_size
    from uc4.dbo.rt
       , uc4.dbo.ah
       , uc4.dbo.oh
    where ah_timestamp1 > cast('20191201 00:00:00:000' as DATETIME) -- how far back in time
    and   ah_oh_idnr = oh_idnr
    and   rt_ah_idnr = ah_idnr
    and   not oh_name in ('APPUTILK', 'AE_PROD#WP001') -- Objects to exclude from the selection
    group by oh_name, ah_timestamp1, ah_timestamp2, ah_runtime
    having count(*) > 500 -- MAX_REPORT_SIZE is set in UC_HOSTCHAR_DEFAULT
    order by 2;​


    ------------------------------
    Pete
    ------------------------------



  • 11.  RE: RT table increasing

    Posted Dec 03, 2019 03:15 AM
    Wonderful. Thank you Pete. 

    I see most of the entries are from Agents and Work processes are occupying the RT table.  Is there anyway we can make sure that the agent and work processes doesnt store its logs in the DB but stores it in the OS level ?


  • 12.  RE: RT table increasing

    Posted Dec 03, 2019 02:26 PM
    UC_SYSTEM_SETTINGS , parameter LOG_TO_DATABASE, set to N  =>  no log of the WP and CP in the Database

    UC_HOSTCHAR_DEFAULT or UC_HOSTCHAR_xxxxxx, parameter LOG_TO_DATABASE, set to N  =>  no log of the agents using this variable as setting is loaded in the database

    Warning : you will have only the history period that is available in the files. If you need to have a longer history you will have to manage archiving and renaming of the files. Number and size of files availables are defined in .ini files (number of files) and the two variables indicated above (size, parameter CHANGE_LOGIN_MB)

    Hope this can help.

    Regards.

    Alain


  • 13.  RE: RT table increasing

    Posted Dec 16, 2019 11:49 AM
    Hi Alain,

    Thank you for your feedback.

    Just an additional question.  With the wonderful script from Pete we noticied that most of the RT tables are occupied by ONLY couple of agents which are used very heavily in our Automic Environment. 
    Hence we would like to turn off the database log logging only for those agents. Can you please give me a short steps on how to do ?

    Thanks and Regards
    Vimalan


  • 14.  RE: RT table increasing

    Posted Dec 21, 2019 08:28 AM
    Hi,

    1 - Create a new UC_HOSTCHAR_BIGLOG (for example) variable in the client 0000. Same folder as UC_HOSTCHAR_DEFAULT, just duplicate this variable.
    2 - Change the parameter LOG_TO_DATABASE to N
    3 - In the UC_EX_HOSTCHAR variable in client 0000 change the value for each Agent that you don't want the log in the DB to BIGLOG instead of DEFAULT.
    4 - Restart Agents so they will use the new parameter in the new Variable.

    Should do the job.

    For any new or other Agent that you don't want the log in the DB, simply change the value in UC_EX_HOSTCHAR to BIGLOG and restart Agent.

    Regards.

    Alain