This ksh script will produce output in csv format that shows the total # of documents for a given list of app groups. First, create a file called ag_list that is a list of app groups, one per line. Then run this script from the same dir.
Output looks like this:
appgroup1,GBP,0
appgroup2,VUK,2010
appgroup3,UUK,167
appgroup4,VVI,0
appgroup5,NVN,8
appgroup6,YTK,930
You could then save this as a .csv file so it can be opened directly in MS Excel.
** Run at your own discretion! This does eat up resources on the server, so better to schedule this off hours! **
#!/bin/ksh93
function db2getagidname
{
db2 +o connect to archive1
db2 -x "select agid_name from arsag where name='$1'"
db2 +o terminate
}
function db2gettables
{
db2 +o connect to archive1
db2 list tables | grep -E "^$1[0-9]+" | awk '{print $1}'
db2 +o terminate
}
function db2gettblcnt
{
db2 +o connect to archive1
db2 -x "select count(*) from $1"
db2 +o terminate
}
while read agname
do
agidname=$(db2getagidname $agname)
agidname=${agidname# *}
agidname=${agidname%% *}
tblnames=$(db2gettables $agidname)
printf "$agname,$agidname,"
let tblcnt=0
for tbl in ${tblnames[@]}
do
typeset -i t=$(db2gettblcnt $tbl)
let tblcnt=tblcnt+t
done
printf "$tblcnt\n"
done < ag_list