Intermittent Index and Load Failures arsadmin.c

[jpdevin]

 Environment ? Client 8.4. CMOD 7.1. With DB2 DB and TSM. Yes, some back level until next upgrade. Don?t think version levels in this case are the problem.

 Getting intermittent archive failures at different points of the Index and Load process.
 Two thirds of loads work first time, one third do not.
 No rhyme or reason to one specific download directory or temporary files.
 After rerunning process one or more times without doing anything differently, it works.
 In addition, Client is getting intermittent ?Server Failed During a Search Operation?, for example simply attempting to bring up Syslog. After immediately trying once or twice more, document retrieval works normally from client.
 Did run ARSMAINT and got cache down from 88% to 15%. No luck. Look in syslog surrounding the failure time, unless I?m overlooking something, don?t see any DB2 errors.
 
 
 
Example of fail during Indexing:
arsload: Processing file >/brdwnld/br.ac1m1.ac1m1.ac1m1.12039.082204.ARD<
An unexpected error occurred.  Contact your System Administrator and/or consult the System Log.  File=arsadmin.c, Line=5230

arsload: Processing failed for file >/brdwnld/br.ac1m1.ac1m1.ac1m1.12039.082204.ARD<

Example of fail during Load:
arsload: Processing file >/download3/mvs.cocc21.cocc21.coccrpts.12039.081923.ARD<

arsload: 02/08/12 08:19:27 -- Indexing started, 119669 bytes to process
0425-415 CC=NO
0425-415 CCTYPE=Z
0425-415 CONVERT=NO
0425-415 CPGID=850
0425-415 MCF2REF=CPCS
0425-415 TRC=NO
0425-415 FILEFORMAT=STREAM,(NEWLINE=X'0A')
0425-415 TRIGGER1=*,68,X'49432041544D',(TYPE=GROUP)
0425-415 FIELD1=2,86,10,(TRIGGER=1,BASE=0)
0425-415 INDEX1=X'7264617465',FIELD1,(TYPE=GROUP,BREAK=YES)
0425-415 DCFPAGENAMES=NO
0425-415 UNIQUEBNGS=YES
0425-415 IMAGEOUT=ASIS
0425-415 INDEXOBJ=GROUP
0425-415 INDEXSTARTBY=1
0425-415 INSERTIMM=NO
0425-415 RESTYPE=NONE
0425-415 inputdd=/download3/mvs.cocc21.cocc21.coccrpts.12039.081923.ARD
0425-415 outputdd=/dev/null
0425-415 indexdd=/arsacif/acif3/mvs.cocc21.cocc21.coccrpts.12039.081923.ARD.ind
0425-415 resobjdd=/dev/null
0425-440 ACIF AT PK72203 HAS COMPLETED NORMALLY WITH RETURN CODE 0.
arsload: 02/08/12 08:19:27 Indexing completed
arsload: 02/08/12 08:19:27 -- Loading started, 119669 bytes to process
An unexpected error occurred.  Contact your System Administrator and/or consult the System Log.  File=arsadmin.c, Line=5230 (every failure is a different line number)arsload: 02/08/12 08:19:27 Loading failed
arsload: Processing failed for file >/download3/mvs.cocc21.cocc21.coccrpts.12039.081923.ARD<

[Justin Derrick]

Looks like a transient database issue.  Check db2diag.log (generally /home/archive/sqllib/db2dump/db2diag.log) for hints as to what the problem might be.

-JD.

[jpdevin]

 Thanks for the DB2 log tip. It looks like we have a memory issue. My understanding is there may be fix packs vs DB2 which MAY address this.
 In the interim, short of rebooting our environment, including the hardware to reset memory, is there a dynanic technique to clear possible "leaked memory"?
 2012-02-07-17.54.49.122476-240 I90681864C508      LEVEL: Error

PID     : 884912               TID  : 1           PROC : db2agent (ARCHIVE) 0

INSTANCE: archive              NODE : 000         DB   : ARCHIVE

APPHDL  : 0-24                 APPID: *LOCAL.archive.111019153608

FUNCTION: DB2 UDB, access plan manager, sqlra_fp_alloc, probe:10

RETCODE : ZRC=0x8B0F0001=-1961951231=SQLO_NOMEM_APPH

          "No memory available in 'Application Heap'"

          DIA8301C No memory available in the application heap.

[Alessandro Perucchi]

Hello jpdevin,

so you can try to change the DB2 parameters to give more heap.

ASLHEAPSZ = Application support layer Heap size

maybe others (I have only DB2 9.7 here.... and maybe in older version you might have other parameters...).

To find them, you can do a simple

db2 get db cfg | grep -i heap

or

db2 get dbm cfg | grep -i heap

Hope it helps a bit.

Sincerely yours,
Alessandro

[jpdevin]

 We were able to get out of our immediate issue, by
 1) killing ARSSOCKD
 2) stopping arsdb - hv
 3) starting arsdb - gkv
 4) starting ARSSOCKD

As previous posters say, sometimes that's not enough, entire platform must be recycled.
Apparently there is a service pack for DB2 for this condition which may not have helped in our particular case, because we discovered the root cause:
 Someone ran two concurrent scripts which were extracting reports which were archived generically, and then archiving them with indexing. Likely 2 two processes ended up in a deadly embrace on a resource, and were hung.
 Killed those two processes, and hopefully we're good for now.

[Justin Derrick]

As usual, Alessandro is right.  You need to increase the amount of RAM allocated to the CMOD 'applications' that connect to DB.

Since you're (severely) backleveled on DB2, the parameter you want to check for is APPLHEAPSZ.

To update this parameter in DB2, use this command:
    db2 update db cfg for archive using applheapsz <new size in pages>

Your friendly neighbourhood DBA should be able to recommend a value based on the rest of your DB2 configuration.  In the (distant) past, I've set this value to 1024 (in combination with other changes to other parameters like DBHEAP) to resolve similar issues with failed queries.  Of course, always make a note of your current values so you can go back to them if the change doesn't have the desired effect.

-JD.