OnDemand User Group

Tips and Tricks => Tips and Tricks => Topic started by: mburnham on August 21, 2015, 01:07:05 PM

Title: Retrieve User activity from database?
Post by: mburnham on August 21, 2015, 01:07:05 PM

Hello all,

I'm told there's a table that contains user activity (like the system logs) but I can't find it.

Does this exist?

Thanks much in advance,

Mark

Title: Re: Retrieve User activity from database?
Post by: rstockton on August 21, 2015, 01:53:03 PM

Mark,

You should have a system log folder.  One of the Index fields should be UserID.

Russell

Title: Re: Retrieve User activity from database?
Post by: Justin Derrick on August 22, 2015, 04:07:16 AM

Hey Marc.  It's not just one table -- the system log tables (starting with SL2) have the data you're looking for. You can find this in the System Log Folder inside the CMOD GUI, but I suspect you're talking about quering from the command line.

Some background:

CMOD was created before DB2 had native support for automatic table segmentation.  In the modern versions of DB2, you can, for example, split a customer database by first letter of last name, or an accounting database by year, to reduce the size of individual tables, and/or in some situations, increase the parallelism of some applications by splitting those tables across different systems.

How it works:

Because DB2 didn't have this feature originally, OnDemand does it's own 'table segmentation', based on the number of rows in a database table.  You set the maximum number of rows, then when a table grows to that size, it 'closes' the current table, and opens a new one.  In OnDemand, this helps performance stay close to linear for the most frequent searches -- "last 90 days" or "last 9 months".  Tables that contain data outside the selected date range aren't even searched.  This is also why Application Groups require at least one date field.

How to search tables:

First, search the arsag table to find your Application Group.  Then use the agid to search the arsseg table.  Once you search the arsseg table (preferably with a range of dates) you'll get a list of the tables that contain the data you're looking for.  The last step is to query the actual table itself.

Hope that helps.

-JD.

P.S. - This was an interesting exercise for me to write up...  I wonder if anyone would be interested in having me do a presentation / video / webinar on this.

Title: Re: Retrieve User activity from database?
Post by: mburnham on August 22, 2015, 12:25:46 PM

Excellent - super useful!

Thanks much!

Mark