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
 

 

Extract selective records from input file using SORT/ICETOOL

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

New User


Joined: 28 May 2009
Posts: 6
Location: Bangalore

PostPosted: Sat Jun 02, 2012 2:16 pm    Post subject: Extract selective records from input file using SORT/ICETOOL
Reply with quote

Hi,

I have an input file (qsam, FB) with many records. My requirement is to selectively extract few records into an output file and the remaining records that didn't qualify for the extract criteria into another output file.

Input file:
----------
Code:

----+----1----+----2----+----3----+----4
10000120000001                MARK
10000220000002                SCOT
10000320000003XXXXXX          KRISHNA
10000420000004                WILLIAM
100005AAAAAAAAYYYYYY          JOHN
100006BBBBBBBB666666          KING


The criteria in extracting records into Output file-1 is when Input records have valid values till column 20. Therefore only record number 3, 5 & 6 should be written into this output file. All other remaining records in Input file (records 1, 2 & 4 with value spaces from column 15 though 20) should be written into Output file-2.

Output file-1:
-------------
Code:

10000320000003XXXXXX          KRISHNA
100005AAAAAAAAYYYYYY          JOHN
100006BBBBBBBB666666          KING


Output file-2:
-------------
Code:

10000120000001                MARK
10000220000002                SCOT
10000420000004                WILLIAM


I was wondering if it was feasible through SORT/ICETOOL or any other utility.

Could some one please guide me for a solution?
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1712
Location: UK

PostPosted: Sat Jun 02, 2012 3:34 pm    Post subject:
Reply with quote

