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