Re: SQL error causes all batch jobs to fail

David FOXWELL wrote:

This is a subject that everyone seems to ignore here and I will
be informing everyone.

I would just like to be clear in my own head as to what happened.

Our system is set to CCSID 65535. A programmer wrote some code
containing the <!!> characters to replace <concat>. I'm assuming
he used these characters as he found it had the result that he
would have wanted from the characters <||>. The Robot SYSAUTO
submitted the jobs running this program with the same CCCSID, and
so no problem. Then AUTOMATOR was introduced causing SYSAUTO to
run with CCSID 297 (I'm not sure how). The program actually
running the SQL instructions saw something other than <!!>.

I realise that we <should> be on CCSID 297, but surely there
would be issues if we just changed now. Also, there could be
other problems as AUTOMATOR takes over from SYSAUTO. Unless we
use 65535 for that as well...

I'd be glad if someone could confirm what I've written or put me
right here.

To anyone that had not been accustomed to using the exclamation symbol in place of the bar symbol, having done so in order to make the SQL function, should have been an glaring indication something was afoul. In that case, the problem would best have been addressed then, instead of delaying. Although using CONCAT avoids the issue altogether, simply changing to use CONCAT in place of the exclamation symbols would merely be a circumvention, to an error in either or both of the CCSID tagging of and use of the source data. Note however that using !! would seem /normal/ to some people who might even have seen documentation many years ago where using those characters would have been described as valid; e.g. pre v2r1m1 as I recall.

Realize that both the CCSID of the job and the CCSID of the source which defines the SQL statement are relevant in what transpired. If the source were also to be tagged as *HEX [aka 65535], then changing the job CCSID probably would not have had any impact. When either half of the equation is *HEX, that implicitly is a request to treat the character data as [hex] code points, not as /characters/ [which humans understand visually by their glyph].

Multi-lingual installations will likely have _some_ problems changing from QCCSID of *HEX to another value, except when all users had previously been created or changed to use their preferred CCSID; i.e. the QCCSID was already made irrelevant for lack of any user defaulting to CCSID(*SYSVAL). The failing scenario originating this thread is just one such example. If such a problem happened with SQL, the problem is likely to exist also, in source of other languages.

Uni-lingual installations where data is USA-like, English language centric, changing from *HEX to CCSID(37) is rarely much of an issue. However all of the same cautions and diligence are required to prevent the change from having some data manifest as incorrect output. That so few failures occur in transitions noted by those in the USA is more likely due to having few or no multi-language support requirements, than for their data having been properly tagged along with their then more appropriately established environment with whatever non-hex CCSID.

Regards, Chuck