/* tblmon.p
 *
 * program to interactively monitor table statistics
 *
 * known bugs:
 *
 *   time rollover at midnight is not handled properly
 *
 *
 */

define variable interval as integer no-undo initial 2.
define variable show_top as integer no-undo initial 20.

define variable sort-criteria as character no-undo initial "i" case-sensitive.

define variable tlist as character no-undo format "x(70)".

define variable r as integer no-undo.
define variable t as integer no-undo.

define variable s_time as integer no-undo.

define variable cum-total as integer no-undo.
define variable int-total as integer no-undo.

define temp-table tt_xstat no-undo
  field xid       as integer
  field xname     as character format "x(20)" label "Table"
  field base-stat    as integer
  field last-stat    as integer
  field this-stat    as integer
  field cum-stat     as integer format ">,>>>,>>9" label "Accum"
  field int-stat     as integer format ">,>>>,>>9" label "Inter"
  field cum-rate     as integer format ">,>>>,>>9" label "Accum"
  field int-rate     as integer format ">,>>>,>>9" label "Inter"
  field cum-pct      as integer format ">>9.99%"   label "Accum"
  field int-pct      as integer format ">>9.99%"   label "Inter"
  index int-idx int-stat
  index cum-idx cum-stat
  index xidx is unique primary xid.

define query q for tt_xstat.

procedure show-help:

  display
    "Table Read Monitor" "Page 1 of 2" to 75 skip
    skip(1)
    "Monitors table READ statistics available from the _TableStat VST." skip
    "You must have VSTs enabled and may need to adjust the -tablebase" skip
    "and -tablerangesize startup parameters if you have more than 50" skip
    "tables or wish to monitor a subset of your tables." skip
    skip(1)
    "The monitor interatively samples read activity against every table" skip
    "and displays the total number of records read since starting, the" skip
    "number read in the interval, the rate of reads per second since" skip
    "starting, the rate for the most recent interval and the percentages" skip
    "of total reads (across all tables) for both samples.  Data is sorted" skip
    "by the most active table for either the interval or cumulatively." skip
    "" skip
    "The overall read rate, both cumulatively and for the interval, is also" skip
    "shown." skip
    "" skip
   with
    frame show-help1
    row 1.

  display
    "Table Read Monitor" "Page 2 of 2" to 75 skip
    skip(1)
    "Commands include:" skip
    skip(1)
    "  ^d, q, Q -- Quit the program." skip
    "  ^h, h, H -- Obtain this help message." skip
    "      i, I -- Adjust the monitoring interval." skip
    "      r, R -- Change the number of rows displayed." skip
    "      s, S -- Sort criteria:" skip
    "               i interval descending." skip
    "               I interval ascending." skip
    "               c cumulative descending." skip
    "               C cumulative ascending." skip
    "      t, T -- Comma delimited list of tables to track." skip
    "      z, Z -- Re-initialize the counters." skip
    "" skip
   with
    frame show-help2
    row 1.

  pause.

  hide frame show-help1 no-pause.
  hide frame show-help2 no-pause.

  return.

end.

procedure initialize:

  /* initial values for baseline
   *
   */

  s_time = time.

  for each _TableStat no-lock:

    find first _File no-lock where _File._File-Num = _TableStat._TableStat-Id no-error.

    if ( available _File ) then
      do:

        find first tt_xstat exclusive-lock where tt_xstat.xid = _tablestat._tablestat-id no-error.
        if not available tt_xstat then create tt_xstat.
        assign      
          tt_xstat.xid       = _tablestat._tablestat-id
          tt_xstat.xname     = _file._file-name
          tt_xstat.base-stat = _tablestat._tablestat-read
          tt_xstat.last-stat = _tablestat._tablestat-read
          tt_xstat.this-stat = _tablestat._tablestat-read
        .

      end.

  end.

  return.

end.

