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:
Database Request Statement Cache contains the following information:
- 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.
- 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. Using PROMON to enable Client Database Request Statement Cache:
- From the PROMON menus
- With ABL programs using the _Connect VST
$ 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 Option Descriptions:
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
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
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):
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
Example:1. prodb test sports2000
---start of file enableCaching.txt
---end of file enableCaching.txt
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.
5. Start a client session and execute 'test.p' which should fail due to the unique constraint on Cust Num, leave the session here.
6. Back in the PROMON screen, select " 7. View Database-Request Statement Cache" and enter user number 5View 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.pWhere:
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 statementIf "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: