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
 

 

DFSORT query

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

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Mon Apr 20, 2015 3:11 pm    Post subject: DFSORT query
Reply with quote

Hi,

I need small help/suggestion on my DFSORT query. Detail of the issue given below..

Our job failed while loading data into Prod DB2 table because of Junk value.Here Junk means Non-Numeric char in Numeric field (Data type mentioned in table as DECIMAL). To prevent from that happening I am thinking to use sort before Loading step.

Data format: Example
Code:
------------------------------------------------------------------------------------
0680106701067 DRESDEN            0000168 1 1001SCHLOSSER  D0   9999
0580116711067 DRASIEN            0101168 1 1001SCHAOSYEU  E0   99AB
06801069010DF DRESDEN            0000168 1 1001SCHLOSSER  D0   999H

OUTPUT: (Should be)
Code:
------------------------------------------------------------------------------------
0680106701067 DRESDEN            0000168 1 1001SCHLOSSER  D0   9999
0580116711067 DRASIEN            0101168 1 1001SCHAOSYEU  E0   99
06801069010    DRESDEN            0000168 1 1001SCHLOSSER  D0   999


Means SORT will check specific columns whether it is Numeric or not. Once it gets Non Numeric value in any of those specific columns that that specific Non Numeric value/s will be replaced with space/spaces.

Can anyone please guide me on should I start. I was thinking of using
Code:

