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.?
midrange.com
