DB-13: Understanding VSTs

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.