Feedback
Did this article resolve your question/issue?

   

Article

How use VST's to enable Client Database-Request Statement Cache

« Go Back

Information

 
TitleHow use VST's to enable Client Database-Request Statement Cache
URL NameP145378
Article Number000150649
EnvironmentProduct: OpenEdge
Version: 10.1C, 10.2x, 11.x
OS: All supported platforms
Question/Problem Description
How to use the _Connect VST for Client Database-Request Statement Cache?
Which _Connect Virtual System Table field is used to enable Client Database-Request Statement Cache ?
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
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-Id - 1) 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-id = 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-Id - 1) "; "  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-id  _connect._connect-CachingType.
END.



 
Workaround
Notes
Last Modified Date11/20/2020 7:24 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.