The usage records database table holds records that are generated by the Usage Record component Web service. Periodic maintenance of the table is recommended to optimize system performance. Maintenance consists of pruning the database table to remove files that are no longer needed.
The maintenance schedule depends on the database's transaction rate and the amount of space available in the database. In most instances it should be sufficient to prune the usage records database table once a week.
The following sample Java application is provided to help you. It uses the default table name of USAGERECORDS. Before using the application, modify it as needed for your installation.
//IBM Sample Source Materials // //Sample source materials are supplied As-Is. //No warranty is expressed or implied. // //(C)Copyright IBM Corp. 2000, 2006 , 2009 // //The source code for this program supplied under the terms of the //End User License Agreement (EULA) that accompanied this product. //****************************************************************** import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.NumberFormat; import java.util.Date; import java.util.Properties; /** * The TWSS Usage Records common component is a write-only common which produces usage records for * each TWSS service used. These usage records are used for recording billing information. * Once a usage record has been processed, the processed entry should be deleted from the database. * * This Sample Usage Record pruning class removes usages records from a database based on the records * age. A SQL DELETE query is made of all the usage records older than the user provided input date and time. * This query limits each delete request to 100 deletes, to provide user feedback, and also improve performance by * issuing frequent database commits. * * Running the sample class CleanUpDbTable: * * Program assumptions: * * - Run program on a TWSS server running environment. * - Run program inside a database command-line environment * - Suggested JVM version java 1.41 * * Running the Sample: * * set CLASSPATH=.;%CLASSPATH% * java CleanUpDbTable DB2 TWSSDB62 localhost 50000 userid password 2007-09-14 11:00:00 TableName TimeFieldName PrimaryKeyName * * or * * java -cp .;%CLASSPATH% CleanUpDbTable.class DB2 TWSSDB62 localhost 50000 userid password 2007-09-14 11:00:00 TableName FieldName PrimaryKeyName * * * Typical runtime problems: * * Error: ClassNotFoundException * Action: Ensure WAS Server and database is running or Program can't find JDBC driver in classpath. * * Error: Incorrect Userid or Password * Action: Ensure correct information , recheck command line positions. * * Error: SQLException errors * Action: Check proper date or time command line input. * * @author Administrator */ public class CleanUpDbTable { /** * Error messages */ static final String ILLEGAL_ACCESS = "IllegalAccessException during driver resolution: " ; static final String INSTANTIATION_EXCEPTION = "InstantiationException during driver resolution: "; static final String CLASS_NOT_FOUND = "ClassNotFoundException during driver resolution: "; static final String SQL_DRIVER = "SQLException during driver getConnection: "; static final String SQL_COUNT = "SQLException during UseageRecord count query: "; static final String SQL_EXECUTE = "SQLException during query executeUpdate: "; static final String SQL_DELETE = "SQLException during query delete processing: " ; static final String SQL_CLOSE = "Error closing connection: " ; /** * Informational messages */ static final String USAGE1 = "Usage: database-alias server-hostname portNumber userId password date(ex:2007-08-01) time(ex:11:00:00) TableName FieldName PrimaryKeyName\n"+ "Example parameters: [DB2|ORACLE] WPS_AL dbserverhostname 50000 username <password> 2007-09-01 11:00:00 USAGERECORDS RECORDTIME RECORDID"; static final String MSG1 = "Starting query count of usage records to delete..."; static final String MSG2 = "SQL Query = \n"; static final String MSG3 = "Usage Records = " ; static final String MSG4 = "Committed delete UsageRecords row(s): "; static final String MSG5 = "Current elapsed time is: "; static final String MSG6 = "Averaging delete time: " ; static final String MSG7 = "Total UsageRecord cleanup time: "; static final String MSG8 = "UsageRecord total delete count query time: " ; static final String MSG9 = "UsageRecord count query matching row(s) count: "; static final String MSG10 = "Starting Delete usage query..."; static final String MSG11 = "Preparing jdbc URL: "; static final String MSG12 = "Preparing jdbc driver class: "; static final String MSG13 = "No UsageRecords found matching query criteria. "; /** * Remember database type */ static boolean isDB2 = false; static boolean isOracle = false; /** * URL property file inputs */ static final String USERID = "user"; static final String PW = "password"; /** * CommandLine parameter inputs */ static final int _DB = 0 ; static final int _ALIAS = 1 ; static final int _SERVER = 2 ; static final int _PORTNUM = 3 ; static final int _USERID = 4 ; static final int _PW = 5 ; static final int _TIME = 6 ; static final int _DATE = 7 ; static final int _TABLE = 8 ; static final int _TIMEFIELD = 9 ; static final int _PRIMARYKEY = 10 ; static final int MAX_PARM = 11; /** * Java Main Application entry point * * @param args */ public static void main(String[] args) { CleanUpDbTable example = new CleanUpDbTable(); // check for parameters if (args.length > MAX_PARM || args.length < MAX_PARM) { example.myexit (-1 , USAGE1 ); } example.processCmd(args); String alias = args[_ALIAS] ; String server = args[_SERVER]; int portNumber = Integer.valueOf(args[_PORTNUM]).intValue(); String userId = args[_USERID]; String password = args[_PW]; String tableName = args[_TABLE]; String timeFieldName = args[_TIMEFIELD]; String primaryKeyName = args[_PRIMARYKEY]; // for collecting some simple statistics Date startTime = new Date(); int count = 0; int records = 0; Connection connection = null; String url = null; String driver = null; if (isDB2) { url = "jdbc:db2://" + server + ":" + portNumber + "/" + alias; driver = "com.ibm.db2.jcc.DB2Driver"; } else { url = "jdbc:oracle:thin:@" + server + ":" + portNumber + ":" + alias; driver = "oracle.jdbc.driver.OracleDriver"; } // MSG11 = "Preparing jdbc URL: "; // MSG12 = "Preparing jdbc driver class: "; example.tracemsg (MSG11 + url); example.tracemsg (MSG12 + driver); try { Class.forName(driver).newInstance(); } catch (IllegalAccessException e) { e.printStackTrace(); example.myexit(-2,ILLEGAL_ACCESS + e); } catch (InstantiationException e) { e.printStackTrace(); example.myexit(-2,INSTANTIATION_EXCEPTION + e); } catch (ClassNotFoundException e) { e.printStackTrace(); example.myexit(-2,CLASS_NOT_FOUND + e); } Properties props = new Properties(); props.setProperty(USERID, userId); props.setProperty(PW, password); try { connection = DriverManager.getConnection(url, props); connection.setAutoCommit(false); } catch (SQLException e) { e.printStackTrace(); example.myexit(-3,SQL_DRIVER + e); } if (connection != null) { try { // msg1: "Starting count of usage records to delete..."; example.tracemsg (MSG1); //----------------------------------------------------- // Query the DB for a total all records older // than the time/date provided //---------------------------------------------------- Statement statement = connection.createStatement(); String query = "SELECT COUNT( " + timeFieldName + " ) AS TOTAL FROM " + tableName + " WHERE (" + timeFieldName + " < TIMESTAMP('" + args[_TIME] + " " + args[_DATE].trim() + "'))"; // msg2:"Using query: "; example.tracemsg (MSG2+ query); //-------------------------------- // Perform Usage Record Count //-------------------------------- ResultSet rs = statement.executeQuery(query); rs.next(); records = new Integer(rs.getInt("TOTAL")).intValue() ; // records = Integer.parseInt(recordCountString); // msg3:"Usage Records = " + records example.tracemsg (MSG3+ records); rs.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); example.myexit(-4,SQL_COUNT + e); } Date finshTime = new Date(); // MSG8 = "UsageRecord total delete count query time: " ; // MSG9 = "UsageRecord count query matching row(s) count:"; example.tracemsg (MSG8+ example.timeInSeconds(finshTime.getTime() - startTime.getTime())); example.tracemsg (MSG9+records) ; // Check if any delete matching criteria if (records != 0) { // Prepare to delete and commit 100 Usage records per loop count = 0; startTime = new Date(); //msg10 = "Starting Delete usage query..."; example.tracemsg (MSG10) ; String query = "DELETE FROM " + tableName + " WHERE " + primaryKeyName + " in (SELECT " + primaryKeyName + " FROM " + tableName + " WHERE " + timeFieldName + " < TIMESTAMP('" + args[_TIME] + " " + args[_DATE].trim() + "') ORDER BY " + primaryKeyName + " FETCH FIRST 100 ROWS ONLY)"; example.tracemsg (MSG2+ query); try { PreparedStatement statement = connection.prepareStatement(query); Date runningTime = new Date(); int delcnt = 0 ; while (records > 0) { try { delcnt = 0; //-------------------------------- // Perform Usage Record Delete //-------------------------------- delcnt= statement.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); example.myexit(-4,SQL_EXECUTE + e); } count+= delcnt; records -= delcnt; //---------------------------------------------- // To prevent accessive locks commit often and // improves preformance //---------------------------------------------- connection.commit(); finshTime = new Date(); long intermediateTime = finshTime.getTime() - runningTime.getTime(); // msg4: "Committed delete UsageRecords row(s): " +delcnt // msg5: "Current elapsed time is: " + example.timeInSeconds((finshTime.getTime() - startTime.getTime())) // msg6: "Averaging delete time in seconds: " + example.timeInSeconds(intermediateTime / 100)); example.tracemsg (MSG4+ delcnt ); example.tracemsg (MSG5+ example.timeInSeconds( (finshTime.getTime() - startTime.getTime()) ) ); example.tracemsg (MSG6+ example.timeInSeconds(intermediateTime / 100)); runningTime = new Date(); } statement.close(); } catch (SQLException e) { e.printStackTrace(); example.myexit(-4,SQL_DELETE + e); } finshTime = new Date(); // msg7 "Total UsageRecord cleanup time: "; example.tracemsg (MSG7+ example.timeInSeconds(finshTime.getTime() - startTime.getTime())) ; } else { // if count // MSG13 = "No UsageRecords found matching query criteria. "; example.tracemsg (MSG13) ; } } // if connection if (connection != null) { // Disconnecting ... try { connection.commit(); connection.close(); } catch (SQLException e) { e.printStackTrace(); example.myexit(-4,SQL_CLOSE + e); } } example.myexit(0,""); } /** A number formatter*/ private NumberFormat nf = NumberFormat.getInstance(); /** * Convert time in milliseconds to a String in seconds * @param time * @return */ private String timeInSeconds(float time) { return nf.format(time/1000F) + " seconds"; } /** * processCmd: Check input parms. * @param args Program input parms. */ private void processCmd (String[] args ){ // Did the user request help? if ( (args[_DB].indexOf("?")> -1) || (args[_DB].trim().toLowerCase().indexOf("help")> -1) ) { myexit (-1 , USAGE1 ); // Check for DB2 } else if ( (args[_DB].trim().toUpperCase().indexOf("DB2") > -1) ) { isDB2 = true; // Check for Oracle } else if ( (args[_DB].trim().toUpperCase().indexOf("ORACLE") > -1) ) { } else { myexit (-1 , "" ); } } /** * tracemsg: Output messages * @param message display message */ private void tracemsg (String message ){ System.out.println(String.valueOf(message)); } /** * Exit: System exit * @param retc return error code * @param errmsg display message */ private void myexit(int retc , String errmsg ){ tracemsg(String.valueOf(errmsg)); System.exit(retc); } }