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

On 1/19/11 10:25 AM, Joe Pluta wrote:

In our upgrade from V5R4 to 7.1, we've run into a situation where
SQL seems to be acting differently. Granted, it's a problem with
our data, but I'm trying to find out what our exposure is and
whether there's a way to minimize the disruption.

The issue is that we are inserting from one table into another
(an identically formatted work table). The problem is that in some
of the records to be copied, a signed numeric field has blanks
rather than zeros. On our V5R4 box, this completes successfully but
on the 7.1 box it does not.

Using the exact same data and syntax in STRSQL, I see the following
behavior:

In V5R4, I see multiple CPF5035 errors with what looks like an
automatic reply of C. One is issued for every bad record, but the
INSERT continues to completion and the inserted records show zero
in the offending column.

In 7.1, I see a CPF5035 error for the first record with bad data,
followed by two identical CPF5029 errors for the same record,
followed by an SQL0406 (type 6 = invalid numeric data) which
terminates the statement.

So, did IBM just tighten up the rules a bit on the INSERT? Is there
a new setting somewhere that will allow me to ignore these CPF5035
entries? I don't have an entry in the system reply list for this
message, and the message definitions for CPF5035 are identical on
the two machines, so I've run out of ideas.

The "C"=Cancel replies are default replies to program-to-program messages; in this case, the CPF5029. Note that CPF5035 is not capable of receiving replies. The CPF5029 should be identical in both releases, and must have 'C' as default reply to avoid horrendously negative effects [for having lied to programs, by ignoring I\O errors]. What might differ, and disclose the change, is the "to program" of the CPF5029; the sender copy. The new symptoms suggest the QSQINS or equivalent function, the insert, is the recipient of the mapping error. The old symptom could be experiencing the validation of the data in the QDBGETM, the read, for which the effect is only a warning rather than the terminating -406 SQLcode. Reviewing the job traces of the two INSERT SQL requests would probably be the most informative.

The issue could easily be a side effect of the SELECT query implementation, the access plan, for which the same effect may have been possible on any release; i.e. that the query inserts the bad data on a prior release is probably just [bad] luck. While theoretically the CPF5035 could be ignored generally for non-selection fields for generating the SELECT query result set when using a DDS-created file as target of the INSERT, the means of getting the physical data into the target from the query ODP may prevent success on the "insert" due to the mapping rules that will be applied for copy of the data between the two. 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.

Regards, Chuck