The history of VST changes by versions

[quote user="George Potemkin"]By the way, _File._Last-Change returns 463860476 that means "Sep 12, 1984 18:07:56" for all VST tables regardless from their versions. 1984 is the year of the first Progress commercial release (V2) but VSTs were originally added in V8.2 (Apr 28, 1997). Is it some secret date inside Progress like Chip Ziering's birthday?[/quote]

The comment on that value says "date/time of last schema change set in empty database by the scdbg utility. it is 9/12/84 2PM." The date was chosen in 1991 or earlier, but the source control system doesn't go back far enough to determine exactly when it came into existence.

My guess is that the developer needed a value so they spit out the current time and used it.

All system tables has the same _Last-Change except for:
_Db
_File
_Field
_Index
_Index-Field
Their _Last-Change is 617740379 or 07/29/1989 18:32:59
Another birthday?

FUNCTION Timestamp RETURN DATETIME(ipTimestamp AS INTEGER):
  RETURN ADD-INTERVAL(DATETIME(1, 1, 1970, 0, 0, 0, 0), ipTimestamp, "seconds":U).
END FUNCTION. /* Timestamp */

FOR EACH _File NO-LOCK
   WHERE _File-Number LT 0
     AND _Last-Change NE 463860476:
     DISPLAY _File-Name _Last-Change Timestamp(_Last-Change).
END.

