Skip to main content
W
WindchillAdmin1-Visitor
1-Visitor
May 5, 2015
Solved

How to Get Physical Vault File size for each iterated CAD Document in a particular lifecycle state on each Product containter

  • May 5, 2015
  • 10 replies
  • 4360 views

How to Get Physical Vault File size for each iterated CAD Document in a particular lifecycle state on each Product containter

Best answer by BenPerry

I think there is a command line tool that can be leveraged, but I usually prefer SQL query. The key is joining the EPMDocument table to the ApplicationData table.

Is something like this sufficient for the information you're looking for?

select


 edm.cadname,


 ed.versionida2versioninfo as rev,


 ed.iterationida2iterationinfo as iter,


 (ad.filesize/1024) as filesize_KB,


 prod.namecontainerinfo as product_name


from


 applicationdata ad,


 holdertocontent htc,


 epmdocument ed,


 epmdocumentmaster edm,


 pdmlinkproduct prod


where


 ad.ida2a2=htc.ida3b5


 and htc.ida3a5=ed.ida2a2


 and ed.ida3masterreference=edm.ida2a2


 and ed.ida3containerreference=prod.ida2a2


 and ad.role='PRIMARY'


 and ed.statestate='PRODUCTION'


order by


 edm.cadname;

10 replies

BenPerry
BenPerry15-MoonstoneAnswer
15-Moonstone
May 5, 2015

I think there is a command line tool that can be leveraged, but I usually prefer SQL query. The key is joining the EPMDocument table to the ApplicationData table.

Is something like this sufficient for the information you're looking for?

select


 edm.cadname,


 ed.versionida2versioninfo as rev,


 ed.iterationida2iterationinfo as iter,


 (ad.filesize/1024) as filesize_KB,


 prod.namecontainerinfo as product_name


from


 applicationdata ad,


 holdertocontent htc,


 epmdocument ed,


 epmdocumentmaster edm,


 pdmlinkproduct prod


where


 ad.ida2a2=htc.ida3b5


 and htc.ida3a5=ed.ida2a2


 and ed.ida3masterreference=edm.ida2a2


 and ed.ida3containerreference=prod.ida2a2


 and ad.role='PRIMARY'


 and ed.statestate='PRODUCTION'


order by


 edm.cadname;
W
WindchillAdmin1-VisitorAuthor
1-Visitor
May 6, 2015

Thanks a Lot.

It is exact what i expected.

I am trying the estimate size for doing Windchill Purge activity. Can you help me in getting same data except last revision.

BenPerry
BenPerry15-Moonstone
15-Moonstone
May 6, 2015

That is where it starts to get complicated. You'll need the maximum of ed.versionsortida2versioninfo from the query above. At this point, it might be easier to put it into Query Builder. Or perhaps someone else in the community can help.

W
WindchillAdmin1-VisitorAuthor
1-Visitor
May 6, 2015

Dear Ben Perry ,

can you clarify, why the below 2 query output differs. I am fetching epmdocuments with INWORK Status. why Query1 and Query 2 output differs.

Query 1 :

select

count (*)

from

applicationdata ad,

holdertocontent htc,

epmdocument ed,

epmdocumentmaster edm,

pdmlinkproduct prod

where

ad.ida2a2=htc.ida3b5

and htc.ida3a5=ed.ida2a2

and ed.ida3masterreference=edm.ida2a2

and ed.ida3containerreference=prod.ida2a2

and ad.role='PRIMARY'

and ed.statestate='INWORK'

output : 238563 rows selected

Query 2 :

select count(*) From epmdocument where statestate='INWORK'

output : 449569 rows selected

BenPerry
BenPerry15-Moonstone
15-Moonstone
May 6, 2015

There could be a number of different reasons for the difference, but we can start with this...

Query 1 is only getting EPMDocuments that are located in Product containers. You mentioned that was your original specification. Query 2 is getting EPMDocuments from everywhere - Products, Libraries, and Projects (if applicable).

W
WindchillAdmin1-VisitorAuthor
1-Visitor
May 7, 2015

oh. Great.

Which table hold the Library , Project list.

As i am planning to do a windchill purge for the first time, i am doing this exercise to collect how much space occupied by EPM Documents on all products, libraries & projects. And planning to purge only EPM Documents which is under ÏN-WORK State to free some space from Vault.

I noticed from your 1st query that entire vault files are located in applicationdata table and whichever file name refers CAD_NAME it linked to a epmdocument .

And other than CAD_NAME , i could see more files and space occupied by *.ol than CAD_NAME . when this .ol file getting generated, is that anyway to clear this type of files, since this .ol file type occupies by 70% of vault space.

Terms & ConditionsAccessibility statement