How to Truncate Stage Tables for Oracle Database
Article ID: 10438
Updated On:
Products
Issue/Introduction
The server collects information about completed Applications and stores this information in the relational database. Over time, the history tables can become huge. To create more disk space for the database and improve server performance, regularly move some of the history information in the database tables to stage tables (H_* tables). To move the history information, issue the MOVEHISTORYDATA cli command.
However, if regular cleanup or housekeeping of stage tables is not done, then number of rows in H_* tables can reach millions. In this scenario, a regular delete cannot be an option, since it could take several hours, or even days.
The solution for this problem is to truncate the related tables.
Environment
Resolution
Important Notes:
CA does not encourage customers to modify the dSeries database directly, however, there are situations where direct maintenance on the database is required. These actions shall be performed by a DBA and in consultation with CA support. Performing this action will permanently remove all entries from the stage tables.
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.
1. Open a support issue and get approval from CA support to perform the steps below.
2. Shutdown dSeries (if High Availability is configured, shutdown Standby first to avoid an undesired changerole).
3. Backup dSeries database.
*** If the database is Oracle 11g, please execute the steps below from #4 to #10. If Oracle 12c, go directly to step #11.
4. Execute the below query to obtain a list of SQL statements to disable the foreign key constraints of tables dependent on 'H_APPLICATION' table:
select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINTS '||CONSTRAINT_NAME||';' from user_constraints where R_CONSTRAINT_NAME in (select CONSTRAINT_NAME from user_constraints where table_name IN ('H_APPLICATION','H_GENERIC_JOB') AND CONSTRAINT_TYPE = 'P');
The result of the above query will be similar to the one below:
ALTER TABLE H_AS400_JOB DISABLE CONSTRAINTS SYS_C0011176;
ALTER TABLE H_SAP_JOB DISABLE CONSTRAINTS SYS_C0011177;
ALTER TABLE H_BDC_JOB DISABLE CONSTRAINTS SYS_C0011178;
ALTER TABLE H_SPDA_JOB DISABLE CONSTRAINTS SYS_C0011182;
ALTER TABLE H_BWIP_JOB DISABLE CONSTRAINTS SYS_C0011179;
ALTER TABLE H_BWPC_JOB DISABLE CONSTRAINTS SYS_C0011180;
ALTER TABLE H_SPPM_JOB DISABLE CONSTRAINTS SYS_C0011181;
ALTER TABLE H_PEOPLESOFT_JOB DISABLE CONSTRAINTS SYS_C0011183;
ALTER TABLE H_VIRTUAL_JOB DISABLE CONSTRAINTS SYS_C0011184;
ALTER TABLE H_FILEMONTR_JOB DISABLE CONSTRAINTS SYS_C0011185;
ALTER TABLE H_GENERIC_JOB DISABLE CONSTRAINTS FK_H_APPL_GJ_1;
5. Execute these ALTER TABLE statements above in the order they appear.
6. Execute the query below to verify the constraints have been disabled:
select table_name, CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS from user_constraints where STATUS='DISABLED';
7. Execute the truncate statements below in the order they appear:
Example:
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;
8. On completion of step #7, copy the 'ALTER TABLE' statements from Step #4 and modify it to have 'ENABLE' in the place of 'DISABLE'. Execute these 'ALTER TABLE' statements to enable the constraints that were disabled in Step #4.
Example:
ALTER TABLE H_AS400_JOB ENABLE CONSTRAINTS SYS_C0011176;
ALTER TABLE H_SAP_JOB ENABLE CONSTRAINTS SYS_C0011177;
ALTER TABLE H_BDC_JOB ENABLE CONSTRAINTS SYS_C0011178;
ALTER TABLE H_SPDA_JOB ENABLE CONSTRAINTS SYS_C0011182;
ALTER TABLE H_BWIP_JOB ENABLE CONSTRAINTS SYS_C0011179;
ALTER TABLE H_BWPC_JOB ENABLE CONSTRAINTS SYS_C0011180;
ALTER TABLE H_SPPM_JOB ENABLE CONSTRAINTS SYS_C0011181;
ALTER TABLE H_PEOPLESOFT_JOB ENABLE CONSTRAINTS SYS_C0011183;
ALTER TABLE H_VIRTUAL_JOB ENABLE CONSTRAINTS SYS_C0011184;
ALTER TABLE H_FILEMONTR_JOB ENABLE CONSTRAINTS SYS_C0011185;
ALTER TABLE H_GENERIC_JOB ENABLE CONSTRAINTS FK_H_APPL_GJ_1;
9. Execute the query below to verify there are no constraints in disabled state. It should return 0 records:
select table_name, CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS from user_constraints where STATUS='DISABLED';
10. Start dSeries
Steps only for Oracle 12c:
11. Execute the truncate statement below:
truncate table H_APPLICATION cascade;
12. Start dSeries
Feedback
