IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SyncSort/SyncTooL to extract records based on Key


IBM Mainframe Forums -> SYNCSORT
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Hariharan BK

New User


Joined: 11 Sep 2011
Posts: 14
Location: India

PostPosted: Tue Jun 04, 2013 2:42 pm
Reply with quote

Hi All,
Good Morning..

I am facing one challenge in extracting accounts from one of the input VB file based on 2 key identifiers -

The input file details are as follows:
Organization . . . : PS
Record format . . . : VB
Record length . . . : 6800
Block size . . . . : 27998

Input file data:
***************************** Top of Data ************
0001 GTFLFL411720130601 <---
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411720130601 <---
0001D GTFLFL <---
0001D GTFLFL <---
0001D GTFLFL <---
0001D GTFLFL <---
0002 GTFLFL900020130601
0002 GTFLFL900034235 R1 06002139190
0002 GTFLFL900034235 R1 06002139190
0002 GTFLFL900034235 R1 06002139190
..
..
..

----------------------------------------------------------------------
Requirement is -

I want to extract records based on the 11 digit value at pos 29 (06620758890).

Lets consider the value - 06620758890. Extraction based on this key can be done using INCLUDE COND statement.

But here we have one additional requirement as well -
While extracting this key value, we also need to extract the additional records available as header/trailer records before & after this key, with first 4 chars as '0001'. we need to extract these accounts as well.
These additional records are marked with arrow in the above sample data.

The first 4 chars wont be same all the time, it differs based on the every key field. So dynamicaly we need to include these additional records as well, when considering the key records as well.

Could someone please help me achieve this challenge and get the desired output.. Thanks !!

Expected Output file data:
***************************** Top of Data ************
0001 GTFLFL411720130601 <---
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411720130601 <---
0001D GTFLFL <---
0001D GTFLFL <---
0001D GTFLFL <---
0001D GTFLFL <---
----------------------------------------------------------------------
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


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

PostPosted: Tue Jun 04, 2013 2:51 pm
Reply with quote

Hari,

I believe You have not yet mentioned the condition for which the group needs to be picked.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8796
Location: Welsh Wales

PostPosted: Tue Jun 04, 2013 2:59 pm
Reply with quote

Also, it may help to supply information as to which sort product you are using along with its release level.
Back to top
View user's profile Send private message
Hariharan BK

New User


Joined: 11 Sep 2011
Posts: 14
Location: India

PostPosted: Tue Jun 04, 2013 3:25 pm
Reply with quote

hi Expat, Pandora,

Any SORT products which can help me to get the issue resolved would be great.

I think we can do this in 2 steps using ICETOOL/DFSORT.

Please correct me if i still dint provide the valid required info.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8796
Location: Welsh Wales

PostPosted: Tue Jun 04, 2013 3:29 pm
Reply with quote

The reason we wish to know the product that YOU are using is so that the responders do not waste their time in giving you a solution for another product which will not work.

So please provide the requested information.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


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

PostPosted: Tue Jun 04, 2013 3:35 pm
Reply with quote

Hari,

You haven't shared the conditions for filtering the desired data/group
Back to top
View user's profile Send private message
Hariharan BK

New User


Joined: 11 Sep 2011
Posts: 14
Location: India

PostPosted: Tue Jun 04, 2013 3:48 pm
Reply with quote

The products which are compatible in my system are SORT, SYNCSORT, DFDORT, ICETOOL.

Desired group for resolution would be ICETOOL or SYNCSORT or SORT.

Hope this helps !! Pls advise
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


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

PostPosted: Tue Jun 04, 2013 3:51 pm
Reply with quote

Hariharan BK,

How do you expect us to know which data should be picked from the input file?

We are expecting the conditon to filter out from the input file
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8796
Location: Welsh Wales

PostPosted: Tue Jun 04, 2013 3:53 pm
Reply with quote

So, you have both SYNCSORT and DFSORT installed at your site.
Is this what you are saying ?

How many more time has poor Pandora going to have to ask you for the required information. Please respond with ALL required information.
Psychic day was yesterday, and not everyone likes playing 20 questions.

Please run the step below and post the SYSOUT complete with message codes.
Code:

