SyncSort/SyncTooL to extract records based on Key

Hariharan BK · New User Joined: 11 Sep 2011 Posts: 14 Location: India

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 <---
----------------------------------------------------------------------

Pandora-Box · Posted: Tue Jun 04, 2013 2:51 pm

Hari,

I believe You have not yet mentioned the condition for which the group needs to be picked.

expat · Posted: Tue Jun 04, 2013 2:59 pm

Also, it may help to supply information as to which sort product you are using along with its release level.

Hariharan BK · New User Joined: 11 Sep 2011 Posts: 14 Location: India

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.

expat · Posted: Tue Jun 04, 2013 3:29 pm

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.

Pandora-Box · Posted: Tue Jun 04, 2013 3:35 pm

Hari,

You haven't shared the conditions for filtering the desired data/group

Hariharan BK · New User Joined: 11 Sep 2011 Posts: 14 Location: India

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

Pandora-Box · Posted: Tue Jun 04, 2013 3:51 pm

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

expat · Posted: Tue Jun 04, 2013 3:53 pm

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.

Hariharan BK · New User Joined: 11 Sep 2011 Posts: 14 Location: India

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

expat · Posted: Tue Jun 04, 2013 4:06 pm

Topic moved to the JCL forum, where SYNCSORT topics should be.

Arun Raj · Posted: Tue Jun 04, 2013 4:16 pm

Hariharan BK · New User Joined: 11 Sep 2011 Posts: 14 Location: India

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

Hariharan BK · New User Joined: 11 Sep 2011 Posts: 14 Location: India

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

Pandora-Box · Posted: Tue Jun 04, 2013 4:29 pm

If your version of sort product supports to do something similar to JNFCNTLn you could filter and sort based on JOINKEYS

Hariharan BK · New User Joined: 11 Sep 2011 Posts: 14 Location: India

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 !!

Hariharan BK · New User Joined: 11 Sep 2011 Posts: 14 Location: India

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..

Arun Raj · Posted: Tue Jun 04, 2013 4:43 pm

Are you saying that you have multiple values to be checked? Are these constants varying as well?

Hariharan BK · New User Joined: 11 Sep 2011 Posts: 14 Location: India

yes Arun. The constants will vary. I will have multiple values in the key field.

Arun Raj · Posted: Tue Jun 04, 2013 4:57 pm

Hariharan BK · New User Joined: 11 Sep 2011 Posts: 14 Location: India

Yes. this is the requirement Arun.
Yes they are sorted.

xknight · Posted: Tue Jun 04, 2013 5:30 pm

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.

Hariharan BK · New User Joined: 11 Sep 2011 Posts: 14 Location: India

no Naveen. They differ. they are dynamic....

Arun Raj · Posted: Wed Jun 05, 2013 11:49 am

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".

Hariharan BK · New User Joined: 11 Sep 2011 Posts: 14 Location: India

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

Thanks All..