Can someone provide other debugging ideas, commands, or variables in program A or the service program that I can look at to see what is going on?
The programming pattern that I use for this sort of thing is that when
the program ends up with an SQLSTATE that is really doesn't recognise,
the program issues a DUMP(A).
exec sql update fred set barney = pal;
if sqlstate <> sql_normal
and sqlstate <> sql_no_data;
dump(a) 'update fred';
leave / return / hard exit
endif;
The idea being there is a trail of breadcrumbs to follow.
Modern SQL isn't a simple replacement for CHAIN/UPDATE. The table
being updated might have a trigger that in turn tries to update some
other table that might have a trigger; triggers might be referring to
service programs which aren't in the library list; SQL UDFs or SQL
UDTFs that aren't in the PATH. When I get totally lost, it's often
because I've fallen back to thinking that I'm updating just one row.
I'm often not.
Check SQLSTATE diligently after each and every executable SQL
statement. CLOSE, OPEN, FETCH, UPDATE - all of them. I've amazed
myself (not that hard) to find that the problem with my FETCH has been
that I wasn't authorised to the table, and it was the OPEN that
actually fell over but the program kept going.
Further debugging tactics might involve writing to a log file. You
'log' variables and statements that you think will narrow things down.
The log file can be an IFS stream file (I like LOG4RPG for this) or
even a simple O-Spec printer file. OVRPRTF that to a PDF and the
customer can email it to the vendor / you. Along those lines, don't
discount the printed job log. Have the customer recreate the problem,
then SIGNOFF LOG(4 0 *SECLVL) to generate a job log spooled file.
For this specific problem, record the library list, SQL PATH and
SCHEMA settings before and after calling program B. I hope that might
yield a clue.
--buck
midrange.com
