No SQL queries allowed for that. Informatica never publishes SQL queries to change repository contents, so that's completely ruled out; Informatica clearly forbids mingling with the repository contents "manually".
Having written that, in one of my recent projects we've implemented that by some completely different approach. We were facing the same issues as you (DeleteObject doesn't work with versioned repositories, PurgeObject cannot remove all kinds of objects, and so on).
In the end we went a completely different road. For each folder to be deployed (or, in your case, kept), we create an XML export of the contents we want to keep (in our case based on workflows to be deployed), create a new folder, import the XML file into this folder, validated everything, and - in case validation was 100% successful - removed the original folder and renamed the newly created folder to the original folder name.
This can be completely automated, and it will remove many of the old objects.
Thanks for the reply!
Sorry, that bit about deleting stuff from the repository was with tongue firmly in cheek out of frustration for something I thought would be pretty straight-forward - I'll just go find and script up the pmrep commands for this... yeah, no, not so much. And in spite of being told I could find a way to use PurgeVersion for this, I've not found that to be true yet.
Interesting approach... hadn't thought of it yet in spite of doing something like that for YEARS with relational tables when the quantity to delete far outweighs what you need to keep. The only "downside" I can see initially would be a loss of all history on the saved objects, they would all look to be brand new all over again. I'll certainly keep that in mind as an option, however.
Regarding the version history there's one thing to keep in mind:
While the repository contents (after having copied most recent objects to the newly created folder) look as if there's no history, you can simply keep the XML export as the history. Meaning you only keep a history of objects which actually have been deployed, no more, no less.
Isn't that a nice prospect? A clean history, no objects missing, no superfluous objects?
I would like to go back and address your question about PurgeVersion and specifically your point "There's also a "PurgeVersion" function but it looks like the objects need to be in a deleted status first."
This is news to me. I do not recall this being the case at all.
PurgeVersion can be used to purge/delete any and ALL versions of any existing, active object, deleted or not, as I recall.
That is the way I read the doc anyways:
But I have not tried this myself recently to be honest.
Did you try this on a non-Deleted/Active object and receive an error saying the objected needed to be delete first before purging?
I guess I may stand corrected on this:
Interesting. I guess this scenario is not so common. Most of the times users delete objects through the Client tools and then purging versions is a requirement.
The other option I think of is to create an object query of all objects you want to delete in the Repository Manager then delete them in Repository Manager. Also the objects then need to be checked in and then Purged.
Sorry, just got back to this... something I've basically given up on doing outside of a pure manual task.
My first surprise with PurgeVersion is that it doesn't allow me to tell it what specific object I want to "purge", rather seeming to want to do a broad sweep through the repository. The -q query option seemed like an alternative but in the limited amount of time I was willing to futz with it, I could not get it to do anything. No errors, no messages (even when using -b and/or -k) other than it making no changes in the repository.
May revisit this in a later version if they add the FR noted in your link.