System Log query failing while using ARSDOC QUERY

[Anand]

Hi All

In order to generate user login report for past 6 months in Ondemand, I planned to run arsdoc query with msg_num 30 and output the result to text file and then use scripts to generate the report like number of logins by each user and last login time. But when I execuete below arsdoc query in command line, I am getting "server failed during search operation" error even though I queried for 1 day timestamp.

/usr/lpp/ars/bin/arsdoc query -h localhost -u <userid> -p <password> -f 'System Log' -i "where time_stamp between 1439308800 and 1439395199" -H
/usr/lpp/ars/bin/arsdoc query -h localhost -u <userid> -p <password> -G 'System Log' -i "where time_stamp between 1439308800 and 1439395199" -H

08/12/15 16:30:00 : The server failed during a search operation.  View the System Log for additional messages.

While checking in the System log through Windows client, I could see error below DB error with message number 13.

13    DB Error: [IBM][CLI Driver][DB2/AIX64] SQL0101N  The statement is too long or too complex.  SQLSTATE=54001  -- SQLSTATE=54001, SQLCODE=-101, File=arsdoc.c, Line=2529

For the DB error code SQL0101N , reason stated is due to Statement Heap size.  But the above query is not a complex one and also the same working fine when I tried through Windows Client.

I also checked the System Log AG field properties and time_stamp is defined as Segment field and for partition. Similar setup we have in UAT and I am able to query without any issue.

Ondemand Version : 8.5.0.9 MP in AIX platform
Db2 : 9.7

Please advise.

[Maciej Mieczakowski]

Hi,

I think you forgot to narrow the search to message 30 only that you also mentioned about. I run pretty similar script for checking license utilization. So add a missing part and it should work

/usr/lpp/ars/bin/arsdoc query -h localhost -u <userid> -p <password> -f 'System Log' -i "where time_stamp between 1439308800 and 1439395199 AND msg_num=30" -H

I also run it from root account as it requires much memory for execution, so you can rise the limits (ulimit) or use root account instead

[Justin Derrick]

Yeah, and you may want to create a separate folder for this query.  I suspect that you don't want all of the fields that the System Log folder returns.  You can build a new folder with Time_stamp, Userid, and msg_text to reduce the amount of data returned, and make the code that processes this a little easier.

Also, consider working on your SQL skills, and building an SQL query that does the work for you.  You can build lots of useful queries with relatively little work.

-JD.

[mfeith]

Hi!  I'm upgrading to v9.5 and also trying to implement a script for license usage as well.  I need to report the number of internal and external users on a monthly basis now so would using MsgNum=30 be a correct representation since the license is now "per seat"?  I'm not sure how to translate the "per seat" when it comes to service id's from our web services.  Any guidance how how to correctly gather this information would be appreciated!

[Alessandro Perucchi]

Hello mfeith,

Well the license per seat is basically how many real users are defined in CMOD.
For example, if you have defined 1000 real users and 3 technical users (which are only use for arsload, setting up the rights and the last one for statistics), then you will basically pay for 1000 licenses.

Now if you have a technical user, that helps all you e-banking users to get documents in CMOD through this technical user. In CMOD one 1 user is define, but behind it, you might have 1 million users... then you need to have also 1 million external user license.

Between the user license and the external user license there is a big difference in price (normally the external is much cheaper than the user one).

Meaning, that the message 30 has no meaning anymore, the most important is really the number of real user defined in your archive, and how many external users are accessing CMOD.

If you want more details and exactly the explanation for your case for your company, I would strongly advise to contact your IBM/IBM Business Partner sales representative. He will explain you exactly how to find out how to find the information.
(Probably this will be a copy paste of what I said, or maybe there are some special case for your company... I don't know).

Hope that helps,
Alessandro