DB2/400 sluggish performance

[cncjay]

All,

Time to time, end users claim slowness - by which I mean the interactive programs sit of 'processing' longer than the norm. In talking with our AS/400 administrator, he states the disk utilization seems to have been up.

Per AS/400 admin, typically, we run at 25% utilization but it seems be up around 50% to 60% when users are complaining slowness

Our UBE and Logic servers are windows based so iSeries is serving only as a database.

What tuning parameters can we review to help address this ?

thanks,

 

[Sebastian Sajaroff]

1. Reindex your JDE tables (R9698713)
2. Assign more memory to the pool where you run QZDASOINIT
3. How about your disk occupation %? Is it lower than 70%?

 

[cncjay]

Hello Sebastian,

Thank you for your reply.

For recommendation #1 - if indexes are a potential concern, wouldn't that trigger the issue as ongoing - rather than sporadic ?

I'll certainly follow up with the AS/400 and inquire regarding #2 and #3

Thanks,

 

[Sebastian Sajaroff]

Hi,

Maybe there's a just a couple of large tables that are
requiring a reindex.
So, you're seeing slow performance only when you hit those
tables.
It's just a conjecture.

 

[jdel6654]

Are other AS400 based apps slow at the same time (ie. green screen)?

What is your tcp send and receive buffer size?

 

[ebearman]

If average disk arm utilization is above 30% for more than one minute, the issue needs to be analyzed. If utilization is above 40% for the same, a slowdown in performance can be measured. 50%, forget about it.

You need to do as Sebastian said. Indexes, data archiving, user restrictions, more main storage, more disk arms, or newer technology.

-Ethan

 

[Tom_Davidson]

cncjay,

On the AS/400 (IBM i) I wouldn't worry about re-indexing, the indexes are self balancing.

It does sound though like you are running a process, a large join, or something like it, that is creating a temporary index, when these occur is your temporary space rather large? And does disk utilization go back to you 25% after performance returns to normal?

If so you probably need to look at one or more indexes that will prevent the temporary index. I'd suggest that you look at the index advisor in System i Nav, and look for indexes over large files with a long build time. See if the last recommended time is close to your last incident.

If so, I'd try building it outside of JDE, if it works, create it in JDE. Please note that there may be more than one index involved (probably is)

Tom

 

[DBohner-(db)]

Have you looked at your iSerie's Index Advisor ~ to see if there is an Index that could be created to satisfy any performance issues?

Additional coniserations:
- are any users changing the sort sequence on grids (add a new index to accomodate)
- are any users QBE'ing on a field that is 'not' indexed (causes a table scan ~ add a new index to accomodate)
- are users filtering by != ('Not Equal' forces a Table Scan ~ educate them to 'not' use 'Not Equal')

(db)

 

[craig_welton]

Just a stab, but we saw the same thing when moving to the web at one client.

I didn't catch your iSeries OS level, but we found a nice "go faster" setting in the QAQQINI file.

Thread 167733

Craig