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

On 1/19/11 1:19 PM, Joe Pluta wrote:

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.

From what I recall, the combined database, query, and SQL features never should have effected any result of zeroes for blanks on the SQL INSERT..SELECT across matching formats. Although the combined database and query /cleansing/ of decimal data is available if\for having used the CQE with the "Ignore Decimal Data Errors" feature enabled [i.e. the IGNDECERR() parameter on OPNQRYF], that feature would not be available to the SQL INSERT statement. I am suggesting that for a result of zeroes from blanks using SQL INSERT..SELECT, that there would be a defect on v5r4 for effecting neither of direct-map nor a mapping error causing the SQL0406 [or SQL0302]; i.e. either the target non-SQL database file should receive the unmapped\uncleansed data to the corresponding like-defined field, or the INSERT should see\receive the mapping error and fail the INSERT [just as noted the 7.1 SQL request does]. AFaIK only "native" database requests like CPYFRMQRYF, CPYF, Query/400 should ever be using the /cleanse/ feature; and those, only to enable mimicking that horrible behavior of the S/36 to "ignore" bad data.

FWiW I can not reproduce the noted effect in v5r3. Perhaps for my curiosity, you could provide a scripted example; sample DDS, sample [bad] data, CRTPF requests, CPYF requests, and the SQL INSERT..SELECT statement that recreates the zeroes versus blanks or statement termination.?

Regards, Chuck