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
 

 

Exclude, Select, Filter..

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
genesis786

Active User


Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London

PostPosted: Fri Mar 20, 2009 6:36 pm    Post subject: Exclude, Select, Filter..
Reply with quote

Hi,

I have a file like this:

Code:

000188001
000188002
000188003
000188004
000188005
000188006
000188007
000188008
000188009
000189001
000189002
000189003
000189004
000189005
000190000
000190001
000190002
000190003
000190004
000190005
000191000
000191001
000191002
000192000
000192001
000193000
000193001


first 6 chars are account, next 3 are sub header

I want to filter account/subheader records into an output which don't have a subheader '000'. i.e. output file will look like:

Code:

000188001
000188002
000188003
000188004
000188005
000188006
000188007
000188008
000188009
000189001
000189002
000189003
000189004
000189005


Thanks in advance.
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Mar 20, 2009 7:52 pm    Post subject:
Reply with quote

Here's a DFSORT job that will do what you asked for. I assumed your input file has RECFM=FB and LRECL=80, but the job can be changed appropriately for other attributes.

Code:

//S1    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file (FB/80)
//SORTOUT DD DSN=... output file (FB/80)
//SYSIN    DD    *
  INREC IFTHEN=(WHEN=INIT,                   
    OVERLAY=(81:SEQNUM,3,ZD,RESTART=(1,6))), 
   IFTHEN=(WHEN=GROUP,BEGIN=(81,3,ZD,EQ,1),   
       PUSH=(84:7,3))                         
  OUTFIL OMIT=(84,3,CH,EQ,C'000'),           
    BUILD=(1,80)                             
