Table partitioning

TrueSight Server Automation supports database table partitioning to improve database management functions, such as cleanup, storage, and performance. The partitioning has the following characteristics:

• A separate partition is created for data belonging to each distinct date. 
• The tables are either range partitioned or reference partitioned.
• The existing cleanup solution works in the same way for the tables that are not partitioned. The partitions that contain stale data are dropped using the configured result retention policy. The cleanup log shows the names of dropped partitions. 

Supported databases

TrueSight Server Automation  supports partitioning on the following databases:

• Oracle 12c R1, Enterprise Edition or later
• SQL Server 2012, Enterprise Edition or later

Prerequisites for Oracle

Make sure that the following prerequisites are met:

1. A separate license for Oracle partitioning is purchased, and the partitioning option is enabled. For more information, see Enabling and Disabling Database Options After Installation.

2. The result retention time period is set as follows:
   • You can set different retention time for different job types. However, BMC recommends that you set the same retention time for all job types.
   • An override is not set for the retention time at individual job instance level. For example, at the property dictionary level, global result retention time for all jobs is set to 90 days. It can be overridden at the Compliance job type level to 60 days. However, it cannot be further overridden at individual Compliance job level. For example, for the Compliance Jobs, ComplianceJob_1 and ComplianceJob_2 you cannot set the result retention time to 20 and 30 days respectively.
3. Run the data cleansing utility to get the tables to be partitioned ready for partitioning using following steps:

1. 
   
   1. Connect to the database as the TrueSight Server Automation database user using sqlplus

   2. Run the following command:

      exec lib_partition_install.data_cleansing;
2. Plan the downtime required for partitioning. The downtime varies with the database size. BMC recommends that you perform partitioning in a test environment first, using a copy of the production database to better estimate the downtime window.

3. Ensure that tablespaces have sufficient free space required for partitioning.
   The partition installation utility provides an ability to validate whether the database meets the following prerequisites:

   • Installed version of the database
   • Availability of the free space required to perform partitioning
   • whether partitioning option is enabled

   Do the following to validate prerequisites: 

   1. Connect to the database as the TrueSight Server Automation database user using sqlplus

   2. Run the following command: 

      exec lib_partition_install.validate_env;

      The following output indicates that free space is not sufficient for partitioning.
      

Prerequisites for SQL Server

1. The result retention time period is set as follows:
   
   • An override is not set for the retention time at a job level.
   • An override is not set for the retention time at individual job instance level. For example, at the property dictionary level, global result retention time for all jobs is set to 90 days. It can be overridden at the Compliance job type level to 60 days. However, it cannot be further overridden at individual Compliance job level. For example, for the Compliance Jobs, ComplianceJob_1 and ComplianceJob_2 you cannot set the result retention time to 20 and 30 days respectively.
2. Plan the downtime required for partitioning. The downtime varies with the database size. BMC recommends that you perform partitioning in a test environment first, using a copy of the production database to better estimate the downtime window.

Install table partitioning on Oracle

You can install partitioning for a specific module, such as Compliance, Job Run Event, Patching and RBAC. Following are the steps:

1. Connect to the database as the TrueSight Server Automation database user using sqlplus

2. Run the following command:

   exec lib_partition_install.install_<module_name>

   In the command, <module_name> can have the following values:

   Module	module_name
   Compliance	compliance
   Job Run Event	job_run_event
   Patching	patching
   RBAC	rbac

   For example, to partition the Compliance module, run the following command:

   exec lib_partition_install.install_compliance;

   If partitioning is successful, the command output looks like the following:
   
   

Install the table partitioning on SQL Server

You can install partitioning for a specific module, such as Compliance, Job Run Event, Patching and RBAC. Following are the steps:

1. Connect to the database as the TrueSight Server Automation database user.
2. Run the following command:

In the command, <module_name> can have following values:

For example, to partition the Compliance module, run the following command:

If partitioning is successful, the command output looks like the following:

Partitioned tables

Module wise list of partitioned tables:

After you install partitioning (Oracle)

The scheduled database cleanup process drops stale partitions. Once the partitions are dropped, global indexes on the partitioned tables have orphan entries referring to the data in the dropped partitions. You can remove these orphan entries using any of the following methods:

• Schedule the database cleanup. For details, see Scheduling automatic data cleanup.

• Use the following BLCLI command to recreate the indexes marked with orphan entries:

  Delete cleanupHistoricalData UpdateGlobalIndex

  For more information, see Delete - cleanupHistoricalData.