Update: the files above (except _Db) exist in all versions of Progress. _Db was added in V5 or V6 (from Dan Foreman's "Progress System Tables" PPT).

My guess: Sep 12, 1984 - on this date in 1984 Michael Jordan signed his first NBA contract with the Chicago Bulls.

[quote user="George Potemkin"]

All system tables has the same _Last-Change except for:
_Db
_File
_Field
_Index
_Index-Field
Their _Last-Change is 617740379 or 07/29/1989 18:32:59
Another birthday?

[/quote]That one is called VERS6DATE in the code. V6 was released in 1989. The comment says "date/time of last schema change set in vers6 database by scdbg uitilty. it is 7/31/89, and only for _file _db _index _field _index-f".

Very ancient kbase # 13191: How to determine the version compatibility of your database

     version 11      PROGRESS 2.1
     version 12      PROGRESS 2.2 and 2.3
     version 13      PROGRESS 2.4
     version 30      PROGRESS 3.0
     version 31      PROGRESS 3.0 after 12/18/85
     version 32      PROGRESS 3.0 after 12/19/85
     version 40      PROGRESS 4.0 after 1/7/87
     version 41      PROGRESS 4.0 after 1/19/87
     version 42      PROGRESS 4.0 after 2/28/87 (after-image)
     version 50      Reserved for PROGRESS with foreign dbs
     version 51      PROGRESS with foreign dbs after 1/13/88
     version 60      Resrved for PRGRS with foreign dbs & SQL
     version 61      PROGRESS Release 5.0 and 6.0 from 4/12/88
     version 62      PROGRESS Release 6.0 after 8/01/89
     version 63      PROGRESS Release 6.0A after 8/14/89
     version 64      PROGRESS Release 6.3A1B after 10/31/91

KB-P25408: The History of Progress Software Corporation

1981 – Progress Software Corporation was founded.
1983 – Though still not commercially available, RDL was ready to be shown at COMDEX.
1984 – Renamed Progress Version 2, this was the first commercial release.
1985 - Version 3
1987 - Version 4
1988 - Version 5
1990 - Version 6
1991 – Version 6.3
1993 – Version 7
1995 - Version 8
1998 - Version 9.0

No matching dates

That would be fairly close to the date when I first used Progress.  I discovered it at the 1984 COMDEX and did an eval that fall.

BTW, 1989 seems to be a birthday of Progress Knowledgebase.

For example:

ALL BTOS VERSIONS-local bi(lbi) causes disk fragmentation

Written by SLK 01/13/89

Ghostly big brother looking for everyone, or just a simple bug bug, that shows no content on Great Gus post?

I attached the file (in the first post) with the history of the VST changes on the field level. It does not replace the documentation or Dan Foreman's VST Guide. Field descriptions in the file are just the field labels.

By the way:

1. Are there any plan to add new VST (or to extend the _Latch VST) that would report the same information as promon/19. BHT Latch Stats?

2. The _ActRecord table does not have the *-PartitionId field like in _ActIndex or in other correspondent VSTs. Is it a defect?

1. Can't answer that officially but I know of no current effort to add that.  Maybe an enhancement request is in order.

2. I don't know what the partition id in the _actindex refers to.  I think that is the bug.

1. Can't answer that officially but I know of no current effort to add that.  Maybe an enhancement request is in order.

2. I don't know what the partition id in the _actindex refers to.  I think that is the bug.

All VSTs (except _ActRecords) related to the objects resided in the areas seem to have the *-PartitionId fields:

11.6 _Connect-PartitionId               inte
12.0 _UserLobStat-PartitionId           inte
11.0 _UserLock-PartitionId              inte[512]
11.0 _Index-PartitionId                 inte
11.0 _Lock-PartitionId                  inte
11.0 _TableStat-PartitionId             inte
11.0 _IndexStat-PartitionId             inte
12.0 _LobStat-PartitionId               inte
11.0 _UserTableStat-PartitionId         inte
11.0 _UserIndexStat-PartitionId         inte

Yes, but don't each of those VST columns refer a particular object number /partitionId combination.

The _actindex refers to a summary of all index activity, not just one particular index / partition so what does the partition Id mean in that case?

Thanks, Richard, I got it.

_Connect VST has the field _Connect-ServerTid (the thread ID of the client's server) but _Servers or _ActServer VSTs don't have such fields. Should we ignore this field?

What is the purpose of _Connect-Tid (the thread ID of the user session)? ABL sessions can consist of two threads if a database is running with a non-zero -usernotifytime but the second thread is just an attention thread and it does not have a separate record in _Connect table.

_Connect-Tid: PASOE clients are multi-threaded clients to the db.

The thread IDs do not seem to be used to accumulate the activity of PASOE clients or the activity of new multi-threaded servers. _ActServer, _UserIO, _User*Stat don't have the Tid fields. Am I understand correctly that the _Connect-Tid, _Connect-ServerTid, _MyConn-Tid, _MyConn-ServerTid fields are for a troubleshooting only?

"Am I understand correctly that the _Connect-Tid, _Connect-ServerTid, _MyConn-Tid, _MyConn-ServerTid fields are for a troubleshooting only?"

Yes, just like pid's are used for "troubleshooting".  The stats you are referring to are collected on a database connection level, not on a pid/tid level.

I did not yet deal with the multi-threaded PASOE clients.

I see how the multi-threaded servers are represented in _Connect table:

Db log:

[2019/12/03@09:37:27.498+0300] P-15608      T-15704 I TSRV    1: (452)   Login by George on CON:. 
[2019/12/03@09:37:28.472+0300] P-15608      T-7008  I TSRV    1: (742)   Login usernum 24, userid George client type ABL , on GP using TCP/IP IPV4 address 127.0.0.1. 
[2019/12/03@09:37:59.734+0300] P-15608      T-16004 I TSRV    1: (742)   Login usernum 23, userid George client type ABL , on GP using TCP/IP IPV4 address 127.0.0.1. 

Program:

FOR EACH _Connect WHERE _Connect-Usr GT 0:
  DISPLAY
    _Connect-Usr 
    _Connect-PID
    _Connect-TID
    _Connect-Type
    _Connect-Server 
    _Connect-ServerPid
    _Connect-ServerTid.
END.

_Connect records:

User-Id   Pid   Tid Type Srv Server Pid Server Tid
------- ----- ----- ---- --- ---------- ----------
 1      15608 15704 TSRV  ?           0          0
23      12940  2868 REMC  1       15608      16004
24      12940  2868 REMC  1       15608       7008

Server process consists of 3 threads (at least). One of them owns a slot in Usrctl Table. Two other threads serve the remote users but they share the _Connect record with the first thread.

Would be the same for the multi-threaded clients? Will the only one thread be represented by a record in _Connect table? We will not have the duplicated values of the _Connect-Usr field, will we?

Will the “waits” be reported per _Connect-Usr or per _Connect-Tid?
_Connect-Wait
_Connect-Wait1
_Connect-Wait2

Back to the history...

The file in the first post is updated. Also VstVersion.p program is added.

VstVersion.p reports the version of VST set in DICTDB database – a first Progress version that can create a database with the same set of VST tables. For example, database created in V11.2 uses the same set as in V11.1, Progress V11.7 can open a database that has one of 12 different VST sets. VstVersion.p can be used as a reminder to run updatevst.

VstVersion.p identifies a VST version based on the table below.

The changes of the number of VST tables, the number of their fields and the sum of their CRCs:

Version  Tables Fields  Sum of CRCs
8.2A      30      426 * 1102031
8.3A      35      463 * 1300471
9.0A      39      495 * 1652590
9.1D      42      532 * 1733574
10.0A     44      545   1768780
10.1A     45      549   1800468
10.1B     47      564   1395763
10.1C     47      572   1408370
10.2B     47      577   1389841
10.2B05   47      581   1418527
10.2B06   47      590   1379260
11.0      47      605   1403679
11.1      47      616   1411960
11.3      47      617   1379269
11.4      47      623   1427556
11.5 **   47      631   1422804
11.6 **   47      678   1471059
11.7 **   47      744   1523741
11.7.3 ** 47      745   1526457
11.5      48      640   1479956
11.6      52      763   1590668
11.7      54      854   1707500
11.7.3    54      855   1710216
12.0      55 ***  862   1837964
12.1      55      867   1766538

*) Only for VST tables the _File._numfld does not match _Field._field-rpos.

For most VSTs the _File._numfld equates to (2 * _Field._field-rpos - 2)
For some VSTs the deviation from the formula is +/- one.
Fixed in V10.0A.
The values above are a real field count.

**) After proutil -C disablenewvsttables

