Re: Dealing with NUL (hex code 00) characters in files/tables

[Alan Campin <alan0307d@xxxxxxxxx>]

This is an example of where writing a simple internally described file
I/O program makes sense. Just read the file record by record into a
single field and scan for a null.

You could also journal and just read the records in from the journal
and scan for the null or trigger that just looks at the record as a
single field and scans for the null.

the other thing to do, if you could, would be to define the tables
using DDL. SQL will not allow invalid characters to be written to a
database table where DDS described will.

On Fri, Jun 29, 2012 at 11:07 AM, Matt Olson <Matt.Olson@xxxxxxxx> wrote:
> Folks,
>
> We found an issue with the IBM DB2 Driver for .NET (also occurs if you try to do the Data Transfer from IBM i) when dealing with characters in a CHAR field (CCSID 37) that contain a NUL (hex code 00) character in the field.  In both cases (.NET driver and Data Transfer from IBM i) we get a character code conversion error and the whole SELECT SQL statement fails.
>
> Any easy way to find these values in all tables, and hopefully we can track down how the heck this bad data is getting entered.
>
> I attempted: select * from tablename where fieldname like '%' || chr(00) || '%' to no avail.
>
> Matt
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.