Document Count Discrepancies Using DQL

Hi,

I did a DQL to find the count of documents in each folder location under /Cabinet/Folder1/Folder2.

when I count the descending folders individually we get a different figure compared to when we count the entire folder. we are missing about 2000+ objects. i need some help to find why that is the case, what could be the reason.. how do i find the objects causing this discrepancies.

Thanks

Rajesh Mani

Find more posts tagged with

Comments

aflowers001

Do you have document linked into multiple folders?

Ifd this is the case then querying a specific folder will give all document linked to it, but querying across many folder, say from a cabinet down, will give a count of unique documents found.

What are the queries you are using?

Rajesh_Mani_dctm

Hi Andy,

Below query I use to get the folder path and count of docs under them:

SELECT fdr.r_folder_path, COUNT(*) FROM dm_document (ALL) doc, dm_folder_r fdr

WHERE ANY doc.i_folder_id = fdr.r_object_id

AND fdr.r_folder_path LIKE '/Minerals Repositories/Legacy/DEPT(SHA_MEL, SHA_LHR)%'

GROUP BY fdr.r_folder_path ORDER BY fdr.r_folder_path

Now If i goto the actual folders and look for the count match I am seeing differences from the above query.

Thanks

Rajesh Mani

CláudioGil

When manually checking the folders you must select "Show All Objects and Versions", and filter our anything that is not a dm_document, to get a similar result. Even so, if you have not changed the preferences, an interface like Webtop filters out documents that are marked as hidden which may add the difference between what you see and the query.

Imagine the following structure:

Cabinet
- Folder A
  - Document 1 version 0.1
  - Document 1 version 0.2 CURRENT
  - Document 2 version 0.1 CURRENT hidden
- Folder B
  - Document 1 version 0.1
  - Document 3 version 0.1 CURRENT

You query counts 3 in Folder A and 2 in folder B while webtop shows

1 in Folder A and 1 in Folder B with "Show Files and Folders" and normal preferences
2 in Folder A and 2 in Folder B with "Show All Objects and Versions" and normal preferences
3 in Folder A and 2 in Folder B with "Show All Objects and Versions" and preferences changed to show hidden objects

Regards,

Cláudio

Rajesh_Mani_dctm

Hi Gil,

I am not using webtop to check the counts. I am using DQL query to check the counts.. i agree that the difference in the count is due to files/folder being linked to multiple folders..

I need a DQL query or some other mechanism to detect and report them. All i need is some idea to track them so i can write a DQL or a small script to report them.

Any help?

-Rajesh Mani

CláudioGil

I was wrongly assuming that by individually you meant one by one through an interface and not, like you did, executing the query for each one.

In this case I have little to add to Andy Flowers comment. Just that folders can also have several links and therefore multiple r_folder_path. If this happens then you have that folder counted several times (/A/Folder, /B/Folder, /C/Folder, etc).

msroth

Interesting discussion. Documents linked to folders will definitely throw off your count. Here is a modification of your query to highlight where linked documents might reside:

SELECT fdr.r_folder_path, COUNT(doc.r_object_id) as doccnt, 
sum(doc.i_reference_cnt) as refcnt, sum(doc.r_full_content_size)/1024 as sizeKB 
FROM dm_document (ALL) doc, dm_folder_r fdr
WHERE ANY doc.i_folder_id = fdr.r_object_id
AND fdr.r_folder_path like '/Temp%'
GROUP BY fdr.r_folder_path ORDER BY fdr.r_folder_path

The results look something like this:

r_folder_path     doccnt     refcnt     sizekb
/Temp                         3     4     35.4423828125
/Temp/Jobs/Detect Duplicate Content     1     1     1.724609375
/Temp/Jobs/dm_AuditMgt     4     4     8.7236328125
/Temp/Jobs/dm_UpdateStats     17     17     841.8955078125
/Temp/Jobs/dm_Consistency Checker     18     19     13.2890625
/Temp/Jobs/testJob     26     26     40.21875
/Templates     48     48     1661.005859375

