Spring cleaning is the practice of thoroughly cleaning a house in the springtime. The practice of spring cleaning is especially prevalent in climates with a cold winter.

The most common usage of spring cleaning refers to the yearly act of cleaning a house from top to bottom which would take place in the first warm days of the year typically in spring, hence the name. However it has also come to be synonymous with any kind of heavy duty cleaning or organizing enterprise. A person who gets their affairs in order before an audit or inspection could be said to be doing some spring cleaning.

In this series we will go through the maintenance tasks that all Agresso customers should consider implementing to optimise the performance of their systems. Some of the series will concentrate on the more technical aspects of Agresso, whilst others can be implemented within the Agresso application. If you have any suggestions or feedback then please leave a message for us in the comment section.

Report Clean-up

Have you been in the “Maintenance of Ordered Reports” screen in Agresso and pressed F7 (search) without any search criteria? Then, when your report has not completed continuously pressed F7 until the status changes to “T”?

On a newly implemented system this will not place too much load on any size of database server. However, after a few years of Agresso usage this list can get very large. When you press F7 thousands upon thousands of rows could be returned each time; even with the relevant search criteria the query still has to go through large volumes of data to return your status ‘N’ records. Previously the General Ledger fuelled the largest table in the database, however without the proper housekeeping this is often dwarfed by the size of tables recording server process logs and outputs (which are held in the database as BLOBS).

There are also times when a super user will need to go to the “Server Logging” or “Report Results” directory. As a super user, have you ever seen windows explorer hang when opening this folder? If you have (and the QTC team certainly have!) this is because there are thousands of small log files which Windows Explorer is trying to retrieve and sort. This often sends the business server’s CPU to 100% utilisation, affecting Agresso performance for every user.

The good news is that this is easily avoided by implementing a simple maintenance routine that any customer can implement. If you have a maintenance routine in place, might it be a good time to review it?

The benefits of implementing Report clean up routines are as follows:

  1. Reduce load on the database server, freeing up CPU and Memory for other queries
  2. Reduce database size – Extend the lifespan of available disk capacity on servers, reduce time to restore databases to test systems, reduce offsite backup costs, etc.
  3. Free up disk space on the Agresso business server due to smaller log store.
  4. Likely general performance increase for across the various server processes.

Below we explore the options available to your organisation, including how they can be mixed and matched to fit your requirements.

The Options

The various clean-up options, their benefits and also their potential pitfalls are as follows:

Option 1: AG57 – Report Deleting

This server process can be found in System Administration>Reports.

This process can be run manually and also scheduled if required. It has a discreet number of options that can be entered to target specific reports for deletion and these can be used at your own discretion. We will focus on the options surrounding the following criteria:

Cleanup by – This has 3 options which are “Days” (number of days to keep reports) , “Run” (the number of a certain type of reports to keep) and “CLEAN_UP” (a system parameter that we will look at later)
Cleanup Values – This is where you specify the actual number of days or versions to keep.

When you run the AG57 process it will go through the tables removing all entries based on the criteria that you have selected and will also delete the associated log files in the Agresso “Server Logging” and “Report Results” directories.

The upside of using this process is that you can manually target reports, users and vary how long you keep different report types.

The downside to this process is that if you have never previously completed any housekeeping (and therefore have a large amount of data), the process can take a considerable amount of time. The key potential risk is that users cannot run reports until the deletion process has completed, or worst case the database log file may become full if not monitored closely during the process. This could lead to Agresso becoming unavailable until the database has been restarted and a roll-back of the transaction has completed. It is for this reason, QTC recommends running the process in stages, or conducting the clean-up outside standard business hours.
More help on the process is available in the Agresso Web Help via F1 in the AG57 Screen.

Option 2: Automating report clean-up

The easiest way to keep on top of your housekeeping is to enable the automatic clean-up of reports within Agresso. This can easily be achieved by activating two Common Parameters in Agresso, either through the Agresso application or via the Agresso Management Console in later versions. The parameters are:

CLEAN_UP – Specifies how long report and log files can be stored, specified by the number of days or by number of versions (for example keeping the last 100 GL07’s). The format for populating this parameter are:

-n – A negative number specifies the number of days for reports and logs to be held, for example -30 will keep reports for 30 days.
0 – This is a special parameter that specifies that reports are retained for 7 days.
n – A positive number specifies the number of reports and logs to be held, for example 50 will keep the last 50 reports of each type i.e. GL07, CS15, etc.

