Remove NULL values

Hima1985 · New User Joined: 17 Apr 2009 Posts: 70 Location: India

Hi All,

Can you please let me know how to remove NULL values from a file using sort.

My input file is LRECL=708 and its FB file.

EX:- When i browse

enrico-sorichetti · Posted: Mon Jun 15, 2009 7:54 pm

mild suggestion...
review Your understanding of the "null value" concept...
there is no such a a thing as a null value, every byte of eight bit is a value,
even 00
nullness is a convention to indicate that a value is not provided
when You browse a dataset and You do not see anything, what You do not see
are either blanks or unprintable/undisplayable character
meditate a bit and repost with correct info

Hima1985 · New User Joined: 17 Apr 2009 Posts: 70 Location: India

Enrico,

Yes, That's right. The thing is my input file is geting generated some where from a table where some table fields are NULL's

They are generating the file and giving it to me, when i browse it it will be show as my input file which i pasted above.

I need to removes those dots and replace with spaces.

enrico-sorichetti · Posted: Mon Jun 15, 2009 8:29 pm

what do You see when You try to browse with "hex on"

dbzTHEdinosauer · Posted: Mon Jun 15, 2009 8:37 pm

if this is a db2 dump,
the length of some of the 'unprintable' char seem very long,
perhaps some of the columns are numeric,
in which case I would go back to the dump and CAST those columns to char.

While you are at it, you can use a VALUE scalar function to return spaces for NULL columns.

enrico-sorichetti · Posted: Mon Jun 15, 2009 8:37 pm

I know that I am right, but You do not seem to understand my point..

what is going to be the record layout of the file...
from the data posted just substituting blanks for unprintables will just yield garbage

looks like You are considering everything as char, what You see as dots (unprintables) might also be COMP values

deepuvja · New User Joined: 06 Mar 2008 Posts: 5 Location: chennai

Your query is not clear.

Is your data contain any hexa decimal(non-displayable ) characters?

or it contains only extra ' . ' as you shown in sample data.

If so we can change all . to spaces easily by using syncsort or dfsort

here is the code for it

Here i am trying to convert all . to spaces

hex value of . is 4b and for space is 40

enrico-sorichetti · Posted: Mon Jun 15, 2009 8:59 pm

when browsing under ISPF dots are not dots... just unprintable/not_displayable chars

to find out the exact value You must browse in hex mode "hex on"

until the TS shows a HEX ON screen no suggestion can be made

Skolusu · Posted: Mon Jun 15, 2009 10:45 pm

Hima1985,

Look at the syspunch dataset from the unload step. It WILL list all the columns which has a null value and their positions. Also note that REMOVE is quite different from REPLACE. If you don't want the trailing low values on a varchar column , all you need to do use a char function on that column and it would replace all the low values to a space.

Hima1985 · New User Joined: 17 Apr 2009 Posts: 70 Location: India

Kolusu

Yes we can do that but my problem is i am not dumping it from a DB2 table, some one else doing it. they have pre designed program to do the same.....and they are not ready to change their codes for this...I need to find out a soulution

I am not sure what will be there...when ever i browse it shows like dots and i need to make it to spaces....

Skolusu · Posted: Mon Jun 15, 2009 11:12 pm

Hima1985 · New User Joined: 17 Apr 2009 Posts: 70 Location: India

Kolusu

Its showing like this

enrico-sorichetti · Posted: Mon Jun 15, 2009 11:24 pm

what You see is a packed ( comp-3 in COBOLESE ) number with a value of 4

investigate about...
the record layout ( the order in which the columns are unloaded )
the format of each column