IBM DB2 Tips

From trapsink.com
Jump to: navigation, search


SysV Initscript

This script is RHCS friendly and LSB compliant, designed to provide proper shutdown of all resources to allow DB2 to exit cleanly and unmount filesystems during a cluster event (planned or unplanned). It does not require RHCS, however.

  • Customize DB2PROF and DB2LOG as needed
/etc/init.d/ibmdb2
#!/bin/sh
#
# SysV initscript for IBM DB2
#
# chkconfig: 2345 80 30
# description: IBM DB2 Database Server
 
# Source function library.
. /etc/rc.d/init.d/functions
 
# Application profile
DB2PROF=/home/db2inst1/sqllib/db2profile
DB2LOG=/var/log/ibmdb2.log
 
RETVAL=0
prog="DB2"
 
# For SELinux we need to use 'runuser' not 'su'
if [ -x /sbin/runuser ]
then
  SU=/sbin/runuser
else
  SU=/bin/su
fi
 
dprofile() {
  if [[ -e ${DB2PROF} ]]; then
    . ${DB2PROF}
    return 0
  else
    echo -n $"${prog} profile not found; exiting with error."
    failure
    echo ""
    exit 1
  fi
}
 
dstart() {
  echo -n $"Starting $prog: "
  dprofile || failure
  db2gcf -s 1>>${DB2LOG} 2>&1
  RETVAL=$?
  if [ $RETVAL -gt 0 ]; then
    db2start 1>>${DB2LOG} 2>&1 && success || failure
    RETVAL=$?
    echo ""
  else
    echo -n $"${prog} is already running"
    success
    RETVAL=0
    echo ""
  fi
}
 
dstatus() {
  dprofile || failure
  db2gcf -s 1>>${DB2LOG} 2>&1
  RETVAL=$?
  if [ $RETVAL -gt 0 ]; then
    echo $"${prog} is stopped"
    return 3
  else
    echo $"${prog} is running..."
    return 0
  fi
}
 
dstop() {
  echo -n $"Stopping $prog: "
  dprofile || failure
  db2gcf -s 1>>${DB2LOG} 2>&1
  RETVAL=$?
  if [ $RETVAL -gt 0 ]; then
    echo -n $"${prog} is already stopped"
    failure
    RETVAL=3
    echo ""
  else
    db2 terminate 1>>${DB2LOG} 2>&1
    db2 force applications all 1>>${DB2LOG} 2>&1
    db2stop 1>>${DB2LOG} 2>&1 && success
    RETVAL=$?
    if [ $RETVAL -gt 0 ]; then
      db2stop force 1>>${DB2LOG} 2>&1 && success || failure
    fi
    echo ""
  fi
}
 
# See how we were called.
case "$1" in
  start)
    dstart
    ;;
  stop)
    dstop
    ;;
  status)
    dstatus
    ;;
  restart)
    dstop
    sleep 5
    dstart
    ;;
  *)
    echo "Usage: $0 {start|stop|status|restart}"
    RETVAL=1
esac
 
exit $RETVAL


Database Storage

IBM DB2 supports using raw partitions; modern Best Practices from IBM developerWorks have shifted to recommending using file systems instead of raw partitions, however.

Outline for Best Practices paper:

  • Executive summary
  • Introduction to database storage
  • Goals of good database storage design
  • Simplicity in database storage design
  • Recipe for database storage success
    • Think about real physical disks, not just storage space
    • Have dedicated LUNs and file systems per non-DPF DB2 database server / per DPF partition
    • Stripe at most in two places
    • Separate DB2 transaction logs and data
    • Use file systems instead of raw devices—one file system per LUN
    • Where possible, use RAID-10 for transaction logs, RAID-10 or RAID-5 for data
    • Set EXTENTSIZE
    • Use the NO FILE SYSTEM CACHING clause
    • Use DB2 automatic storage to stripe everything everywhere
    • Do not hand-tune the NUM_IOCLEANERS, NUM_IOSERVERS, and PREFETCHSIZE configuration parameters
  • Best Practices
  • Conclusion
  • Further reading


References