Note: If you decide to use -n number of days, this starts counting the number of days from when you activate the parameter. Reports prior to the parameter being activated may not have this counter activated and some reports may need to be manually cleansed via the AG57 process.

AUTO_DELETE – This parameter will need to be activated for the automatic report clean-up to take place. It uses the values set in CLEAN_UP to determine how many reports to keep. Activating this parameter adds an extra step in a server process which enables it to clean up its own report type. A real life example of this would be a PO01 running to print purchase orders. At the end of the process an extra step is included to specifically clean-up the PO01 reports; it will not try to clean up other processes. This can be confusing, as many users often feel that the process is not working correctly at first.

This method is likely to the best option for most organisations, but if you are not comfortable deleting these records then simply enabling it with a CLEAN_UP value of -365 (365 days) will be beneficial to your organisation.

The issues that you may face when activating this for the first time are very similar to option 1. If you have not kept on top of your housekeeping it could potentially cause adverse performance when it is initially activated due to the enormous clean-up task that it must initially undertake.

Mitigating Risks

The only real risk involved in using these options is if you have a large volume of data to cleanse. If your organisation has small processing requirements, you are a relatively new Agresso customer or currently still at the implementation stage then you will most likely avoid any adverse effects. Note: For those who have not previously done any housekeeping, you should definitely carry on reading the next section!

TEST, TEST, TEST

Whichever option you decide to employ, you should enable this on a Test or Development server first. This will allow you to monitor how long the process takes to complete and record any failures and possible remedial action that might be required. This test should give you a ‘worst case scenario’ for how long the process will take (remembering that your Production servers are most likely faster than your test servers).

Performance Problems

During your testing, if the system fills up and becomes unresponsive for large amounts of time it may be preferable to delete any reports (that adhere to the criteria specified in the CLEAN_UP parameter) directly from the database. This is often much faster and usually means that your DBA should be involved so that they can monitor the status of the database whilst the process progresses.

Note: This method requires the manual clean up of the “Server Logging” and “Report Results” folders, as these are not handled by the database server.

If you have sufficient disk space, another option is to increase the size of the Agresso log file and TEMPDB. Whilst it is hard to judge what size the log will grow to (depending on your database settings), this effectively safeguards the log file filling up during the process.

Some SQL to Help You Plan

Below is some SQL to help you understand how many server process logs and results you have stored in your database and how much space these occupy. You can use this SQL to target certain reports to delete with a scheduled AG57 for example, or decide if you would like to clean the data as mentioned above.

You will find that the number column seems like it is doubling up, however each server process that runs will have a log and a report output (in most cases) and this accounts for the perceived double counting:

SELECT CASE 
         WHEN ( Grouping(a.client) = 1 ) THEN 'Total' 
         ELSE a.client 
       END 
       AS client, 
       CASE 
         WHEN ( Grouping(a.report_name) = 1 ) THEN 'Total' 
         ELSE a.report_name 
       END 
       AS report_name, 
       Round(( Sum (Cast(c.blob_size AS FLOAT)) / 1024 ), 2) 
       AS total_size_kb, 
       Round(( Sum (Cast(c.blob_size AS FLOAT)) / 1048576 ), 2) 
       AS total_size_mb, 
       Round(( Sum (Cast(c.blob_size AS FLOAT)) / 1073741824 ), 2) 
       AS total_size_gb, 
       Count(a.report_name) 
       AS number, 
       Round(( Sum (Cast(c.blob_size AS FLOAT)) / 1024 ) / Count 
       (a.report_name), 2) AS 
       average_size_kb 
FROM   acrrepord a 
       JOIN acrrepfile b 
         ON b.orderno = a.orderno 
            AND b.report_name = a.report_name 
       JOIN acrprintblob c 
         ON c.blob_id = b.blob_id 
GROUP  BY a.client, 
          a.report_name WITH rollup 
ORDER  BY a.client, 
          a.report_name

Summary

Hopefully this has provided an insight into the importance of cleaning up reports within Agresso and has also demonstrated a number of different ways to achieve it. A well maintained system should provide your organisation with increased performance and lower overheads, as the technical resources required to run the Agresso ERP application are reduced.