ARSMAINT -r error - Resolved

[Gordon R]

Morning All;
                       We are having an issue with ARSMAINT specifically with the ?r.
We are running 8.4.1 and recently upgraded the DB to Oracle 11g.
The first run after the Oracle 11g upgrade the ?r started failing.
Running AIX 6.1  The full error in the CMOD System Log is as follows,
       02/27/2013 01:18:36   ARSMAINT      15794180   Error   No       13   DB Error: RUNSTATS ON TABLE ars.SL2 -- SQLSTATE=Not Defined, SQLCODE=-1, File=arssys.c, Line=1354

The full ARSMAINT string is  -vrmcds  and all work individually when run, except the -r   

Any assistance, would be greatly appreciated.
Gord

[Ed_Arnold]

Gord - this is not my area of expertise but I run arsmaint -r quite often but always against DB2, never against Oracle.

http://publib.boulder.ibm.com/infocenter/cmod/v8r4m1/index.jsp?topic=/com.ibm.ondemand.administeringmp.doc/dodma003.htm

-r
    Runs database statistics, which causes the database manager to optimize application group index data and make access to information as efficient as possible. IBM recommends that you schedule the ARSMAINT program to optimize the database every day.

Does arsmaint -r work against anything other than DB2?

Ed

[Justin Derrick]

I've noticed occasional failures of the arsmaint -r option.  Have your DBA issue the reorg/runstats commands (or their equivalent on Oracle) manually for the table, and if it runs successfully, you shouldn't get that error in the future.  If it doesn't, it's time for a PMR. 

-JD.

[Gordon R]

Ed / Justin;
                    Thanks for the replies.
Our DBA ran the runstats (or Oracle equivalent) last Wednesday.  He states that a reorg/compress is not necessary due to all the 1.4 million records are on One Table in Oracle and taking that action would not ?do anything?. 

[Gordon R]

RESOLVED -
Our DBA was able to run the "ANALYZE TABLE?" while I ran the ARSMAINT -r in the Q.A. system which does not have an error.
Then we ran the same in our Production system ane he was able to see an Oracle error. See below...

The SYSTEM tablespace was at 70% usage (out of 400MB) when arsmaint ?r failed the first time. After I increased another 100MB to SYSTEM, I was able to run the ANALYZE TABLE? statement while Gord was able to run arsmaint ?r on the unix side. In addition, I noticed that the ARS database account is configured to use SYSTEM as temporary tablespace which I have reset to use TEMP now.

Thanks for the help.  It guided us on the correct path to resolve this.
Gord

[Justin Derrick]

Thanks for keeping us posted with a solution.  If you could provide a little more detail (which commands, etc.) it might help someone else who comes across this issue in the future -- thanks!

-JD.

[johnnoel]

I found this explaination

http://www-01.ibm.com/support/docview.wss?uid=swg21383609

Problem(Abstract)
Running the arsmaint utility against my IBM? Content Manager OnDemand server to update database statistics intermittently throws the following similar error in the System Log:

ARSMAINT 434684 Error No 13 DB Error: RUNSTATS ON TABLE root.SL25 -- SQLSTATE=Not Defined, SQLCODE=1, File=arssys.c, Line=1287

Cause
The error is occurring because runstats is attempting to update statistics on a table that is currently being inserted into.

Arsmaint -r should be run at a time when little to no activity is occurring in the OnDemand System.