What is Client Database-Request Statement Caching?

What is Client Database Request Statement Caching?
How to activate Client Database Request Statement Caching?
Is there a way to keep the Database-request statement caching enabled after stopping and re-starting the database?

Client Database Request Statement Caching is a new feature added in OpenEdge 10.1C. When enabled it allows the DBA to determine the ABL program and line number or the SQL statement that has caused or is causing the current database action.

Once activated, the Database Request information can be viewed via PROMON or a retrieved through VST queries.

Database Request Statement Caching cannot be kept enabled after stopping and re-starting the database. While the Client Database Request Statement Caching can be enabled or disabled online via PROMON or VSTs once the database is running, these options are not stored in the database so they do not persist across a database restart. Neither is there a database startup parameter to trigger its enablement.

Contrary to popular belief, Statement Cache is not for enquiring about what the client it is doing at a given time. For that, the generation of a protrace file can be forced to get the ABL stack (again introduced in 10.1C) with proGetStack <pid> on Windows or KILL –USR1 <pid> on UNIX. Statement Cache is intended for checking what the code was, that generated a database request since it has been enabled for that client.

Once Client Database Request Statement Caching is enabled:

• The statement cache is sent to the database along with the client request. When a client is running application code that does not involve a database request and statement cache has been enabled for that client, a message stating that the information is not available will initially be returned since there was no database request after it was enabled.
• As soon as a database request is made by the statement cache enabled client, then the information requested will become available.
• It is important to understand that when the statement cache option is enabled, the database does not ask for anything from the client. The client is the one that notices that it is enabled and then sends this information along with it's request to the database.
• The Database Request Statement Cache is updated by the individual clients and is refreshed any time the client performs a database operation. 
• The statement cache is not cleared once it is set (unless it gets explicitly disabled or client disconnects). It will always keep the information on the last request that the client sent to the database. When for example a procedure with line number -1 is seen, this just means that the last thing that the client asked of the database was at the end of the procedure, when it was returning to its caller. The “-1” indicates that the last request happened at the end of a given procedure. At the end of a procedure, any buffers that are still connected will be disconnected and that will cause a database request to go through, which then causes the client to send this last statement cache information to the database.

Database Request Statement Cache contains the following information:

• An ABL program name and line number of the ABL code that is performing a database request.
• An ABL program stack of up to 32 program names and line numbers beginning with the current program name and line number that is performing a database operation.
• The most recent SQL statement.

When Statement Cache is active, the client processes sends additional information to the database as described above. This will incur a minor penalty for performance on the client but only when Statement Caching is enabled on the database. The performance penalty is due to an additional operation when performing an interaction with the database regardless of whether the client is a shared memory connection or remote connection.  For remote connections the client must send additional packets of information to the database.

Enabling Client Database Request Statement Cache

There are two methods Client Database Request Statement Caching (CDRSC) can be enabled:

1. From the PROMON menus
2. With ABL programs using the _Connect VST

1.  Using PROMON to enable Client Database Request Statement Cache:
The following menu will be displayed:
 
Client Database-Request Statement Caching Menu Option Descriptions:
NOTE: 

When using: Option 3. Activate For All Future Users
The corresponding Option must be used: 6. Deactivate For All Future Users
Otherwise the database will continue capturing program information for all new connections.
Even if Option 5. Deactivate For All Users is selected, all new connections will have statement caching active.

When Option “1. Activate For Selected Users” is selected, the following options are requested to set the type of statement caching for the client :
Where:
The next menu will display all connected self-service, remote client, and remote client servers.  Multiple self-service; remote client; or remote client servers may be chosen from this menu. If a self service or remote client is selected by user number, then CDRSC will be enabled for that client based on the statement cache type (single, stack, one time request).

If one of the remote client servers is chosen, all clients of that remote server will have CDRSC enabled based on the statement cache type chosen previously (single, stack, one time request).

When all selections have been made, enter “P” or press enter to return to the prior menu:
“Client Database-Request Statement Caching Menu”

When Option “2. Activate For All Users” is selected, the type of statement caching next selected will be activated for all currently connected users.

When Option “3. Activate For All Future Users” is selected, the type of statement caching next selected will be activated for all future user connections.  In order to activate/deactivate for "Future Users", this can only be enabled through PROMON as there is no way to "set up" a _Connect field to enable Statement Cache for Future Users through the _Connect VST.

After starting the database, Client Request Statement Caching could be activated as follows for all future and current users:

•  How to run Promon to set the Statement Cache Temporary Directory from a script?  

Example:

1. prodb test sports2000
2. proserve test -S 8787
3. promon test,
  R&D > 1 Status Displays > 18. Client Database-Request Statement Cache > 2. Activate For All Users,  2-Stack

4. Create the following code and save it as test.p

DEFINE VARIABLE ii AS INTEGER     NO-UNDO.
DO ii = 1 TO 3:
    CREATE customer NO-ERROR
        .
    ASSIGN custnum = 2106.
    PAUSE.
END.

5. Start a client session and execute 'test.p' which should fail due to the unique constraint on Cust Num, leave the session here.

RUN test.p.

6. Back in the PROMON screen, select " 7. View Database-Request Statement Cache" and enter user number 5

View Database-Request Statement Cache
  Usr   Name       Type       Login time     Serv  Type  Cache Update  IPV# Remote Address
    5  uname1     SELF/ABL   05/12/15 14:18    0   L1  05/12/15 14:18

7. The Resulting Server Statement Cache:

View Database-Request Statement Cache

User number                     : 5
User name                       : uname1
User type                       : SELF/ABL
Login date/time                 : 05/12/15 14:22

Statement caching type          : SQL Statement or Single ABL Program Name
Statement caching last updated  : 05/12/15 14:22

Statement cache information     : 5 : test.p

Where:
The format of the ABL stack trace is: line-number : procedure information
The line number indicates the point in the ABL code where the statement was executing when the ABL stack trace was generated: Line number information is found in the debug listing file generated by the DEBUG-LIST option in the COMPILE statement

If "RUN test.p" were saved as "runtest.p", and executed from the cmd line: prowin32 test -p runtest.p
The line number would be -1, indicating the end of the procedure before returning to its caller.


Using VSTs:

The VST _Connect 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:
The related _Connect-Cache* fields are documented in:
For further _Connect-Cache* VST information and a code examples, refer to Article: 

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