Re: Where is CLOB data physically stored?

On 03-Mar-2015 14:26 -0600, Needles,Stephen J wrote:

<<SNIP>> I've got disk being burnt quick and it seems as though it
may be due to converting a 32K character column to a CLOB(1M).

I think that I've found the reason...

In determining the length of the CLOB column at write time, it was
calculated using:

CLOB_Len = %len(%trim(clob));

But this seems to only trim the x'40' characters off the end of the
string. Somehow , the clob variable was padded with x'00'...so the
%trim() didn't work...it returned the whole length...1M.

The %TRIM opcode accepts a second parameter which allows naming the characters to be stripped, so x'00' as well as x'40' can be stripped with the same request:

CLOB_Len = %len(%trim(clob:x'0040'));

Note: that %TRIM is the equivalent to the the SQL scalar TRIM using the BOTH keyword, as contrasted with the effect of using the TRAILING keyword, with regard to where trimming takes place.

To repair it, I used SQL trim to whack the trailing x'00' characters,
calc'ed the length of this much shorter string, and got the correct
answer:

reset Clob;
CLOB = %trim(parm_in);
exec sql
set :length
= length(trim(trailing x'00' from :parm_in));
CLOB_Len = length;

That code snippet seems suspect to me; perhaps the lack of the supporting declarations and code, and I am just not understanding at all, but...

While I am not sure about what are the definitions for the variables and how each is being used, the naming "PARM_IN" seems possibly to suggest that the data is passed-in as a parameter [thus by reference] into the RPG program and per use of %TRIM() is not already declared as varying and thus perhaps should be *VARSIZE such that the length would be known per the invoker.? In that case, perhaps the "somehow" that the hex zeroes are trailing the expected "much shorter string" data is actually an unpredictable outcome; i.e. perhaps the data is inconstant and unpredictable automatic storage which may not be predictably\always hex zeroes. Plus, if the CLOB_Len was not previously being calculated properly per use of the simple %TRIM() [i.e. omitting the optional second argument {:characters-to-trim}] then the value of CLOB using the same opcode would similarly have been set improperly in the above code snippet, per the assignment giving the string value with the undesired trailing hex zeroes; in that case, the %TRIM() coded in that snippet would seem either to be pointless or perhaps instead requires the /correction/ to request trimming of both characters.? Finally, the assignment of host variable :length, presumably, could be assigned directly to the :CLOB_Len variable; minimally, the difference between the %TRIM() /trimming both ends/ versus what should be the equivalent SQL /trimming only trailing/ need to be synchronized.?