SQL Trace Dump in Shop Order Launch

Hi Al,  

I would order the latest Group PTF for SQL for your release of OS/400
(SF99501 for V5R1) to get up to date on that as a first step. 

To really debug this you are going to need a joblog, no ifs ands or
buts. 

If it is just entering the program that causes this, maybe you can
reproduce it yourself and collect what you need to show IBM that way.
Otherwise, you'll have to get strict with the users and make them save
this (tell them SIGNOFF *LIST for example if this happens and call you
immediately).

If the program has not changed, it could be an issue in the SQL runtime
on the iSeries. The SQLCA is the SQL 'communications area' which is used
at runtime to store SQL variables that your program is sending to the
SQL runtime layer and is where the database layer can send errors back
to your program. 

You can get SQL dumps for various reasons, but so far I have only seen
these kinds of errors caused by runtime bugs in SQL itself - normally
other types of SQLCA issues are caught at compile time when the SQL
pre-compiler runs or when the SQL runtime gets an error back from
database in the SQLCA, a much nicer SQLnnnn type of message is then sent
to your program to document normal, expected errors (like 'Result of
Select returned more than 1 row' etc.). 

Base BPCS CD has no SQL, so I imagine this is one of your modified
parts. You can read more about the SQLCA here if you are really
interested:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.
ibm.db2.udb.doc/admin/r0002212.htm 

Thanks,

Genyphyr Novak
Senior System Software Engineer
SSA Global R&D
 
message: 3
date: Fri, 24 Feb 2006 04:12:14 -0600
from: Al Mac <macwheel99@xxxxxxxxxxx>
subject: [BPCS-L] SQL Trace Dump in Shop Order Launch

What I seek: Clue me
* what the heck this is;
* what it supposed to be telling me;
* where to look in a program, what could trigger it.

AS/400: model 170
OS/400: V5R1
BPCS: 405 CD

Our AS/400 resources seem quite healthy.
What tends to go down a lot is ma bell and the electric company.

My users tell me this is now happening when they launch shop orders.
They 
supply me with some screen prints that are unintelligible to me, and
they 
apparently delete any other useful clues, other than the reports they
need. 
... I am lucky to get what I have got.  There's several ways to launch
shop 
orders.  I launch a few to seek a sample of what's going on, but it
works 
fine for me.

This is a program that needs to be run several times a day (different 
people managing the work for different customers) ... I spy on joblogs
of 
the folks generating the orders, but I not yet catching this in the act.

They are supposed to run it off our default JOBQ (only one person at a
time 
updating some of the stuff).

I do not know
how long this has been going on
how consistent it is
what if anything else is wrong ... like perhaps they get the output 
desired, and this dump along the way

A few months ago we altered how we launch shop orders, and how we report

labor against them, to save on paper and clerical time.  I not know if
this 
stuff started at the same time, or is unrelated.

They have supplied me with a "User Trace Dump" from QTEMP/QAP0ZDMP 
consisting of data, supposedly from QSQLSRV that means nothing to me
from 
SQLCA section:
00000056:
then a 6 digit decimal # from 236872 to 717680
then D39DEDD291
then a 6 character hexadecimal # from 018A09 to 018A90
then a bunch more hexadecimal strings, most of them all zeros
The dump is 130 print positions wide,of which my users supplied first
100

The program is heavily modified, approx 20% of source code came with
system 
& 80% what we (me) done over the years.  Last modified Sep 2005, tested 
then, working fine.  When this has blown up in the past, either it has
been 
a typo during more mods, or some of our data has gone beyond prior
extremes 
or ceilings.

This is an RPG/400 program with embedded SQL/400 which is used both
through 
cursors and direct, to get
* customer name, that the production is for
* data from various engineering and shop order related files such as 
revision #, calculate rates, facility, type & class of item, work
center, dept

It also calls several sub-programs to get BOM active where-used array
and 
identify key components.  Compilation approx 150 pages. Base program
size 
(execution object) = 700 k
It also has a DDS, data structure, and the connected programs

Nothing involved here modified for many months.
Most recent modifications have been to unrelated software.

At year end, we reset next shop order at # 1,
and since then have reached 9,000
Order # 200,000 and up is still open from last year
Inventory history, smallest shop order # after 10,000 is 60,000
Labor history smallest shop order # after 10,000 is 50,000
so we are 4 months away from a collision there

-
Al Macintyre  http://www.ryze.com/go/Al9Mac
Find BPCS Documentation Suppliers 
http://radio.weblogs.com/0107846/stories/2002/11/08/bpcsDocSources.html
BPCS/400 Computer Janitor at http://www.globalwiretechnologies.com/
Replacement company web site (same company, new 
domain)  http://www.globalwti.com/