WCC collects data in the Reporting Database for the use of some services such as QuickView.
The Database can rapidly grow up to a point that it affects performances. Various symptoms may be seen:
- An autosys job just created does not display in QuickView.
- Job statuses seems not accurate (e.g. running instead of success) or are updated some minutes after the job completes etc...
When this happens, we want to go trough some cleanup process.
- The first method is to set Reporting preferences that will ask WCC to go about the cleanup process, at what interval etc..
- The second method is to go about the cleanup manually. Use caution with this method, only use it when the performances are very poor and are hindering business. Do not hesitate opening up a case with support should you need assistance.
First Method:
The Database Cleanup category contains the following fields (see Screen Shot):
- Enable Cleanup: indicates to automatically perform a periodic database cleanup. It is selected by default.
- Cleanup Interval: specifies the number of days between database cleanup activity. The default is 2 days.
- Purge Data: specifies the number of days that the data can exist in the database before being removed during database cleanup. The default is 30 days.
Second Method:
1- Make sure that WCC services are down.
2- run the following SQL statements:
ALTER DATABASE [WCCREP] SET RECOVERY SIMPLE WITH NO_WAIT;
DELETE FROM RPT_AS_JOB_RUN_EVENT_FACT where DATE_ID < 'YYYY-MM-DD ...';
DELETE FROM RPT_AS_JOB_RUN_FACT WHERE START_DATE_TIME < 'YYYY-MM-DD ...'
3- compress the database:
3.1: Oracle:
ALTER TABLE mytable
MOVE PARTITION mytable_part1
COMPRESS;
or:
ALTER TABLE mytable
MOVE PARTITION mytable_part1
COMPRESS FOR ALL OPERATIONS;
Also consult this link:
http://www.oracle.com/technetwork/database/options/compression/faq-092157.html
3.2: Derby
- Exit ij or the jvm accessing the database, and make a tar zip copy of the database, preserving date stamps if possible.
- Do an offline compress of all the tables. Below is some sample code for doing this. To run it, you need
- to compile it (java -cp),
java -cp "<path>\derby.jar";. CompressAll
- then execute it in the JVM:
java compressAll <url>
Where <url> has the following syntax:
jdbc:derby:[databaseName]
Sample Java program:
import java.sql.*;
public class CompressAll {
public static void main(String[] args) throws Exception {
if (args.length == 0) {
System.err.println("Usage: java CompressAll <url>");
System.exit(1);
}
String url = args[0];
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
Connection conn = DriverManager.getConnection(url);
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT SCHEMANAME, TABLENAME FROM sys.sysschemas s, sys.systables t WHERE s.schemaid = t.schemaid and t.tabletype = 'T'");
CallableStatement cs = conn.prepareCall ("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)");
while (rs.next() ) {
String schema = rs.getString(1);
String table = rs.getString(2);
System.out.println("Now compressing " + schema + " " + table);
cs.setString(1,schema);
cs.setString(2,table);
cs.setShort(3, (short) 1);
cs.execute();
}
// finally shutdown the database
try {
DriverManager.getConnection(url +";shutdown=true");
}
catch (SQLException se) {
if (se.getSQLState().equals("08006")) {
System.out.println("Compress and shutdown complete");
} else throw se;
}
}
}