//SORTCOPY EXEC PGM=SORT   
//SYSOUT   DD SYSOUT=*     
//SORTIN   DD *             
DUMMY                       
/*                         
//SORTOUT  DD SYSOUT=*     
//SYSIN    DD *             
  OPTION COPY               
/*                         
//                         
Back to top
View user's profile Send private message
Hariharan BK

New User


Joined: 11 Sep 2011
Posts: 14
Location: India

PostPosted: Tue Jun 04, 2013 4:04 pm
Reply with quote

SYSOUT INFO:
------------------

SYNCSORT FOR Z/OS 1.4.1.0R U.S. PATENTS: 4210961, 5117495 (C) 2010 SYNCSO
**** XXXXXXXX**** z/OS 1.12.0
SYNCSORT LICENSED FOR CPU SERIAL NUMBER 4EE77, MODEL 2817 615 LICEN
SYSIN :
OPTION COPY
WER108I SORTIN : RECFM=FB ; LRECL= 80; BLKSIZE= 80
WER073I SORTIN : DSNAME=V523241.SMPLSRT.JOB41049.D0000101.?
WER110I SORTOUT : RECFM=FB ; LRECL= 80; BLKSIZE= 80
WER074I SORTOUT : DSNAME=V523241.SMPLSRT.JOB41049.D0000104.?
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
WER054I RCD IN 1, OUT 1
WER169I RELEASE 1.4 BATCH 0518 TPF LEVEL 1.0
WER052I END SYNCSORT - SMPLSRT,SORTCOPY,,DIAG=C000,42C4,E82C,0064,88D2,6CEB,2A
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8796
Location: Welsh Wales

PostPosted: Tue Jun 04, 2013 4:06 pm
Reply with quote

Topic moved to the JCL forum, where SYNCSORT topics should be.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Tue Jun 04, 2013 4:16 pm
Reply with quote

Hariharan BK wrote:

While extracting this key value, we also need to extract the additional records available as header/trailer records before & after this key, with first 4 chars as '0001'. we need to extract these accounts as well
Please mention the rules to be used to extract the additional records.

Quote:
I want to extract records based on the 11 digit value at pos 29 (06620758890).
Will this always be one single constant? Or do you have multiple values to be looked upon?
Back to top
View user's profile Send private message
Hariharan BK

New User


Joined: 11 Sep 2011
Posts: 14
Location: India

PostPosted: Tue Jun 04, 2013 4:22 pm
Reply with quote

I have used this simple SORT JCL - to extract the records with the desired key field

Code:

//SMPLSRT  JOB (S,JTZT,BA0572),CLASS=A,                               
//         MSGCLASS=T,REGION=0M,NOTIFY=&SYSUID                       
//SORTCOPY EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD DISP=SHR,DSN=NTF8CBS.CBSFGH05.BILLVERF.PKT.IHXB00BK.CPY
//SORTOUT DD DSN=NTF8CBS.CBSFGH05.BILLVERF.PKT.IHXB00BK.OUT,         
//            DISP=(NEW,CATLG),                                       
//            UNIT=SYSDA,SPACE=(CYL,(50,50),RLSE),                   
//            DCB=(*.SORTIN)                                         
//SYSIN    DD *                                                       
  OPTION COPY                                                         
  INCLUDE COND=(33,11,CH,EQ,C'06620758890')                           
/*                                                                   
//


This code extracts the records with the desired key.. but as per requirement i do like to extract the additional records as well (as mentioned in the first post)

INPUT FILE: NTF8CBS.CBSFGH05.BILLVERF.PKT.IHXB0

0001 GTFLFL411720130601 ***
0001 GTFLFL411734235 R1 06620758890 <---
0001 GTFLFL411734235 R1 06620758890 <---
0001 GTFLFL411734235 R1 06620758890 <---
0001 GTFLFL411734235 R1 06620758890 <---
0001 GTFLFL411720130601 ***
0001D GTFLFL ***
0001D GTFLFL ***
0001D GTFLFL ***
0001D GTFLFL ***
0002 GTFLFL900020130601
0002 GTFLFL900034235 RVBB06002139100
0002 GTFLFL900034235 RVBB06002139100
0002 GTFLFL900034235 RVBB06002139100

In this above input file data, we have got the arrowed records (<--- ) into the output file... we also require the starred ones (***) as well.

These arrowed records and the starred records share the same first 4 chars (0001).
I really got no idea about how to get the value 0001 from the arrowed records and extract the starred records as well using 0001. this 4 digit value changes as per the key values.

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

New User


Joined: 11 Sep 2011
Posts: 14
Location: India

PostPosted: Tue Jun 04, 2013 4:26 pm
Reply with quote

Hi Arun,

The is no static rule to be defined for extracting additional records.

Rules need to be driven dynamically based on the value available in the first 4 chars of the arrowed records ( arrowed records are easily extractable as mentioned in the above post)

My guess would be like -
The value in the first 4 char need to be stored in a temp variable and then can be used in INCLUDE COND for extraction. I have no idea how to store values temporarily.

Thanks
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


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

PostPosted: Tue Jun 04, 2013 4:29 pm
Reply with quote

If your version of sort product supports to do something similar to JNFCNTLn you could filter and sort based on JOINKEYS
Back to top
View user's profile Send private message
Hariharan BK

New User


Joined: 11 Sep 2011
Posts: 14
Location: India

PostPosted: Tue Jun 04, 2013 4:35 pm
Reply with quote

I have tried JOINKEYS using SYNCSORT earlier..

Thou i m not sure whether JNFCNTLn is a different one to JOINKEYS or the same.

Could you please help me with some old samples.. so i will check this out for compatability. Thanks Pandora !!
Back to top
View user's profile Send private message
Hariharan BK

New User


Joined: 11 Sep 2011
Posts: 14
Location: India

PostPosted: Tue Jun 04, 2013 4:38 pm
Reply with quote

Yes Arun.. For time being now, that part is single constant one...

Once that is working, we might need to have multiples values in include statement for multiple key record fetchings..
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Tue Jun 04, 2013 4:43 pm
Reply with quote

Are you saying that you have multiple values to be checked? Are these constants varying as well?
Back to top
View user's profile Send private message
Hariharan BK

New User


Joined: 11 Sep 2011
Posts: 14
Location: India

PostPosted: Tue Jun 04, 2013 4:50 pm
Reply with quote

yes Arun. The constants will vary. I will have multiple values in the key field.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Tue Jun 04, 2013 4:57 pm
Reply with quote

Pandora-Box wrote:
If your version of sort product supports to do something similar to JNFCNTLn you could filter and sort based on JOINKEYS
I am at an older version here. Not sure if 1.4 supports it!
Hariharan BK wrote:
yes Arun. The constants will vary. I will have multiple values in the key field.
So let's say you have a file with keys alone in it-FILEA. Let FILEB be your other input file which you showed earlier. You need to extract all the matching records from FILEB for all the keys present in FILEA. Also along with the extracted group of records, you need additional data with the same value at pos 1-4 (without considering the RDW) above and below the group until the data at 1-4 changes. Is this what you are trying? Is the data already sorted on 1-4 and the key field?
Back to top
View user's profile Send private message
Hariharan BK

New User


Joined: 11 Sep 2011
Posts: 14
Location: India

PostPosted: Tue Jun 04, 2013 5:09 pm
Reply with quote

Yes. this is the requirement Arun.
Yes they are sorted.
Back to top
View user's profile Send private message
xknight

Active User


Joined: 22 Jan 2008
Posts: 117
Location: Liberty city

PostPosted: Tue Jun 04, 2013 5:30 pm
Reply with quote

Hello Hari

In the given input, does the set of records has predictable count in between the header/trailer or will it be dynamic count.

For an instance,

the below set always have 4 records between the header & trailer.

Code:
0001 GTFLFL411720130601 ***
0001 GTFLFL411734235 R1 06620758890 <---
0001 GTFLFL411734235 R1 06620758890 <---
0001 GTFLFL411734235 R1 06620758890 <---
0001 GTFLFL411734235 R1 06620758890 <---
0001 GTFLFL411720130601 ***
Back to top
View user's profile Send private message
Hariharan BK

New User


Joined: 11 Sep 2011
Posts: 14
Location: India

PostPosted: Tue Jun 04, 2013 6:42 pm
Reply with quote

no Naveen. They differ. they are dynamic....
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Wed Jun 05, 2013 11:49 am
Reply with quote

Hariharan BK,

The below syncsort will give what you are trying. I have modified the input a bit just for clarity. I assumed no two "groups" will have the same value at pos 5,4. The first step will extract the unique group ids for which there is a match on 29,11. The next step is to extract all the records within each "group".
Code:
//STEP01   EXEC  PGM=SORT                                             
//SORTJNF1 DD DISP=SHR,DSN=Input key-file (FB/80)
//SORTJNF2 DD DISP=SHR,DSN=Input data-file (VB/6800)
//*                                                                   
//SYSOUT   DD SYSOUT=*                                                 
//SORTOUT  DD DSN=&&T1,DISP=(,PASS),UNIT=SYSDA,SPACE=(CYL,(5,5),RLSE) 
//SYSIN    DD  *                                                       
 JOINKEYS FILE=F1,FIELDS=(01,11,A)                                     
 JOINKEYS FILE=F2,FIELDS=(29,11,A)                                     
 REFORMAT FIELDS=(F2:5,4)                                             
 SORT FIELDS=(1,4,CH,A)                                               
 DUPKEYS FIELDS=NONE                                                   
/*                                                                     
//*                                                                   
//STEP02   EXEC  PGM=SORT                                             
//SORTJNF1 DD DSN=&&T1,DISP=(SHR,PASS)                                 
//SORTJNF2 DD DISP=SHR,DSN=Input data-file (VB/6800)                       
//*                                                                   
//SYSOUT   DD SYSOUT=*                                                 
//SORTOUT  DD DSN=Output data-file (VB/6800)
//SYSIN    DD  *                                       
 JOINKEYS FILE=F1,FIELDS=(01,04,A)                     
 JOINKEYS FILE=F2,FIELDS=(05,04,A)                     
 REFORMAT FIELDS=(F2:1,4,5)                           
 SORT FIELDS=COPY                                     
/*                                                     
//*   
Input key-file FB/80
Code:
----+----1---
06620758890 
06620758892
Input data-file VB/6800
Code:
----+----1----+----2----+----3----+--
0000 GTFLFL411720130601             
0000 GTFLFL411734235 R1 06620758880
0000 GTFLFL411734235 R1 06620758880
0000 GTFLFL411734235 R1 06620758880
0001 GTFLFL411720130601 <---       
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411720130601 <---       
0001D GTFLFL <---                   
0001D GTFLFL <---                   
0001D GTFLFL <---                   
0001D GTFLFL <---                   
0002 GTFLFL900020130601             
0002 GTFLFL900034235 R1 06002138891
0002 GTFLFL900034235 R1 06002138891
0002 GTFLFL900034235 R1 06002138891
0003 GTFLFL411720130601 <---       
0003 GTFLFL411734235 R1 06620758892
0003 GTFLFL411734235 R1 06620758892
0003 GTFLFL411734235 R1 06620758892
0003 GTFLFL411734235 R1 06620758892
0003 GTFLFL411734235 R1 06620758892
0003 GTFLFL411734235 R1 06620758892
0003 GTFLFL411734235 R1 06620758892
0003 GTFLFL411720130601 <---       
0004D GTFLFL <---                   
Output data-file VB/6800
Code:
----+----1----+----2----+----3----+--
0001 GTFLFL411720130601 <---       
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411734235 R1 06620758890
0001 GTFLFL411720130601 <---       
0001D GTFLFL <---                   
0001D GTFLFL <---                   
0001D GTFLFL <---                   
0001D GTFLFL <---                   
0003 GTFLFL411720130601 <---       
0003 GTFLFL411734235 R1 06620758892
0003 GTFLFL411734235 R1 06620758892
0003 GTFLFL411734235 R1 06620758892
0003 GTFLFL411734235 R1 06620758892
0003 GTFLFL411734235 R1 06620758892
0003 GTFLFL411734235 R1 06620758892
0003 GTFLFL411734235 R1 06620758892
0003 GTFLFL411720130601 <---       
Back to top
View user's profile Send private message
Hariharan BK

New User


Joined: 11 Sep 2011
Posts: 14
Location: India

PostPosted: Wed Jun 05, 2013 4:05 pm
Reply with quote

Great.. Thanks much Arun. Let me give a try on this.

Thanks All.. icon_biggrin.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> SYNCSORT Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts Syncsort help SYNCSORT 3
No new posts combine multiple unique records into ... DFSORT/ICETOOL 2
No new posts SYNCSORT-DB2 interface SYNCSORT 0
No new posts Sort based on the record type DFSORT/ICETOOL 1
No new posts To Omit records based n SORT condition DFSORT/ICETOOL 6
Search our Forums:

Back to Top