RE: SQL performance problem on one job only

["Ingvaldson, Scott" <SIngvaldson@xxxxxxxxxxxx>]

> > Triple checked all PTFs / Cumepacks / Feng Shui 

Is this box facing East or West?

See: http://www.wofs.com/fsw.php?load=arcview&article=488&c=workshops

Regards,
 
Scott Ingvaldson
iSeries System Administrator
GuideOne Insurance Group


-----Original Message-----
date: Thu, 27 Apr 2006 08:01:39 +0100
from: "Bath, Martin" <martin.bath@xxxxxxxxxxxxxxxxxxxx>
subject: SQL performance problem on one job only

Hi guys

Anyone help me with this before I scream, please?
I have one 3rd Party job that contains 30 embedded SQL commands that
runs ok
for a while (runtime between 10 secs and 3 minutes in batch) and then,
inexplicably, runs for over an hour without ever finishing.

So far, I've
1) Checked DBMON for missing logicals. Created some but this seem to
make it
worse.
2) Checked for damaged logicals. Replaced them all anyway
3) Recompiled program.
4) Converted program from OPM to ILE and recompiled it.
5) Created copy of program for just that (BPCS) environment.
6) Triple checked all PTFs / Cumepacks / Feng Shui
7) Checked with supplier / internet.  "The job is functioning as
designed"
8) Archived data  (it's a General ledger job)
9) Amended QAQINI (or whatever it's called) settings. 
10) Removed some logicals that weren't in other environments - no
effect.
11) Checked PRTSQLINF - learnt nothing from this.
12) Swore a lot

I have found references to problems with progs over 16mb in size and
this
prog is over 16mb.
Currently the only workaround that works is to have it a scheduled job
to
recompile it every two hours. My gut feeling is that the fact that this
workaround seems to work proves that the 16mb issue is the crux of the
problem.  It's not the only prog above 16mb but it does appear to be the
only one with the problem.

BTW This is on a 720 running V5R2.

I've run out of solutions other that rewrite the prog.

Regards

Martin Bath
Global IT Group
Invensys Controls 


DISCLAIMER:
This message and accompanying documents are covered by the Electronic 
Communications Privacy Act, 18 U.S.C. ?? 2510-2521, and contains information 
intended for the specified individual(s) only. This information is 
confidential. If you are not the intended recipient or an agent responsible for 
delivering it to the intended recipient, you are hereby notified that you have 
received this document in error and that any review, dissemination, copying, or 
the taking of any action based on the contents of this information is strictly 
prohibited. If you have received this communication in error, please notify us 
immediately by e-mail, and delete the original message.