CMOD upgrade from 8.4.0.3 to 8.5.0.6 question

[wdssnyder]

arsload table reorg takes much longer in 8.5.0.6 than it did in 8.4.0.3, Anyone know why?
                                                                 
After upgrading to 8.5.0.6 we were getting a 911 lock time out error 
when running arsdb -mv.                                                 
We were able to solve this issue in test by raising the LOCKTIMEOUT     
database paramater to 150 from 30.   In production we also had it set   
to 30 and we had raise it to 1200, which is 20 minutes to get through   
the reorg of the ARSLOAD table.   We'd like to know what caused this   
seemingly drastic difference?  Everything seems to be working OK, but   
we are wondering if having it set so high could cause us other issues. 
When we set it to 20 minutes we timed it and just barely finished the   
reorg before the 20 minutes was up. So we've set it even higher to make
sure we don't get a timeout when regular production runs. Can you give 
us any insight as to what changed between 8.4 and 8.5 to make such a   
difference or is it a problem.                           

[Justin Derrick]

You shouldn't be getting a lock on the table unless some other process or task is trying to modify it.  Have you stopped your arsload/arsmaint processes before doing a table re-org?

-JD.

[wdssnyder]

Yes our ARSLOAD processes is stopped, prior to arsmaint -r and arsdb -ms. Our DB2 is v9.5.3, could there be 'locklist escalation' problem?

[Paul]

Has arssockd been stopped?

[wdssnyder]

arssockd and arsjesd are processing, when arsmaint -r and arsdb -ms are running

[Mike]

I ran into the same upgrade when I upgraded about a month ago, my solution was to just skip that step and have the DBA manually reorg and do runstats on all of the tables that the process went through.

It went much quicker that way and I've personally had no side effects from doing it that way.

[wdssnyder]

Would setting 'self_tuning_mem' to ON and 'database_memory' to automatic, help with -911 locktimeout? DB2 is 9.5.3

Self tuning memory                    (SELF_TUNING_MEM) = OFF
Size of database shared memory (4KB)  (DATABASE_MEMORY) = COMPUTED

[Justin Derrick]

Self tuning memory has nothing to do with lock lists.

Take Mike's advice -- have your DBA do the re-orgs manually on the "ars*" tables, and see if the DBA reports back any issues.

Don't forget to report back to us with your results!

-JD.

[Paul]

Justin,
Would you recommend always having the dba reorg and stat the database?  I am having some issues having upgraded from 8.5.0.7 to 9.0.0.2 and then back out to 8.5.0.7.

[Justin Derrick]

I'd have the DBA do these steps manually and see if they encounter any issues (and correct any issues found).  Then try to go back to running arsmaint.

I've seen this happen in a variety of upgrades, where arsmaint runs long (or fails) after major upgrades, but resumes working properly after a manual runstats/reorg.  Of course, this can take a prohibitively long time on larger systems.

-JD.

[Paul]

Thank you, Justin.

I've had a DBA ask me what exactly is the script doing when we run the arsmaint command to run stats and reorg the database.  Is there somewhere where I can share with the DBA what the command is doing?

[Justin Derrick]

arsmaint simply issues runstats and reorgs.  There's no magic there.  The snag is that when arsmaint encounters issues, you generally don't get the 'full picture' of what the error was (or if there were any warnings), etc.

By running these commands interactively, you get to see if there are warnings or errors during processing, which gives you the opportunity to correct the issues.

-JD.

[wdssnyder]

I believe my original post, in regards to 'arsdb -ms' running long in v8.5.0.6 has been resolved.

PM93010 - fixed in v8.5.0.8
arsdb -m creates many DB2 logs and therefore consumes an
unacceptable amount of discspace and time

My company applied v8.5.0.8 March/2014 and 'arsdb -ms' went from 50+ minutes to 2

[Justin Derrick]

Excellent.  Thanks for the update.

-JD.