RE: DB2EUG: REQ : User Exit Shell / Perl Script

[Vilas, Ajith (USPC.PCT.Hopewell)]

USEREXIT is an INSTANCE level parameter. You have to recycle the DBM server (db2stop 
and db2start) only if you change the USEREXIT parameter in dbm cfg. You don't have to 
restart the database manager,
just because you changed the program. Make sure you name the program "db2uext"  and is 
in the sqllib/adm path of the instance. In order to verify if the program is working 
correctly,  send a non zero
return code from the program and this should show up in the db2diag.log as an error.
Ajith

-----Original Message-----
From: McLeod, Scott [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 10, 2001 2:02 PM
To: [EMAIL PROTECTED]
Subject: RE: DB2EUG: REQ : User Exit Shell / Perl Script


DB2 does not tell you its invoking the USEREXIT.  Your script must do that
by recording the information somewhere.  I would suggest a file with a
hardcoded path.  Since you mentioned that you already tried this there must
be some additional error in configuration.  After you change userexit you
probably have to do some shutdown restart to get it to take effect.  It
could be the database or the entire DBMS - I don't remember which.  The
USEREXIT executeable must also be in the path for DB2 to be able to invoke
it.

Remember your userexit runs under DB2's userid so you need to execute and
setup all the environment variables for it.

Below is a version of a userexit I used in another company to move log files
to an archive directory where they would eventually be moved to tape.  I
sent it out once before.  This is a standard korn shell script so it should
work fine on all the platforms you described below.  It could also be easily
converted to perl.


#!/bin/ksh
############################################################################
####
#
# Usage:  db2uexit
#
# Description: This script migrates active log files to the archive logs
# directory.  It is invoked by DB2 automatically when a log full condition
# occurs.
#
# Parameters: See the IBM manual for information.
#
############################################################################
####
############################################################################
####
# Run the profile of the DB2 subsystem that invokes the exit
############################################################################
####
MYHOME="/db2_6000/`whoami`"
. $MYHOME/.profile > /dev/null 2>&1

############################################################################
####
# Set a path for a log file that contains info on archiving.
############################################################################
####
DIAGPATH="`db2 get dbm cfg | grep DIAGPATH |  cut -c60-100`"
DIAGLEVEL="`db2 get dbm cfg | grep DIAGLEVEL | cut -c60-90`"
AUDIT_LOG=true
AUDIT_LOG_FILE="$DIAGPATH/db2uexit.log"
AUDIT_LOG_FILE="/db2_6000/db2p/sqllib/db2dump/db2uexit.log"

if [ "$AUDIT_LOG" = true ];then
   echo "***************************************************************"
>> $AUDIT_LOG_FILE
   echo "Userexit begins at `date`"  >> $AUDIT_LOG_FILE
   echo "DIAGLEVEL=$DIAGLEVEL,DIAGPATH=$DIAGPATH" >> $AUDIT_LOG_FILE
fi
#---------------------------------------------------------------------------
---#
#--  Test the number of parms passed and the format of them.
--#
#---------------------------------------------------------------------------
---#
if [ $# -ne 4 ];then
   if [ "$AUDIT_LOG" = true ];then
      echo "Incorrect number of parms passed to Userexit program" >>
$AUDIT_LOG_FILE
      echo "Number of parms passed was $#"  >> $AUDIT_LOG_FILE
   fi
   exit 20
fi

if [ "$1" = ARCHIVE ];then
   continue
else
   if [ "$1" = RETRIEVE ];then
      continue
   else
      if [ "$AUDIT_LOG" = true ];then
         echo "Unsupported function requested from Userexit program" >>
$AUDIT_LOG_FILE
         echo "Function requested was $1"  >> $AUDIT_LOG_FILE
      fi
      echo
"***************************************************************\n"  >>
$AUDIT_LOG_FILE
      exit 20
   fi
fi

#---------------------------------------------------------------------------
---#
#--  ARCHIVE requests will copy file from activelogs to archivelogs
#---------------------------------------------------------------------------
---#
if [ "$1" = ARCHIVE ];then
 
#---------------------------------------------------------------------------
#
   #-- Audit the request
--#
 
#---------------------------------------------------------------------------
#
   if [ "$AUDIT_LOG" = true ];then
      echo "`date` DB2UEXIT AUDIT LOG ENTRY" >> $AUDIT_LOG_FILE
      echo "Request to $1 file $4 from $3" >> $AUDIT_LOG_FILE
   fi
 
#---------------------------------------------------------------------------
#
   #-- Copy the file to archivelogs
--#
 
#---------------------------------------------------------------------------
#
   LCDBNAME="`echo $2 | tr 'A-Z' 'a-z'`"
   echo "cp $3$4 $MYHOME/archivelogs/$LCDBNAME" >> $AUDIT_LOG_FILE
   cp $3$4 $MYHOME/archivelogs/$LCDBNAME >> $AUDIT_LOG_FILE 2>&1
   UNIXRC=$?
   if [ "$UNIXRC" -ne 0 ];then
      STANDRC=$STANDRC_28
      if [ "$AUDIT_LOG" = true ];then
         echo "cp Command Failed with RC:$UNIXRC" >> $AUDIT_LOG_FILE
      fi
      echo
"***************************************************************\n"  >>
$AUDIT_LOG_FILE
      exit 28
   fi
 
#---------------------------------------------------------------------------
#
   #-- Audit the request
--#
 
#---------------------------------------------------------------------------
#
   if [ "$AUDIT_LOG" = true ];then
      echo "Request to $1 file $4 from $3 was successful" >> $AUDIT_LOG_FILE
   fi
fi

#---------------------------------------------------------------------------
---#
#--  RETRIEVE requests will cp a file from archivelogs to activelogs
#---------------------------------------------------------------------------
---#
if [ "$1" = RETRIEVE ];then
 
#---------------------------------------------------------------------------
#
   #-- Audit the request
--#
 
#---------------------------------------------------------------------------
#
   if [ "$AUDIT_LOG" = true ];then
      echo "Request to $1 file $4 to $3" >> $AUDIT_LOG_FILE
   fi
 
#---------------------------------------------------------------------------
#
   #-- Copy the file
 
#---------------------------------------------------------------------------
#
   LCDBNAME="`echo $2 | tr 'A-Z' 'a-z'`"
   echo "cp $MYHOME/archivelogs/$LCDBNAME/$4 $3" >> $AUDIT_LOG_FILE
   cp $MYHOME/archivelogs/$LCDBNAME/$4 $3 >> $AUDIT_LOG_FILE 2>&1
   UNIXRC=$?
   if [ "$UNIXRC" -ne 0 ];then
      if [ "$AUDIT_LOG" = true ];then
         echo "cp Command Failed with RC:$UNIXRC" >> $AUDIT_LOG_FILE
         echo " "                            >> $AUDIT_LOG_FILE
      fi
      echo
"***************************************************************\n"  >>
$AUDIT_LOG_FILE
      exit 28
   fi
 
#---------------------------------------------------------------------------
#
   #-- Audit the request
--#
 
#---------------------------------------------------------------------------
#
   if [ "$AUDIT_LOG" = true ];then
      echo "Request to $1 file $4 to $3 was successful" >> $AUDIT_LOG_FILE
   fi
fi

if [ "$AUDIT_LOG" = true ];then
   echo "Userexit ends at `date`"  >> $AUDIT_LOG_FILE
   echo "***************************************************************\n"
>> $AUDIT_LOG_FILE
fi


> -----Original Message-----
> From: Philip Nelson (DBA) [SMTP:[EMAIL PROTECTED]]
> Sent: Tuesday, July 10, 2001 12:06 PM
> To:   [EMAIL PROTECTED]
> Subject:      DB2EUG: REQ : User Exit Shell / Perl Script
> 
> First I'd better introduce myself.
> 
> My name is Phil Nelson, I work for Scottish Widows as a Senior DBA and I
> have been using DB2 since it was OS/2 DBM (in fact OS/2 EE).
> 
> I currently look after AIX, Solaris and Linux DB2 servers (also DB2 for
> OS/390, IMS, SQL Server and Oracle).
> 
> Now here's my first question (your starter for 10) -
> 
> I want to set up user exit on our Solaris boxes to move logs to another
> box when they are archived.
> 
> I want to use a script rather than a compiled program - mainly because I
> can handle Perl and shell scripts better than C, and also because we don't
> have a C compiler on our Solaris boxes.
> 
> I've created a very simple Perl script, with a view to testing what is
> passed into the script.  It basically prints out the contents of @ARGV.
> I've tried just writing to STDOUT, also to a file.
> 
> I set USEREXIT on in DB CFG.  I then ran a big update of a test table and
> watched for new logs to appear.
> 
> No output appears anywhere.  There's also nothing in db2diag.log to say
> the user exit wasn't found.
> 
> HELP !!!
> 
> TIA
> 
> Phil Nelson
> Senior DBA
> Bojnice Database Consulting
> 
> 
> =====
> To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
> For other info (and scripts), see
> http://people.mn.mediaone.net/scottrmcleod

=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod



=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod