Re: SQL error causes all batch jobs to fail

[CRPence <CRPbottle@xxxxxxxxx>]

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