View previous topic :: View next topic
|
Author |
Message |
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 <---
---------------------------------------------------------------------- |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Hari,
I believe You have not yet mentioned the condition for which the group needs to be picked. |
|
Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8796 Location: Welsh Wales
|
|
|
|
Also, it may help to supply information as to which sort product you are using along with its release level. |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8796 Location: Welsh Wales
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Hari,
You haven't shared the conditions for filtering the desired data/group |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8796 Location: Welsh Wales
|
|
|
|
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 |
|
|
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 |
|
Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8796 Location: Welsh Wales
|
|
|
|
Topic moved to the JCL forum, where SYNCSORT topics should be. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
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 |
|
|
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
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 |
|
|
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 |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
If your version of sort product supports to do something similar to JNFCNTLn you could filter and sort based on JOINKEYS |
|
Back to top |
|
|
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 !! |
|
Back to top |
|
|
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.. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Are you saying that you have multiple values to be checked? Are these constants varying as well? |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
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 |
|
|
Hariharan BK
New User
Joined: 11 Sep 2011 Posts: 14 Location: India
|
|
|
|
Yes. this is the requirement Arun.
Yes they are sorted. |
|
Back to top |
|
|
xknight
Active User
Joined: 22 Jan 2008 Posts: 117 Location: Liberty city
|
|
|
|
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 |
|
|
Hariharan BK
New User
Joined: 11 Sep 2011 Posts: 14 Location: India
|
|
|
|
no Naveen. They differ. they are dynamic.... |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
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 |
|
|
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.. |
|
Back to top |
|
|
|