Start a new topic

Stambia S19 - How to user SQL_STAT_* to populate a following treatment table ?


We have différents Mapping that feds différents tables.

We have some centralize table that follow all tables feedings.

Table Name, Launch Chain Name, Nb Insert, Nb Update, Nb Delete, Alim Date, Alim Hour, etc....

We found some complicated Rhino to do so, but we d love to found an easier way.

We are one Stambia S19.

Any good idea please?


Hello Virginie,

You want to register the statistics from previous sessions ? Or are you looking for a way to log statistics each time a mapping is executed ?


It can be both.

For the project it is to log statistic in a last Process for all the Mappings and Process that just finish before.

Hi Virginie,

If your needs is to register statistics for each execution of your mappings : You'll have to create a common process that will insert statistics in your centralized stats table.

This process will simply use some input variables in SQL query.

Then you will be able to use this common process on your existing process just after your mapping step.

You will have to specify the good values on that common process. Stambia will put this values on the SQL query and will execute the good insert statement on your centralized stats table.

This is an example of common process :


Here, I use a table named STATS_TABLE with some fields (TABLE_NAME, CHAIN_NAME, NB_INSERT, NB_UPDATE, NB_DELETE).

On the existing process, this is the way you will use the common process (by drag and drop) :

First usage of common process (with sum of sql_stats)

Second usage of common process (with full path of sql stats)

You see differents usages to put the good values of your statistics :

- The first one is the most simple use case that will perform sum of SQL_STATS. You just have to specify the name of the step that you want to get statistics. This solution will use light rhino script (very light).

There is a limit of that solution if your mapping perform a LOAD step, your statistics could be wrong because the number of inserted rows will be higher.

- The second solution is to specify the complete path of your mapping insert (or update) step. So, Stambia will get the real statistics. But this solution is a little more complicated because writing the complete path of steps could take time.

Remember you can add every information that you need to register on your centralized stats table (date, session_id, etc...). You just have to change SQL query on the common process.

If you want to register previous sessions statistics, you'll have to read log database. I suggest looking at this following tables :

- stb_log_session_sess to get session informations

- stb_log_session_stat_sst to get all session statistics

- stb_log_action_stat_ast to get all session step statistics

- stb_log_action_s_act to get session step informations


Thanks for this information. We used part of it.

As we have numbers of Mapping, the ideau would me to automate as much as possible. Not make it step by step


So you want to use a final common process (on your existing process) that will get statistics ?

I suggest to use the log database with a mapping (with somes log tables in source and centralized stats table in target) and session_id as filter.

You will get every stats you want, with the good step to get statistics (stb_log_action_stat_ast and stb_log_action_s_act tables).

Good idea, do you have a Physical Model of those tables, with the key join ?

Hello Virginie,

This is the two tables usage : 

The first table (stb_log_action_stat_ast) contains every step (act_id) executed on a session (sess_id). Each of the lines contains volume statistics (type of statistic acp_short_name and the value ast_stat_sum).

If you want to get the name of the step, you'll need to use the second table (stb_log_action_s_act) which contains name of the step (act_short_name), and the path (act_parent_path).

I suggest doing several tests to validate that you are using the correct values. It all depends on what you want to retrieve in your table that centralizes the statistics.

1 person likes this


That can be a good idea.

How do you catch the sess_id in the Process that end its execution ?


That could be a good way.

A few questions are coming :

  • When is the Repository database updated ? While the process is executing or after ?
  • Where could we get the execution timestamp ?
  • Do you have a complete modele of all link when can use on this database 

Thanks for your help.
For information, each day, we can this data in our DWH


Login to post a comment