Proutil -C enablenewvsttables/disablenewvsttables were introduced in V11.5.
They are the retired commands since V12.0 (message #19352).

Disablenewvsttables is intended to downgrade a database from 11.5 to an earlier version (11.0 to 11.4).

Proutil -C disablenewvsttables removes "New VST Tables" feature bit (_Database-Feature._DBFeature-ID = 23):
fmaFeatureDisable: The feature New VST Tables has been disabled. (11175)

Disablenewvsttables drops the VST tables with _File-Number LE -16432 (if they exist):

 -16432  _DbParams                  Added in 11.5
 -16433  _Repl-AgentControlActivity Added in 11.6
 -16434  _Repl-AgentActivity        Added in 11.6
 -16435  _DbServiceManager          Added in 11.6
 -16436  _DbServiceManagerObjects   Added in 11.6
 -16437  _Cache                     Added in 11.7
 -16438  _Repl-InterAgentActivity   Added in 11.7

Disablenewvsttables does not drop the fields added in V11.5 and higher to old VST tables.

Access to the fields that exist in a downgraded database but are unknown for a current version of Progress will result in the errors 450 and 3191:
SYSTEM ERROR: Cannot read field from record, not enough fields. (450)
SYSTEM ERROR: Failed to extract field <field-num> from <file-name> record (table <table-num>) with recid <RECID>. (3191)

Feature bit can be easy zeroed in master block. This will allow to open a V11.5+ database by earlier Progress version without deleting new VST tables. In this case a query to VST tables that exist in a database but unknown to a current Progress version will crash a session.

Proutil -C enablenewvsttables restores "New VST Tables" feature bit but it does NOT re-create new VST tables.

Proutil -C updatevst will re-create them. It also enables and activates the feature bit automatically:
New VST Tables has been enabled for database empty. (12479)

_DBFeature-ID = 23 is "Reserved" in V12.

***) V12.0: 2 tables were dropped and 3 tables were added.

In V8.2 and V8.3 VSTs need to be explicitly enabled:
proutil db -C enablevst

Once enabled the VSTs can't be disabled:
proutil empty -C disablevst

PROGRESS version 8.2A as of Mon Apr 28 1997
Not implemented

PROGRESS version 8.3A as of Wed Aug 05 1998
VST Table Deletion has begun. Please standby.
SYSTEM ERROR: ixdel with no transaction. (422)

Proutil -C enablevst/disablevst commands don't exist in V9 and above.

proutil -C Conv89 | Conv910 | Conv1011 | Conv1112 automatically updates VSTs to the same version as the correspondent updatevst.

Right now there are 24 different sets of VST tables.

Hi Marian! Do you mean the following?

_*-Attributes fields introduced in V11.0 as logical extent 64 a.k.a. 64 bits: _*-Attributes[i] = TRUE => bit "i" is on.

_File-Attributes

1 Multi-Tenant Enabled Table ("m")
2 Keep Default Area (the default area is to be kept for the default tenant)
3 Partition Enabled Table ("p")
4 The meaning is unknown
5 CDC Source Table ("c")
6 CDC Change Table ("ct")

_Index-Attributes

1 Local (otherwise Global)

_Field-Attributes

1 Multi-Tenant Enabled LOB

_Seq-Attributes

1 Multi-Tenant Enabled Sequence ("m")

Yes, something like that only with corresponding keyword in data definition syntax (if any): MULTITENANT, ENCRYPTION, CIPHER-NAME, IS-PARTITIONED, NO-DEFAULT-AREA, BUFFER-POOL, IS-LOCAL...

Results of quick search:

10.2B ENCRYPTION
10.2B CIPHER-NAME
10.2B BUFFER-POOL
11.0 MULTITENANT
11.0 NO-DEFAULT-AREA
11.4 IS-PARTITIONED
11.4 IS-LOCAL

