1 / 47

DB-13: Understanding VSTs

DB-13: Understanding VSTs. By Paul Guggenheim. About PGA. Working in Progress since 1984 and training Progress programmers since 1986 Designed six comprehensive Progress courses covering all levels of expertise

Download Presentation

DB-13: Understanding VSTs

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. DB-13:Understanding VSTs By Paul Guggenheim

  2. About PGA • Working in Progress since 1984 and training Progress programmers since 1986 • Designed six comprehensive Progress courses covering all levels of expertise • Major consulting clients include Foxwoods Casino, Health Informatics, Bank One, Textron Fastening Systems, and American Academy of Orthopaedic Surgeons

  3. What are VSTs ? • VST stands for Virtual System Table. • VSTs provide 4GL access to information contained in the Progress Monitor (promon) utility. • VSTs are schema tables that are stored in memory.

  4. Why Use VSTs ? • VSTs may be easily integrated into existing Applications. • Some startup parameters may be updated through VSTs. • Statistical Database Performance Reporting can be easily accomplished. • The Databases can be constantly monitored from within the Application. • Additional information about locking conflicts may be provided to the end-user.

  5. Accessing VSTs • VSTs are metaschema tables • Automatically included in Version 9 • Must enable them in Version 8 with the following command: proutil dbname –C enablevst • To look at in GUI Dictionary, select: View->Show Hidden Tables • To look at in CHUI Dictionary, enter VST table name

  6. Viewing VSTs • VSTs are records in the _file metaschema table • _file-number range for VSTs (9.1D): -16385 to -16423 • Two new tables were added to OpenEdge 10. • _Code-Feature • _Database-Feature • One new field is added to OpenEdge 10. • _server._server-pendconn

  7. VST Inquiry • In program vstinq.p, the VST schema may be viewed.

  8. VST Characteristics • Every VST has one field that ends in ‘id’. • This field serves as the VST’s unique index. • This field is sequentially numbered from 1. • Exception is _TxeLock where Id field is ?.

  9. VST Characteristics • Many VSTs pre-allocate records based upon startup parameters. For example: proserve school -n 3 -Mn 3 -L 250 -B 500 produces a –n of 4. • Progress adds an extra user for shutting down the database or promon.

  10. VST Characteristics • Let’s look at the _Trans table:

  11. VST Characteristics • The default initial values in most VST fields is unknown (?). • The–Mn value is 3. • One record for each server and the one record for the database broker is displayed.

  12. VST Characteristics • Let’s look at _UserLock table. It shows which locks a particular user currently holds.

  13. VST Characteristics • _UserLock-Id 1 is the broker, 2,3 and 4 are the servers, 5,6 and 7 are connected users and 8 is a promonsession. • The formula for this table is: –n + -Mn + 2 where –n is the startup number of users. • Since the –L is 250, Progress rounds it to the nearest multiple of 32 which is 256.

  14. VST Characteristics • The _Lock file will look like the following:

  15. Resolving Locking Conflicts • As a developer, you had 2 choices in resolving locking conflicts: Option 1: Let Progress handle it.

  16. Resolving Locking Conflicts • As a developer, you had two choices in resolving locking conflicts: Option 2: Handling locking conflicts in the procedure repeat: prompt-for student.student-id. find student using student-id exclusive-lock no-wait no-error. if available student then update sfirst-name slast-name. else if locked student then do: find student using student-id no-lock no-error. message "Student record is locked” skip "Please wait until later." view-as alert-box. end. else message "Student number does not exist" view-as alert-box. end.

  17. Resolving Locking Conflicts • Problem with Option 1: • User has to undo their transaction. • The stop key, unless trapped, forces the user to the beginning of the application. • Problem with Option 2: • User doesn’t know who has locked the record…UNTIL VSTs!

  18. Resolving Locking Conflicts • Adding the statements below now provides the user with additional information about who has the record locked: find first _lock where _lock-recid = integer(recid(student)) no-error. message "Student record is locked by user:" _lock-name skip "Please wait until later." view-as alert-box.

  19. Record Locking Inquiries • For each user, Progress provides up to 512 locked records for display and lookup.

  20. Record Locking Inquiries • In lockinq.p, we start with the _UserLock table to show available users in a browse. • For each user selected there is another browse that shows the locked records for that user, it’s table name, RECID and lock flags. • To accomplish this, a temp-table is built storing the RECID and chain number from the corresponding array fields in the _UserLock table.

  21. Record Locking Inquiries • In lockinq.p, we start with the _UserLock table to show available users in a browse. • The second query is opened using this RECID and chain number to read the _Lock record to get the table number. • The table number is then used to read the _file metaschema table to get the table name for the browse.

  22. Record Locking Inquiries • In lockinq.p, we start with the _UserLock table to show available users in a browse. • For each locked record selected, the table name and RECID are passed to the getfieldvalues procedure which dynamically creates a query for that record. • It then calls the procedure showtable which displays the field names and values for that locked record using the tfld temp-table.

  23. Record Locking Inquiries

  24. 4GL User Disconnect • The next program, disuser.p, allows someone to disconnect a user from within the 4GL. • The program uses the _connect and _myconnection VSTs.

  25. 4GL User Disconnect • The next program, disuser.p, allows someone to disconnect a user from within the 4GL. • The _connect table is used for the query and browse for connected users. • The _connect-usr values that are unknown and the previously disconnected users (connect-disconnect = 1) are filtered out. • The _myconnection table is used to prevent someone from disconnecting themselves. • This table was added to V9 to make it easy to identify which connection is the current session.

  26. 4GL User Disconnect • The next program, disuser.p, allows someone to disconnect a user from within the 4GL. • The actual user disconnection is performed by the _mprshut command using a temporary command text file as standard input. • Because a disconnection may take a few seconds, a pause of 15 seconds executes before re-opening the query. The _connect-disconnect field will be 1 when the disconnected process is finished.

  27. DB Area Statistics • The _DBStatus and the _AreaStatus VSTs supply useful information about the database and database areas.

  28. DB Area Statistics • In program dbareastat.p, the size of the bi is monitored along with the total number of locks, the highest number of locks and highwater marks for database areas. • In V9, the multi-volume bi size is reported by dividing the high water mark blocks by the bi block size.

  29. Record Requests • Promon provides a tremendous amount of database and performance statistics. • By using VSTs, this data can be processed any way desired.

  30. Record Requests • For example, the program userio.p shows the amount of activity for requests vs. record reads per user and in summary. • The _UserIO table provides information on a per user basis for reading and writing to the database, before-image and after-image files. This information may be found on the Block Access screen (3) in promon. • The _ActSummary table provides summary information and some of its data may be found on the Activity screen (5) in promon. • Some useful information provided by _ActSummary is the number of checkpoints and database buffers flushed.

  31. Record Requests

  32. Table Statistics • Table statistics are available through VSTs but not through promon. • Statistics on the number of times a table was read, updated, created and deleted are kept. • Note that data that is undone does not reduce the corresponding statistic. • Unfortunately, there is nothing in VSTs that link user access to specific records in tables. • For example, to show which records were read by a given user would be useful for security or for application troubleshooting purposes.

  33. Table Statistics • The program tablestat.p is used show the tables with the highest number of reads. • The Refresh button may be pressed to show the latest data. • Tablestat.p uses the _TableStat VST.

  34. Buffer Status • One of the most important parameters to set is the database buffers parameter, –B. • Setting it too high can waste memory and could cause swapping. • Setting it too low can cause increased disk access and performance degradation. • The _BuffStatus VST can be used to measure the effectiveness of the –B parameter.

  35. Buffer Status • In buffstat.p, the program opens the _bfstatus record in the query and displays it to the screen when the Refresh button is pressed. • This is the same information that is shown in promon under R&D->1 (Status Displays)-> 7 (Buffer Status).

  36. Buffer Status • In buffstat.p, the program opens the _bfstatus record in the query and displays it to the screen when the Refresh button is pressed. • With the –B set at 500, notice that the total buffers is 502. • One extra buffer is for the Database Master Block. • The 2nd block included is called the Index Anchor Block and is not used in V9.

  37. Buffer Status • If a long report is run, such as: for each registration: end. then the unused buffers become filled from the report.

  38. Setting Read Only Buffers • Question: How do we prevent all the buffers being used up by a report? Answer: Set the read only buffers on the client. • Two ways to do this: 1. At startup using the –Bp parameter 2. Updating the _MyConn-NumSeqBuffers field in the _MyConnection VST.

  39. Setting Read Only Buffers • Read only buffers (ROBs) are new to V9. The –Bp parameter specifies the number of read-only blocks a client may have. • ROBs are part of the total –B buffer pool, so that the remaining buffers are available for modified buffers. • Once a client has ROBs allocated to them, a large report’s records will stay confined to the ROBs.

  40. Setting Read Only Buffers • We will start a session with a –Bp = 64. • We will run a series of reports from the editor: for each student no-lock for each registration no-lock • The used buffers increases slightly, but then remains fixed.

  41. Setting Read Only Buffers • Next, we run updrobuf.p, and set the read only buffers equal to 0. • We then run the reports and notice that the used buffers equal to the total buffers. • The used buffers is a high water mark. It will not go down even though the buffers may be re-used.

  42. Customizing PRO*Tools Toolbar • A convenient place to put the VST programs you created is on the PRO*Tools Toolbar.

  43. Customizing PRO*Tools Toolbar • It is fairly easy to add your own tools to this toolbar. • Right-mouse-click on the toolbar and select Menu Bar. A pull-down menu bar will appear on the toolbar. • Select File->Customize… then press the Add button to add your program. • Select Run Persistent, Display on palette and supply an image. • Select “yes” to override protools.dat so that your tools will show up on every session.

  44. Customizing PRO*Tools Toolbar • Make sure that the PROPATH environment variable contains the directory where your VST programs are located. • Two include files are required in order to be compatible with the PRO*Tools toolbar. • _adetool.i – contains a single internal procedure called ADEPersistent, which prevents the Progress Editor from removing the instance when running other programs. • _runonce.i – prevents multiple instances of the tool to be run at the same time.

  45. Customizing PRO*Tools Toolbar • Each option also contains its own dynamic window and is parented to the PGA toolbar window.

  46. Acknowledgements • I would like to thank Dan Foreman for writing the Virtual System Tables Guide. It was very helpful in creating this presentation.

  47. Summary • VSTs provide 4GL access to information contained in promon. • Many useful reports and tools may be created.

More Related