The difference between the doccnt and refcnt indicate folders where documents are linked. In this case, /Temp and /Temp/Jobs/dm_Consistency Checker contain linked documents.

For a little more insight, this query shows which documents are linked, and to which folders they are linked:

SELECT doc.r_object_id, doc.object_name, fdr.r_folder_path,doc.i_folder_id
FROM dm_document (ALL) doc, dm_folder_r fdr
WHERE ANY doc.i_folder_id = fdr.r_object_id
AND fdr.r_folder_path like '/Temp%'
AND doc.i_reference_cnt > 1
ORDER BY fdr.r_folder_path ENABLE(ROW_BASED)

The results look like this:

r_object_id                 object_name                                    r_folder_path                 i_folder_id
09003afd80520108     10/13/2013 2:32:43 AM dm_Consistency Checker     /Temp     0c003afd80000107
09003afd80520108     10/13/2013 2:32:43 AM dm_Consistency Checker     /Temp/Jobs/dm_Consistency Checker  0b003afd80473a65

So, in this case, the discrepancies in the /Temp and /Temp/Jobs/dm_Consistency Checker folder are explained by a single linked document. Hope that was helpful. It was fun to sort through.

Rajesh_Mani_dctm

Hi Scott,

My case is very complex here.. let me explain:

These are the folder paths I am checking for counts:

Folder Path	Count
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)	634340
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)/Archived_Minp_JNB	16843
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)/Min_HOU	24701
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)/Min_JNB	31146
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)/Min_LHR	4097
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)/Min_MEL	1217
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)/Min_SIN	0
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)/Min_SCL	0
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)/Min_PER/Exploration	56495
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)/Min_PER/Development	100413
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)/Min_PER/Risk Management	36382
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)/Min_PER/Legal	41924
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)/Min_PER/Administration	17354
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)/Min_PER/Safety	8868
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)/Min_PER/Supply Management	628
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)/Min_PER/Financial Management	288273
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)/Min_PER/Health	3017
/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)/Min_PER/Information Services & Systems	390
Difference	2592

As you can see the root path common for all the above location is /Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR). I got 634340 as the count of documents under the root folder. Now this count minus the sum of all the counts of the subfolders is coming as 2592 and this is what i want to justify.

Below is the query i am using...

select count (r_object_id) from dm_document where folder('/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)', descend) and i_cabinet_id = '0c027d6280020319'

I am using i_cabinet_id because i see some of the above folders are linked to cabinets outside /Minerals

Now to see how many documents are linked to multiple folder location i did the query:

select count (r_object_id) from dm_document where folder('/Minerals/Legacy Information/Legacy Repositories (SHA_MEL, SHA_LHR)', descend) and i_cabinet_id = '0c027d6280020319' and i_reference_cnt > 1

To my surprise i am getting ZERO(0) value for the above query.

Now i changed the above query to dm_sysobject and i got 3 folders having i_reference_cnt as 2. and there are only 21 documents under these folders..

Now tell me where to look at..

-Rajesh Mani

msroth

Rajesh, the only suggestion I have off the top of my head is to use r_folder_path like '/path fragment' instead of folder('/path fragment'). When I used folder() in my query above, it produced incorrect counts of i_reference_cnt.

Rajesh_Mani_dctm

Hi Scott,

That did not help as i am getting the same values even when using r_folder_path LIKE option.

Another interesting thing I noticed is the count of folder_id. here is the query

select distinct r_object_id from dm_folder where folder ('/Minerals/Legacy/Legacy Repositories (SHA_MEL, SHA_LHR)', descend)

Result count:37109

select distinct i_folder_id from dm_document(ALL) where folder ('/Minerals/Legacy/Legacy Repositories (SHA_MEL, SHA_LHR)', descend)

Result count: 32539

So a difference of about 4570 which gave me an impression that they will be empty but the count of empty folders in that location is only 60

Any thoughts..?

-Rajesh Mani