Find the records processed by a Sterling Agent

A Sterling Agent is a java process which is built on sterling agent framework.This framework is one the most important/key feature and a time tested solid feature of sterling Suite. Many of the times we would be interested to know few of the performance parameters of a sterling agent.The scenario can be anything:

1.    An Architect might be interested to know the performance of the agents to tweak the memory footprint,time for getJobs,successful execute jobs etc.

2.    A Sterling support team personnel may be interested to know the how many number of order lines got scheduled,number of orders back ordered last hour.

Or in any scenario if a developer/support team personnel is interested in knowing these statistics there is a simple way to figure this out. YFS_STATISTICS_DETAIL records many key statistics from inside the sterling. 

Below are the steps to find out the key statistics of an Agent:

1.    Determine the time range where you would like to find the statistics:The primary key of this table statistics_detail_key is basically a time stamp. So If i would like to pull the data for the performance of an agent between 01-Jan-2016 ,8 AM and 01-Jan-2016,9 Am then my statistics detail key will be statistics_detail_key>'0101201608' and statistics_detail_key<'0101201609'.

2.    Determine the agent name: You would know to which agent you would like to get the stats.If you are unsure about this then query YFS_FLOW table for the same.Lets say you would like to find number of orders scheduled by scheduling agent.

3.    Determine the statistics tracked for this agent by navigating to knowledge centre here.And then search for the transaction associated with this agent.For scheduling agent ,the transaction associated with it is 'Schedule'.Scroll down until you find the statistics tracked section.

Here you would be interested to know the NumOrdersScheduled statistics.

4.Create the query:

select count(Statistic_value) from yfs_statistics_detail where statistics_detail_key>'From time' and statistics_detail_key<'To time' and server_name='<Your agent name>' and statistic_name='<Statistics Name>';

For this scenario it will be :

select count(Statistic_value) from yfs_statistics_detail where statistics_detail_key>'0101201608' and statistics_detail_key<'0101201609' and server_name='ScheduleOrder' and statistic_name='NumOrdersScehduled';

Note: yfs_statistics_detail  table is a very heavy table and when you fire a query to this table it consumes lot of system resources.Hence try to keep your query as lean as possible,use parallel hints,and try to use it during off business hours.