Best way to "select count" from ondemand?

[Johan Dahlgren]

Hello,

I have a question. What is the best way to do a sort of "select count" from ondemand?

Backstory: we want to know how many of a certain type of document we have in a folder. We have a folder, lets call it LETTERS, with about 25.000.000 documents. we want to know how many type 1 and how many type 2 documents are in that folder.

What I have tried so far:
I have tried using the ARSDOC QUERY command to do this, se example below, but is there a better way to do this?

command i have used:
arsdoc query -v -h <SERVER> -u <USER> -p <PASSWORD> -f "<FOLDER NAME WITH SPACES>" -i "where id LIKE '%' and type= 'one'" | wc -1

Any hints or tips would be greatly appreciated.

Regards,

Johan Dahlgren

[Justin Derrick]

That's probably the most straightforward, but it *is* possible to query directly from the database table, but it's complicated, as there can be multiple tables, and the tables are named by the internal App Group ID (AGID_NAME).

You can visit the Content Manager OnDemand wiki for a head start, but the article doesn't go as far as what you're trying to do... 

https://cmod.wiki/index.php?title=Useful_SQL_Queries_for_Content_Manager_OnDemand

-JD.

[Johan Dahlgren]

Hello again, i found a way to do it. Thank you Justin for pointing me in the right direction.

First i had to find out what tables my documents were in by tracing them from ARSAG table to get the names of my application groups and then by looking at the AGID_NAME finding what names my tables had. From there it was just the question how to write an SQL query to find and count my documents.

this is what i ended up with. It did the job atleast.

Code: [Select]

db2 "select count(*) from (
    SELECT * from GKA1 UNION ALL
    SELECT * from GKA2 UNION ALL
    SELECT * from GKA3 UNION ALL
    SELECT * from GKA4 UNION ALL
    SELECT * from GKA5 UNION ALL
    SELECT * from GKA6 UNION ALL
    SELECT * from GKA7 UNION ALL
    SELECT * from GKA8 UNION ALL
    SELECT * from GKA9 UNION ALL
    SELECT * from GKA10) as l
where TYPE='one'"
Thanks again Justin for quick response and for pointing me in the right direction.

-JD

[Justin Derrick]

Awesome!  I'm glad the CMOD wiki was helpful. 

From one "JD" to another "JD"...  take care! 

-JD.