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 but luckily Allegro is the keeper of all things Telamon, so visit Allegro's Website on how best to contact them.

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:

DATABASE	DATASET	DATASETTYPE	LOADFACTOR	SECONDARIES	CAPACITY	ENTRIES
TEST	DLINE-BIG-RECORD	D	100.00	0.00	15	15
TEST	MSTANDALONE	M	88.24	53.33	17	15
KB	M-CALL-HDR	M	98.58	0.00	10000	9858
KB	D-CALL-TEXT	D	98.21	0.00	11200	10999

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 "dba@customer.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

---

More On Emailing From the e3000

In the article above, Neil Armstrong showed how to email a database analysis from the e3000 server to the DBA.

On the Ecometry.org web site, Anthony Ballo (aballo@ecometry.org) has published an article on three methods of e3000 emailing and another article with detailed instructions for the free Telamon Mail program.

Remote DBA for Ecometry Databases

Anthony also took Neil's idea for automated remote DBA and converted it to work on the Ecometry databases. Nice job Anthony.

Checking Logical Consistency

Ole Nord (ole@OleNordAB.se), Robelle's representative in Scandinavia, liked Neil's article about using Telamon's mail program so much, he implemented it in one of his jobs. Below is a job that uses the ASKPLUS tool from Vitalsoft to check a data base for logical consistency such as invoice details must exist for invoice header, current maintenance contracts must have been invoiced etc. (all done in the kundw script). The job is run every night and if there is output, it is mailed, if not everything is OK.

 !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 ole@olenordab.se -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).

---

Neil.Armstrong@robelle.com
December 30, 2000