Upgrading › Prepare for the Upgrade › Prepare the Catalog Database and the DBMS › Optimize the System Change Detail Tables

Optimize the System Change Detail Tables

The System Change Detail tables are important for auditing and database performance. You can optimize the System Change Detail tables by eliminating redundant or obsolete records. This procedure helps optimize the performance of the database and CA Service Catalog.

Follow these steps:

1. Determine the number of each type of record, as follows:
   1. Enter the following command to determine the number of useful records:

      select count(*) from usm_system_change_detail where old_value!=new_value
      

   2. Enter the following command to determine the number of obsolete records:

      select count(*) from usm_system_change_detail where old_value=new_value
      

   Complete the remaining steps if a significant number of obsolete records exist, especially If more obsolete records than useful records exist. Otherwise, skip the remaining steps and finish preparing the Catalog database and the DBMS.

2. Enter the following command to filter the useful records from usm_system_change_detail table into a temporary table:

   Select * into usm_system_change_detail_temp from usm_system_change_detail where old_value!=new_value 
   

3. Drop usm_system_change_detail table, as follows:
   1. Back up the indexes and database constraints for the following tables:
      • usm_system_change_detail
      • usm_system_change_detail_ext

      Note: For detailed instructions to perform the backup, see your DBMS documentation. For a sample script that restores indexes and constraints, see the sample script for SQL Server that follows these steps.

   2. Drop the foreign key constraints for the usm_system_change_detail_ext table.
   3. Drop the usm_system_change_detail table.
4. Rename the usm_system_change_detail_temp table to usm_system_change_detail.
5. Use the script that you created in Step 3a to restore the indexes and database constraints for the following tables.
   • usm_system_change_detail
   • usm_system_change_detail_ext

Sample Script

The following sample script applies to SQL Server. This script backs up indexes and database constraints. You can use this script as a model for backing up the indexes and database constraints in Step 3a.

ALTER TABLE [dbo].[usm_system_change_detail]
ADD CONSTRAINT [XPKusm_system_change_detail] PRIMARY KEY CLUSTERED 
(
[id] ASC,
[name] ASC
)
WITH 
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, 
ONLINE = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON
) 
ON [PRIMARY]

ALTER TABLE [dbo].[usm_system_change_detail] 
WITH CHECK ADD CONSTRAINT [$usm_s_r00002c5700000000] FOREIGN KEY
(
[id]
)
REFERENCES [dbo].[usm_system_change] ([id])

ALTER TABLE [dbo].[usm_system_change_detail]
CHECK CONSTRAINT [$usm_s_r00002c5700000000]
ALTER TABLE [dbo].[usm_system_change_detail_ext]
WITH CHECK ADD CONSTRAINT [$usm_s_r00002c6100000000] FOREIGN KEY
(
[id],
[name]
)
REFERENCES [dbo].[usm_system_change_detail] ([id], [name])

ALTER TABLE [dbo].[usm_system_change_detail_ext]
CHECK CONSTRAINT [$usm_s_r00002c6100000000]

You have optimized the System Change Detail tables. Next, finish preparing the Catalog database and the DBMS.