When I read Neil Armstrong's article on how to automate database checking and have the exceptions delivered to the DBA by email, I had to try it on the Ecometry application databases.

Here is a sample of what the emailed message to the DBA would look like:

Sent: Tuesday, January 09, 2001 12:25 PM
To: aballo@musiciansfriend.com
Subject: Database Analysis



Your MACS Databases were analyzed using Robelle's
HowMessy. These datasets may require attention:

http://marge/IT/Data/MACS.htm

Secondaries were > 30
Loadfactors were > 80


[Generated by: abdbchek.streams2]

And here is the database analysis in HTML:

My job stream is the same as Neil's with the following changes:

1. I added variables for the selection criteria of suspicious databases:

   	!setvar _secondary 30
   	!setvar _loadfactor 80
   

2. I added the FTP routine to store the report.
3. And also build a nice email message to point to the report.

The edits to this job needed for another Ecometry shop to use it are:

1. substitute your MGR.SGAII password in the first line:

   	!job jdbcheck,MGR/userpass.SGAII,MACSDATA;outclass=lp,3,1
   

2. Change the email parameters at the end of the job: -t is the To and -f is the From. address and s

   !comment ** Now, generate the email and send **
   !  setvar who "aballo"
   !  setvar mailserver "mail"
   !  run MAIL.TELAMON.SYS;info="-t !who@musiciansfriend.com -f homer@musiciansfriend.com &
   !      -s 'Database Analysis' -h !mailserver -m email2"
   

It doesn't matter which version of Ecometry you have installed - this job should work because the database names have not changed.

Finally, here is Robelle's job stream, modified for Ecometry:

!job jdbcheck,MGR/userpass.SGAII,MACSDATA;outclass=lp,3,1
!comment
!comment ============================================
!comment This job is designed to analyze the databases
!comment and determine potential problems and email
!comment the problem datasets in a report to myself.
!comment
!comment This way I only know if there is a problem.
!comment ============================================
!comment First clean up any files in the messdata
!comment group.
!comment
!setvar _secondary 30
!setvar _loadfactor 80
!purge load@.messdata
!file loadfile=loadfile.messdata;save
!run howmessy.pub.robelle
MACORD

!rename loadfile.messdata,loadord.messdata
!run howmessy.pub.robelle
MACITM

!rename loadfile.messdata,loaditm.messdata
!run howmessy.pub.robelle
MACMAN

!rename loadfile.messdata,loadman.messdata

!run howmessy.pub.robelle
MACUSR

!rename loadfile.messdata,loadusr.messdata

!comment
!comment Finished analyzing the databases
!comment
!comment  Now summarize the load files into one
!comment  single file.
!comment
!purge tonights.messdata
!purge suprin,temp  > $null
!purge suprin.pub   > $null
!setvar _secondary 30
!setvar _loadfactor 80
!echo if secondaries > !_secondary or loadfactor > !_loadfactor  > suprin
!run suprtool.pub.robelle
in loadord.messdata
numrecs 10000
out tonights.messdata
xeq
in loaditm.messdata;out tonights.messdata,append;xeq
in loadman.messdata;out tonights.messdata,append;xeq
in loadusr.messdata;out tonights.messdata,append;exit
!purge load@.messdata
!purge except.messdata
!purge myhtml.messdata
!run suprtool.pub.robelle
in tonights.messdata
use suprin
ext database
ext dataset
ext datasettype
ext loadfactor
ext secondaries
ext capacity
ext entries
out except.messdata
xeq

!setvar filen "myhtml.messdata"

export input except.messdata
export output myhtml.messdata
export html table
export col fixed
export heading fieldnames
export xeq
exit
!if suprtooloutcount <> 0 then
!echo OPEN marge                        > ftpin
!echo USER kickmail                    >> ftpin
!echo kickmail                         >> ftpin
!echo CD /sys/public_html/IT/Data      >> ftpin
!echo PUT !filen MACS.htm              >> ftpin
!echo BYE                              >> ftpin
!run FTP.ARPA.SYS  < ftpin
!comment ** Build a nice email message to send **
!setvar _recs 100
!setvar _size 70
!purge email2 > $null
!purge email2,temp > $null
!build email2;rec=-!_size,,f,ascii;disc=!_recs
!file emailx=email2,old;dev=disc;acc=append;rec=-!_size
!echo                                                    >>*emailx
!echo Your MACS Databases were analyzed using Robelle's  >>*emailx
!echo HowMessy. These datasets may require attention:    >>*emailx
!echo                                                    >>*emailx
!echo http://marge/IT/Data/MACS.htm                      >>*emailx
!echo                                                    >>*emailx
!echo Secondaries were > !_secondary                     >>*emailx
!echo Loadfactors were > !_loadfactor                    >>*emailx
!echo                                                    >>*emailx
!echo                                                    >>*emailx
!echo [Generated by: abdbchek.streams2]                  >>*emailx
!comment ** Now, generate the email and send **
!  setvar who "aballo"
!  setvar mailserver "mail"
!  run MAIL.TELAMON.SYS;info="-t !who@musiciansfriend.com -f homer@musiciansfriend.com &
!      -s 'Database Analysis' -h !mailserver -m email2"
!endif
!deletevar who
!deletevar mailserver
!deletevar file
!eoj

Anthony Ballo
January 11, 2001