Re: Is hex 00000 in a string field equal to null?

On 06-Dec-2011 12:03 , Mike Wills wrote:

I might be able to handle the bug okay now that I know what to
expect, but I am trying to determine what that is and why it is
in there.

Logging\journaling can assist in determining the "how"; at least as far as helping to identify the job, timestamp, and probably the program that added or updated the row to have the undesirable data in the column(s). STRJRNPF, if not already journaled.

A Check Constraint and\or triggers can prevent the undesirable data, so as to avoid dealing with the undesirable data later. Or a "Before Insert" Trigger can proactively correct the data in the same way that data would be handled reactively, e.g. treated as an empty string, and could optionally notify of the specific case of all 0x00 being written to the column instead of having to refer to the logs\journal-receiver data. In the case an update versus write\insert were or might be the origin, an Update Trigger would be desirable in conjunction with the Insert Trigger.

Given an original TABLE definition as described by the following CREATE TABLE, the next two statements for adding a CHECK CONSTRAINT and creating a TRIGGER might be defined to look something like [while implying neither completeness nor correctness of the given; i.e. a possibly desirable effect is merely assumed]:

<code>

create table MPRTCSP
( EM_FULL_N varchar(50)
, EMP_COMMENTS varchar(80)
, SUPV_COMMENTS varchar(80)
)

alter table MPRTCSP
add constraint "MPRTCSP.SUPV_COMMENTS.NOT_HEX_ZEROES"
CHECK(supv_comments='' /* empty comment or NULL comment OK */
or translate(supv_comments, ' X', x'0040') <> '' /* all
hex zeroes however, is not OK */
) /* hex zeroes plus blanks or non-x'00' characters,
either embedded or trailing, would be allowed;
the REPEAT scalar may work here, but not on v5r3 */

create trigger MPRTCSP_BI
before insert on MPRTCSP
referencing new as n
for each row mode db2row
begin
if supv_comments<>'' /* not already the empty string */
and left(supv_comments,1)=x'00' /* first byte is x'00' */
and supv_comments=repeat(x'00',length(supv_comments)) /* all
bytes of varlen string are x'00's */
then set supv_comments=''; /* insert empty string instead */
/* call zero_data; */ /* log attempt to insert all x'00's */
end if;
end

</code>

Regards, Chuck