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

[Alan Campin <alan0307d@xxxxxxxxx>]

As I think about it, the journal would probably be the simplest.
Normally the problem with a journal entry is defining the record
format but in this case you don't want the record format. You just
want to want to look at each record received from a RTVJRNE for a null
character in the record and the journal would tell which program and
user wrote the record.

On Fri, Jun 29, 2012 at 11:19 AM, Matt Olson <Matt.Olson@xxxxxxxx> wrote:
> Didn't work.  There is actually other data in the field.  It's just that one character is a NUL.
>
> I'm thinking that SQL isn't designed to look for this type of stuff as Alan Campin pointed out that SQL (DDL) described tables would have never allowed this bad data to be entered in the first place.  This is only a problem with DDS described tables.
>
> Adding this as yet another reason to kick off our database modernization project!  Burned by DDS again!
>
>
> -----Original Message-----
> From: AJordan@xxxxxxxxxxxxxx [mailto:AJordan@xxxxxxxxxxxxxx]
> Sent: Friday, June 29, 2012 12:16 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: Dealing with NUL (hex code 00) characters in files/tables
>
> Matt
>
> Try
>
> Select * from tablename where fieldname is null
>
>
> Kind Regards
> Alan Jordan
>
> RODIN by Coglin Mill | 507.282.4151 x 103 | 507.261.4495 Mobile | Rochester, Minnesota USA | www.thinkrodin.com
>
>
>
>
> From:   Matt Olson <Matt.Olson@xxxxxxxx>
> To:     "midrange-l@xxxxxxxxxxxx" <midrange-l@xxxxxxxxxxxx>,
> Date:   06/29/2012 12:07 PM
> Subject:        Dealing with NUL (hex code 00) characters in files/tables
> Sent by:        midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> 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.
>
>
> --
> 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.
>
> --
> 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.