While consulting at an Open Skies site recently, I heard consistently that people were forced to manually monitor database loads to ensure that datasets did not overflow or performance decline. My response was:
"What?, this is MPE, the HP e3000, we can automate anything."
So I came up with the job at the end of this article, which was built to analyze all the databases associated with this customer's Open Skies application and e-mail a report in html format to the DBA.
The email program that I used is from the Telamon website but sadly this is no longer available on-line that I have been able to find.
This mail program requires an SMTP capable mail server, but it doesn't have to be on your e3000. I just point it at our MS Exchange server.
The job stream uses the simple feature of HowMessy that creates a self-describing 'loadfile' that contains all of the various columns in the Loadrept. The rest of the job stream manipulates those HowMessy files into an html file using a combination of Suprtool and STExport, dropping the datasets which have nothing unusual in the report. You are left with a short report showing only the datasets that may need your attention.
Here is a sample of what the emailed report would look like in HTML:
In retrospect I would improve this jobstream by putting the database names into variables and run the bulk of the job stream in a while loop, and also save the information into a history file, so that some trend analysis can be done.
Stay tuned for the next installment in remote database administration and for the time being enjoy the current job stream.
!job jdbcheck,mgr.acct,base ;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 mailserver "mailserver.robelle.com" !purge load@.messdata !file loadfile=loadfile.messdata;save !run howmessy.pub.robelle sch !rename loadfile.messdata,loadsch.messdata !run howmessy.pub.robelle people !rename loadfile.messdata,loadpeop.messdata !run howmessy.pub.robelle iatadb !rename loadfile.messdata,loadiata.messdata !run howmessy.pub.robelle histdb !rename loadfile.messdata,loadhist.messdata !run howmessy.pub.robelle batdb !rename loadfile.messdata,loadbat.messdata !run howmessy.pub.robelle cust !rename loadfile.messdata,loadcust.messdata !run howmessy.pub.robelle authdb !rename loadfile.messdata,loadauth.messdata !run howmessy.pub.robelle ctl !rename loadfile.messdata,loadctl.messdata !run howmessy.pub.robelle flt !rename loadfile.messdata,loadflt.messdata !comment !comment Finished analyzing the databases !comment !comment Now summarize the load files into one !comment single file. !comment !purge tonights.messdata !run suprtool.pub.robelle in loadsch.messdata numrecs 10000 out tonights.messdata xeq in loadpeop.messdata;out tonights.messdata,append;xeq in loadiata.messdata;out tonights.messdata,append;xeq in loadhist.messdata;out tonights.messdata,append;xeq in loadbat.messdata;out tonights.messdata,append;xeq in loadcust.messdata;out tonights.messdata,append;xeq in loadctl.messdata;out tonights.messdata,append;xeq in loadflt.messdata;out tonights.messdata,append;xeq in loadauth.messdata;out tonights.messdata,append;exit !purge load@.messdata !purge except.messdata !purge myhtml.messdata !run suprtool.pub.robelle in tonights.messdata if secondaries > 30 or loadfactor > 80 ext database ext dataset ext datasettype ext loadfactor ext secondaries ext capacity ext entries out except.messdata xeq export input except.messdata export output myhtml.messdata export html table export col fixed export heading fieldnames export xeq exit !if suprtooloutcount <> 0 then ! setvar who "email@example.com " ! setvar file "myhtml.messdata" ! run mail.exe.acct;info="-t !who -h !mailserver -ah !file & ! Please see attached file for report." !endif !stream jdbcheck.job.acct;at=00:45 !eoj
On the Ecometry.org web site, Anthony Ballo (firstname.lastname@example.org) has published an article on three methods of e3000 emailing and another article with detailed instructions for the free Telamon Mail program.
!job KUNDVALI,mgr.adm,dbase !File ASKLIST;dev=netlp,10 !purge kundvali !ASKPLUS x kundw out=kundvali x kundvali.ask out=term e !qedit l kundvali exit !if (qeditcount > 0) !mail.pub.sys;& !info="-t email@example.com -s dbcheck -h ntsvectra -m dbmess -a kundvali" !endif !set stdlist=delete !eoj
This job uses Qedit to put the record count of the output file
into a variable, but if could also be done with CI programming:
if (finfo("kundvali","eof") > 0).
December 30, 2000