procedure upd-stats:

  /* update the sample
   *
   */

  for each _TableStat no-lock:

    find tt_xstat exclusive-lock where tt_xstat.xid = _tablestat._tablestat-id no-error.

    if not available tt_xstat then
      next.  /* this should be impossible... at least until online schema changes happen */

    tt_xstat.this-stat = _TableStat._TableStat-Read.

  end.

  assign
    cum-total = 0
    int-total = 0
    t = time - s_time
  .

  for each tt_xstat exclusive-lock:

    assign
      tt_xstat.cum-stat  = tt_xstat.this-stat -  tt_xstat.base-stat
      tt_xstat.cum-rate  = tt_xstat.cum-stat / t
      tt_xstat.int-stat  = tt_xstat.this-stat - tt_xstat.last-stat
      tt_xstat.int-rate  = tt_xstat.int-stat / interval
      tt_xstat.last-stat = tt_xstat.this-stat
      cum-total = cum-total + tt_xstat.cum-stat
      int-total = int-total + tt_xstat.int-stat
    .

  end.

  for each tt_xstat exclusive-lock:
    assign
      tt_xstat.cum-pct = ( tt_xstat.cum-stat / ( if cum-total = 0 then 1 else cum-total )) * 100
      tt_xstat.int-pct = ( tt_xstat.int-stat / ( if int-total = 0 then 1 else int-total )) * 100
    .
  end.

  return.

end.

/************************************************************
 ************************************************************

 The Main Event

 ************************************************************
 ************************************************************/

run initialize.

/* the monitoring loop
 *
 */

do while true:

  display
    string( time, "hh:mm:ss") "Table Read Monitor" to 50 today to 80 skip
    skip(1)
    "Overall Cumulative Rate:" integer( cum-total / t ) format ">,>>>,>>9/sec" "     Overall Interval Rate:" integer( int-total / interval ) format ">,>>>,>>9/sec" skip
    skip(1)
                "Total" to 40    "Rate"  to 62    "Percentage" to 80 skip
   with
    no-box
    no-labels.

  run upd-stats.

  case sort-criteria:

    when "I" then 
      open query q for each tt_xstat no-lock by tt_xstat.int-stat.

    when "i" then 
      open query q for each tt_xstat no-lock by tt_xstat.int-stat descending.

    when "C" then 
      open query q for each tt_xstat no-lock by tt_xstat.cum-stat.

    when "c" then 
      open query q for each tt_xstat no-lock by tt_xstat.cum-stat descending.

  end.

  clear frame a all no-pause.

  r = 0.
  do while r = 0 or available tt_xstat:

    get next q.

    if not available tt_xstat then leave.

    if tlist <> "" then if lookup( tt_xstat.xname, tlist ) = 0 then next.

    r = r + 1.
    if r > show_top then leave.
    
    display
      tt_xstat.xname
      tt_xstat.cum-stat to 30
      tt_xstat.int-stat to 40
      tt_xstat.cum-rate to 52
      tt_xstat.int-rate to 62
      tt_xstat.cum-pct  to 72
      tt_xstat.int-pct  to 80
     with
      frame a
      row 6
      show_top down
      no-box.

    down 1 with frame a.

  end.

  close query q.

  /* wait interval for next sample or process a user command if something is typed
   *
   */

  pause interval no-message.

  if (( lastkey =   4 ) or		/* ^d	*/
      ( lastkey =  81 ) or		/*  Q	*/
      ( lastkey = 113 )) then leave.	/*  q	*/

   else if (( lastkey =   8 ) or	/* ^h	*/
      ( lastkey =  63 ) or		/*  ?	*/
      ( lastkey =  72 ) or		/*  H	*/
      ( lastkey = 104 )) then		/*  h	*/
    do:
      run show-help.
    end.

   else if (
      ( lastkey =  73 ) or		/*  I	*/
      ( lastkey = 105 )) then		/*  i	*/
    do:
      message "New interval:" update interval.
    end.

   else if (
      ( lastkey =  82 ) or		/*  R	*/
      ( lastkey = 114 )) then		/*  r	*/
    do:
      message "Show top rows:" update show_top.
    end.

   else if (
      ( lastkey =  83 ) or		/*  S	*/
      ( lastkey = 115 )) then		/*  s	*/
    do:
      message "Sort criteria:" update sort-criteria.
    end.

   else if (
      ( lastkey =  84 ) or		/*  T	*/
      ( lastkey = 116 )) then		/*  t	*/
    do:
      message "Track:" update tlist.
    end.

  else if (
      ( lastkey =  90 ) or		/*  Z	*/
      ( lastkey = 122 )) then		/*  z	*/
    do:
      run initialize.
    end.

end.

return.