Transcript Presentation

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
 Major consulting clients include Foxwoods Casino,
Health Informatics, Bank One, Textron Fastening
Systems, and American Academy of Orthopaedic
Surgeons
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
2
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
3
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
4
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
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
5
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
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
6
VST Inquiry
 In program
vstinq.p, the
VST schema
may be
viewed.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
7
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 ?.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
8
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
9
VST Characteristics
 Let’s look at the _Trans table:
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
10
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
11
VST Characteristics
 Let’s look at
_UserLock
table. It shows
which locks a
particular user
currently holds.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
12
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 promon
session.
 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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
13
VST Characteristics
 The _Lock
file will look
like the
following:
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
14
Resolving Locking Conflicts
 As a developer, you had 2 choices in resolving locking
conflicts:
Option 1: Let Progress handle it.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
15
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
16
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!
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
17
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
18
Record Locking Inquiries
 For each user, Progress provides up to 512 locked
records for display and lookup.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
19
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
20
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
21
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
22
Record Locking Inquiries
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
23
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
24
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
25
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
26
DB Area Statistics
 The _DBStatus and the _AreaStatus VSTs supply
useful information about the database and database
areas.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
27
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
28
Record Requests
 Promon provides a tremendous amount of database
and performance statistics.
 By using VSTs, this data can be processed any way
desired.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
29
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, beforeimage 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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
30
Record Requests
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
31
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
32
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
33
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
34
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).
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
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.
 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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
36
Buffer Status
 If a long report is run, such as:
for each registration:
end.
then the unused buffers become filled from the report.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
37
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
38
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
39
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
40
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
41
Customizing PRO*Tools Toolbar
 A convenient place to put the VST programs you created
is on the PRO*Tools Toolbar.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
42
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
43
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.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
44
Customizing PRO*Tools Toolbar
 Each option also contains its own dynamic window and
is parented to the PGA toolbar window.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
45
Acknowledgements
 I would like to thank Dan Foreman for writing the Virtual
System Tables Guide. It was very helpful in creating
this presentation.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
46
Summary
 VSTs provide 4GL access to information contained in
promon.
 Many useful reports and tools may be created.
Copyright © 2005
Paul Guggenheim & Associates
DB-13: Understanding VSTs
47