DB2 DBA tip – UNIX script to reduce typing (and save time) for reorgs after altering tables

At my current work engagement, I interact with a team of developers who work on multiple projects on a large multi-terabyte data warehouse. Some of these interactions are in the form of requests for DDL (Data Definition Language) changes. Many of such DDL requests have ‘alter table … ‘ statements in them, sometimes, multiple alters on the same table.

When there are more than 3 alter statements for the same table and in few other cases like altering few data types of columns, DB2 puts altered tables into reorg-pending mode. It becomes imperative to perform a reorg on such a table as no Insert, Update and Delete statements could be run until reorg operation is done.

This effort involves identifying tables that are in reorg-pending mode and then performing reorg on each of the table that is in reorg-pending mode. It is repetitive in nature. I usually come up with a UNIX script to get mundane tasks done. In this blog post, I will share this UNIX script that will aid in identifying tables in reorg-pending mode and then perform reorg operation.

Tested Environments:

This script has been tested in the following environments:

1) DB2 LUW 9.7 FixPack 7 on SLES (SUSE Linux Enterprise Server) 10.4

2) DB2 LUW 10.5 FP3 on AIX 7.1

The Script:

This script primarily does the following:

1) Connect to the database (prompt for the database name if multiple local databases are found)

2) Read DDL (alter statements..) to generate a list of distinct table names (schema.table)

3) For each table, query sysproc.admin_get_tab_info to check if the table is in reorg pending mode

4) Run reorg if #3 is true; proceed to next table after reorg is done

5) Proceed to next table if #3 is false

(If the table is not found in the database, the script simply indicates that reorg is not needed for that table)

6) Write entire output on the screen to a log file

 #!/bin/ksh

## Date: Oct 17th 2014
## Author: Pavan Kristipati

## This script needs a DDL filename as its input
## Example: reorg_after_alter.ksh ddlfile
#################################################################
##This script does the following:
##1) Connects to the database (prompts for the database name if multiple local databases are found)
##2) Reads the DDL (alter statements..) and generates a list of distinct table names (schema.table)
##3) For each table, checks if it is in reorg pending mode
##4) Runs reorg if #3 is true; proceeds to next table after reorg is done
##5) Proceeds to next table if #3 is false
#################################################################

ddl=$1
pathk=$HOME/dba/custom/scripts
## Modify this path if this does not exist

logdir=$(pwd)
logfile="$logdir/`basename $0`.`date +%Y-%m-%d-%H-%M-%S`.log"

