Truncating staging tables (H_) in CA Workload Automation for MS-SQL databases

Products

• DSERIES- SERVER
• CA Workload Automation DE - System Agent (dSeries)

Issue/Introduction

The DE server collects information about completed Applications and stores this information in the database. Over time, the history tables can become very large.  To create more disk space for the database and improve server performance, regularly move the history tables data to stage tables (H_* tables).  To move the history information, issue the MOVEHISTORYDATA cli command.  If for some reason the MOVEHISTORYDATA command fails, the H_ tables may need to be cleaned or truncated.

Environment

CA Workload Automation DE 11.3 / R12.x

MS SQL Server (all versions)

Resolution

1.  Shutdown CA WA DE (for High Availability, shutdown Standby then Primary).
2.  Backup CA WA DE database (it is recommended to take full back up of the database).
3.  Execute these SQL statements below to obtain the ALTER statements required to drop the Foreign Key constraint. 

SELECT 'ALTER TABLE ' +  OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT ' + name FROM sys.foreign_keys WHERE referenced_object_id = object_id('H_APPLICATION');
SELECT 'ALTER TABLE ' +  OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT ' + name FROM sys.foreign_keys WHERE referenced_object_id = object_id('H_GENERIC_JOB');

     Note: The following is sample output.  Your output may differ.

ALTER TABLE dbo.[H_GENERIC_JOB] DROP CONSTRAINT FK_H_APPL_GJ_1
ALTER TABLE dbo.[H_AS400_JOB] DROP CONSTRAINT FK__H_AS400_J__JOB_I__628FA481
ALTER TABLE dbo.[H_SAP_JOB] DROP CONSTRAINT FK__H_SAP_JOB__JOB_I__6383C8BA
ALTER TABLE dbo.[H_BDC_JOB] DROP CONSTRAINT FK__H_BDC_JOB__JOB_I__6477ECF3
ALTER TABLE dbo.[H_BWIP_JOB] DROP CONSTRAINT FK__H_BWIP_JO__JOB_I__656C112C
ALTER TABLE dbo.[H_BWPC_JOB] DROP CONSTRAINT FK__H_BWPC_JO__JOB_I__66603565
ALTER TABLE dbo.[H_SPPM_JOB] DROP CONSTRAINT FK__H_SPPM_JO__JOB_I__6754599E
ALTER TABLE dbo.[H_SPDA_JOB] DROP CONSTRAINT FK__H_SPDA_JO__JOB_I__68487DD7
ALTER TABLE dbo.[H_PEOPLESOFT_JOB] DROP CONSTRAINT FK__H_PEOPLES__JOB_I__693CA210
ALTER TABLE dbo.[H_VIRTUAL_JOB] DROP CONSTRAINT FK__H_VIRTUAL__JOB_I__6A30C649
ALTER TABLE dbo.[H_FILEMONTR_JOB] DROP CONSTRAINT FK__H_FILEMON__JOB_I__6B24EA82

 

4. Execute the ALTER TABLE statements generated from step 4 to drop the Foreign key constraints.  Next execute the truncate statements below in the order they appear to truncate the tables. 

TRUNCATE TABLE H_GENERIC_JOB;
TRUNCATE TABLE H_AS400_JOB;
TRUNCATE TABLE H_SAP_JOB;
TRUNCATE TABLE H_BDC_JOB;
TRUNCATE TABLE H_BWIP_JOB;
TRUNCATE TABLE H_BWPC_JOB;
TRUNCATE TABLE H_SPPM_JOB;
TRUNCATE TABLE H_SPDA_JOB;
TRUNCATE TABLE H_PEOPLESOFT_JOB;
TRUNCATE TABLE H_VIRTUAL_JOB;
TRUNCATE TABLE H_FILEMONTR_JOB;
TRUNCATE TABLE H_APPLICATION;

 

5. Execute the ALTER TABLE statements below to recreate the Foreign Key constraints
 

ALTER TABLE H_AS400_JOB ADD FOREIGN KEY (JOB_ID) REFERENCES H_GENERIC_JOB ON DELETE CASCADE;
ALTER TABLE H_SAP_JOB ADD FOREIGN KEY (JOB_ID) REFERENCES H_GENERIC_JOB ON DELETE CASCADE;
ALTER TABLE H_BDC_JOB ADD FOREIGN KEY (JOB_ID) REFERENCES H_GENERIC_JOB ON DELETE CASCADE;
ALTER TABLE H_BWIP_JOB ADD FOREIGN KEY (JOB_ID) REFERENCES H_GENERIC_JOB ON DELETE CASCADE;
ALTER TABLE H_BWPC_JOB ADD FOREIGN KEY (JOB_ID) REFERENCES H_GENERIC_JOB ON DELETE CASCADE;
ALTER TABLE H_SPPM_JOB ADD FOREIGN KEY (JOB_ID) REFERENCES H_GENERIC_JOB ON DELETE CASCADE;
ALTER TABLE H_SPDA_JOB ADD FOREIGN KEY (JOB_ID) REFERENCES H_GENERIC_JOB ON DELETE CASCADE;
ALTER TABLE H_PEOPLESOFT_JOB ADD FOREIGN KEY (JOB_ID) REFERENCES H_GENERIC_JOB ON DELETE CASCADE;
ALTER TABLE H_VIRTUAL_JOB ADD FOREIGN KEY (JOB_ID) REFERENCES H_GENERIC_JOB ON DELETE CASCADE;
ALTER TABLE H_FILEMONTR_JOB ADD FOREIGN KEY (JOB_ID) REFERENCES H_GENERIC_JOB ON DELETE CASCADE;
ALTER TABLE H_GENERIC_JOB ADD CONSTRAINT FK_H_APPL_GJ_1 FOREIGN KEY (APPL_ID) REFERENCES H_APPLICATION ON DELETE CASCADE

6. Start CA Workload Automation DE

Additional Information

NOTE:
CA recommends to not modify the dSeries database directly.  In certain case, direct maintenance on the database may become necessary.  These actions shall be performed by a DBA and in consultation with CA support.

CA also recommends that deleting the data from H_* tables should only be done if the data is no longer needed for any reporting or any other business requirements.  Always, take full backup of the database before making any changes to it.