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

[CRPence <CRPbottle@xxxxxxxxx>]

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