Re: SQL performance problem on one job only -- MIDRANGE-L

My experience with a sometimes running fine, sometimes running forever
embedded sql were data problems, which explained the random behaviour.
Mostly single quotes within the data (which I now properly handle).
I put this in at the top of the sql
C/EXEC SQL WHENEVER SQLERROR GOTO $ERR
C/END-EXEC
and $err section writes a history record of the prepare field plus all the
fields from IBM's error data structure, plus timestamp, etc.
It makes finding the problem much easier.
jim franz

----- Original Message -----From: "Vernon Hamberg" <vhamberg@xxxxxxxxxxx>

To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Sent: Thursday, April 27, 2006 8:16 AM
Subject: Re: SQL performance problem on one job only

Martin - I think it's well past time to call IBM - the 16MB limit is
the size of what is called a space, the basic building block of
several objects on an iSeries. Now you're talking about stuff none of
us have any reason to know about, so I suggest you go to the horse.

A 16MB program? Egad!! Bloat lives!

Good luck
Vern

At 02:01 AM 4/27/2006, you wrote:

Hi guys

Anyone help me with this before I scream, please?

I have one 3rd Party job that contains 30 embedded SQL commands that runsok

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 makeit

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 / Cumpacks / 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
Tel:      +44 (0) 1752 724388
Mobile: +44 (0) 7736 017318
Fax :    +44 (0) 1752 732455
 **** PLEASE NOTE NEW EMAIL ADDRESS  ****
Email : martin.bath@xxxxxxxxxxxxxxxxxxxx
***********************************************************

CONFIDENTIALITY NOTICE

This e-mail and any attachments are confidential and also may beprivileged.

If you are not the named recipient, or have otherwise received this

communication in error, please delete it from your inbox, notify thesenderimmediately, and do not disclose its contents to any other person, usethem

for any purpose, or store or copy them in any medium. Thank you for your
cooperation.





--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailinglist

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailinglist

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.