This script is useful for checking if a table has rules associated to it, or if it is referenced in a database procedure.
Run the script and pass in the database name and table name.
#!/bin/ksh
############################################################################
#
# PROGRAM NAME: find_rules_procs
# USAGE: find_rules_procs
# on a specified table
# SCCS ID:
#
############################################################################
# Assign input params
DATABASE=$1
TABLE=$2
#
# Check all parameters are input
#
if [[ -z $DATABASE || -z $TABLE ]]
then
echo ‘Usage: find_rules_procs
exit
fi
#
#
RULE_OUT_TMP=/tmp/find_rules.tmp
PROC_OUT_TMP=/tmp/find_procs.tmp
#
# Retrieve details from the database
#
sql $DATABASE >| $RULE_OUT_TMP << !
SELECT DISTINCT rule_name
FROM iirules
WHERE table_name = '$TABLE'
\p\g
!
sql $DATABASE >| $PROC_OUT_TMP << !
SELECT DISTINCT procedure_name
FROM iiprocedures
WHERE text_segment like '%$TABLE%'
\p\g
!
#
#
echo "Rules in database" $DATABASE "for table" $TABLE
echo " "
cat $RULE_OUT_TMP | grep "|" | grep -v rule_name | sed -e s/\|//g
echo " "
echo "DB Procedures in database" $DATABASE "for table" $TABLE
echo " "
cat $PROC_OUT_TMP | grep "|" | grep -v procedure_name | sed -e s/\|//g
#
/bin/rm -f $RULE_OUT_TMP
/bin/rm -f $PROC_OUT_TMP
exit