OpenEdge Database VST Wish List

NEW!! Tables _Connect and _MyConnection:

It would be nice to have an updateable field in both tables - preferrably char - to publish user or workstation specific data to the database, like an application internal user identification or such (staff ID#).

Benefits are that if the connection is dropped for some reason, that user-supplied information will disappear as the _Connect and _MyConnection VST records disappear.

Additional info:

Some users currently have a solution of their own, a combination of VST and tables, where users fill in data at the moment they log onto the app, maintain it during app lifetime and clear it when they end their session. But this implies a running batch job to periodically compare the table against the 'live' VST records. A user-supplied data field would make the need for a batch job redundant.

NEW!! New table: _DbLog

A simple VST solution to access the contents of the current .lg file by 4gl or SQL. Should contain date, time, server/broker information, user information, and the log text.

This could be very useful for stations having no direct access to the server machine but have a 'Progress connection', like remote admins, or DataServers replicating important information to somewhere else.

What is that $@#^&% 4gl session doing!?!

It is hard to overstate the need for this.  Strictly speaking this might not be viewed as a “database” issue.  The database, however, is viewed by customers and developers as “guilty until proven innocent” when there are performance problems and this is a major stumbling block in resolving those problems.  Without specific and clearly actionable items to assign to 4gl developers code driven performance problems either fester unattended or are “resolved” through hardware upgrades while blame, in all cases, reverts to the database.

It is generally easy use PROMON or VSTs to see that a particular db session is causing problems.  But it is often impossible to determine the root cause of the problem because there is no information available to the DBA about what the user is actually doing.  “Calling the user” and asking them what they are doing is rarely productive – the user is often simply “using the system” with no understanding of the internals of that code.  Many times the users cannot even pinpoint what specific modules they might be running.  Or the user is unavailable.  Or the session is a batch client.  Static analysis of the code is complex and leads down many unproductive paths.  Features like sending SIGUSR1 don’t work on all platforms and are unreliable for determining the session state.  DBA’s need more insight into what the client is executing at the moment a problem is occurring in order to lead programmers to the problem so that they can fix it.

One possible path would be to track at least the program name and the debug line# associated with every db reference.  We would then be able to determine the last 4gl line that we know the client executed.  (It would be better to somehow have the whole execution stack but I’m assuming that would require more work with the 4gl.)  Just passing a few additional bytes when it’s already talking to the db engine seems more feasible and within the capabilities of the db group.

A suggested _UserDBRef VST:

Field Name	Description
_UserDBRef-Id	Unique Identifier
_UserDBRef-Usr	User Id running the utility
_UserDBRef-Name	Name of the User
_UserDBRef-QId	Unique Id of this DB reference
_UserDBRef-QStr	WHERE clause that goes with the query (some other useful data for creates, updates & deletes...)
_UserDBRef-ProgramName	The 4gl program which is making this reference.  The entire 4gl program stack would be the ultimate
_UserDBRef-ProgramLine	The DEBUG line# in the program which made this reference.
_UserDBRef-Lock	Lock type
_UserDBRef-DBAccess	Number of DB Accesses caused by this reference

Ideally this VST would track not only the most recent db reference but a list of the N most recent references (like the _Checkpoint VST but with a tunable “window”).  It also seems possible that such a VST could eventually provide detailed information regarding the processing of individual queries – i.e. the number of db accesses, os reads & writes, fragments read, records returned to the client, locks taken, server side functions vs client side functions and so forth.

_UserTableStat and _UserIndexStat

_TableStat & _IndexStat are very useful VSTs.  But the inability to drill down into the activity of an individual session prevents them from being fully leveraged.  In a large production environment there is too much “noise” to make effective use of these VSTs to reliably analyze a specific issue.  User specific versions of these VSTs are needed in order to separate the wheat from the chaff.

64 Bit Counters

Some customers roll over the 32 bit counters several times a day.  Others roll over every few days.  In either event it makes it very difficult to monitor and manage high availability systems with VST based programs.

_Cache

_Cache would be similar to _Block but specific to blocks that are in the buffer pool.  This would permit analysis of the effectiveness of various strategies for managing the buffer cache.  One could, for instance, use this feature to count blocks from various storage areas to establish their share of –B or to count blocks from particular tables to validate hypothesized root causes such as large reports hogging –B thus supporting the use of -Bp.

Current Bi Cluster

It is not currently possible to determine the mapping between the “current” checkpoint number and the current bi cluster without having a transaction active (or starting a transaction):

        do transaction:
          create dictdb._user.
          find first dictdb._Trans no-lock where _Trans-num = dbtaskid( "dictdb" ).
          find _BuffStatus no-lock.
          chkp-base = _Trans-counter - _BfStatus-LastCkpNum.
          undo.
        end.

This shows the problem (you need to run it against an active db that sometimes has transactions and sometimes doesn’t):

        define variable trx as integer no-undo.

        do while lastkey <> 4:

          trx = 0.

          for each _Trans no-lock where _Trans-usrnum <> ?:
            trx = max( trx, _Trans-counter ).
          end.

          find _Buffstatus no-lock.
          display _BfStatus-LastCkpNum trx.
          pause 1 no-message.

        end.

A new field is needed in one of the VSTs that stores variations on LastCkpNum.  Perhaps  _NextBiCluster would be the correct name for the field.

4gl TRX Trace

It would be extremely useful to add a field to display the 4gl program name and line number that started a transaction in the _Trans VST.  In this way poorly constructed 4gl code (transactions that span user input or which are simply too large in scope) could be nailed down and assigned to programmers to be properly dealt with.

_Trans._Trans-txtime

Accessing this as a string is a PITA.  There should be a DateTime version of this field (actually that’s true of all the places where formatted date and time strings are being stored).  There should also be a field indicating when a transaction was allocated – that way we can determine how old long standing allocated transactions are.

_zQIL

A VST based interface to the query analysis data that –zqil writes to the .lg file.

_TabAnal and _IdxAnal

A “persistent VST” (something new…) that simply records the results of proutil –C tabanalys in an easily accessible format (an alternative would be to add UPDATE 4GL STATISTICS command).  This would make customized Table Analysis reports very easy to write and would also allow quick approximations of record counts to be readily determined by 4gl programs.

_UtilStatus

This VST has considerable overlap with _UserStatus but it is better named.  _UtilStatus ought to provide more insight into database utilities.  In particular I would like to be able to use a VST based program to monitor the progress of binary dump & loads and index rebuilds.  The information that is currently output by these utilities (table being worked on, phases that they pass through, record counts processed etc) should be available via this VST.

 

Field Name	Description
_Util-Id	Unique Identifier
_Util-Usr	User Id running the utility
_Util-UtilName	Idxbuild, Idxcompact, BinLoad, BinDump, TableMove, TabAnalys, IdxAnalys, DBAnalys, IdxFix, ProBkUp, ProRest, ProStrct Create, dbrpr etc…
_Util-Start	DateTime when the utility started running
_Util-Phase	“Phase” that a utility is currently executing.
_Util-Progress	Informative progress indicator such as the record count displayed by idxbuild.
_Util-Expected	Expected count (when known)
_Util-ObjectId	_StorageObject._Object-Num being worked on
_Util-ObjectType	_StorageObject._Object-Type being worked on
_Util-Source	Table, Area or file name being worked on
_Util-Target	Table, Area or file name being worked on
_Util-Param	Parameter such as % compaction
_Util-Config	Configuration options provided by the user such as startup parameters (may require multiple fields)
_Util-Resources	Resources allocated by the utility such as temp file names (may require multiple fields)

Client Side VSTs

The 4gl needs VSTs for the client.  Temp-tables are an obvious area to expose with a set of VSTs – currently there is no analysis, management or tuning information available for temp-tables.  All you can do is guess at a value for –Bt and increase it if the DBI file on disk grows.

• # of data blocks used
• # of index blocks used
• logical reads & write
• physical reads & writes
• hit ratio
• CRUD data on a per temp-table table basis (_TempTableStat like _TableStat)

Other obvious areas are the information presented in –y, -yx etc output, client IO data (reads & writes to various files, named streams, log files, lbi, srt & DBI files that database VSTs are ignorant of) and, of course, the ever popular 4gl execution stack.

Less obvious, but very compelling, metrics to gather on the client side have to do with client side sorting and selection of records.  When analyzing performance problems it is sometimes the case that there is a gap between the db engine and the 4gl client point of view regarding how many records a query returns.  For instance:

        find first _userio where _userio._userio-usr = 0 no-lock.
        display _userio._userio-dbaccess with side-labels.
        pause.

        find customer where customer.cust-num = 84 no-lock.

        find first _userio where _userio._userio-usr = 0 no-lock.
        display _userio._userio-dbaccess with side-labels.
        pause.

        find customer where customer.address = "34 Dudley St" no-lock.

        find first _userio where _userio._userio-usr = 0 no-lock.
        display _userio._userio-dbaccess with side-labels.
        pause.

The 1st FIND CUSTOMER is an indexed FIND and only returns the one record. DB accesses are 2 just as one would hope. The 2nd FIND CUSTOMER isn't indexed and scans the whole darned table -- 169 db accesses (or 83 records) to return 1 “useful” record.  The 2nd query would benefit from an index on customer.address.

In this example it is fairly easy to see that the desired result would have been “1” record from the 4gl POV – but in most real world situations you cannot determine by simple inspection of the source what the 4gl was expecting to see in a given block of code.

A new VST called _MyTableStat (a client-side version of _TableStat) would show 1 record read.  If that were compared to _UserTableStat (the server oriented user specific version of _TableStat) result of 83 then we would know that we have a problem.

Without such a VST there is no way to automatically determine at runtime that only 1 useful record was returned.  This is an example of a very common problem in running systems – code is introduced into production and is running.  A user has a very high logical IO rate.  You know that that user is almost certainly running a query that isn’t properly indexed.  But you cannot determine 1) What table the logical IO is against  2) what the WHERE clause was  3) what 4gl program is executing 4) how many of the records the db engine returned to the 4gl client are actually usable by that client session’s code or 5) which of the many WHOLE-INDEX candidates revealed by a compile XREF is actually guilty.  _MyTableStat makes it possible to debug this at the client – even automatically if you embed appropriate checks into the application.

If the various client side VSTs were implemented in shared memory they would then be available from the server’s point of view enabling centralized analysis and management of the session specific data.

_TriggerStat, _TableTrig and _UserTrig

Similar to the _TableStat family but focused on database triggers.  3 layers of statistics are needed – global, by table and by user by table.  At each layer statistics regarding the execution of database triggers should be collected.

Field Name	Description
_Trig-Id	Unique Identifier
_Trig-Usr	User Id (where applicable)
_Trig-Table	Name of the table (where applicable)
_Trig-FIND	Number of FIND trigger invocations
_Trig-CREATE	…
_Trig-DELETE	…
_Trig-WRITE	…
_Trig-ASSIGN	…
_Trig-RepCREATE	…
_Trig-RepDELETE	…
_Trig-RepWRITE	…
_Trig-FIND-err	Number of FIND trigger invocations that returned errors.
_Trig-CREATE-err	…
_Trig-DELETE-err	…
_Trig-WRITE-err	…
_Trig-ASSIGN-err	…
_Trig-RepCREATE-err	…
_Trig-RepDELETE-err	…
_Trig-RepWRITE-err	…