DX UIM data_engine probe and SQL maintenance jobs best practices
search cancel

DX UIM data_engine probe and SQL maintenance jobs best practices

book

Article ID: 222888

calendar_today

Updated On:

Products

DX Unified Infrastructure Management (Nimsoft / UIM) Unified Infrastructure Management for Mainframe CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

Our DBA team is requesting if the UIM application has the ability where we can disable/turn off your UIM MSSQL "maintenance" jobs that run daily/weekly so they do not conflict with our in-house developed DBA MSSQL maintenance jobs.

Our DBA team would like to manage SQL maintenance type jobs, for example compressing the DB or rebuilding index's.

What is the best practice/recommendation considering this request?

Environment

  • Release: 20.3 or higher

  • Component: UIM - DATA_ENGINE

Cause

  • data_engine administration and maintenance

Resolution

First,

Please review this KB Article:

How does data_engine maintenance and retention work?

The data_engine indexing option was added to the data_engine a long time ago, but in large environments we do not recommend using it.

The data_engine index maintenance option, when enabled and run by the data_engine, only runs on the RN, HN, BN, DN, and VN tables. 

RN = Raw data
HN = Historical data
DN = Daily (data aggregation/summary)
BN = Baseline data
VN = View tables used specifically for CABI

Second,

  • We do NOT recommend disabling the data_maintenance option 100%.
    In the Raw Configuration, "data_management_active" should be set to yes. Otherwise, you would be circumventing recommended best practices when it comes to data maintenance.

  • That said, there may be customers who have worked with their DBAs to disable data maintenance and worked around it by running their own jobs/procedures but this should be well maintained and not overlooked before or after any upgrades and the results should be periodically checked to make sure the UIM database is healthy, currently has enough resources, CPU, Memory, good/fast disk I/O and space allotted for avoiding transaction log full situations.

Third,

In general, we recommend the following:

  • Enable Partitioning (MS SQL Server 'Enterprise Edition' only) for this article but partitioning should be leveraged on Oracle and MySQL as well if that is the backend DB type.

  • Disable the data_engine 'Index Maintenance' option to stop automatic reindexing (run a daily defrag job on select tables instead). In the Raw Configuration of the data_engine this is the same as the data_engine parameter-> auto_reindex = no

IMPORTANT!!!
To enable partitioning, access the data_engine configuration in the Admin Console to turn on partitioning for Microsoft SQL Server Enterprise Edition.

Please refer to and review the following article with your DBA:

Manually partitioning the UIM database for Microsoft SQL Server

Note that based on field experience with large UIM environments/implementations, when you have DB partitioning enabled, there is really NO significant gain in performance and no advantage in defragmentation of the RN, HN, BN, DN, VN etc. table indexes, especially for large to very large tables.

Implementing partitioning is normally sufficient for optimizing DX UIM database performance!

For instance, large tables with high index fragmentation may defrag down to 30-70% fragmentation, but in a few days the fragmentation will likely return, e.g., on the NAS backend tables. As long as you are using partitioning, there is no need to defrag the RN, HN, etc. tables, but...the specific tables listed below MUST be defragged each day (during non-business hours if possible), by running a daily defrag job on them. There are a few options in how to implement this as a job sop consult with your DBA. If you need suggestions, a web search can show you options on how it can be done each day.

Also, please make sure your data_management_timeout parameter in the data_engine is set to 65536 so the jobs can complete (not the default of 7200 which is only 2 hours). 

These are the key tables with indexes that require defragmentation (DAILY). Ask your DBA to set up a job to run this DAILY (off-hours) index defrag job. For example, ask your DBA about scheduling a job to rebuild the table indexes below.

Ensure that a daily job is configured/setup and it was confirmed by the DBA to actually be running each day at the end of the day/off-peak hours, to defragment these key tables listed below.

Note that this list of tables to be reindexed has been updated as of October 2025 so please update your daily job accordingly.

Any serious high % of fragmentation of these tables especially the CM_* and S_QOS_DATA tables in a large environment can adversely affect Operator Console performance.

ALTER INDEX ALL ON CM_COMPUTER_SYSTEM REBUILD;
ALTER INDEX ALL ON CM_DEVICE REBUILD;
ALTER INDEX ALL ON CM_COMPUTER_SYSTEM_ATTR REBUILD;
ALTER INDEX ALL ON CM_DEVICE_ATTRIBUTE REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_METRIC REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_DEFINITION REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_METRIC_DEFINITION REBUILD;
ALTER INDEX ALL ON CM_NIMBUS_ROBOT REBUILD;
ALTER INDEX ALL ON CM_GROUP_MEMBER REBUILD;
ALTER INDEX ALL ON CM_COMPUTER_SYSTEM_ORIGIN REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_ATTRIBUTE REBUILD;
ALTER INDEX ALL ON CM_RELATIONSHIP_CI_CI REBUILD;
ALTER INDEX ALL ON CM_RELATIONSHIP_CI_CS REBUILD;
ALTER INDEX ALL ON CM_RELATIONSHIP_CS_CI REBUILD;
ALTER INDEX ALL ON CM_DISCOVERY_NETWORK REBUILD;
ALTER INDEX ALL ON S_QOS_DATA REBUILD;
ALTER INDEX ALL ON S_QOS_SNAPSHOT REBUILD;
ALTER INDEX ALL ON NAS_ALARMS REBUILD;
ALTER INDEX ALL ON NAS_TRANSACTION_SUMMARY REBUILD;
ALTER INDEX ALL ON NAS_TRANSACTION_LOG REBUILD;

SQL Server Memory Tuning
=======================

Ask your DBA or VMware admin to keep an eye on/analyze MS SQL Server 'memory pressure' over time, e.g., a few months, to see if you need to add some more memory for ‘breathing room' so any/all jobs can complete without issues/hanging.

Lastly,

Compression

Compress data before delete (this is enabled by default in the data_engine):

If selected, compression is done on raw data and copied into historic tables before a delete is performed. And historic data is compressed and aggregated into Daily Data DN tables), before deleting it from the Historical (HN) tables. This is based on the data retention settings you have configured in the data_engine.

Data Maintenance activities

During scheduled maintenance runs, the data_engine probe compresses and aggregates raw data from RN tables into hourly data that is stored in the historic data (HN) tables.

data_engine ‘Compression’ simply means it 'summarizes' or 'rolls up' the data.

An orthogonal configuration that you can set up is to enable MS SQL Server row or page level compression.

This is a DB configuration parameter that can be enabled through the data_engine config in AC, but the process itself is transparent.

  • Older RN data is purged based on a user-defined period (raw data retention setting)
  • Then the DE compresses and aggregates hourly data from HN tables into daily data that is stored in DN tables
  • Older HN data is purged based on a user-defined period
  • The last sample value coming from the probes for each QoS object is collected and populated in the S_QOS_SNAPSHOT table which is used to provide fast QOS data access for the OC portal webapps, views/reports and dashboard data.

Real compression happens during data maintenance. You will see in the tbnlogging table the actual commands that were run to compress data.

Compression mode:
Compression is generally a good thing if you have enough CPUs/processing speed for it.  However, it’s only compressing indexes.

Query to show Table Index compression settings

SELECT  SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName] 
       ,OBJECT_NAME(sys.objects.object_id) AS [ObjectName] 
       ,[rows] 
       ,[partition_number]
       ,[data_compression_desc] 
       ,[index_id] as [IndexID_on_Table]
FROM sys.partitions 
INNER JOIN sys.objects ON sys.partitions.object_id = sys.objects.object_id 
--WHERE data_compression > 0 
           AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS' 
           AND OBJECT_NAME(sys.objects.object_id) = 'RN_QOS_DATA_0001'
ORDER BY SchemaName, ObjectName

Query to show Table compression settings

SELECT [t].[name] AS [Table], [p].[partition_number] AS [Partition],
       [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
WHERE [p].[index_id] = 1
      and [t].[name] = 'RN_QOS_DATA_0001'

If you follow the approach and recommendations above, normally you can go 3+ years without worrying about the data management/maintenance unless for some reason 1 or more jobs started to fail/continued to fail and you weren't aware of it and the tables grew without bounds.

And as always, take a backup/SNAPSHOT of the UIM database before making any significant changes!!!

Powered by Wolken Software