/*
Back to top
View user's profile Send private message
genesis786

Active User


Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London

PostPosted: Fri Mar 20, 2009 8:08 pm    Post subject:
Reply with quote

Hi Frank, thanks for your response. I am getting a SYNTAX error for this./

can u please take a look..

Code:

SYNCSORT FOR Z/OS  1.3.0.0R    U.S. PATENTS: 4210961, 5117495   (C)
                                                      z/OS   1.7.0 
SYNCSORT LICENSED FOR CPU SERIAL NUMBER xxxxx, MODEL 2096 S04       
SYSIN :                                                             
  INREC IFTHEN=(WHEN=INIT,                                         
    OVERLAY=(151:SEQNUM,3,ZD,RESTART=(2,6))),                       
   IFTHEN=(WHEN=GROUP,BEGIN=(151,3,ZD,EQ,1),                       
                *                                                   
       PUSH=(154:8,3))                                             
  OUTFIL OMIT=(154,3,CH,EQ,C'000'),                                 
    BUILD=(1,150)                                                   
WER268A  INREC STATEMENT   : SYNTAX ERROR                           
WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE                       
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Mar 20, 2009 8:16 pm    Post subject:
Reply with quote

The job works fine with DFSORT. The WER messages indicate you're using Syncsort, not DFSORT. I'm a DFSORT developer. DFSORT and Syncsort are competitive products. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on Syncsort.

I'm moving this thread to the JCL Forum.
Back to top
View user's profile Send private message
genesis786

Active User


Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London

PostPosted: Fri Mar 20, 2009 8:23 pm    Post subject:
Reply with quote

oh that's sad icon_sad.gif
Back to top
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 500
Location: USA

PostPosted: Fri Mar 20, 2009 10:19 pm    Post subject:
Reply with quote

genesis786,

Support for WHEN=GROUP was included in SyncSort for z/OS 1.3.2.0. The syntax error you are encountering is due to the fact that you are running an earlier release of the product. For information regarding upgrading to the current release, please contact me offline at alissa.margulies@syncsort.com (it's free!)
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Sat Mar 21, 2009 1:09 am    Post subject:
Reply with quote

genesis786,

Just a couple of questions regarding your requirement.

1. Does the subheader field for a particular account always start with either a '000' or a '001'? Can it start with some other value like '003'?
2. Are the subheader field values in your 'real' file, sequence numbers as shown in your sample data(001,002,003..)? Can it be out of sequence for an account ? Is the below scenario possible?
Code:
000188002
000188003
000188005
000188006
000188009
000188100
Back to top
View user's profile Send private message
genesis786

Active User


Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London

PostPosted: Sat Mar 21, 2009 1:17 pm    Post subject:
Reply with quote

Arun,

1 -- yes it can start with any value. but it will be in a sorted order always.

2 -- yes it can be out of sequence also.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Sat Mar 21, 2009 10:01 pm    Post subject:
Reply with quote

genesis786,

Here's a Syncsort job which I think would work for your version. I dont have access to mainframe and cant test this now. May be you can give this a try and let us know the results. Good luck icon_smile.gif
Code:
//STEP1  EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTIN   DD DSN=...  input file (FB/80)
//SORTOUT  DD DSN=&&T1,DISP=(,PASS)
//SYSIN    DD    *
  INREC OVERLAY=(81:SEQNUM,3,ZD,RESTART=(1,6))
  OUTFIL INCLUDE=(81,3,ZD,EQ,1,AND,7,3,CH,EQ,C'000'),           
    BUILD=(1,6)                             
/*
//STEP2  EXEC  PGM=SORT
//SYSOUT   DD  SYSOUT=*
//SORTJNF1 DD DSN=...  input file (FB/80)
//SORTJNF2 DD DSN=&&T1,DISP=(OLD,PASS)
//SORTOUT  DD DSN=...  output file (FB/80)
//SYSIN    DD    *
  JOINKEYS FILE=F1,FIELDS=(1,6,A)
  JOINKEYS FILE=F2,FIELDS=(1,6,A)
  JOIN UNPAIRED,F1,ONLY
  SORT FIELDS=COPY
/*
Back to top
View user's profile Send private message
genesis786

Active User


Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London

PostPosted: Mon Mar 23, 2009 6:10 pm    Post subject:
Reply with quote

Hi Arun, job's not giving the desired output.

1 - a couple of cc=16's in the beginning.

then i changed the first sort statement to :
(account number begins from position 2)

Code:

 //S2 EXEC PGM=SORT                                         
 //SYSPRINT DD SYSOUT=*                                     
 //SYSOUT DD SYSOUT=*                                       
 //SORTIN DD DSN=File1,DISP=SHR   (lrecl-150)
 //SORTOUT DD DSN=&&T1,DISP=(,PASS)                         
 //SYSIN   DD *                                             
  SORT FIELDS=COPY                                   
  INREC FIELDS=(1,13,14:SEQNUM,3,ZD,RESTART=(2,6))   
  INCLUDE COND=(14,3,ZD,EQ,1,AND,8,3,CH,EQ,C'000')   
  OUTREC FIELDS=(2,6)                               


and second sort :

Code:

//S2 EXEC PGM=SORT                                           
//SYSPRINT DD SYSOUT=*                                       
//SYSOUT DD SYSOUT=*                                         
//SORTJNF1 DD DSN=File1,DISP=SHR   
//SORTJNF2 DD DSN=&&T1,DISP=(OLD,PASS)                       
//SORTOUT DD DSN=File2,                         
// DISP=(NEW,KEEP,KEEP),                                     
// DCB=(RECFM=FB,LRECL=150,BLKSIZE=0),                       
// SPACE=(TRK,(50,10),RLSE)                                   
//SYSIN   DD *                                               
  JOINKEYS FILE=F1,FIELDS(2,6,A)
  JOINKEYS FILE=F2,FIELDS(1,6,A)
  JOIN UNPAIRED,F1,ONLY         
  SORT FIELDS=COPY               


but in the output file -- showing 000 records as well.. icon_sad.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Mar 23, 2009 9:36 pm    Post subject:
Reply with quote

Hello,

Is your data fixed or variable length?
Back to top
View user's profile Send private message
genesis786

Active User


Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London

PostPosted: Mon Mar 23, 2009 9:59 pm    Post subject:
Reply with quote

hi dsch.. it's fixed..
Back to top
View user's profile Send private message
genesis786

Active User


Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London

PostPosted: Mon Mar 23, 2009 10:13 pm    Post subject:
Reply with quote

Hi all, i used the following code. and it worked.

thanks icon_smile.gif

Code:

//S2 EXEC PGM=SORT                                         
//SYSPRINT DD SYSOUT=*                                     
//SYSOUT DD SYSOUT=*                                       
//SORTIN DD DSN=File1,DISP=SHR   
//SORTOUT DD DSN=&&T1,DISP=(,PASS)                         
//SYSIN   DD *                                             
  SORT FIELDS=COPY                                         
  INCLUDE COND=(8,3,CH,EQ,C'000')                           
  OUTREC FIELDS=(2,6)                                       
/*                                                         
//S2 EXEC PGM=SORT                                         
//SYSPRINT DD SYSOUT=*                                     
//SYSOUT DD SYSOUT=*                                       
//SORTJNF1 DD DSN=File1,DISP=SHR 
//SORTJNF2 DD DSN=&&T1,DISP=(OLD,PASS)                     
//SORTOUT DD DSN=File2,                       
// DISP=(NEW,KEEP,KEEP),                                   
// DCB=(RECFM=FB,LRECL=150,BLKSIZE=0), 
// SPACE=(TRK,(50,10),RLSE)             
//SYSIN   DD *                         
  JOINKEYS FILE=F1,FIELDS(2,6,A)       
  JOINKEYS FILE=F2,FIELDS(1,6,A)       
  JOIN UNPAIRED,F1,ONLY                 
  SORT FIELDS=COPY                     
/*                                     
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Tue Mar 24, 2009 8:04 pm    Post subject:
Reply with quote

Quote:
first 6 chars are account, next 3 are sub header
genesis786,

Glad to hear it's working for you. But your key position was from pos 1-6 as per your initial post. icon_rolleyes.gif

I was assuming an input file of LRECL=80 and I missed the SORT statment too! icon_lol.gif
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Select first 2 group data vice_versa DFSORT/ICETOOL 10 Mon Jun 13, 2016 2:21 pm
No new posts Query to select Null row on priority RahulG31 DB2 24 Thu Jan 21, 2016 5:45 pm
No new posts Can I use sort to select sample recor... Steve Ironmonger DFSORT/ICETOOL 3 Tue Nov 10, 2015 10:02 pm
No new posts Performance issue in the DB2 program ... Pradst57 DB2 19 Mon Oct 12, 2015 8:31 pm


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