Configuring SQL Tuning Services logging

By default, SQL Tuning Services logs are written to a single file that you must manually maintain. To automate the maintenance of SQL Tuning Services logs, you can enable logging with rollovers.

Before you begin

Consider creating a backup copy of the TMS_HOME/Config/application.properties file before you start the following procedure.

About this task

Logging with rollovers enables you to automatically control the size, age, and number of logs that are retained at any one time. Logs that exceed the criteria that you specify are deleted when a rollover occurs.
Important: When you enable logging with rollovers, separate logs are generated for SQL Tuning Services and the WebSphere® Liberty server, which can make troubleshooting more difficult. Consider enabling logging with rollovers only after you have configured your SQL Tuning Services environment and verified that the environment is stable and operating properly.

Procedure

Complete the following steps to enable logging with rollovers:

  1. Optional: Specify the location to save SQL Tuning Services log files.
    By default, SQL Tuning Services server logs are saved to the wlp_user_dir/servers/server_name directory. If this location is acceptable, skip this step. Otherwise, to specify a different directory, edit the Liberty Server server.env file, and set the WLP_OUTPUT_DIR parameter. You must specify an absolute path. For example:
    WLP_OUTPUT_DIR=/SQLserver/application/logs
    See Configuring the Liberty environment for more information.
  2. Edit the TMS_HOME/Config/application.properties file and uncomment the following parameter to enable logging with rollovers: 
    #logger.ibm.appenderRef.rollingFileInfo.ref=rollingFileInfo
  3. Within the application.properties file, set the following parameters:
    • Set property.ROLLING_INTERVAL to indicate how often a rollover occurs based on the most specific time unit in the date pattern.
    • Set property.MAX_LOG_SIZE to the maximum size that a log file can reach. When a log file reaches this size, a new log file is created, and a rollover occurs.
    • Set property.MAX_HISTORY to the maximum number of log files to retain. When the number of log files exceeds this value, the oldest log file is deleted, and a new one is created when the next rollover occurs. A rollover occurs either when the ROLLOVER_INTERVAL or MAX_LOG_SIZE specification is reached, which means that even though the number of saved log files exceeds the MAX_HISTORY specification, they will not be deleted or archived until a rollover occurs.
    • Set property.MAX_AGE to the maximum number of days that a log file is saved. When the age of a log file exceeds this value, it will be deleted when the next rollover occurs. A rollover occurs either when the ROLLOVER_INTERVAL or MAX_LOG_SIZE specification is reached, which means that even though a log file is older than the MAX_AGE specification, it will not be deleted until a rollover occurs.
  4. Save and close the application.properties file and restart the SQL Tuning Services server.

Example

With the following settings, a rollover occurs every seven days or every time a log file reaches a size of 10 MB. If five log files exist at the time of a rollover, the oldest log file is deleted and a new one is generated. Similarly, any log files that are older than three days are also deleted. If all the log files are older than three days when a rollover occurs, all are deleted regardless of the MAX_HISTORY setting.
ROLLING_INTERVAL=7
MAX_LOG_SIZE=10MB
MAX_HISTORY=5
MAX_AGE=3d