Mass delete of Preliminary Items with a Pending Change request
I have a scenario where i need delete set of Preliminary items which are having a pending change request.
Generally the deletion is done once dependency is removed manually. But i have huge set of records.
Is it possible from Database end to perform this bulk deletion.
Doing things in the database can be rather risky. The schema is very complicated, and it is possible to not merely shoot yourself in the foot, but to blow your entire leg off.
If you use what I specify below, TRY IT FIRST in a Development or Test environment. Make sure it works there, and develop a procedure of the steps to follow when doing something like this. The first 2 of those steps *will* be stopping Agile and making a backup of the Agile database, before running any script. After the script has been run (and the log file checked for any SQL*Plus issues), then start Agile back up again, and check your results within Agile itself.
To soft-delete an item, use the following query :
update item set delete_flag=1 where item_number = ‘<prelim item #>’;
This will only soft-delete the items. And no, DO NOT just delete the record from the ITEM table, as there are MANY related records that would also need to be deleted. if they are still around, it *WILL* cause issues. 2 more steps would be to run Averify both before and after you run the soft-delete script, just to make sure it is happy with the database (note that Averify *is* your friend). If there are issues reported after running the script that were not there before running it, you did something wrong.
If I am sounding a bit hard-core here, it is because I have been working in and with the database for over 16 years, and I know it pretty well. I cannot count the times I have seen very well-intentioned folks badly screw a database up (and in one case, with no recent backup). And yes, I have done so too, but I *always* have a backup handy.
Thanks for the reply. The steps you have share i.e.
1.Taking a prior DB back-up
2. Shutdown App. server.
3. Run A-Verify n clear errors.
4. Execute query to perform Soft-Delete.
5. Run A-Verify to see errors
If no errors then it’s good. This approach perhaps provides minimum scope for issues however What i was looking that is there a way to hard delete them. Can i proceed with the same to hard delete.
On the other hand, now i am thinking of writing code for the same. Will that be a less risky and better approach compared to DB approach.
Absolutely, a PX would be the best way to approach this. The PX can use the API, which goes through the application server. So there are no issues about what needs to be done as the server knows exactly what to do, and it can hard-delete the prelim items. However, using the API also has the same restrictions as doing it manually, so it should remove the item from the change first, and then hard-delete the item. It can read from an outside text file, so what should be processed is whatever is in the file.