Re: CCSID-Problem when calling a stored procedure

On 08/11/2007, at 11:14 PM, Matthias Zeile wrote:

i have a problem running a java stored procedure. I know why the problem occures, but i don't know how to solve it:

My iSeries' CCSID-sysval (QCCSID) is 65535. I am not allowed to change this value.

That's just stupid. No system should be running with QCCSID as 65535 unless all user profiles are set with a proper CCSID and even then I consider it a wrong configuration.

My job, that runs the procedure inherits the QCCSID via my user profile. When i call my procedure with 'call mylib.myprog' in sql- mode, the system says it can't find the stored procedure. The name of the procedure in the error message is not 'mylib.myprog' but a stream of strange characters.

How are you invoking the stored procedure? From STRSQL? From Java? If so then probably Unicode data.

When i change my job's ccsid to 273 with cl-command chgjob CCSID (273), the call works fine.
Now i would like to know if i can run the stored procedure without change the QCCSID or chgjob.

You have a few options:
1) Use CHGPRF to set your profile to 273
2) Specify CCSID(273) on the SBMJOB command
3) If invoking from Java then use as400.setCcsid(273)--but that's the same as CHGJOB
4) Waylay the system administrator and 'impress' upon him the need for a correct QCCSID

Maybe y can change the encoding of the class-file of the stored procdure before calling
create procedure mylib/myprog()
language java
parameter style java fenced
external name 'WriteToDataQueue.write'
'
???

I don't believe that will have any effect on the encoding of the compiled Java class file.

There is another thing, that puzzles me:
The following java code executed on the iSeries with strqsh returns '273' and not '65535'.

AS400 as400 = new AS400(IP, USER, PASSWD);
as400.setGuiAvailable(false);
int ccsid = as400.getCcsid();
System.out.println(ccsid);

(The AS400-Class is from the jt400.jar)

Are there different CCSID-configurations on our iSeries (V5R4)?

No, but there are two CCSID settings in a job. There is the 'Job CCSID' and the 'Job Default CCSID'. The first is inherited from the user profile and can be overridden on CHGJOB or SBMJOB. The second is set by the system to the same as the first *IF* the first is a proper CCSID. If the first is 65535 then the second is set to a proper CCSID based on the language identifier of the job.

It is quite possible that the getCCSID() method is returning the second value when it finds the first is 65535 or perhaps it just returns the second value anyway because they will be the same unless the first is 65535. I have written my own (non-Java) code to do a similar thing to ensure the caller gets a useful CCSID for a job. A quick test indicates this is the case.

Regards,
Simon Coulter.
--------------------------------------------------------------------
FlyByNight Software OS/400, i5/OS Technical Specialists

http://www.flybynight.com.au/
Phone: +61 2 6657 8251 Mobile: +61 0411 091 400 /"\
Fax: +61 2 6657 8251 \ /
X
ASCII Ribbon campaign against HTML E-Mail / \
--------------------------------------------------------------------