Off-topic question: how to differentiate the undocumented information and the information that Progress does not like to share publicly?

For example, there are the 4GL functions that are not KEYWORD'ed. Is it done intentionally?

The side result of VST investigation was a collecting information about schema table changes. The database schema is not changing in the service packs – only the major Progress versions are allowed to change database schema. Column “Change” in the Excel file contains the values: “new”, “dropped” or the number of fields added (or dropped) to a table in the given release. Zero can mean the changes of field types (e.g. integer -> int64).

Update: the uploaded files were moved to the first post.

The values in _Database-Feature records:

10.0A _Database-Feature[-16427]

From  To     # Feature Name                    Ver   Command to enable/disable a feature
----- ----- -- ------------------------------- ----- -----------------------------------
10.0A 10.1A  0 V100A_STORAGE
10.0A 10.1A  1 FATHOM_REPLICATION              9.1D  proutil -C enablesitereplication / disablesitereplication
10.1A 10.1A  2 FAILOVER_CLUSTERS               10.0A _dbutil prostrct cluster enable / disable
10.0A 10.1A  3 PEER_DIRECT                     10.0A proutil -C enablepdr / disablepdr
10.0a 10.1A  4 *** Invalid Feature ***
10.0A 10.1A  5 LARGE_FILES                     9.1C  proutil -C enablelargefiles
10.1A 10.1A  6 AUDITING                        10.1A proutil -C enableauditing / disableauditing
10.1A 10.1A  7 AI_ARCHIVER                     10.1A rfutil  -C aiarchiver enable

10.1B        1 OpenEdge Replication            10.1B proutil -C enableoerepl / disableoerepl  a.k.a. 9.1D enablesitereplication / disablesitereplication
10.1B        2 Failover Clusters               10.0A _dbutil prostrct cluster enable / disable
10.1B        3 DataXtend Remote Edition        10.1B proutil -C enableDXRE / disableDXRE
10.1B        4 Reserved
10.1B        5 Large Files                     9.1C  proutil -C enablelargefiles
10.1B        6 Database Auditing               10.1A proutil -C enableauditing / disableauditing
10.1B        7 JTA                             10.1A proutil -C enablejta / disablejta
11.7         8 After Image Management/Archiver 10.1A aiarchiver enable / disable
10.1B        9 64 Bit DBKEYS
10.1B       10 Large Keys                      10.1B proutil -C enablelargekeys (for 4K or 8K dbblocksize)
10.1B       11 64 Bit Sequences                10.1B proutil -C enableseq64
10.1B       12 DataXtend Integration Edition   10.1B proutil -C enableDXIE / disableDXIE
10.2B       13 Encryption                      10.2B proutil -C enableencryption / disableencryption
11.0        14 Multi-tenancy                   11.0  proutil -C enablemultitenancy / disablemultitenancy
11.2  11.7  15 Concurrent JTA and Replication
11.4        16 Reserved
11.3  11.7  17 MT Index Rebuild                11.3  proutil -C enablemtidxbld / disablemtidxbld (retired since 12.0)
11.3  11.7  18 MT Data Move                    11.3  proutil -C dbrestrict datamove enable / disable
11.3        19 Roll Forward Restricted Mode    11.3  proutil -C dbrestrict rollforward enable / disable
11.4  11.7  20 TP Index Rebuild                11.4  proutil -C enabletpidxbld / disabletpidxbld (retired since 12.0)
11.4        21 Table Partitioning              11.4  proutil -C enabletablepartitioning / disabletablepartitioning
11.5  11.7  22 Read-only Partitions            11.5  proutil -C enablereadonlypartitions / disablereadonlypartitions (retired since 12.0)
11.5  11.7  23 New VST Tables                  11.5  proutil -C enablenewvsttables / disablenewvsttables (retired since 12.0)
11.5  11.5  24 TP Partition Move                     proutil -C enablepartitionmove / disablepartitionmove (was not implemented)
11.6        25 Partition Copy                  11.6  proutil -C dbrestrict partitioncopy enable / disable
11.6        26 Authentication GATEWAY          11.6  proutil -C enableauthgateway / disableauthgateway
11.6        27 Change Data Capture             11.6  proutil -C enableCDC / disableCDC (since 11.7)
11.7        28 Backup Counter Extension        11.7  proutil -C enablebackupcounterextension / disablebackupcounterextension (mb_bkincr_HIGH, mb_buseq_HIGH)

The features that seemed to be not implemented:

15 Concurrent JTA and Replication
24 TP Partition Move

