Scott,
Thanks for the script.
I finally got my test script "visible", by writing the output to a file.
The results of the few tests I've run to date have been interesting -
looks like I'll have to look pretty closely at what I want to achieve with
the user exit and what DB2 does automatically on successful completion of
the exit.
Here's what I did -
Printed off all the parameters passed to the script by DB2. Most
interesting of these is the log file name Snnnnnnn.LOG.
Caused the script to end with a non-zero return code, so that it would
write a message to db2diag.log - there seems to be some difference between
the log file in the diagnostic message and that passed to the exit.
Changed to script to end with return code zero. It now appears that log
file which DB2 has notified to the script has been deleted from the source
log file. Worrying when you know how good a programmer I am <G>.
I saw the user exit as a means of obtaining a backup offsite copy of the
log file, rather than moving the only copy off the box. To achieve this I
guess I'd have to end to user exit with a non-zero return code (which in
turn will also write records to db2diag.log).
A side effect of a non-zero return code is that DB2 believes the script
has failed and suspends future execution of the script for 5 minutes. Not
really what I want either if there is the possibility that at peak
activity I cut more than one archive log in this period !!! Can the
"suspend period" be controlled ?
My last observation for now (I'm only just starting on this) is that we
may just skip the user exit altogether and go for the approach we take to
Oracle logs - run a cron script periodically which checks which logs are
ready for archiving and copies them.
On Tue, 10 Jul 2001, McLeod, Scott wrote:
> 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
