Hi CA Guys,
I have a doubt about CA UIM Database.
Is possible to create a report about the growth of ca uim database per customer or per hub ?
I want to know how much the nimsoft database is growing per day and, if possible, will be very interesting to know the percent of growing by secundary hubs (customers).
Have you tried using DB probes to achieve ? What is the DB flavor ?
We are using a MS SQL Server 2012, Standard, and we are monitoring the CA UIM Database (NIS) with the probe SQL Server, but with this probe we only have data about database size, database growth, etc. But what we need is a level more of details, e.g:
Database: CAUIM - NIS
Disk E: 1.2TB
Database Size 1.1TB
Customer 001 - Hub 001 - Database Usage 11.4%
Customer 002 - Hub 002 - Database Usage 13.4%
Customer *** ...
We want to know the percent fo usage of a database by secondary hub.
Here is an idea.
I would not say it would be perfect, but give you some rough assumption.
S_QOS_DATA table has list of QoS data in UIM database.
The table has "origin" field.
You can estimate percentage by this.
(The number of entries with specific origin) / (Total number of entries in S_QOS_DATA table)
Hello Jean, such a tool or reporting option does not exit in the current product. You may raise an idea in this community to get this reviewed by product management and considered for a future release.
Is possible to execute the following query and configure a filter in Excel.
SELECT qd.origin as CLIENTE, s.Name AS SchemaName, t.NAME AS TableName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, ( SUM(a.total_pages) * 8 ) / 1024 AS TotalSpaceMB, (( SUM(a.total_pages) * 8 ) / 1024)/1024 AS TotalSpaceGB, SUM(a.used_pages) * 8 AS UsedSpaceKB, ( SUM(a.used_pages) * 8 ) / 1024 AS UsedSpaceMB, (( SUM(a.used_pages) * 8 ) / 1024) /1024 AS UsedSpaceGB, ( SUM(a.total_pages) - SUM(a.used_pages) ) * 8 AS UnusedSpaceKB, ( ( SUM(a.total_pages) - SUM(a.used_pages) ) * 8 ) / 1024 AS UnusedSpaceMB, (( ( SUM(a.total_pages) - SUM(a.used_pages) ) * 8 ) / 1024)/1024 AS UnusedSpaceGB, GROUPING(t.Name)FROM sys.tables t INNER JOIN sys.schemas s ON s.schema_id = t.schema_id INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id --LEFT OUTER JOIN S_QOS_DEFINITION q ON t.name = q.r_table INNER JOIN S_QOS_DEFINITION q ON t.name = q.r_table INNER JOIN S_QOS_DATA qd ON qd.qos_def_id = q.qos_def_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 --AND t.NAME ='RN_QOS_DATA_0098'
GROUP BYqd.origin,s.Name, t.Name, p.Rows WITH ROLLUP ORDER BY s.Name, t.Name