This is for AIX and DB2, but probably the same concepts could be applied to other environments.
These are some of the queries & commands I've compiled over the years and that I reference on a regular basis....PLEASE make corrections/suggestions as necessary!
------------------------------------------------------------------
1 - Since CMOD stores its internal dates in Unix epoch format, when you run a database query sometimes it is nice to convert each hit found on the fly. Just pipe the output to a "while read" loop that does the arsdate conversions:
$ db2 -x "select bankno,rdate from spr1 group by bankno,rdate order by bankno,rdate" | while read ; do printf "$REPLY\t" ; arsdate `echo "$REPLY" | awk '{print $2}'` | awk -F'\t' '{print $3}' ; done
001 11837 05/29/02
001 11842 06/03/02
001 12206 06/02/03
...
2 - This query will return the application group's segment date field. This is helpful in a shell script when you need to query app groups by their segment date field where they don't all use the same name. The mask field in the arsagfld table is a bitmask that allows for multiple values to be stored in a single field. The Filter/Index Type as well as the various check boxes in the Field Information tab are captured in this field.
$ db2 "select name from arsagfld where agid in (select agid from arsag where name='REPORT1') and mod((mask-mod(mask,4))/4,2)=1"
NAME
------------------
rdate
...
3 - Find application groups that never have had anything loaded in them. This follows the premise that the first load of a report is what generates the segment table.
db2 "select count(*) from arsag left join arsseg on arsag.agid=arsseg.agid where arsseg.agid is null"
...
4 - If the segment table has already been created, but the application group is EMPTY due to expirations or deletes, this query will find those.
db2 "select arsag.name from arsag left join arsload on arsag.agid=arsload.agid where arsload.agid is null order by arsag.name"
...
5 - Find applications that do not have large object set.
db2 "select comp_obj_size from arsapp where comp_obj_size < 102400"
...
6 - Show all folders pointing to an app group:
db2 "select distinct substr(arsfol.name,1,25) as folname, substr(arsag.name,1,20) as agname from arsfol,arsag,arsag2fol where arsfol.fid=arsag2fol.fid and arsag.agid=arsag2fol.agid and arsag.name in ('APPGROUP1')"
...
7 - Show all app groups contained in a folder:
db2 "select distinct substr(arsfol.name,1,25) as folname, substr(arsag.name,1,20) as agname from arsfol,arsag,arsag2fol where arsfol.fid=arsag2fol.fid and arsag.agid=arsag2fol.agid and arsfol.name in ('FOLDER1')"
...
8 - Get average doc size of an application group.
db2 "select avg(cast(doc_len as bigint)) from bda11"
...
More to come??
