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 2014, Enterprise Edition or later
Prerequisites for Oracle
Make sure that the following prerequisites are met:
- 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.
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.
- Run the database cleanup on the following domains that are relevant to your use cases. For more information, see Cleaning-up-TrueSight-Server-Automation-data.
- Compliance
- Job Run Event
- Patching
- Snapshot
- Run the data cleansing script to get the tables to be partitioned ready for partitioning.
- Obtain the data cleansing script (data_cleansing.sql) from the following directory in the product installation files, TSSA<version>-<platform>64\TSSA<version>-<platform>\Disk1\files\configurations\db_scripts\oracle\utility\partition.
- Run the data cleansing script:
- Connect to the database as the TrueSight Server Automation database user.
Run the following command:
@data_cleansing.sql
- Obtain the partitioning script (install_partitioning.sql) from the following directory in the product installation files, TSSA<version>-<platform>64\TSSA<version>-<platform>\Disk1\files\configurations\db_scripts\oracle\utility\partition.
- 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.
- Ensure that tablespaces have sufficient free space required for partitioning.
The partitioning script provides the CHECK option to validate whether the database meets the following prerequisites:- Installed version of the database
- Availability of the free space required to perform partitioning
Do the following to run the script
- Connect to the database as the TrueSight Server Automation database user.
Obtain the lib_partition.sql script from the TSSA<version>-<platform>64\TSSA<version>-<platform>\Disk1\files\configurations\db_scripts\oracle\utility\partition and run the following command:
@lib_partition.sqlRun the following command:
@install_partitioning.sql CHECKThe following output indicates that free space is not sufficient for partitioning.
Prerequisites for SQL Server
- 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.
- Run the database cleanup on the following domains that are relevant to your use cases. For more information, see Cleaning-up-TrueSight-Server-Automation-data.
- Compliance
- Job Run Event
- Patching
- Snapshot
- 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.
- Obtain the partitioning script (install_partitioning.sql) from the following directory in the product installation files, TSSA<version>-<platform>64\TSSA<version>-<platform>\Disk1\files\configurations\db_scripts\sqlserver\utility\partition.
Running the table partitioning script on Oracle
- Connect to the database as the TrueSight Server Automation database user.
Run the following command:
@install_partitioning.sql FORCEIf partitioning is successful, the command output looks like the following:
Running the table partitioning script on SQL Server
- Connect to the database as the TrueSight Server Automation database user.
Run the following command:
execute install_partitioning.sqlIf partitioning is successful, the command output looks like the following:
Partitioned tables
The following tables are partitioned after you run the partitioning script:
Module Name | Table name | Partition type (Oracle) | Interval | Partition type (SQL Server) | Interval |
|---|---|---|---|---|---|
Job Run Framework | JOB_RUN_EVENT | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date |
DEPLOY_JOB_RUN_EVENT | Reference | Inherits the JOB_RUN_EVENT partitioning scheme | NA | NA | |
Compliance | COMPLIANCE_RULE_RESULT | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date |
COMP_RULE_GROUP_RULE_RESULT | Reference | Inherits the COMPLIANCE_RULE_RESULT partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
COMP_ENTRY_RESULT | Reference | Inherits the COMPLIANCE_RULE_RESULT partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
COMP_ENTRY_RESULT_DETAILS | Reference | Inherits the COMPLIANCE_RULE_RESULT partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
CR_RULE_RESULT_SELECTOR | Reference | Inherits the COMPLIANCE_RULE_RESULT partitioning scheme | NA | NA | |
Patching | PAJOB_RUN_CATALOG_ASSOC | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date |
PATCHING_JOB_RESULTS_ASSOC | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date | |
PATCHING_JOB_RUN_PART | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date | |
PAJOB_RUN_CATALOG_ASSOC | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date | |
PATCHING_JOB_RESULTS_ASSOC | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date | |
PATCHING_JOB_RUN_PART | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date | |
PATCH_RMD_DNL_JOB_RUN_ASSOC | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date | |
PATCH_CUJ_RESULT | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date | |
BL_PATCH_JOB_RESULT_COUNT | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date | |
BL_PATCH_JOB_RES_PATCH_COUNT | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date | |
PATCH_DEPENDENCY_MAP | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date | |
BL_PATCH_ANALYSIS_RES_DATA | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date | |
BL_PATCH_RES_DEVICE_CNT | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date | |
BL_PATCH_DOWNLOAD_RES_DATA | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date | |
PATCH_DOWNLOAD_RESULT | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date | |
PATCH_RMD_RESULT | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date | |
Snapshot | SNAPSHOT_AUTH_DEPLOY_JOB_RUNS | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date |
SNAPSHOT_COMPONENT_STATS | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_PART | Range | Daily - Separate partition for data belonging to each distinct date | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_GENERIC_ASSET | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_SYSTEM_INFO_OBJECT | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_EVENT_LOG | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_DOT_NET_ASSEMBLY | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_COM_OBJECT_CNT | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_METABASE_OBJECT_CNT | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_BLFILE_CNT | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_GPO_SETTING | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_VMWARE_OBJ_CNT | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_CFG_FILE_OBJECT_CNT | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_COM_OBJECT | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_GPO_SETTING_CNT | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_METABASE_OBJECT | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_BLFILE | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_OS_USER | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_OS_GROUP | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_CONFIG_FILE_OBJECT | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_SOFTWARE | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_SERVICE | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_SYS_INFO_OBJECT_CNT | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_REGISTRY_OBJECT | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_REGISTRY_OBJECT_CNT | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date | |
SNAPSHOT_VMWARE_OBJECT | Reference | Inherits the SNAPSHOT_PART partitioning scheme | Range | Daily - Separate partition for data belonging to each distinct date |
After you run the partitioning script (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 UpdateGlobalIndexFor more information, see Delete - cleanupHistoricalData.