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

[WindchillAdmin]

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

[BenPerry · Accepted solution]

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;

[WindchillAdmin]

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]

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.

[Marco_Tosin]

In this document Resource for reporting there is a query builder to catch EPMDocs size.

You can modify it for your needs.

[WindchillAdmin]

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]

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).

[WindchillAdmin]

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.

[BenPerry]

In order to get library and project files, replace the PDMLINKPRODUCT with WTLIBRARY and PROJECT2 tables.

Can you share your SQL where you see the .ol files? FYI...You can switch to Advanced Editor when you're replying, and then highlight the SQL code that you paste in the reply window, and then format it as code using the >> Syntax Highlighting > SQL option on the toolbar.

[WindchillAdmin]

Great.

.ol files found in applicationdata table. details below

select filename,description,filesize,role from applicationdata;

sample output :

FileName	Description	FileSize	Role
4223456_5.ol	BBOX -0.0365 -0.0363979 0 0.0365 0.0363979 0.002 M	14539	SECONDARY
4223456_6.ol	BBOX -0.399832 -0.2225 -0.152006 0.367999 0.2225 0.00239103 M	562553	SECONDARY
4223456_7.ol	BBOX -0.015 -0.019 -0.0149746 0.015 0 0.0149746 M	26430	SECONDARY
4223456_8.ol	BBOX -0.2085 -0.0749955 0 0.2085 0.075 0.028 M	75779	SECONDARY
4223456_9.ol	BBOX -0.03575 -0.0205 -0.081 0.03575 0 0.081 M	28327	SECONDARY
4223456.jpg		3295	THUMBNAIL
4223456.pvt		23588	THUMBNAIL3D
023A_34m.jpg		2702	THUMBNAIL
023A_34m.pvt		7101	THUMBNAIL3D
023A_34m.log		378	SECONDARY
023A_34m.ol	BBOX -0.3913 -0.00552726 -0.0362 0.3782 0.203019 0.4532 M	206691	SECONDARY
autocad.pvs		273	PRODUCT_VIEW_ED
023A_34m.pvt		7101	THUMBNAIL3D
023A_34m.jpg		2702	THUMBNAIL

[BenPerry]

Go to the information page in Windchill for 4223456. Navigate to the Content tab, and view the Attachments table. Is there anything listed there?

Else, if nothing is listed there, then is 4223456 an assembly? And 023A_34M is a component maybe? I think these are actually publish files. If so, then I think they will also be deleted along with the actual EPMDocument. I've never done it, so can't say for sure, but seems like that would be the case.