Tcl – Oldest message in Recovery Database

[Mark Thompson]

Clovertechies,

Working on a new alert…  Does anyone know of a way for Tcl view the Recovery Database?  I’m looking for a way to find the timestamp and threadname of the oldest message in the RDB.  Hoping for something a little more direct than parsing the output of hcidbdump.  Any ideas?

- Mark Thompson
HealthPartners

Topic

[Max Drown (Infor)]

There is no direct way for tcl to interact with the recovery database, i.e., there are no builtin commands. Before you attempt to parse the output of hcidbdump, you may want to read through that code to see how it’s done. You would not want to hose your database be mishandling it in a tcl script.

There are some built-in alerts that alert on the recovery and error databases.

-- Max Drown (Infor)

[Peter Heggie]

We run a job every morning to summarize messages found in the error database or recovery database. There is a parameter that sets the ‘look back’ time, which you could try changing from 1 day to 999 days.

This is the report output:

Quote:

From: Cloverleaf@cloverleaf2.snet.crouse.org [mailto:Cloverleaf@cloverleaf2.snet.crouse.org]

Sent: Thursday, December 26, 2013 6:30 AM

To: DL Interfaces

Subject: chonlprd – Error DB report

Error DB report for chonlprd – looking back 24 hours 0 seconds

Txnid     Date            Source          Destination          Length T Error State                         Recovery State  UD

---

---

---

 

---

 

---

–

---

 

---

—

358556352 20131225 232702 tcpf_INVG3_all  to_SMTP              232    D Tcl failure in OB data TPS (203)    IB msg sent      

358559095 20131225 232952 tcpf_INVG3_all  to_SMTP              224    D Tcl failure in OB data TPS (203)    IB msg sent      

2 messages found

Here is the shell script that invokes the tcl:

Code:

# run hcidbdump – parms: going back one day, database type (error or recovery),
# site name, email addresses, only email when messages found

errorrpt.tcl -d 1 -t e -r chonlprd -a DLInterfaces@crouse.org -o

And here is the tcl:

Code:

#! /usr/bin/ksh
# The following line is seen as a continuecomment by Tcl
exec $QUOVADX_INSTALL_DIR/integrator/bin/hcitcl “$0” ${1+”$@”}

global HciRoot
global env

proc gts {} {
  set tt [clock format [clock seconds] -format {%y/%m/%d %H:%M:%S} ]
  set ms [format %03d [expr {[clock clicks -milliseconds] % 1000}]]
  return $tt.$ms
}

proc getrecoverystate {RS} {
 switch  $RS {
   “1” { set RST “IB pre-SMS” }
   “2” { set RST “IB post-SMS” }
   “3” { set RST “IB msg sent” }
   “4” { set RST “XL msg rcvd” }
   “5” { set RST “XL pre-proc” }
   “6” { set RST “XL xlate” }
   “7” { set RST “XL post-proc” }
   “8” { set RST “XL msg sent” }
   “9” { set RST “OB msg rcvd” }
   “10” { set RST “OB pre-SMS” }
   “11” { set RST “OB post-SMS” }
   “12” { set RST “FWD pre-SMS” }
   “13” { set RST “FWD post-SMS” }
   “14” { set RST “OB delivered” }
   “15” { set RST “OB failed” }
   default { set RST “default”}
 }
 return $RST
}

