This is a quick share to all, especially the folks in my training class last year, PTC Windchill 11.0 System Maintenance. The conversation was excellent and I hope to maintain connections with you all. We did not spend much class time learning purging but it was a discussion point. I wrote this a long time ago but it should still work in current versions. If you follow the standard rules that once something is released, you do not need to keep all the iterations the led up to the latest iteration. You would construct a purge query to clean these out. But how do you know which products to target first and what the expected results of your purges will be? This report looks at how much file space can be recovered by purging various products. I call this "bloat". You can execute this in TOAD or other SQL programs. My suggestion would be to run this first, do your purges, then run again to see difference. Don't forget to remove unreferenced files from vaults. Enjoy.
SELECT P.IDA2A2 CONTEXT_ID, P.NAMECONTAINERINFO CONTEXT_NAME, COUNT(*) TOTAL_PURGABLE_FILES, (SUM(A.FILESIZE)/1048576) TOTAL_BLOAT_FILESIZE_IN_MB
FROM ( SELECT M.DOCUMENTNUMBER, M.IDA2A2 MASTERID, E1.IDA2A2 ITER_ID, E1.ITERATIONIDA2ITERATIONINFO ITERATION, E1.BRANCHIDITERATIONINFO BRANCH FROM PDMLINKPROD.EPMDOCUMENT E1, PDMLINKPROD.EPMDOCUMENTMASTER M
WHERE E1.IDA3MASTERREFERENCE = M.IDA2A2) ITERATIONS, PDMLINKPROD.EPMDOCUMENT E2, PDMLINKPROD.PDMLINKPRODUCT P, APPLICATIONDATA A, HOLDERTOCONTENT H WHERE E2.IDA3MASTERREFERENCE = ITERATIONS.MASTERID AND E2.LATESTITERATIONINFO = 1 AND E2.BRANCHIDITERATIONINFO = ITERATIONS.BRANCH AND E2.STATESTATE = 'RELEASED' AND E2.IDA3CONTAINERREFERENCE = P.IDA2A2 AND ITERATIONS.ITERATION < (E2.ITERATIONIDA2ITERATIONINFO - 1) AND H.IDA3A5 = ITERATIONS.ITER_ID AND H.IDA3B5 = A.IDA2A2 GROUP BY P.IDA2A2, P.NAMECONTAINERINFO
