1 / 19

VST Practical Use

VST Practical Use. By Ben Holmes Eaton Corp. 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.

jerica
Download Presentation

VST Practical Use

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 VST’s? • 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. • VST’s 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 wod_det 51628077 419829 19735 5175 spt_det 46880062 1677243 131345 0 trq_mstr 46702262 0 9 4 usrw_wkfl 46497108 12072 25281 25316

  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. IF AVAILABLE _connect THEN DO: ASSIGN cStr0 = "". IF _connect._connect-name EQ "progress" THEN DO:

  14. Locking Report vstlck1@ 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 !!!

More Related