Db2 Luw , find all the table contain non-ascii value

Deb Kumar

Db2 Luw , find all the table contain non-ascii value
Hi All,
Is there any way to find the all the table which contain non-ascii value in db2 luw.

ThanksDeb

James Campbell

Db2 Luw , find all the table contain non-ascii value
(in response to Deb Kumar)
Can't really answer about Db2 LUW, but since all byte codes are valid representations of
*some* ascii value (not true, however for Unicode), perhaps you should be a little more
specific about what you mean by "non-ascii value"? And how the data is currently stored.

A bit string, for example, might contain x'FFFF'. If that is supposed to be a binary number do
you want that reported?

I think the answer will be "SQL on every table to check every column for invalid values".

James Campbell

On 13 Feb 2018 at 12:50, Debabrata wrote:

>
> Hi All,
>
> Is there any way to find the all the table which contain non-ascii value in db2 luw.
>
>
> Thanks
> Deb
>

Greg Palgrave

RE: Db2 Luw , find all the table contain non-ascii value
(in response to Deb Kumar)

Hi Deb,

Here's an article that I think covers your question: https://stackoverflow.com/questions/17462802/how-to-find-special-characters-in-db2

Unfortunately, you will have to determine which columns to check for each table.

To correct the non-ascii (special characters?) values you can use the REPLACE function (assuming you are single-byte character set)

e.g.

UPDATE <table>
set <column> =  replace(<column>,x'C2',x'20' )
where ...

 

Regards

Greg


In Reply to Deb Kumar:

Hi All,
Is there any way to find the all the table which contain non-ascii value in db2 luw.

ThanksDeb

Michael Hannan

RE: Db2 Luw , find all the table contain non-ascii value
(in response to Deb Kumar)

Deb,

Some examples might help explain your question better. It is possible to use a TRANSLATE function on a column translating all acceptable byte values to a blank, then all resulting values that are not blank (not completely blank) will indicate exception problem rows. I see the previous reply is a similar thing, except I do not suggest using LENGTH function since TRANSLATE should translate one character to one character, and resulting string will not be empty (at least on z/OS).

You could generate such an SQL, using another SQL on the Catalog, for each table and all CHAR and VARCHAR columns, then run the generated SQLs. This is a very crude approach that would not perform well, of course. FETCH FIRST 1 ROW ONLY allows scan to stop as soon as a faulty row is found.

Not sure if I am really covering your question or not.  There may also be a way to UNLOAD data and process with a system utility to look for unusual characters, however I am more of an SQL Expert. Ha ha!

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Feb 14, 2018 - 09:51 AM (Europe/Berlin)

Mark Barinstein

RE: Db2 Luw , find all the table contain non-ascii value
(in response to Deb Kumar)

Hi,

You should do something like this:
Generate the following query for each your user table needed. Such a query must include all string columns: (VAR)CHAR, may be (VAR)GRAPHIC and (DB)CLOB if needed.

Such a set of queries can be generated with a single select statement on the SYSCAT.COLUMNS view.

 

select t.str1, t.str2
--, t.strN
from mytable t
where
xmlcast(xmlquery('fn:matches($s, "[^\x00-\x7F]")' passing t.str1 as "s") as int)=1
or xmlcast(xmlquery('fn:matches($s, "[^\x00-\x7F]")' passing t.str1 as "s") as int)=1
--or xmlcast(xmlquery('fn:matches($s, "[^\x00-\x7F]")' passing t.strN as "s") as int)=1
;