Sam,
OM01 is "Outlet Master" for an older application still used by companies such as ours, and OM08 is "Marketing Support".
Both are table/PF which are journaled and have application-enforced create and change user, date and time values.
OM08 stores the one value showing an Outlet requires EDI 856 ASN delivery.
Custom files store delivery detail for all Outlets serviced by one of two computer-automated picking systems, and
other custom files store detail required for EDI 856 ASN. Local code runs for over 150 truck-loads six days a week
for nine locations in western US, and loads vary from about 12 to 50 pallets.
All files are generally intact, accurate and show "problem outlets" as correctly defined and which have been, until
recently, receiving regular ASN delivery since March - April of this year.
Interestingly, "problem outlets" have all been, so far, in one particular location.
I expect we have several system bugs; both in our behind-on-ptfs-iSeries and our local code, but I can't point to one.
I don't see how SQL 02000 can be due to OM08 field values, unless you mean key values are not as expected,
and that seems unlikely, but interesting.
The pattern this week, the one I and my teammate were able to see and document yesterday, has been that first loads
in the one location (first report late last week) will show about six "false SQL 02000" for Outlets which, upon inspection,
have shown valid OM01/OM08 values for ASN delivery. Reprocessing a load containing one or more of these Outlets has
shown that, "eventually", the load processes correctly; meaning expected ASN data appears in custom files.
A key step in reprocessing a load is to delete existing custom records for the load.
The pattern for "eventually" appears to be sometime around 3:30 - 5:00 PM MST.
That brings me to your (and Dieter's) suggestion to look at journal entries.
From your "temporarily changed" comment I now see real value in looking for OM08 changes.
Thank you Sam, I always find your comments helpful and on-target . . .
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Sam_L
Sent: Friday, July 17, 2015 8:52 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Unexpected SQLSTATE returned from embedded SQL
As I understand it, OM01 is the master list of outlets, and OM08 contains transactions.
Is OM01 a table/PF, or is it a view/LF with some records possibly excluded, perhaps temporarily during maintenance? I'm trying to determine if OM01 can be excluded as the cause of the problem.
If you are convinced that the outlets have existed for several months prior to the problems, then (baring a system bug) the SQL 02000 must be due to the OM08 field values. Is is possible that some other process is updating these values as the program runs? For example, I've seen systems where the record status is temporarily changed as a way to "locK" the record for a short time, then changed back. (Not a good design IMHO, but such systems are out there...)
And, FWIW, if you are still have issues looking at journal records, I recommend the freeware EXPJRNE command. I've used it for years to look at journal entries. You have to download and install a couple of pre-req service programs, then download and install the tool, but the EXPJRNE command is very useful. And you can isolate the tools by putting then in their own library.
http://www.tools400.de/English/Freeware/Utilities/utilities.html.
Sam
On 7/17/2015 8:31 AM, Gary Thompson wrote:
Thank you Dan.
No. The job log looks normal.
Yesterday myself and another programmer found a report from the
program showing several SQL 02000 report detail lines and matched
those to the joblog for that time period.
Because these particular jobs are closely scrutinized, they are set to
create the most verbose joblog so we get a lot of 3 to 5 thousand page
logs for each Order Dispatch person daily.
With the date and time from the program report we found six outlets
where the report shows SQL 02000 for an Outlet which did, in fact,
have the combination of OM1 and OM8 data which is required for ASN delivery.
For these six outlets, we have processed some 70 to 90 ASN documents
each since they started on ASN delivery in March - April of this year.
Also, up to today, all "problem" Outlets have been in one Location of
the several currently providing ASN delivery.
This issue has me baffled and confused and I think there is some
detail I've missed. Yesterday I added an additional program step so
that when SQL 02000 is returned, the program does a CHAIN to just the
OM8 file and I will be monitoring that closely today.
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
