Feedback
Did this article resolve your question/issue?

   

Article

What is Client Database-Request Statement Caching?

« Go Back

Information

 
TitleWhat is Client Database-Request Statement Caching?
URL NameP150383
Article Number000143470
EnvironmentProduct: OpenEdge
Version: 10.1C and later
OS: All supported platforms
Question/Problem Description
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?
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
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:
      
$   promon <dbname>

Enter: R&D > 1. Status Displays > 18. Client Database-Request Statement Cache

The following menu will be displayed:
 
OpenEdge Release 10 Monitor (R&D)
Client Database-Request Statement Caching Menu

                 1. Activate For Selected Users
                 2. Activate For All Users
                 3. Activate For All Future Users
                 4. Deactivate For Selected Users
                 5. Deactivate For All Users
                 6. Deactivate For All Future Users
                 7. View Database-Request Statement Cache
                 8. Specify Directory for Statement Cache Files

Client Database-Request Statement Caching Menu Option Descriptions:

1. Allows the user to activate database-request statement caching for one or more clients
2. Allows the user to activate database-request statement caching for all clients
3. Allows the user to activate database-request statement caching for all future client connection to the database
4. Allows the user to deactivate database-request statement caching for one or more clients
5. Allows the user to deactivate database-request statement caching for all clients
6. Allows the user to deactivate database-request statement caching for all future client connections
7. Allows the user to view the current client Database Request Statement Cache
8. Allows the user to specify the directory where all temporary Database Request Statement Cache files (*.cst) are stored

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 :
              
Enter to activate or Q to quit (1-Single, 2-Stack, 3-One Time):

Where:
1 Single: Only the current ABL program name and line number or a single SQL statement is reported.
2 Stack: The current ABL program and line number and up to 31 prior ABL program names and line numbers or a single SQL statement are reported by the client. 
3 One Time Request: The ABL or SQL client will report tracing information once.  Once this has been acted upon by the client statement caching is turned off.

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:
            

$   promon dbname < enableCaching.txt

---start of file enableCaching.txt
R&D
1
18
3
2
2
2
x
---end of file enableCaching.txt
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:
ASSIGN custnum = 2106
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:
$   proutil dbname -C updateVST

The related _Connect-Cache* fields are documented in:
OpenEdge Data Management: Database Administration, Virtual System Tables, Virtual system table summaries, Database connection (_Connect)

For further _Connect-Cache* VST information and a code examples, refer to Article: 
Workaround
Notes
Last Modified Date11/20/2020 7:28 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.