Automic Workload Automation

Expand all | Collapse all

DB Maintenance with Automic utilities

Legacy User

Legacy UserApr 21, 2017 08:56 AM

Anon Anon

Anon AnonApr 21, 2017 09:37 AM

  • 1.  DB Maintenance with Automic utilities

    Posted Jul 21, 2016 09:13 AM
      |   view attached
    Database maintenance is quite the hot topic in an Automation Engine environment, as the system's performance is highly dependent upon the DB's response time.
    Maintenance has to be performed with Automic utilities DB Archive, DB Reorg and DB Unload on a regular basis.

    A/ Principle

    1 - Reorganized data

    The Automic DB contains a lot of data related to jobs processing and object versions. Here is the list of tables that are affected by maintenance:
    • MELD :contains messages [visible in the message window of the GUI].
    • RH and RT :contain all information related to execution reports.
    • AH : contains statistics.
    • OH : Contains all the objects (JOBS, EVNT, USER, CLNT, ...) and their versions [visible in the documentation tab]
    • XAO :Records the history of modifications against objects, which can consulted through theRevision Report.
    2 - Automic Utilities

    B/ How it works

    1 - Adjusting the retention period

    • With utilities (DB Archive / DB Reorg) in graphical mode:
    bhv7zrho36dc.png
    jkoqj8vjj8me.jpg

    2 - Maintenance workflow

    Preferred way of maintaining the DB is to use a workflow that will run utilities one after the other. An example template (v10/v11 compatible) is attached to this post. Please feel free to import it and use it in your environment.

    Contents:
    • VARA.SQLI.CLIENT.LIST: lists all clients.
    • JOBP.FOREACH.ARCHIVE.REORG: recursively runs DB Archive and DB reorg on all clients.
    • JOBP.MAINTENANCE: contains the above workflow + unload job.
    • JOBS.WIN.ARCHIVE
    • JOBS.WIN.REORG
    • JOBS.WIN.UNLOAD

    C/ FAQ

    Q : How far back should be records kept?
    A : It totally depends on the amount of tasks run in your environment, more specifically the number of records generated by tasks and stored in the DB. 30 days is usually a pretty safe option.

    Q : Are all maintenance steps mandatory? Or is there a way of speeding up the maintenance?
    A : Only Reorg and Unload are necessary to remove entries from the DB. DB Archive makes backup copies but does not remove anything.

    Q : How often should maintenance be performed?
    A : The more often you will run it, the fewer time it will take. As mentioned before, it will depend on your environment. Once a week is generally sufficient.

    Q : How does maintenance impact the overall performance while being run? Should a downtime be planned?
    A: Maintenance can take up to several hours if it's not run on a regular basis. DB Unload is usually the longest taking one, as it performs DELETE statements against the DB.

    Q : What are the risks of not maintaining a DB with the utilities?
    A : Performance will keep getting worse, until the Automation Engine becomes totally unavailable.

    Q : Aside from maintenance with the utilities, should anything else be performed on the DB side?
    A :  Rebuilding indexes and shrinking the DB / tables can also be performed on a regular basis. These should be done by a DBA.

    Attachment(s)

    xml
    maintenance_workflow.xml   32 KB 1 version


  • 2.  DB Maintenance with Automic utilities

    Posted Jul 21, 2016 09:16 AM
    This post also contains a lot of useful information related to DB maintenance :

    https://community.automic.com/discussion/763/sample-database-maintenance-workflow-for-v9

    Please don't hesitate to share your feedback and / or experience with DB maintenance!



  • 3.  DB Maintenance with Automic utilities

    Posted Aug 08, 2016 08:41 AM
    Please be aware that there is another option for DB Maintenance, namely Information Lifecylc Management.
    It is available for MS SQL and Oracle and uses DB partitioning, the partitions represent periods of time and are switched in and out accordingly. This produces less load on the system and it may become a necessity to use this option if your system grows too big. (e.g. some systems would need more than 24 hours to reorganize and unload the data of 24 hours)

    For ILM details please refer to:
    http://docs.automic.com/documentation/AE/11.2/english/AE_WEBHELP/help.htm?product=awa#ucacqa.htm?Highlight=ILM




  • 4.  DB Maintenance with Automic utilities

    Posted Aug 15, 2016 08:28 AM
    FYI there is also additional steps if you are using Audit to extract a copy for external auditors or legal constraint.

    Do not forget to do also the houskeeping into the Agent temp and output directories as some files may be left forever in it and number of files grows until file system is full or accessing directory takes ages. huge impact on Agent performance if you have tens, or even hundreds, of thousands file entries.


  • 5.  DB Maintenance with Automic utilities

    Posted Aug 18, 2016 04:27 AM
    Additional Info:
    Please do not forget to run Maintenance Jobs for Client 0 as well - here are the AE WP and CP logs stored as report (RT + RH Tablw) that can became quite huge if not reorganized.


  • 6.  DB Maintenance with Automic utilities

    Posted Apr 21, 2017 08:27 AM
    DharmeshPatel604091
    May I ask why you downvoted my add - was anything wrong with it?


  • 7.  DB Maintenance with Automic utilities

    Posted Apr 21, 2017 08:56 AM

    It happened by Mistakenly....  I Liked it now



  • 8.  DB Maintenance with Automic utilities

    Posted Apr 21, 2017 09:37 AM
    oh thanks :-)


  • 9.  DB Maintenance with Automic utilities

    Posted May 23, 2017 10:47 AM

    You Asked

    Do we need to change the settings defined in UC_UTILITY_ARCHIVE or UC_UTILITY_REORG now that we are using ILM?

    And we said …

    You can keep the settings how it is. If ILM is active the UC_UTILITY_ARCHIVE will ignore the other settings.

    But you need to execute UC_UTILITY_ARCHIVEand UC_UTILITY_REORG to maintain object versions and deleted objects.

    Below a short summary...

    UC_ILM_SETTINGS - Settings for Partitioning with ILM

    This variable is supplied in system client 0000. Its settings apply to the whole AE system and can only be modified in client 0000. 

    It is still necessary to run the utilities to reorganize object versions and deleted objects as they are not covered by partitioning with ILM.

    Object versions = version management

    Version Management can be used to track of changes that are made to objects. Using it duplicates objects each time they are modified. The copy is then available in the Version Management folder. Additionally, the Documentation tab of each object includes a list of all object versions.

    The administrator can activate Version Management for each client in the variable UC_CLIENT_SETTINGS using the entry "Y" in the validity key VERSION_MANAGEMENT.

    The administrator can also specify that Version Management is automatically activated when the administrator turns on the logging for the Revision Report. Further information is provided in the Administration Guide, utility AE.DB Revision Report.

    The administrator can limit the number of objects that are displayed in the Version Management using the entry VERSIONS_SHOW_MAX in the variable UC_SYSTEM_SETTINGS.

    Deleted objects = recycle bin

    Deleted objects that have been archived using the utility AE DB Archive are not available in the Recycle Bin.

    Note: that reorganizing the database also includes reorganizing the objects that are in the Explorer's Recycle Bin, statistical data and the corresponding reports.



  • 10.  Re: DB Maintenance with Automic utilities

    Posted Jul 19, 2018 09:52 AM

    Sorry for the novice question. How can we open the AE DB Archive/Reorg interfaces. We've ucybdbar.sh and ucybdbre.sh. Should I just run them or is there any other way?



  • 11.  Re: DB Maintenance with Automic utilities

    Posted Jul 19, 2018 02:54 PM

    hi

     

    you can run the jar files as well...

     

     

    cheers, Wolfgang



  • 12.  Re: DB Maintenance with Automic utilities

    Posted Oct 15, 2018 10:45 AM

    Dear community,

     

    We had 2weeks ago (6.10.18) a problem with our Automic database. (If you need more details just ask.)
    Regarding to this problem our Automic reorganization workflow failed. 

     

    My Questions is now do I have to start the whole workflow or is it enough to just catch up (restart) the unload Job?

    Our workflow is just set up in this construction we have 3 clients.

     

    Automic reorganization workflow

    1. archive Job --> Job which execute UCYBDBAR.EXE -B -S00XX - (3 time - one job per client) XX -placeholder client number

    2. reorg Job --> Job which execute UCYBDBRE.EXE -B -S00XX - (3 time - one job per client) reorg Job

    3. unload job --> Job which execute UCYBDBUN.EXE -BREORG - one job for all 3 clients

     

    Also beloging to this workflow two  variables

    1. UC_ULTILTY_ARCHIVE 

    2. UC_ULTILITY_REORG

     

    Does the values in the variables need to be Change before I catch up the last unload job? I just affraid if forgot to set up anything the reorganisation which put out inconsistant data files.

     

    This workflow runs every saturday at 4pm. The last run I skipped because of the issue I mention. 
    I hope somebody can help me because I got the link from this disscution from the Automic Support.

     

    Thank you and best regards,

     

    Thinh Nguyen Van - MDM Braunschweig



  • 13.  Re: DB Maintenance with Automic utilities

    Posted Oct 15, 2018 02:08 PM

    You can run just the unload job or restart it from the beginning. The Archive and Reorg are just setting a database flag so the unload knows which records to remove.

     

    As this was two weeks ago, I would assume that the successful unload from last Saturday took care of all the flagged records. If the unload ran on 13.10.18 I wouldn't worry about the run from the 6th.



  • 14.  Re: DB Maintenance with Automic utilities

    Posted Oct 16, 2018 03:16 AM

    Hello Michael,

     

    thanks for Response. The facts are that I skipped the last workflow run on last saturday (13.10.18) because I wasnt sure If there isnt anything to fix. 


    Question 1:
    If I would start the only the unload Job so i think he would just handle the flags from 6.10.18 because there wasnt a run last saturday.


    Question 2:
    If this saturday (20.10.18) the whole worklfow runs would it also handle/set flags to the old files from 2 weeks ago (the skipped staurday) or is this period to far? 
    I think that can be set up in this variables right? 
    Could you tell me if the variables are Set up correctly so it would catch the needed period of time?

    Best regards,

     

    Thinh Nguyen Van



  • 15.  Re: DB Maintenance with Automic utilities

    Posted Oct 16, 2018 12:28 PM

    Question 1:

    Yes, if you run just the unload job it will remove records from the Archive/Reorg run on 6.10.

     

    Question 2:

    If you just run the whole workflow on 20.10 it will flag everything older than 120 days. So yes, everything that was flagged before will still be included as it meets this criteria.



  • 16.  RE: DB Maintenance with Automic utilities

    Posted Jun 30, 2023 11:22 AM

    Hello everyone,

    We have been on Automic AWA version 12.3 for about 2 years now and we're finally getting around to setting up our database maintenance tasks for Automic, but because we've been operating for 2 years without purging, we have quite a bit of data that needs to be caught up. When I try to run the reorg on one of our clients, it takes a very long time to run. Is there a good method for incrementally purging past data to catchup before we start running this on an ongoing basis?

    Thanks!




  • 17.  RE: DB Maintenance with Automic utilities

    Posted Jul 01, 2023 02:21 AM

    I came into a company that hadn't run their Database maintenance successfully in 7 years and their DB was in the terabytes, after I fixed their maintenance jobs, the jobs were still timing out, so I came up with a band aid solution. 

    I ended up setting the ucybdbre.ini, auto_Reorg = 2500, ran the Database maintenance (7 years X365=2555)

    Then kept decreasing the increments by 60 days if it didn't time out (30 days if it did) and running the Maintenance until I got down to 365. 

    I checked the counts to verify they are going down after each DB maintenance run as well. 

    SELECT DATEPART (year, AH_TIMESTAMP1), count (ah_idnr) from ah where ah_deleteflag=0

    group by DATEPART(year, AH_TIMESTAMP1)

    Took a long time, but it worked.  Good luck.