Article

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

« Go Back

Information

 
Article Number000013106
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/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
Attachment 
Last Modified Date10/2/2018 12:09 PM


Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025