It is simple enough in sort in two passes - just select on one conditon in the first pass and reverse the condition on the secon pass. There may be a way to avoid the second pass if sort allows you to write the discards to another file. Either sort can directly or ICETOOL can (I know - I've seen it on a forum. Search on discard).
Back to top
View user's profile Send private message
nkrishnamoorthy

New User


Joined: 28 May 2009
Posts: 6
Location: Bangalore

PostPosted: Sun Jun 03, 2012 8:11 am    Post subject: Reply to: Extract selective records from input file using SO
Reply with quote

Hi Nic Clouston,

Thanks for your reply.

Could you please help me out with the sort condition for selectively extracting the records into Output file-1 (refer example in my previous post) as that is the one I'm struggling to come with.

If you have observed the entries in the Input file, few records have valid values for the first 14 columns and few records with the first 20 columns. How do I write a condition to select either of these records?
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: Sun Jun 03, 2012 8:44 am    Post subject:
Reply with quote

Hello,

Which sort product is used on your system?

Read about INCLUDE and OMIT in the product documentation.

If you find somethng in the manual that is not clear, post what you found and your doubt. Someone will be able to clarify.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1712
Location: UK

PostPosted: Sun Jun 03, 2012 1:32 pm    Post subject:
Reply with quote

Try a substring search (SS) for blank in bytes 1 to 20
Back to top
View user's profile Send private message
Prakash J

New User


Joined: 23 May 2012
Posts: 14
Location: India

PostPosted: Sun Jun 03, 2012 3:10 pm    Post subject: Reply to: Extract selective records from input file using SO
Reply with quote

Hi Krishna,

Please check out this, if this helps.

//EXTRCT EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=... input file
//OUT1 DD DSN=... output file1
//OUT2 DD DSN=... output file2
//SYSIN DD *
OPTION COPY
OUTFIL FNAMES=OUT1,INCLUDE=(15,6,CH,NE,C' ')
OUTFIL FNAMES=OUT2,INCLUDE=(15,6,CH,EQ,C' ')
/*

NOTE:I haven't tested this as my system is not available. icon_sad.gif
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Sun Jun 03, 2012 4:40 pm    Post subject: Re: Reply to: Extract selective records from input file usin
Reply with quote

Prakash J wrote:
Hi Krishna,

Please check out this, if this helps.

//EXTRCT EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=... input file
//OUT1 DD DSN=... output file1
//OUT2 DD DSN=... output file2
//SYSIN DD *
OPTION COPY
OUTFIL FNAMES=OUT1,INCLUDE=(15,6,CH,NE,C' ')
OUTFIL FNAMES=OUT2,INCLUDE=(15,6,CH,EQ,C' ')
/*

NOTE:I haven't tested this as my system is not available. icon_sad.gif


You could use SS, as suggested by NicC, in the INCLUDE and use SAVE for the second OUTFIL to avoid the negated repeat of the test.
Back to top
View user's profile Send private message
nkrishnamoorthy

New User


Joined: 28 May 2009
Posts: 6
Location: Bangalore

PostPosted: Mon Jun 04, 2012 3:57 pm    Post subject: Reply to: Extract selective records from input file using SO
Reply with quote

Thanks for all your suggestions. I'll try these things out and get back with an update or if I need more info.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Mon Jun 04, 2012 7:22 pm    Post subject:
Reply with quote

nkrishnamoorthy wrote:
Hi,
...
The criteria in extracting records into Output file-1 is when Input records have valid values till column 20. Therefore only record number 3, 5 & 6 should be written into this output file. All other remaining records in Input file (records 1, 2 & 4 with value spaces from column 15 though 20) should be written into Output file-2.
...

Assuming, your definition of "valid values" as anything greater than spaces in the position 15 through 20th position, see if below works. You don't need separate include conditions, you can use SAVE to write all the discarded records to a separate output file.

Code:
//STEP0100 EXEC PGM=SORT                       
//SYSOUT   DD SYSOUT=*                         
//SORTIN   DD *                               
10000120000001                MARK             
10000220000002                SCOT             
10000320000003XXXXXX          KRISHNA         
10000420000004                WILLIAM         
100005AAAAAAAAYYYYYY          JOHN             
100006BBBBBBBB666666          KING             
//VALID    DD SYSOUT=*                         
//INVALID  DD SYSOUT=*                         
//SYSIN    DD *                               
  OPTION COPY                                 
  OUTFIL FNAMES=VALID,INCLUDE=(15,6,CH,GT,C' ')
  OUTFIL FNAMES=INVALID,SAVE                   
//*                                           
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Mon Jun 04, 2012 7:26 pm    Post subject:
Reply with quote

Hi Sqlcode,

Does 2 outfil statements make it two passes?

Just trying to understand
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Mon Jun 04, 2012 7:40 pm    Post subject:
Reply with quote

Pandora-Box wrote:
Hi Sqlcode,

Does 2 outfil statements make it two passes?

Just trying to understand

No.

It filters whatever is released from Exit35 (E35). For Joinkeys, it filters released records from E35 of the main task.

Thanks,
Back to top
View user's profile Send private message
Balaji Canabady

New User


Joined: 07 Dec 2011
Posts: 19
Location: India

PostPosted: Mon Jun 04, 2012 7:41 pm    Post subject:
Reply with quote

An icetool version of the same...

Code:

//TOOLIN   DD *                             
 COPY FROM(INP) TO(OUT1,OUT2) USING(CTL1)   
//CTL1CNTL DD *                             
 OUTFIL FNAMES=OUT1,INCLUDE=(15,6,CH,NE,C' ')
 OUTFIL FNAMES=OUT2,SAVE                     
/*


Frank's explanation on OUTFIL with INCLUDE
http://ibmmainframes.com/about30467.html
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Mon Jun 04, 2012 8:08 pm    Post subject:
Reply with quote

Thanks Sqlcode
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 04, 2012 10:16 pm    Post subject:
Reply with quote

sqlcode1 wrote:
nkrishnamoorthy wrote:
Hi,
...
The criteria in extracting records into Output file-1 is when Input records have valid values till column 20. Therefore only record number 3, 5 & 6 should be written into this output file. All other remaining records in Input file (records 1, 2 & 4 with value spaces from column 15 though 20) should be written into Output file-2.
...

Assuming, your definition of "valid values" as anything greater than spaces in the position 15 through 20th position, see if below works. You don't need separate include conditions, you can use SAVE to write all the discarded records to a separate output file.

Code:
//STEP0100 EXEC PGM=SORT                       
//SYSOUT   DD SYSOUT=*                         
//SORTIN   DD *                               
10000120000001                MARK             
10000220000002                SCOT             
10000320000003XXXXXX          KRISHNA         
10000420000004                WILLIAM         
100005AAAAAAAAYYYYYY          JOHN             
100006BBBBBBBB666666          KING             
//VALID    DD SYSOUT=*                         
//INVALID  DD SYSOUT=*                         
//SYSIN    DD *                               
  OPTION COPY                                 
  OUTFIL FNAMES=VALID,INCLUDE=(15,6,CH,GT,C' ')
  OUTFIL FNAMES=INVALID,SAVE                   
//*                                           



SQLCODE,

Did you realize that your condition will fail even if the 15th byte(just 1 byte) has a value other than space?

ex:
Code:
10000730000004P               SQLCODE   


As others mentioned you need to use SS format to check

nkrishnamoorthy,

Use the following DFSORT JCL which will give you the desired results.

Code:

//STEP0500 EXEC PGM=SORT                             
//SYSOUT   DD SYSOUT=*                               
//SORTIN   DD *                                     
10000120000001                MARK                   
10000220000002                SCOT                   
10000320000003XXXXXX          KRISHNA               
10000420000004                WILLIAM               
100005AAAAAAAAYYYYYY          JOHN                   
----+----1----+----2----+----3----+----4----+----5---
100006BBBBBBBB666666          KING                   
10000420000004P               SQLCODE               
//VALID    DD SYSOUT=*                               
//INVALID  DD SYSOUT=*                               
//SYSIN    DD *                                     
  OPTION COPY                                       
  OUTFIL FNAMES=INVALID,INCLUDE=(15,6,SS,EQ,C' ')   
  OUTFIL FNAMES=VALID,SAVE                           
//*
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Mon Jun 04, 2012 10:38 pm    Post subject:
Reply with quote

So why not 1,20,SS,EQ,C' ' ?

I wasn't sure what is the true definition of "value values" in terms of OP's requirements.
Quote:
Assuming, your definition of "valid values" as anything greater than spaces in the position 15 through 20th position


Thanks,
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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm


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