Maintaining and Monitoring Your Database : Logged Data : Client database-request statement caching
Client database-request statement caching
OpenEdge supports database request statement caching. This functionality enables the database administrator to monitor ABL and SQL driven database activity using PROMON or the _Connect Virtual System Table (VST). Using database request statement caching, the database administrator can track client activity and identify line numbers within an ABL program (or an SQL statement) associated with database requests.
Note: For information on configuring client database-request statement caching, see PROMON utility.
The database-request statement cache is updated by individual clients, and is refreshed each time a client performs a database operation. The cache can display ABL or SQL activity, including:
ABL program names and line numbers of ABL code executing database requests
ABL program stacks program names and line numbers, beginning with the current program name and line number executing a database operation. You can access the entire call stack from PROMON; the _Connect VST holds up to 32 entries.
Recent SQL statements
Client database-request statement caching for SQL provides the SQL statements that generate database server requests; for ABL, client database-request statement caching provides program names and line numbers of the ABL code that generated database server requests.
Client database-request statement caching can be used to identify statements that are potential performance problems. For example, multi-database queries are often complicated by needing to transfer large amounts of data typically produce complex result sets. If processing large amounts of data results in poor query response time, you might want to monitor use statement caching to monitor the ABL or SQL database activity and identify areas within an ABL program (or SQL statements) associated with the database requests as code to consider re-writing.
Note: Client database-request statement caching may negatively impact application performance due to extraneous APIs (for self-serve connections) and TCP/IP messages sent from the client to the server.
When client database-request statement caching is activated, ABL stack traces indicate which procedure executed a database operation, and what procedure generated the request. Each line in the request contains the line number and additional procedure information, such as the name of the procedure, whether it is an internal or external procedure, a user-defined function, or the method of a class.
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. In some cases, the line number may be -1, indicating the end of the procedure before returning to its caller. Line number information is found in the debug listing file.
Note: The debug listing file is generated by the DEBUG-LIST option in the COMPILE statement. For more information, see OpenEdge Getting Started: ABL Essentials.
The procedure information contains the following information:
The name of the procedure as it was executed by the RUN statement in the ABL application. For example, if the ABL statement used either RUN test.p or RUN test.r, then RUN test.p or RUN test.r appear as the name of the procedure.
Class names without the .CLS extension.
A temporary file with (with a .ped extension) is displayed as the procedure name for procedures executed using the Procedure Editor.
Statements contained inside an internal procedure, a user-defined function, or a method of a class, precede the procedure or class name. For example, if the statement was inside an internal procedure named myproc in test.p, the procedure information contains myproc test.p.
ABL program names and line numbers of ABL code executing database requests