Re: SQL error causes all batch jobs to fail

Hi David

I don't know any negative consequences to making the change from 65535 to whatever is your CCSID. There might be, but I've done this in the middle of the day - no problems. Of course, I'm in the USA and maybe I'm just lucky. Some things won't work at all if you have 65535 for the CCSID - JDBC is one, I believe. I did see an IBM article that says you need also to change any database table columns that are set to 65535, or there could be translation issues. You probably should contact IBM support around this - or ask Bruce Vining to respond here. :-)

There was no reason to use <!!> or <||> instead of concat - other than being shorter - and concat is ALWAYS safe - I have to ask the reason for using the characters instead of the word - check the documentation for SQL operators and it should be explained there.

By the way, I did notice that 297 does not have Euro support - 1147 is the CCSID for France with Euro support.

Here is a link to QCCSID info at infocenter - *http://tinyurl.com/mnm3em*

I googled for "qccsid 65535 change" and got a lot of hits - most support changing the system value. But it is important to determine the risks, if any.

HTH
Vern

David FOXWELL wrote:

Thanks Vern.

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.

-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Vern Hamberg
Envoyé : jeudi 18 juin 2009 15:07
À : Midrange Systems Technical Discussion
Objet : Re: SQL error causes all batch jobs to fail

To add to Rob's explanation, there is a set of invariant
characters between various code pages - and the vertical bar
is not one of them, nor the dollar sign, as he said. We are
told never to use the variant characters.

I googled "invariant character" and got the link at IBM for
it - this will get you there - *http://tinyurl.com/lybjbb

Those are the only characters you are ensured will be the
same, no matter what ccsid you use - with the exceptions
stated in that article.

HTH
Vern

*rob@xxxxxxxxx wrote:

David,

Item one:
You are in a multilingual environment. You should NOT use

|| or !!.

You should use concat instead. Much like the US Dollar

sign will get

changed to various other characters in different character sets so
will those shortcuts for concat. In fact, everyone should

avoid ||.

Never can tell when mergers or acquisitions may take place

and put you

in a multi ccsid environment.

Item two:
SELECT DIGITS(myfield)!!'_'DIGITS(myotherfield) FROM mytable You're
missing the second concat between '_' and DIGITS(myotherfield).

Rob Berendt

--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/midrange-l.