proc sendemail {} {
 global onlye outlist address site TCOM RC
 if { $onlye eq 0 || $RC > 0 } {

   set filename “/tmp/hci_errorReport_Send_SMTP_Email.tmp”
   set fd [open $filename w]
   foreach line $outlist { puts $fd “$line” }
   close $fd
   
   exec mail -r “Cloverleaf” -s “$site – $TCOM DB report” $address -1 } { set debug 1 }

echo “[gts] $mod starting script”

# get current Cloverleaf site name
set oldsite [lindex [split [lindex [split [exec showroot] n] 1] ” “] 3]
set days 0
set cdx [lsearch $argv “-d” ]
if { $cdx > -1 } { incr cdx ; set days [lindex $argv $cdx] }

set hours 0
set cdx [lsearch $argv “-h” ]
if { $cdx > -1 } { incr cdx ; set hours [lindex $argv $cdx] }

set minutes 0
set cdx [lsearch $argv “-m” ]
if { $cdx > -1 } { incr cdx ; set minutes [lindex $argv $cdx] }

set seconds 0
set cdx [lsearch $argv “-s” ]
if { $cdx > -1 } { incr cdx ; set seconds [lindex $argv $cdx] }

set address 0
set cdx [lsearch $argv “-a” ]
if { $cdx > -1 } { incr cdx ; set address [lindex $argv $cdx] }

set site $oldsite
set cdx [lsearch $argv “-r” ]
if { $cdx > -1 } { incr cdx ; set site [lindex $argv $cdx] }

set target “e”
set cdx [lsearch $argv “-t” ]
if { $cdx > -1 } { incr cdx ; set target [lindex $argv $cdx] }

set onlye 0
set cdx [lsearch $argv “-o” ]
if { $cdx > -1 } { set onlye 1 }

set RC 0 ; set NMSGS 0

set hciroot ${::env(HCIROOT)}
catch {eval [exec $hciroot/sbin/hcisetenv -site tcl $site]}  results

set output [exec $hciroot/sbin/showroot]
set lineList [split $output “n”]
set newsite [lindex [split [lindex $lineList 1] ” “] 3]

if {$newsite ne $site} {
   echo “[gts] $mod error changing to $site, restoring”
   eval [exec $hciroot/sbin/hcisetenv -site tcl $oldsite]
   exec showroot
   exit 4
}

# set flag indicating time window desired
set winflag 0
if {$seconds || $minutes || $hours || $days} { set winflag 1}
 

set target [string range [string toupper $target] 0 0]
if { $target eq “R” } {
 set TOPT “r” ; set TCOM “Recovery”
} else {
 set TOPT “e” ; set TCOM “Error”
}

set cmdargs “-${TOPT} -O i”

if {$debug} {
 echo “options in effect:”
 echo “”
 echo “days:      $days”
 echo “hours:     $hours”
 echo “minutes:   $minutes”
 echo “seconds:   $seconds”
 echo “address:   $address”
 echo “site:      $site”
 echo “oldsite:   $oldsite”
 echo “target:    $target”
 echo “onlye:     $onlye”
 echo “winflag:   $winflag”
 echo “TOPT:      $TOPT”
 echo “TCOM:      $TCOM”              
}

# if time window specified, calculate start time = current time – window
if { $winflag} {
 set tsecs [expr ($days * 86400) + ($hours * 3600) + ($minutes * 60) + $seconds]
 set bsecs [expr [clock seconds] – $tsecs]
 set start [clock format $bsecs -format “%Y%m%d %H%M%S”]
 set cmdargs “$cmdargs -S “$start””
 
 set thrs [expr $tsecs / 3600]
 set tscs [expr $tsecs % 3600]
}

# call hcidbdump command
catch {eval exec hcidbdump $cmdargs} results

# command line headers
echo “[gts] $mod $TCOM DB report for $site – looking back $thrs hours $tscs seconds”
echo
set PFORMAT “%-9s %-15.15s %-15.15s %-20.20s %-6.6s %-s %-5.5s %-2.2s %2sn”
echo [format “$PFORMAT” “Txnid” “Date” ” Source” ” Destination” “Length” “T” “State” “DS” “UD”]
echo [format “$PFORMAT” “———” “—————” ” ————–” ” ——————-” “——” “-” “—–” “–” “–“]

# email line headers
set outlist {}
lappend outlist “$TCOM DB report for $site – looking back $thrs hours $tscs seconds”
lappend outlist “”
set EFORMAT “%-9s %-15.15s %-15.15s %-20.20s %-6.6s %-s %-35.35s %-15.15s %2s”
lappend outlist [format “$EFORMAT” “Txnid” “Date” “Source” “Destination” “Length” “T” “Error State” “Recovery State” “UD”]
lappend outlist [format “$EFORMAT” “———” “—————” “————–” “——————-” “——” “-” “———————————-” “—————” “–“]

set ctg 0
set ctr 0
# get all detail lines from hcidbdump command and process each line
set msglist {}
echo
set lineList [split $results n]
foreach line $lineList {
 if {[string first “[” $line] > -1} {
   set temp1 [lindex [split $line “[”] 1]
   set mid [lindex [split $temp1 ” “] 0]
   lappend msglist [lindex [split $mid “.”] 2]
 }
}
set nmsgs [llength $msglist]
foreach msg $msglist {
 # create command to get detail (Long) report for each message ID
 catch {exec hcidbdump -e -m $msg:$msg -L -c} details
 
 # check for message already processed
 if {[string first “Unable to read” $details] > -1} { incr ctg ; continue }

 # add to count for messages processed
 incr ctr

 set dtlList [split $details “n”]
 set mType [lindex [split [lindex $dtlList [lsearch -regexp $dtlList “msgType” ] ] “:”] 1]
 set mType [string range [string trim $mType] 0 0]
 set mState [lindex [split [lindex $dtlList [lsearch -regexp $dtlList “msgState” ] ] “:”] 1]
 set eState [string trim $mState]
 set mState [lindex [split [lindex [split $mState “(”] 1] “)” ] 0]
 set mSrc [lindex [split [lindex $dtlList [lsearch -regexp $dtlList “msgOrigSrcThread” ] ] “:”] 1]
 set mSrc [string trim $mSrc]
 set mDest [lindex [split [lindex $dtlList [lsearch -regexp $dtlList “msgOrigDestThread” ] ] “:”] 1]
 set mDest [string trim $mDest]
 set mDS [lindex [split [lindex $dtlList [lsearch -regexp $dtlList “DbState” ] ] “:”] 2]
 set mDS [string trim [lindex [split [lindex [split $mDS “(”] 1] “)” ] 0]]
 set mUD [lindex [split [lindex $dtlList [lsearch -regexp $dtlList “UserData” ] ] “:”] 1]
 set mUD [string trim $mUD]
 set message [lindex [split [lindex $dtlList [lsearch -regexp $dtlList “message” ] ] “:”] 1]
 set mLength [string length [string trim [string trim $message “‘”] ] ]
 set mDate   [lindex [split [lindex $dtlList [lsearch -regexp $dtlList “msgTimeStartIb” ] ] “(”] 1]
 set mDate [lindex [split $mDate “)” ] 0]
 set mDate [clock format [clock scan $mDate] -format “%Y%m%d %H%M%S”]
 
 set out [format “$PFORMAT” “$msg” “$mDate” “$mSrc” “$mDest” “$mLength” “$mType” “$mState” “$mDS” “$mUD”]
 echo $out
 set RST [getrecoverystate $mDS]
 lappend outlist [format “$EFORMAT” “$msg” “$mDate” “$mSrc” “$mDest” “$mLength” “$mType” “$eState” “$RST” “$mUD”]
}

echo
lappend outlist ” ”
echo “[gts] $mod ${nmsgs} messages found”
lappend outlist “${nmsgs} messages found”
if {$target eq “R” } {
 set DBTYPE “Recovery”
 echo “${ctg} messages subsequently processed”
 lappend outlist “${ctg} messages subsequently processed”
 echo “${ctr} messages still on the queue”
 lappend outlist “${ctr} messages still on the queue”
 if { $ctr > 0 } {
   set RC 8 ; set RESULT “$ctr msgs found in Recovery DB – see email”
 } else {
   set RC 0 ; set RESULT “0 messages found in Recovery DB”
 }
} else {
 set DBTYPE “Error”
 if { $nmsgs > 0 } {
   set RC 8 ; set RESULT “$nmsgs msgs found in Err DB – see email”
 } else {
   set RC 0 ; set RESULT “0 messages found in Error DB”
 }
}
echo

# create audit row in table
set omtflags $env(omtflags)
set omtflags2 “$omtflags:$site.$DBTYPE:database:$RC:$nmsgs”
exec omtstat “$omtflags2” “$RESULT”

# if a email is required, send email
if { $address != 0 } { sendemail }

catch {eval [exec $HCIROOT/sbin/hcisetenv -root tcl $HCIROOT $oldsite]  } results
echo “[gts] $mod changed site back to $oldsite”
   
echo
echo “[gts] $mod ending script – RC $RC”
echo

exit $RC

The code may seem to have duplicate output – that is because the same output is both echoed to std out and sent to an email output.

Maybe this will save you some time.

Pete

Peter Heggie

[Mark Thompson]

Thank you Max and Pete.

- Mark Thompson
HealthPartners

[Author]

Replies