Re: CPF5035 used to be ignored, now causes SQL to fail

Just thinking out loud, but would a BEFORE INSERT trigger allow you to
examine the buffer and make corrections before the 406 error occurred?

Brian May
Project Lead
Management Information Systems
Garan, Incorporated
Starkville, Mississippi



Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
01/19/2011 03:54 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Subject
Re: CPF5035 used to be ignored, now causes SQL to fail






On 1/19/2011 2:09 PM, CRPence wrote:

A "direct map" rule will allow the bad decimal data to transport, but
only if the additional cursor mapping of the target ODP do not include
some additional mapping rules; e.g. data validation for decimal as
required for an SQL TABLE, or data validation for a CHECK constraint.
Presumably the target of the alluded INSERT..SELECT is a DDS created
database file, and direct mapping is allowed, but on v7r1 the direct
mapping simply is not established [as it was on v5r4] for the field with
the bad decimal data. The vagaries of the query engine. where the
*only* valid response to avoiding decimal data errors from bad data is
to clean the data; any other response just allows for yet another minor
implementation change to expose the bad data as a query failure.

The default action today is to put zeros into the file instead of
blanks. That's what happens on V5R4. I did nothing to program that,
it's just what SQL on V5R4 does. Legacy data has lots of blanks in
numeric fields, unfortunately. It's a by-product of the
O-specification. I don't want to leave the bad data there but I would
prefer an option short of crashing the job because of blanks.

That being said, at least we now know what to look for. It is entirely
possible that a job that ran correctly on V5R4 will fail with SQL0406 on
V7.1, and we'll more easily isolate the problem now. Short term we
write programs to patch the data and medium term we fix the old legacy
programs that dump bad data into the files.

Joe