OnDemand User Group

Tips and Tricks => Tips and Tricks => Topic started by: Johan Dahlgren on April 23, 2020, 07:20:29 AM

Title: Best way to "select count" from ondemand?
Post by: Johan Dahlgren on April 23, 2020, 07:20:29 AM

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

Title: Re: Best way to "select count" from ondemand?
Post by: Justin Derrick on April 24, 2020, 06:24:54 AM

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.

Title: Re: Best way to "select count" from ondemand?
Post by: Johan Dahlgren on May 19, 2020, 06:59:21 AM

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

Title: Re: Best way to "select count" from ondemand?
Post by: Justin Derrick on May 19, 2020, 12:31:56 PM

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

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

-JD.