INREC IFTHEN=(WHEN=(Start Pos,End Pos,FS,NE,NUM),

but once it will match that Non Numeric found then shall I use OVERLAY or BUILD? and how to replace those non numeric values only with space?

Thanks
Subrata
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Mon Apr 20, 2015 4:44 pm    Post subject:
Reply with quote

Quote:
To prevent from that happening I am thinking to use sort before Loading step.


Wrong approach - fix the program that is giving bad data, or get the responsible party to fix it.
Back to top
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Mon Apr 20, 2015 5:13 pm    Post subject:
Reply with quote

Hi Nic,

I understand what you said. But it's third party who is creating file in windows system and before the Loading step in JCL, we are just running FTP GET command to copy the file into ZOS system.

So it's our responsibility to have a check at our end. This can be done I guess using pgm but I want to do it using JCL.

Thanks
Subrata
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Mon Apr 20, 2015 6:31 pm    Post subject: Reply to: DFSORT query
Reply with quote

If this is what you want:
Code:
//SYSIN DD *                                       
 OPTION COPY                                       
 INREC IFTHEN=(WHEN=(Start Pos,Length,FS,NE,NUM),OVERLAY=(Start Pos:X)) 
/*                                                 

Put number of spaces you need in front of 'X'
.
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Mon Apr 20, 2015 6:49 pm    Post subject:
Reply with quote

I agree with Nic, but as Subrata says, sometimes you have no control over what an outside vendor sends you.
Back to top
View user's profile Send private message
rinsio

New User


Joined: 16 Feb 2015
Posts: 13
Location: Madrid, Spain

PostPosted: Mon Apr 20, 2015 7:02 pm    Post subject:
Reply with quote

Hello Subrata,

How many fields do you need to fix?
I have a first apprach to solve this problem but it is with many steps depending on the fields to be fixed. Also too you need to have identified the non numeric values.
In my example I am taking into account all the letters as non numeric values and 2 fields to be fixed.


Code:
//SORT01   EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD *                                                         
0680106701067 DRESDEN            0000168 1 1001SCHLOSSER  D0   9999     
0680116711067 DRASIEN            0101168 1 1001SCHAOSYEU  E0   99AB     
06801169010DF DRESDEN            0000168 1 1001SCHLOSSER  D0   99AH     
/*                                                                     
//SORTOUT  DD DSN=XXXXXX.MAINFR.SALIDA,                                 
//            DISP=(,CATLG,DELETE),                                     
//            SPACE=(TRK,(50,50),RLSE),                                 
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=0,DSORG=PS)               
//SYSIN    DD *                                                         
    SORT  FIELDS=COPY                                                   
    OUTFIL FNAMES=SORTOUT,                                             
         IFTHEN=(WHEN=(1,13,FS,NE,NUM),                                 
          FINDREP=(IN=(X'C1',X'C2',X'C3',X'C4',X'C5',X'C6',X'C7',       
                       X'C8',X'C9',X'D1',X'D2',X'D3',X'D4',X'D5',       
                       X'D7',X'D8',X'D9',X'E2',X'E3',X'E4',X'E5',       
                       X'E6',X'E7',X'E8',X'E9'),                   
                                    OUT=X'40',ENDPOS=13))           
/*                                                                 
//SORT02   EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD DSN=XXXXXX.MAINFR.SALIDA,DISP=SHR                     
//SORTOUT  DD DSN=XXXXXX.MAINFR.SALIDA2,                           
//            DISP=(,CATLG,DELETE),                                 
//            SPACE=(TRK,(50,50),RLSE),                             
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=0,DSORG=PS)           
//SYSIN    DD *                                                     
    SORT  FIELDS=COPY                                               
    OUTFIL FNAMES=SORTOUT,                                         
         IFTHEN=(WHEN=(64,04,FS,NE,NUM),                           
          FINDREP=(IN=(X'C1',X'C2',X'C3',X'C4',X'C5',X'C6',X'C7',   
                       X'C8',X'C9',X'D1',X'D2',X'D3',X'D4',X'D5',   
                       X'D7',X'D8',X'D9',X'E2',X'E3',X'E4',X'E5',   
                       X'E6',X'E7',X'E8',X'E9'),                   
                                    OUT=X'40',STARTPOS=64))   
/*                                                           



The output file

Code:
 Command ===>                                                  Scroll ===> CSR 
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
********************************* Top of Data **********************************
0680106701067 DRESDEN            0000168 1 1001SCHLOSSER  D0   9999             
0680116711067 DRASIEN            0101168 1 1001SCHAOSYEU  E0   99               
06801169010   DRESDEN            0000168 1 1001SCHLOSSER  D0   99               
******************************** Bottom of Data ********************************



I hope this example will be useful.

Regards

coded for you
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Apr 20, 2015 7:29 pm    Post subject: Reply to: DFSORT query
Reply with quote

for code snippets and data readability learn how use the code tags
and read the sort manuals about the use of HIT=NEXT
Back to top
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Mon Apr 20, 2015 8:58 pm    Post subject:
Reply with quote

Hi All,

Thanks a lot!! for your time and effort. Thanks again for pasting the code snippet. Let me try at my end with what you all have given.. for any further query I will come back..

Thanks
Subrata
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Apr 20, 2015 10:06 pm    Post subject: Reply to: DFSORT query
Reply with quote

/REPEAT ON ...

... read the sort manuals about the use of HIT=NEXT

or search the forum for the same
Back to top
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Mon Apr 27, 2015 10:14 pm    Post subject:
Reply with quote

Hi,

I apologize for my delay in reply. Thanks to all for their valuable suggestion. I have gone through all the stmts given above... There is one small twist happen in my earlier given scenario. We were actually kept an eye on this issue.. for few days there was no problem and suddenly for yesterday we saw the job went down and issue was little different this time our oncall support person found that there were X'0D' Hex char which is "Carriage Return" and this happened at the end of line (not for all the lines but few out of many). After discussing with few others I got to know this may be related to Carriage Return and Line Feed which is normal for any file gets created under Windows env.

We are still trying to find out why suddenly caused this issue and for temp solution I have written below code (excerpt of the main code)

Code:
SORT FIELDS=COPY                                             
 INREC IFTHEN=(WHEN=(1,1,FS,NE,NUM),OVERLAY=(1:C' '),HIT=NEXT),
  IFTHEN=(WHEN=(2,1,FS,NE,NUM),OVERLAY=(2:C' '),HIT=NEXT),     
  IFTHEN=(WHEN=(3,1,FS,NE,NUM),OVERLAY=(3:C' '),HIT=NEXT),     
  IFTHEN=(WHEN=(4,1,FS,NE,NUM),OVERLAY=(4:C' '),HIT=NEXT),     
  IFTHEN=(WHEN=(5,1,FS,NE,NUM),OVERLAY=(5:C' '),HIT=NEXT),     
  IFTHEN=(WHEN=(6,1,FS,NE,NUM),OVERLAY=(6:C' '),HIT=NEXT)


Anything apart from numeric value it will overwrite with space.

I thought to adopt the code example given by rinsio above. But in that case I had to mentioned all possible combinations (apart from normal A to B I had to include all other numerous junk values) which was not possible so I thought to opt the above way.. Not sure if there would be any other better way to handle through SORT..

Thanks
Subrata
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Mon Apr 27, 2015 10:27 pm    Post subject: Reply to: DFSORT query
Reply with quote

If you want to replace non-numeric characters with space, then that code should do.

Hopefully the Windows system is not sending you signed data.

Quite why you'd want to replace non-numeric values with space, I don't know. Space isn't numeric. Can't you drop the lines or bounce the file? There seem to be too many problems with the data. You, quickly, need to know of potential impacts if the entire data (including the "good" stuff) is wrong, and then down the scale from that.
Back to top
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Mon Apr 27, 2015 11:23 pm    Post subject:
Reply with quote

Hi Bill,

Thanks for the reply..

Quote:
Quite why you'd want to replace non-numeric values with space, I don't know. Space isn't numeric.


Below is excerpt of the Load JCL..

Code:
//SYSIN    DD  *                                               
LOAD DATA INDDN USPR99   LOG NO NOCOPYPEND RESUME NO  REPLACE   
 INTO TABLE XXXXX.<table>                             
 ( GKZ                                                         
        POSITION(00001:00008) DECIMAL EXTERNAL(8,0)             
 , PLZ                                                         
        POSITION(00009:00014) DECIMAL EXTERNAL(6,0)             
 , ORT                   


As DECIMAL EXTERNAL has been mentioned then I guess if I overwrite carriage return with space (say from 999X'0D' to 999<blank>) then it will load into DB2.. plz correct me if I am wrong

Thanks
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Mon Apr 27, 2015 11:55 pm    Post subject: Reply to: DFSORT query
Reply with quote

Well, that may initially work, or it may not. I suspect if you replace the low-order byte of an external-decimal you'll get a problem, somewhere, but I'm not going to guess where, because I wouldn't do that.

If it does not fail, perhaps likely for spaces other than the low-order bytes, whether it does what you think depends on if or how quickly the space becomes a zero.

If you are going to replace non-numeric by something, I'd go for zero. But I'd be really wary of just stuffing "incorrect" things into a data-base.

If the data is supposed to be numeric and meaningful, then clobbering any dodgy-looking data is going to give you "good", but wrong, data. I hope you can find it afterwards.

If it is not meaningful, just load it as is, as character data, so that it can be sorted out later.

We can't know enough about your system to do more than generalise, but I'd really not do what you're doing without a rock-solid audit-trail and sign-offs from your boss and the users.
Back to top
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Tue Apr 28, 2015 9:07 pm    Post subject:
Reply with quote

Hi Bill

I actually tried to overwrite that CR value to Space/Spaces and then tried to load the same into DB2 table.. and it is happening.

BUT I completely agree with you on the authenticity and integrity of the data that we are overwriting with spaces. I also thought the same what u had mentioned above. Yes I am checking with my client on the same... have explained in details through mail and waiting for reply.. lets see what they has to say..

Thanks for your comments
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts How to get the remainder and quotient... vnktrrd DFSORT/ICETOOL 2 Mon Oct 31, 2016 10:59 am
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts Change date (DD/MM/YY) in 2nd record ... uday kiran DFSORT/ICETOOL 12 Wed Sep 07, 2016 10:57 pm


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