This article discusses the process of obtaining aggregate statistics (e.g Maximum memory used) for a particular query from the TIBCO Data Virtualization SYS_REQUESTS table 

How to obtain aggregate statistics (e.g Maximum memory used) for a particular query from the TIBCO Data Virtualization SYS_REQUESTS table?

TDV stores incoming requests in a column named description in the SYS_REQUESTS table.  Based on what requests of interest look like, it is possible to:

• Search the SYS_REQUESTS table's description column to find the requests.
• Use the content of the other columns to generate aggregate information.

For instance, let us say that we want to generate aggregate information for all SQL SELECT queries run on the ViewOrder table.

              

Examples of these SQL SELECT queries are:

           Select CompanyName from ViewOrder;
           Select CompanyName, Discount from ViewOrder where Discount > 0.1;

The following query will:

• Find the requests in the SYS_REQUESTS table.
• Provide aggregate details (such as the Maximum Duration and the Maximum Memory)

--------------------
select 
   count(*)                        "Count",
   avg(total_duration)      "Average Duration",
   max(total_duration)     "Maximum Duration",
   min(total_duration)      "Minimum Duration",
   max(max_memory)     "Maximum memory Reserved",
   max(current_memory) "Current Memory Reserved"
from
   /services/databases/system/SYS_REQUESTS  
where 
   description like LOWER('% select%')
and
   description like LOWER('%from ViewOrder%')
--------------------

Result:

Count                                     : 10
Average Duration                   : 136
Maximum Duration                 : 254
Minimum Duration                   : 0
Maximum memory Reserved  : 2000000
Current Memory Reserved      : 2000000



Note that the SYS_REQUESTS table only retains information for a short while. In view of this, it is best to query the table while the requests of interest are still running (or, within 20 minutes of them completing). Alternatively, you can create a Procedure to run the query on SYS_REQUESTS, and use a Trigger to run the Procedure on a schedule.