The _Connect VST has been updated in OpenEdge 10.1C with additional fields. If the database was a version 10 database pre 10.1C then VSTs must be updated offline with:
$ proutil dbname -C updateVST
_Connect-Cache* definitions:
The following _Connect-Cache* definitions are an extract from:
OpenEdge Data Management: Database Administration, Virtual System Tables, Virtual system table summaries, Database connection (_Connect):
_Connect-CacheInfoType is a character field, which displays the client database-request statement cache types:
- ABL Program: Current ABL program and line number.
- ABL Stack: The ABL program stack. Includes up to 32 ABL fully qualified program names with line numbers. This field identifies the contents of _Connect-CacheInfo and _Connect-CacheLineNumber.
- SQL: SQL statement.
_Connect-CacheInfo is an extended field of 32 extents, which contains tracing information:
When _Connect-CacheInfoType equals:
- ABL Program: Extent 1 contains the fully qualified ABL program with procedure name if applicable.
- ABL Stack: Each extent contains a fully qualified ABL program name. Only the most current 32 program names are returned.
- SQL Statement: Extent 1 contains a SQL statement.
_Connect-CacheLineNumber is an extended field with 32 extents, containing line numbers.
When _Connect-CacheInfoType equals:
- ABL program: Extent 1 contains the line number of the current ABL program.
- ABL Stack: Each extent contains the line number of the fully qualified ABL program name. Only the most current 32 program names are returned.
- SQL Statement: Unknown.
_Connect-CacheLastUpdate is a character field, which displays the current date and time of the client database-request statement cache.
_Connect-CachingType is an integer field, indicating the type of client database-request statement caching performed by the client:
- 1: (single) One ABL program or a single SQL statement will be stored in the client database-request statement cache.
- 2: (stack) An ABL program stack or a single SQL statement stored in the client database-request statement cache.
- 3: (one-time) The client sends an ABL program stack or a single SQL statement to the client database-request statement cache.
How to enable Client Database-Request Statement Cache with the _Connect-Cache* VST:
- After a _Connect record is read the _Connect-CachingType can be updated to instruct that client to perform database-request statement caching.
- These new fields in _Connect-Cach* will be populated when there is a Database Request Statement Cache type set when the _Connect VST is read.
- Caching can be enabled by modifying the value of _Connect-CachingType with a value from 1 to 3 for one of these options (1-Single, 2-Stack, 3-One Time) as described above.
- The value of _Connect-CachingType will indicate the type of statement caching that is performed by the client.
- The client will send an ABL program stack or a single SQL statement to be stored in the Database Request Statement Cache.
- Database Statement Caching can be disabled by modifying the value of _Connect-CachingType with a value of 0.
Example:
ASSIGN _Connect-CachingType=0.
- When setting _Connect-CachingType, pay special attention to the _Connect-Type. For connections other than SELF,REMC,SERV,SQSV (eg "BROK","WDOG","MON") will error:
Updating _Connect Virtual System Table is not supported (14378)
Ensure that the ABL code selects only users of these types to enable the _Connect-CachingType:
Example:
DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE VARIABLE j AS INTEGER NO-UNDO INITIAL 1.
DEFINE VARIABLE vInputList AS CHARACTER NO-UNDO
INITIAL "SELF,REMC,SERV,SQSV":U.
OUTPUT TO value("cacheinfo-" + REPLACE(REPLACE(REPLACE(((STRING(NOW))),':','.'),' ','_'),'/','') + ".txt").
DO j = 1 TO NUM-ENTRIES(vInputList):
FOR EACH _connect WHERE _connect._Connect-type = ENTRY(j, vInputList):
ASSIGN _connect-CachingType = 2.
DISPLAY ENTRY(j, vInputList) _Connect-CacheLastUpdate _Connect-Name _Connect-Usr SKIP
_Connect-CacheInfoType _Connect-CachingType.
DO i = 1 to 32:
IF _Connect-CacheInfo[i] <> ? THEN
PUT UNFORMATTED _Connect-CacheInfo[i] SKIP.
IF _Connect-CacheLineNumber[i] <> ? THEN
PUT UNFORMATTED _Connect-CacheLineNumber[i].
END.
PUT UNFORMATTED SKIP(2).
END.
END.
OUTPUT CLOSE.
An ABL client can also enable the statement cache for itself.
Example:
FIND _myconnection NO-LOCK.
FIND _connect WHERE _connect-usr = _myconnection._MyConn-userid.
_connect._Connect-CachingType = 3.
find first customer.
But note that a client can not query the statement cache for itself reliably; The query on _connect to get the data can get cached, overwriting the cached statement of the actual queries to be investigated. Instead, obtain Client Statement Cache information for a specific client from another session, example:
DEFINE VARIABLE cachetm AS INTEGER NO-UNDO .
cachetm = TIME + 10.
FIND _connect WHERE _connect._Connect-Usr = 7 NO-LOCK NO-ERROR.
IF AVAIL _connect THEN
DO:
OUTPUT TO value("cacheinfo-" + replace(replace(replace(string(now), '/', '_'), ' ', '-'), ":", ".") + ".txt") APPEND.
DO WHILE TIME < cachetm :
DO TRANSACTION :
FIND CURRENT _connect EXCLUSIVE-LOCK NO-ERROR.
_connect._connect-CachingType = 2 .
FIND CURRENT _connect NO-LOCK NO-ERROR.
PUT UNFORMATTED _Connect-Name "; " _Connect-Usr "; " STRING(_Connect-CacheLastUpdate) SKIP
_Connect-CacheInfoType "; " _Connect-CachingType SKIP .
DEFINE VARIABLE i AS INTEGER NO-UNDO.
FIND CURRENT _connect NO-LOCK NO-ERROR.
IF AVAIL _connect THEN
DO i = 1 to 32:
IF _Connect-CacheInfo[i] <> ? THEN
PUT UNFORMATTED _Connect-CacheInfo[i] SKIP.
IF _Connect-CacheLineNumber[i] <> ? THEN
PUT UNFORMATTED _Connect-CacheLineNumber[i].
END.
PUT UNFORMATTED SKIP(2).
END.
PAUSE 1 NO-MESSAGE .
END.
END.
OUTPUT CLOSE.
DO TRANSACTION :
FIND CURRENT _connect EXCLUSIVE-LOCK NO-ERROR.
_connect._connect-CachingType = 0 .
FIND CURRENT _connect NO-LOCK NO-ERROR.
DISP _connect._Connect-Usr _Connect._Connect-CachingType.
END.
