APL Delete Requests came about because of a need to regularly remove records from a system based on various query qualifications, this is useful in situations where you just refreshed your production environment to test but don’t need all of the records over there, or if you have a cleanup job that needs to run that uses complex queries to identify the records that need to be deleted. One thing that’s important to note about this tool is that while it uses SQL to identify the records that need to be deleted, it uses the Remedy API to actually do the delete process. This ensures that if you have business rules that specify a record can’t be deleted for one reason or another, it won’t be. Additionally, because it uses the API, any workflow that you have that causes other records to be deleted, cascade style, or any relationships you have identified are honored.
APL Delete Requests uses a config file that specifies form, and SQL pairs to identify which records need to be deleted. This tool came about because I often found myself being able to identify which records shouldn’t exist via SQL, but translating that into a Remedy qualification was either impossible or difficult because SQL can use complex joins where Remedy really can’t do this easily. This tool is written to optimize the process of record removal while still honoring business rules. It operates in chunkSize so that it can be run in environments where the query count is limited. It also has a ‘simulation’ feature allowing you to run the tool without actually performing any deletes, but checking how many deletes would have happened, as well as validating your SQL is valid. You have the ability to configure a delay between retrieve chunks, your retrieve size, delete chunk size, as well as how many concurrent threads are used to issue the deletes. With these configurations, you have the ability to make it as fast or as slow as you want.
One note regarding concurrent threads. The maximum threads that will be used is limited by the difference between the maxRetrieve and the chunkSize values. i.e. if your chunk size is 1k, and your max retrieve is 2.5 k, there will be a maximum of 3 threads at a time, two processing 1k chunks, and the 3rd processing the remainder 500. If your chunk is 1k and your maxRetrieve is 10k, you can have in upwards of 10 threads doing simultaneous deletes.
The SQL Query needs to return a list of Request ID’s (Column ID 1) that you want to delete on the specified form
Variable replacement is another powerful feature. If you specify a variable name in the config file, and then specify a variable replacement on the command line, your static config file can then be used dynamically. If you specified a config setting like
SELECT Email_ID FROM AR_System_Email_Messages WHERE Create_Date < DATEDIFF(s, ‘1970-01-01 00:00:00’, ‘MyDateTimeVariable’)
then, on the command line you specified -vMyDateTimeVariable “1/1/17”
the end result would be a query of
SELECT Email_ID FROM AR_System_Email_Messages WHERE Create_Date < DATEDIFF(s, ‘1970-01-01 00:00:00’, ‘1/1/17’)
The SQL Query can be as simple or as complex as you need it to be using joins, unions, anything you need, the only requirement for this application
is that it return a list of request id values (Column 1) that you want deleted on the specified form
java -jar APLDeleteRequests.jar -x [-u -p -ras RASHash -t -c -d -sim -cs -to -mr -mt -vVariable VarValue]
Required
-x: This is the server to connect to
Optional
-u: User used to connect to the servers
-p: Password of the user being used to connect
-ras: The Remedy Application Service hash from the ar.cfg file, if specified, no user name is required
-t: TCPPort to connect to server, uses port mapper if undefined
-c: Config file to be used during this run, default is APLDeleteRequests.properties
-d: Delay, in seconds, that you want to wait between deletes to control the impact on the server, default is 0
-sim: Tells the program to run and count how many records would be deleted, but don’t actually do the delete
-cs: How many records to process at a time, default is 1000
-to: How long (in seconds) any api call will wait before timing out, default is 2 hours (7200 seconds)
-mr: Maximum records to retrieve from server per query, default is 2000
-mt: Maximum concurrent threads to do deletes with, default is 2
-v: Able to be specified multiple times, allows you to specify a variable, and the value you want that variable to have, this allows you
to specify a variable of ‘DeleteBefore’ in your config file and specify a value of ‘1/1/17’ on the command line, making your config file
even more versatile (-vDeleteBefore 1/1/17)
Version History
1.10 – September 22nd 2020
- Enhancement: Updated the Java code to keep track of errored out deletes and consider the delete ‘done’ even if those records weren’t able to be deleted
- Update: Changed the code to use 9.x libraries which causes the min Java version to become 1.8 as well
1.8 – August 22nd 2017
- BUG: The TCP Port specified wasn’t being used properly in the code so was always using Port Mapper, which obviously doesn’t work for everyone
1.7 – June 11th 2017
- Initial public release