CODEWITHSUNDEEP
stored-proceduresdb2-400java-stored-procedures
Phil Rice
Phil Rice

Reputation: 41

how to get the data out of a stored procedure when the data is a 'clob' on an IBMi using DB2?

I am accessing a IBMi running DB2 from a java JVM. I have a stored procedure that returns a CLOB and I want to access the clob from Java.

The sql for the stored procedure is:

CREATE OR REPLACE PROCEDURE ZZSP (
IN fromjson VARCHAR(16000) CCSID 1208,
out tojson clob(10000000) CCSID 1208,
out last_id decimal(20, 0),
out sha VARCHAR(20) CCSID 1208,
out version VARCHAR(20) CCSID 1208)
LANGUAGE RPGLE
SPECIFIC ZZSP
DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
COMMIT ON RETURN YES
EXTERNAL NAME 'ZZSP'
PARAMETER STYLE SQL;

The stored procedure can be accessed from 'run sql scripts' in IBMi client access solutions:

create variable MYCLOB5 clob(10000000) ccsid 1208;
CALL ZZSP ('{"returnStatus":" ","fetchKey":{"inp1":1,"seq1":2},"status":"X"}',myClob5,?,?,?);
values myClob5;

When I do this, the 'values myClob5' holds a json string that is almost identical to the passed in json string (because that's what the stored procedure does). I am thus assuming that the rpg code that this calls (also called ZZSP) is working.

However when I try and access this from java I run into a problem. I can use this code to get data out of a resultset, but not out of the statement. Prior to the call we have set up parameters in the callable statement:

void doit(CallableStatement s){ 
      cs.setString(1, json)
      cs.registerOutParameter(2, Types.CLOB)
      cs.registerOutParameter(3, Types.DECIMAL)
      cs.registerOutParameter(4, Types.VARCHAR)
      cs.registerOutParameter(5, Types.VARCHAR)
 // setup the statement with the sql 'call ZZSP(?,?,?,?,?)', and execute it
   
   val jsonOutClob = s.getClob(2)
   val json = jsonOutClob.getSubString(1, jsonOutClob.length().toInt)
   ...
}

While all the other data returned from the stored procedure (outParameters 3,4,5) return the correct value, the data in the clob is 'garbage': it doesn't look like a string to me. The data I expect is something like '{"returnStatus":" ","fetchKey":{"inp1":1,"seq1":2},"status":"X"}', but the actual data is something like '?񅣤񕢣񣤢zɕƿk??𒅨z???󓅉􉕗??񫿢??????񉕗??򐫿񥁉󁂉󉣨⣁󤢿zտЀ'. It's also interesting that the length of the clob is half the length of the expected string. The bytes in the returned string start with List(3f, f1, 85, a3, a4, f1, 95, a2, a3, f1, a3, a4, a2, 7f, 7a, 7f, c9, 95,...)

I have tried other methods on the statement: getCharacterStream for example, and it returns the same data.

It feels that this is a character encoding issue, but I don't know how to address it. It also feels that it might be something around the 'parameter style' which I have set to 'sql'. We have tried general as well and with that the 'iac' program also returns garbage.

I am in week two of this problem... we had a load of other issues in the RPG code which have been fixed, but for this one I am tearing my hair out.

Can anyone advise me how to get the data out of a stored procedure when the data is a 'clob'? If that is impossible, could you advice me how to get a large string (perhaps a megabyte) out of a stored procedure?

Tech stack:

Thanks!

Edited in to answer Charles question:

The rpg program starts like this. This is constantly being changed because we are experimenting with how to make the toJson have the correct CCSID. The two /Set ccsid (*char : *UTF8) are obviously not both needed. We've tried all four permutation (none/first/second/both). This is the code that 'works' when we call the program from IAC.

**free
ctl-opt option(*srcStmt:*noDebugIO) dftactgrp(*no) actgrp('Somegroup') CCSID(*char:*jobRun);

exec SQL
  set option commit = *none;
/Set ccsid (*char : *UTF8)
  dcl-s toJsontemplate        sqltype(CLOB:10000000);
/restore CCSID(*char)

dcl-pi *n;
  fromJson  varChar(16000) ccsid(*utf8);
  /Set ccsid (*char : *UTF8)
  toJson        likeds(toJsontemplate);
  /restore CCSID(*char)
  id        packed(20);
  sha       varChar(20) ccsid(*utf8);
  version   varChar(20) ccsid(*utf8);
end-pi;

The place where the variable is set is looks like this

  exec SQL
   set :tojson = json_object(
       'returnStatus'            : trim(:returnStatus),
       'fetchKey'                : json_object(...

Edit

Following the advice in the answers I used opt-ctl to set ccsid global.

**free
ctl-opt option(*srcStmt:*noDebugIO) dftactgrp(*no) actgrp('Somegroup') CCSID(*char:*UTF8);

exec SQL
  set option commit = *none;
  dcl-s toJsontemplate        sqltype(CLOB:10000000);

dcl-pi *n;
  fromJson  varChar(16000) ccsid(*utf8);
  toJson        likeds(toJsontemplate);
  id        packed(20);
  sha       varChar(20) ccsid(*utf8);
  version   varChar(20) ccsid(*utf8);
end-pi;

This made absolutely no difference. I think the variable tojson holds utf8 data (unless there is a bug in RPG) as opt-ctl sets the ccsid for the entire module. I think it is now clear (since the IAC can actually read the clob correctly) that this is some sort of negotiation issue between the Jdbc and the stored procedure. IAC does it properly and gets the correct data, and my code doesn't and gets the wrong data.

Any idea where I can go from here?

Upvotes: 0

Views: 834

Answers (2)

Phil Rice
Phil Rice

Reputation: 41

This was much harder than expected. In the end I gave up doing it directly: I could not get the result from the clob into java in the manner I expected.

However 'there's always a way in IT'. So we ended up doing the same as we had working in IAC:

We used a global variable ('clob5' in this example). A global variable is defined in 'some library', but each session gets it's own value so we have no parallelism issues. The variable is defined once and then left alone

When we called the procedure in the stored procedure we use the same command we used

CALL ZZSP ('{"returnStatus":" ","fetchKey":{"inp1":1,"seq1":2},"status":"X"}',myClob5,?,?,?);

In order to get the value out of the variable we used (using the same connection)

SELECT myclob5 from SYSIBM.SYSDUMMY1

Upvotes: 0

Charles
Charles

Reputation: 23813

Yeah, I suspect you're doing something wrong with CCSID, but exactly what I can't tell.

What is the RPG program actually doing? Is it really needed? You might find it much easier to use an actual SQL Stored procedure (written in SQL).

If the RPG program is actually needed...

I question the use of the CCSID stuff in the first place. Db2 for i and the JDBC driver have no issues converting from the EBCDIC CCSID into the UTF-16 used by Java. Unless your input actually includes non-alphanumeric characters that only exist in Unicode.

Db2 for i and RPG are generally good at working with Unicode. But a couple of old practices often cause problems.

  1. Not setting a default CCSID for the system dspsysval qccsid It should be something other that 65535.
  2. related, not having a CCSID set on the character columns in your tables dspffd MYTABLE
  3. Jobs running under CCSID 65535, shouldn't be the case if the system is set to an actual default CCSID.

Assuming all those are good, I think coding

ctl-opt ccsid(*EXACT);
ctl-opt ccsid(*CHAR:*UTF8);

Would cause the RPG code to work with UTF-8 data across the board. Db tables with columns as CCSID(37) would be converted to UTF-8 when read with native RPG file op-codes.

Here's a good presentation about CCSID and RPG

Edit
The point about "questioning the need for the CCSID stuff" is that the Db/JDBC driver does quite well converting from EBCDIC to UTF-16 used by Java. Particularly English ccsid(37) and the ASCII subset of Unicode.

Now you're dealing with 30+ countries, but the above holds true for most CCSIDs as far as I know without having actually been in that position.

However, JSON is supposed to be in UTF-8,UTF-16,UTF-32 so yeah the "right" thing to do would be to use Unicode parms. But you'd need to use Unicode all the way down and back up the RPG call stack, otherwise you could run into unconvertable characters.

I've also never really looked into the RPG SQL Pre-Compiler functionality with respect to EBCDIC/Unicode conversion.

I suspect your

  exec SQL
   set :tojson = json_object(
       'returnStatus'            : trim(:returnStatus),
       'fetchKey'                : json_object(...

Is resulting in EBCDIC data in tojson that the system thinks is UTF-8, so it returns it as is.

Upvotes: 0

Related Questions