#!/bin/ksh
###
#  Simple check for row-level distribution over a table's partitions.
#  Usage: check_distribution.ksh instancename dbalias schema tablename
#  Alex Levy, Feb 2004. Sustainable Software Ltd. http://www.sustainablesoftware.net
###

if [[ $# -eq 4 ]]
then
   typeset -l instance=$1 dbalias=$2 
   typeset -u schema=$3 table=$4 colname
   quote=\'
   fullname=$schema.$table
   schema=$quote$schema$quote
   table=$quote$table$quote
else
   print "Notes: Displays distribution of a table's rows across partitions."
   print "Usage: ./check_distribution.ksh <instance> <dbalias> <schema> <tablename> ."
   exit
fi

. /home/$instance/sqllib/db2profile
db2 "connect to ${dbalias}" 1> /dev/null
#colname=$(db2 -x "select colname from syscat.columns where tabschema=${schema} and tabname=${table} and partkeyseq=1")
colname=$(db2 -x "select colname from syscat.columns where tabschema=${schema} and tabname=${table} and colno=1")
if [[ $? -eq 1 ]]
then 
   print $schema $table is not partitioned!
else
# allow for the unusual circumstance that this table has one or more unpopulated partitions.
   db2 "with temp (partnum)                   as     \
        (select dbpartitionnum                       \
         from   syscat.tablespaces         ts        \
                INNER JOIN                           \
                syscat.dbpartitiongroupdef d         \
                ON ts.dbpgname = d.dbpgname          \
                INNER JOIN                           \
                syscat.tables              t         \
                ON ts.tbspaceid = t.tbspaceid        \
                where (t.tabschema = $schema         \
                and   t.tabname   = ${table})        \
        ),                                           \
        temp2 (partnum ,                             \
               rowcount)                 as          \
        (select dbpartitionnum($colname),            \
               count(*)                              \
        from   $fullname                             \
        group by dbpartitionnum($colname)            \
        )                                            \
        select t.partnum,                            \
               coalesce(t2.rowcount,0)as Rows        \
        from   temp t LEFT OUTER JOIN temp2 t2       \
               ON t.partnum = t2.partnum             \
        order by t.partnum"
fi

db2 connect reset 1> /dev/null
