Re: Unexpected SQLSTATE returned from embedded SQL

[Sam_L <lennon_s_j@xxxxxxxxxxx>]

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.