VST Practical Use

1
VST Practical Use

• By Ben Holmes
• Eaton Corp.

2
About Ben

• Started Programming with Progress in 1989,
  version 4.2
• Progress Consultant/DBA for over 12 years.
• Worked at QAD as Technical Service Manager.
• Worked at Eaton for the past 3 years.
• Support over 250 production databases ranging in
  size from 2 GB to 106 GB.
• Additional Credit Lenny McMinn, Programmer from
  Eaton.

3
Game Plan

• What is a VST?
• Who can use VSTs?
• How can VST help to solve problems?
• Locking Issues
• Performance Issues
• Other tools
• Questions

4
What is a VST?

• VST stands for Virtual System Table.
• VSTs provide 4GL access to information contained
  in the Progress Monitor (promon) utility, and
  more.
• VSTs are schema tables that are stored in memory.
• VSTs were added in V8.2
• proutil dbname C enablevst

5
Why Use VSTs ?

• VSTs may be easily integrated into existing
  Applications.
• VSTs contain better data than Promon.
• VSTs can be access using 4GL code.
• Some startup parameters may be updated through
  VSTs.

6
How can VST Help?

• Statistical Database Performance Reporting can be
  easily accomplished.
• By table or Index
• The Databases can be constantly monitored from
  within the Application.
• Additional information about locking conflicts
  may be provided to the end-user.

7
What Eaton Needed

• Main database is over 106GB with 600 users.
• A number of Locking issues.
• Performance was all over the board.
• Needed to validate the bottle necks
• Hardware (Disk/CPU/Memory)
• Network
• Application
• Database

8
Our Approach

• Created a program to detail TABLE READs/CREATEs
  stats for each DB connected.
• Created a program to detail INDEX READs/CREATEs
  stats for each DB connected.
• Set programs to run in Batch mode, every hour on
  the hour.

9
Indexes

• FOR EACH dictdb._IndexStat NO-LOCK
• BY dictdb._IndexStat._IndexStat-read
  DESC
• FIND dictdb._index
• WHERE dictdb._index._idx-num EQ
  dictdb._IndexStat._IndexStat-id NO-LOCK NO-ERROR.
• FIND dictdb._file OF dictdb._index NO-LOCK
  NO-ERROR.
• IF AVAILABLE dictdb._file AND NOT
  dictdb._file._file-name BEGINS "_"
• THEN DO
• DISPLAY STREAM strOut1
• dictdb._file._file-name
  FORMAT "x(15)" WHEN AVAILABLE(dictdb._file)
• dictdb._index._Index-name
  FORMAT "x(15)" WHEN AVAILABLE(dictdb._index)
• dictdb._IndexStat._IndexStat-Read
• dictdb._IndexStat._IndexStat-Create
• dictdb._IndexStat._IndexStat-Delete
• dictdb._IndexStat._IndexStat-split
• dictdb._IndexStat._IndexStat-blockdelete
• WITH FRAME fDetail DOWN WIDTH 132.
• END.
• END.

10
Index Report

• Sorted by Reads
• File-Name Index-Name read
  create delete split blockdelete
• --------------- --------------- -----------
  ----------- --------- ------ ----------
• qad_wkfl qad_index1 305481365 161921
  115958 32 2
• ilg_hist GroupID 74320284
  5861 2929 3 0
• tr_hist tr_date_trn 49964981
  49095 4 54 0
• scx_ref scx_shipfrom 49618193
  0 0 0 0
• tr_hist tr_nbr_eff 47352617
  49054 4 13 0
• wod_det wod_part 42138616
  11133 3420 6 0
• wo_mstr wo_nbr 38041257
  3148 4238 12 5
• vph_hist vph_nbr 30390010
  0 0 0 0

Number of split operations that have occurred to
the index
Number of block deletes that have occurred to the
index
11
Tables

• FOR EACH dictdb._TableStat NO-LOCK BY
  dictdb._TableStat._TableStat-read DESC
• FIND dictdb._file
• WHERE dictdb._file._file-num
  dictdb._TableStat._TableStat-id NO-LOCK NO-ERROR.
• IF AVAILABLE dictdb._file AND NOT
  dictdb._file._file-name BEGINS "_"
• THEN DO
• DISPLAY STREAM strOut1
• dictdb._file._file-name
  FORMAT "x(15)" WHEN AVAILABLE(dictdb._file)
• dictdb._TableStat._TableStat-Read
• dictdb._TableStat._TableStat-Update
• dictdb._TableStat._TableStat-Create
• dictdb._TableStat._TableStat-Delete
• WITH FRAME fDtlReads DOWN WIDTH 132.
• ASSIGN iCount iCount 1.
• END.
• IF iCount GE iInpNbrResults THEN LEAVE.
• END. .

