Feedback
Did this article resolve your question/issue?

   

Article

Why do VSTs not behave like normal tables?

Information

 
TitleWhy do VSTs not behave like normal tables?
URL NameVSTs-do-not-behave-like-normal-tables
Article Number000135592
EnvironmentProduct: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Other: VST
Question/Problem Description
What is the reason Virtual System Tables not have indexes ?
Why do most Virtual System Tables show records with all fields unknown ("?") ?
Why are BY clauses ignored when using VSTs ?
Why do VSTs not behave like normal tables ?
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
VSTs are called virtual tables for a reason, they have no physical record.

VST tables don't use the regular table storage mechanisms, VST data is not stored anywhere, they have no physical record.

When querying a VST, records are created “on run time” when retrieved. The database engine will invoke a dedicated function to pull data directly out of it's internal shared memory structures (in whatever arbitrary format they currently have) and reformat that data into the record storage format. This makes the data available to the client, so that the client can process them as if they were database records to describe current database status and activity.

The structures the data gets pulled from are mostly fixed-size lists, sized based on database startup parameters. Example:
  • -n determines number of slots available to _connect; -L sizes _Lock;
  • The Transaction table is the comprised of -Mn -n -L -maxids 
  • VST Queries that return records that show as all unknown "?" values are the slots in the internal memory structure which currently aren't used. In releases prior to OpenEdge 11.5, unknown "?" values in _Lock (in 11.5 a change was made to filter them out).
When running a VST query, while it may look a regular table, what you really see depends on how the database engine manages it's internals, and anything that the storage manager would take care of (including indexing, sorting etc.) is not guaranteed to work the way you'd expect - that really depends on how the dedicated function supporting a particular VST is implemented.

Example:  When the function underlying the VST doesn't do any sorting on the values, a FOR FIRST will just grab whatever record is the first to satisfy the WHERE clause, and the BY clause ends up simply being ignored. 

As a general guideline use a NO-LOCK VST query with a simple WHERE clause which takes a snapshot, then store that information in a temp-table. You can then analyse the data from that temp-table at it's own pace, with proper indexing/sorting guaranteed and without having to consider further that the contents of these VSTs can be highly volatile.
 
Workaround
Notes
Last Modified Date11/20/2020 7:17 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.