|
View previous topic :: View next topic
|
| Author |
Message |
nkrishnamoorthy
New User
Joined: 28 May 2009
Posts: 6
Location: Bangalore
|
|
|
|
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 |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| 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 |
|
|
nkrishnamoorthy
New User
Joined: 28 May 2009
Posts: 6
Location: Bangalore
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| Try a substring search (SS) for blank in bytes 1 to 20 |
|
| Back to top |
|
|
Prakash J
New User
Joined: 23 May 2012
Posts: 14
Location: India
|
|
|
|
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.  |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| 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. |
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 |
|
|
nkrishnamoorthy
New User
Joined: 28 May 2009
Posts: 6
Location: Bangalore
|
|
|
|
| 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 |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
| 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 |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Hi Sqlcode,
Does 2 outfil statements make it two passes?
Just trying to understand |
|
| Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
| 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 |
|
|
Balaji Canabady
New User
Joined: 07 Dec 2011
Posts: 19
Location: India
|
|
|
|
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
ibmmainframes.com/about30467.html |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Thanks Sqlcode |
|
| Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
| 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 |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
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 |
|
|
|
|
