New post
Question
· Jan 25, 2016

Getting number of processed messages of all productions

#Ensemble #ODBC #SQL

Hi!

Is there any simple way to query data about processed messages in all Ensemble productions?
What I ultimately would like to do is to periodically export that data to another system and run statistics on it.

I've been digging around in the SQL tables view and Ens.MessageHeader seems to contain most of what I'm after.
Using ODBC I could access that table view and query data, but only for one namespace per DSN it seems.

Can Ens.MessageHeader from different namespaces be mapped into a single namespace so that it can be accessed with ODBC?
Or is there a better way to access this data than to use the ODBC interface?


Thanks
Andreas
 

Discussion (5)1
Log in or sign up to continue
David Loveluck · Jan 25, 2016

Andreas

I don't think there is an easy way to do what you are asking for in a single SQL statement. Mapping Ens.MessageHeader data from different databases into one namespace isn't really possible.

I think you will have to run a query per namespace and merge the results. 

The new activity statistics capability will centralize statistics for many namespaces so you can run a single query, but that isn't available until 2016.1.

 

Dave

0 0
Kyle Baxter · Jan 26, 2016

While I don't think there's a 'good' way to do this, I think your approach might work.  Say you have two Namespaces, A & B.  You can link Ens.MessageHeader & Ens.MessageBody from B to A and then run the query:

 

SELECT <Fields> FROM Ens.MessageHeader WHERE (...)

UNION ALL

SELECT <Fields> FROM Ens.MessageHeaderB WHERE (...)

 

While this will work (technically) it is limited.  You won't necessarily be able to JOIN, ORDER, or GROUP the results in a meaningful way, and so you will need to write some additional code to do this.  That said, you could certainly write a stored procedure to handle this situation if you so chose.   The stored procedure here is more in line with what Dave L. said - you need to write code to merge the results.  But if you don't care about order and you think you're only going to get a small number of messages back, no reason this can't work.

0 0
Andreas Östlund · Jan 27, 2016

Thanks for your replies

I guess just beeing able to loop through all namespaces would be good enough.
When looking a bit closer at the ODBC interface it seems possible to change namespace for a DSN by setting a registry key.
Basically doing as a quick hack:

set HKLM:\SOFTWARE\ODBC\ODBC.INI\instance\Namespace to %SYS
query all namespaces with "select Name from Config.Namespaces where CPFName='CACHE' and Id like 'CACHE||Namespaces||%' and Name not in ('%ALL','%SYS')"
loop that result

set HKLM:\SOFTWARE\ODBC\ODBC.INI\instance\Namespace to "namespace name"
query Ens.MessageHeader

repeat loop

A bit ugly but it worked.


However, for namespaces with lots of messages (5+ million) my query on Ens.MessageHeader times out.
Are there limits on how SQL queries through ODBC can perform, or is it more up to how the query is written?

I'm thinking now if it's perhaps better to switch approach, and create a native routine to loop the namespaces.

What would be a good way to access message metadata from a routine?

0 0