The features that are retired since 12.0:

17 MT Index Rebuild
20 TP Index Rebuild
22 Read-only Partitions
23 New VST Tables

"Save Key Events" feature is not reported by _Database-Feature but reported by proutil -C describe.

Since V10.1B the enablekeyevents command creates the _KeyEvt table.
Proutil -C disablekeyevents does not remove the _KeyEvt table from metaschema.
4GL sessions are unable to check if the "Save Key Events" feature is currently enabled.

_Database-Feature VST gets data from the fields in Master Block:

mb_fmaDBContent (8 bytes) Active  feature list
mb_fmaDBEnabled (8 bytes) Enabled feature list

Old features are still stored as the single-byte fields in Master Block but these values are not used except mb_SaveKeyEvents:

mb_largeFilesEnabled  enablelargefiles
mb_replEnabled        enablesitereplication / disablesitereplication
mb_pdrEnabled         enableDXRE / disableDXRE
mb_clustEnabled       procluster enable / disable
mb_XAsupportEnabled   enablejta / disablejta

"Large Keys" feature is duplicated in _Db._Db-res1[1]

Since V11 Progress by default calculates what seems to be an alternate (SQL) CRC: _File._Fil-res1[1]
It’s an addition to 4GL CRC: _File._CRC.

Article: Format of field _File._Fil-res1 is too short for values in OpenEdge 11
https://knowledgebase.progress.com/articles/Article/000043719

FOR EACH _File:
  DISPLAY 
    _File._Tbl-Type
    _File._File-Name
    _File._Fil-res1[1] FORMAT ">>>>>>9"
    _File._CRC.
END.

Both values are evenly distributed in the range from 0 to 32K.

Both are not applied to SQL Views (_Tbl-Type = “V”).
Both are changing if we are modifying a table structure.
But they are not equal.

I guess these CRCs are calculated based on the different sets of field properties. For example, 4GL CRC uses 4GL data type (_Field._Data-Type) while SQL CRC uses SQL data type (_Field._Fetch-Type). Is it correct?

Can we use SQL CRC to track the changes of the tables that are ignored by 4GL CRC? Or do they always change synchronously? For example, if we change a field type from “integer” to “int64” then SQL type will be automatically changed from “integer” to “bigint”. So both CRCs are changed.

> On Dec 16, 2019, at 12:57 PM, George Potemkin wrote:

>

> Since V11 Progress by default calculates what seems to be an alternate (SQL) CRC

dont know about this particular item, but historically, various _fil-res*[*] schema items (and simlarly named ones in other schema tables) have been used by the openedge dataserver code.

the openedge sql server does not care about default 4gl display formats.

Gus, can I upload here your "Metadata" (Engine Crew Monograph Number 17) in pdf format? It's sad that nowadays we can find on the Internet only the shadows of these monographs.

> various _fil-res* schema items (and simlarly named ones in other schema tables) have been used by the openedge dataserver code.

_Db._Db-res1[1] stores the "Large Keys Support" flag, I guess, since proutil -C enablelargekeys was introduced in 10.1B 

Update: enablelargefiles command was mentioned by mistake

> On Dec 17, 2019, at 10:06 AM, George Potemkin wrote:

>

> _Db._Db-res1[1] stores the "Large Keys Support" flag

these "res" fields were put in so that if we needed to add something we could do it without a database version change. at times we used them for things like the large keys flag. the intent was that in the next major release, they would be replaced by properly named fields. this did not always happen.

the dataservers were orginally on a different release cycle than the main product so they used a lot of the "res" fields for their own purposes. a given field might be used for one thing by the oracle dataserver and something else by the sybase dataserver.

later we added the feature bitmaps so we could add new things without waiting for the next (major) release that included a database version change.

> On Dec 17, 2019, at 3:07 AM, George Potemkin wrote:

>

> Gus, can I upload here your "Metadata" (Engine Crew Monograph Number 17) in pdf format?

yes. feel free to post that and any of the others you wish.

i also have a newer metadata document. it isn't quite up to date since i wrote it in the 10.2 time frame.

All files are moved to the first post.

New finding:

Since V12.0 proutil -C dbauthkey does not update _Db._Db-revision (integer) anymore. Now it updates _Db._Db-auth-key added in V12.0.

The _Db-auth-key value looks like: oakb1::qKgHqEYTQ3AXhvXquJN0bW+Zz2WEfOQffDqngiCO9EMD7Pad2D+TN/rc5hIrEze

The message is changed as well:

Before 12.0: Database authorization key changed to <newkey>. (1973)

Since 12.0: Database authorization key has been changed successfully. (19415)