Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Remove NULL values

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Hima1985

New User


Joined: 17 Apr 2009
Posts: 70
Location: India

PostPosted: Mon Jun 15, 2009 7:50 pm    Post subject: Remove NULL values
Reply with quote

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
Code:

aaaa...xxxx....b0b..vvvvvvv.......
CCCc...xxxx....b0b..vvvvvvvccc....
bbbbbbbb........ddddddddddddd......
xx.........v....v....vvvvv...........bb...


Out put should be

Code:

aaaa   xxxx    b0b  vvvvvvv
CCCc   xxxx    b0b  vvvvvvvccc 
bbbbbbbb          ddddddddddddd
xx         v   v      vvvvv          bb


whic means NULL values might present in any position in record, inspite of position and length of NULL value it needs to be removed from record
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Jun 15, 2009 7:54 pm    Post subject: Reply to: Remove NULL values
Reply with quote

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
Back to top
View user's profile Send private message
Hima1985

New User


Joined: 17 Apr 2009
Posts: 70
Location: India

PostPosted: Mon Jun 15, 2009 8:22 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Jun 15, 2009 8:29 pm    Post subject: Reply to: Remove NULL values
Reply with quote

what do You see when You try to browse with "hex on"
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Jun 15, 2009 8:37 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Jun 15, 2009 8:37 pm    Post subject: Reply to: Remove NULL values
Reply with quote

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
Back to top
View user's profile Send private message
deepuvja

New User


Joined: 06 Mar 2008
Posts: 5
Location: chennai

PostPosted: Mon Jun 15, 2009 8:52 pm    Post subject:
Reply with quote

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

Code:

//S1    EXEC  PGM=SORT                                         
//SYSOUT  DD SYSOUT=*                                         
//SORTIN DD DSN=MYIDSR.TEST.TEMP1,DISP=SHR                     
//SORTOUT DD DSN=MYIDSR.TEMP.TEST2,DISP=(NEW,CATLG,DELETE),   
//           SPACE=(CYL,(5,5),RLSE)                           
//SYSIN  DD *                                                 
  SORT FIELDS=COPY                                             
  ALTSEQ CODE=(4B40)                                           
  OUTREC FIELDS=(1,708,TRAN=ALTSEQ)                           
/*                                                             
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Jun 15, 2009 8:59 pm    Post subject: Reply to: Remove NULL values
Reply with quote

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
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Mon Jun 15, 2009 10:45 pm    Post subject:
Reply with quote

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.
Code:

CHAR(VARCHAR COLUMN NAME)


You dont even need a utility when everything can be done in DB2 itself
Back to top
View user's profile Send private message
Hima1985

New User


Joined: 17 Apr 2009
Posts: 70
Location: India

PostPosted: Mon Jun 15, 2009 10:59 pm    Post subject:
Reply with quote

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....
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Mon Jun 15, 2009 11:12 pm    Post subject:
Reply with quote

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


Hima1985,

Read what Enrico was trying to tell. Dots in browse mode can mean any thing.

ex .

1. open a empty file
2. Type HEX on at the command prompt
3. Insert a empty line
Code:

----+----1
**********
         
4444444444
0000000000


3. Now overlay the top 4's with zero's and the last byte on lower with a 1C (which is +1 in packed decimal format)

like this

Code:

----+----1
         
0000000001
000000000C


save this and re open the same in browse mode , you would see dots but if you replace all the dots to spaces you are making the actual packed decimal value invalid.

So you really need to understand what the dots mean and what you need to replace them with. Put a hex on the file and see the value in hex
Back to top
View user's profile Send private message
Hima1985

New User


Joined: 17 Apr 2009
Posts: 70
Location: India

PostPosted: Mon Jun 15, 2009 11:17 pm    Post subject:
Reply with quote

Kolusu

Its showing like this

Code:
0000000004
000000000F
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Jun 15, 2009 11:24 pm    Post subject: Reply to: Remove NULL values
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am
No new posts How can we create a flat file in JAVA... rakesh.v18 Java & MQSeries 7 Fri Sep 23, 2016 10:46 pm
No new posts Using 'parm' to vary SORTOUT record v... Sysaron DFSORT/ICETOOL 13 Wed Sep 07, 2016 9:24 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us