(
if [ $# -ne 1 ]
then
    echo "#####################################"
    echo "Error in $0 - Invalid Argument Count"
    echo "Syntax: $0 ddlfile"
    echo "#####################################"
    exit
fi

. $pathk/get_dbname
## This script calls get_dbname script to get the database name
## get_dbname is found below this script
dbname=$(get_db_name)
db2 connect to $dbname

cat $ddl | sed 's/\. /./g' > $$.ddl2
cat $$.ddl2 | grep -i "alter table" | awk '{print $3}' | sort -u | awk -F '.' '{print $1"  "$2}' > $$.tmp

while read line
 do
  SCHEMA=$(echo $line |  awk '{print $1}'| tr a-z A-Z)
  TABLE=$(echo $line |  awk '{print $2}' | tr a-z A-Z)
  checkx=$(db2 -x "select distinct(reorg_pending) from table(sysproc.admin_get_tab_info('$SCHEMA','$TABLE')) as t")
  echo $checkx | grep -q Y
  returncode=$(echo $?)

   if [ "$returncode" -eq "0" ]; then
    ## if returncode is 0, then 'Y' (YES) was found in the variable $checkx indicating that the table is in reorg-pending mode
    echo "Table $SCHEMA.$TABLE is in reorg-pending mode"
    db2 -v "reorg table $SCHEMA.$TABLE"
  else
    echo "reorg not needed for table $SCHEMA.$TABLE"
  fi

 done < $$.tmp

rm $$.tmp $$.ddl2

) | tee -a $logfile

Thanks to my colleague Rao Balaga for letting me use his script “get_db_name” and for his permission to post it on http://www.db2talk.com.

# Function: get_db_name
 # Author: Rao Balaga
 # Purpose:  You can use this function to get the name of the local database name
 #
 ####################################################################################
function get_db_name {
## DEFINE fonts ##
 NORMAL="33[0m"
 BOLD="33[1m";
 BOLD_RED="33[41m"
#### Get the count of all local databases ####
 db_cnt=`db2 list database directory  | awk '/Indirect/{for(i=1;i<=x;)print a[i++];print} {for(i=1;i<x;i++)a[i]=a[i+1];a[x]=$0;}' x=6  |  grep "Database name" | sed "s/.*= //" | uniq | wc -l`
#### if only one local database exists ####
if [[ $db_cnt -eq 1 ]] ; then
db_name=`db2 list database directory|awk '/Indirect/{for(i=1;i<=x;)print a[i++];print} {for(i=1;i<x;i++)a[i]=a[i+1];a[x]=$0;}' x=6  |grep "Database name" | sed "s/.*= //" | uniq`
 echo "$db_name" | tr a-z A-Z
else
echo  "\nSelect one of the following database name\n" >&2
 db_array=`db2 list database directory  | awk '/Indirect/{for(i=1;i<=x;)print a[i++];print} {for(i=1;i<x;i++)a[i]=a[i+1];a[x]=$0;}' x=6 | grep "Database name"  | sed "s/.*= //" | uniq | awk '{a[$1] ;} END{for (i in a) print i ;}' | sort `
## first part prints array in multiple lines
 printf -- '%s\n' "${db_array[@]}" | awk '{print NR ") " $1}' >&2
printf  "\nSelect database: " >&2
 read db_choice
 echo "                    " >&2
#### find out number of local databases ####
 num_dbs=`printf -- '%s\n' "${db_array[@]}" | awk '{print NR ") " $1}' | wc -l`
#### choice should be between 1 and num_dbs
 if [[ $db_choice -lt 1 ]] || [[ $db_choice -gt $num_dbs  ]] ; then
 echo -e  " ${BOLD_RED} Invalid choice.  Choice should between 1 to `echo $num_dbs | tr d ' '` ${NORMAL} \n" >&2
 exit 1;
 else
 #### get db_name based on the choice from the array ####
 db_name=`echo ${db_array[@]} | cut -d' ' -f$db_choice`
 echo  "$db_name" | tr a-z A-z
 fi
fi
}

 Points to Note:

1) If a table is in reorg pending state, an inplace reorg is not allowed on the table.

2) The script “reorg_after_alter” runs reorg on all database partitions (DPF) specified in the db2nodes.cfg file. This is the default if a node clause has not been specified.

3) In this script, I am not supplying the name of the system temporary tablespace for the reorg operation to use. Because of this, DB2 will store a working copy of the table in the table spaces that contain the table being reorganized.If this is a concern, you should modify this script by adding “USE .. ” clause to specify a system temporary tablespace. Please visit IBM Knowledge Center for more information.

4) Before running a reorganization operation against a table to which event monitors write, you need to deactivate the event monitors on that table.

Disclaimer:

I want to share these scripts with you so that you could benefit from them. Please use them with caution. I am not responsible for any problems that may arise and/or table or database outage because of using any scripts shared on http://www.db2talk.com

Advertisements

5 thoughts on “DB2 DBA tip – UNIX script to reduce typing (and save time) for reorgs after altering tables

  1. Good work @pavan , scripts like these always help us avoiding repetitive tasks , feeling happy to the contribution to the #db2 community from around the world

  2. Good task for automation Pavan and very nice of you to share your scripts. I just thought I will add a few things that I found useful.

    DB2 also reports on how many reorg recommended alters have been performed on a table. I generally found it useful to reorg a table proactively before the 3 alters are performed, especially if the 3 alters do not come at the same time. This way we can also do inplace reorg (in HADR environment caution is needed as this will create a lot of logs).

  3. HI Ravi — Thank you for stopping by db2talk.com. Thank you for your comments.

    Requests with multiple alters (> 3) on the same table are a common occurrence at my current assignment. While I proactively look for candidate tables that need reorg, it wouldn’t be prudent to wait for next-cycle of such proactive effort (to clear the reorg-pending state) as no further UID activity (on tables) will be allowed.

    Your comment on in-place reorg in a HADR is absolutely right… I tend to rely on DBI’s Brother Panther to identify tables with top overflow % as the output from reorgchk is questionable. I had covered this in detail in my IDUG NA (2014) and DB2Night Show presentations. Here is the link for your reference: http://www.dbisoftware.com/blog/db2nightshow.php?id=511

    Best Regards.
    Pavan.

  4. Hello Panvan,
    Thank your for your script.
    In our configuration Database is on HADR and used by ITIM software . The reorg brocked alwayse in a Audit_Event table and some others. tables.So is this this works also for HADR.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s