12
Table Report

• Sorted by Reads
• File-Name read update
  create delete
• --------------- ----------- -----------
  ----------- -----------
• qad_wkfl 669,799,663 60315
  34190 15740
• ih_hist 620,795,514 7645
  3063 0
• tr_hist 302,783,875 267151
  255488 20
• scx_ref 294,468,008 7
  10 6
• ilg_hist 246,392,223 116725
  23295 0
• idh_hist 235,193,507 0
  10835 0
• prh_hist 225,880,661 1749
  1923 966
• gltr_hist 164,632,094 1467958
  507304 0
• cp_mstr 138,574,311 24
  26 1
• so_mstr 104,624,129 104722
  1348 1480
• pt_mstr 97910979 693
  31 0
• glc_cal 94794949 0
  0 0
• wo_mstr 90957077 271699
  4037 4690
• pi_mstr 78314960 327424
  242868 105852
• abs_mstr 57938943 83321
  14960 1546

13
Locking Issue

• FIND FIRST _lock NO-LOCK NO-ERROR.
• IF _lock._lock-recid NE ? THEN DO
• LOCKS
• REPEAT
• IF cUserID EQ ""
• OR (cUserID NE "" AND _lock._lock-name EQ
  cUserID) THEN DO
• FIND FIRST _file NO-LOCK
• WHERE _file-number EQ _lock._lock-table
  NO-ERROR.
• IF AVAILABLE _file
• AND _file._file-name NE "mon_mstr"
• AND _file._file-name NE "cnt_mstr" THEN
  DO
• ASSIGN
• cUID _lock._lock-name
• cRecID STRING(_lock._lock-recid)
• cStr1 _lock._lock-name
• cStr2 STRING(_lock._lock-recid).
• FIND FIRST _connect NO-LOCK
• WHERE _connect-usr EQ _lock._lock-usr
• AND _connect-usr GT 0 NO-ERROR.

14
Locking Report

• vstlck1_at_ ETN 34.20.9.20 VST Lock
  Analysis - No Sort 01/17/06
• ------------ Share, Xclusive, Upgrade, Limbo,
  Queue, Hold, Intent ------------
• UID RecID Lock Table UID
  RecID Lock Message
• -------- ----------- ---- -------- --------
  ----------- ---- -----------------
• progress 488735 XL ld_det E0065361
  92102311 S icccaj,,whwavemt
• 
  
• ------------ Share, Xclusive, Upgrade, Limbo,
  Queue, Hold, Intent ------------
• UID RecID Lock Table UID
  RecID Lock Message
• -------- ----------- ---- -------- --------
  ----------- ---- -----------------
• progress 16995286 SL wo_mstr E3014239
  74097245 X woworc
• ------------ Share, Xclusive, Upgrade, Limbo,
  Queue, Hold, Intent ------------
• UID RecID Lock Table UID
  RecID Lock Message
• -------- ----------- ---- -------- --------
  ----------- ---- -----------------
• progress 16995286 SL wo_mstr E3014239
  74097245 X woworc

15
Performance Tools

• Compile using XREF
• Look for WHOLE-INDEX in XREF
• WHOLE-INDEX means that the selection criteria
  specified to search the table does not offer
  opportunities to use indexes that allow optimized
  key references (bracketed high and low values).
   Instead, PROGRESS must search the entire table
  using available indexes (often only the primary
  index) to satisfy the query, hence a WHOLE-INDEX
  search
• Using Bp to reduce impact of reporting
• Private buffers allow users to have a personal or
  private buffer pool within the database buffer
  pool. The Bp client startup option allows you to
  allocate some of the general buffer pool for
  private use. It is possible to allocate up to 25
  percent of the general buffer pool in private
  buffers.

16
Profiler

• When the 4GL Performance Profiler is used with a
  PROGRESS 4GL application, the result is an output
  file containing a summary of what code was
  executed and timing data for each line that was
  executed. The summary data can be read into a
  Progress database and analyzed.
• DLC/src/samples/profiler
• is not supported by Progress Technical Support
• The runtime interpreter records 4GL statement
  begin and end times with microsecond precision.
• Records the statement line number and a module
  identifier. Each distinct external procedure,
  internal procedure, user-defined function,
  session database trigger, or user interface
  trigger that is executed is considered a module
  and is assigned a unique identifier.

17
Summary

• VSTs provide 4GL access to information contained
  in promon.
• Many useful reports and tools may be created.
• NOT all programs are created equal.

18
QUESTIONS
?
19
Thank You !!!