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
 

 

How to retrieve group of records using icetool

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

Active User


Joined: 26 Nov 2008
Posts: 125
Location: chennai

PostPosted: Thu Mar 04, 2010 9:40 pm    Post subject: How to retrieve group of records using icetool
Reply with quote

My input data file is a VB and it contians

2 digit state code (ex: TX,AR,NV etc..) /* Called as SCODE
3 digit company code (ex: 010,060,070..etc) /* Called as CCODE
3 digit Line code (ex: 088,032,059..etc) /* Called as LCODE
9 digit policy number (ex: 936173500,...etc) /* Called as PNUM
2 digit Record type (ex: 01,03,04,21...etc) note : every policy is a group or records starts with record type 01 only
3 digit Trans type (Ex: BNB,BRN,BEN) /* TTYPE

Code:
----+----1----+----2----+----3----
**********************************
    TX 010 088  936173500 01  BNB
    TX 010 088  936173500 03 
    TX 010 088  936173500 04
    TX 060 032  100000001 01  BNB
    TX 060 032  100000001 12
    TX 060 032  100000001 21
    TX 060 032  100000001 22
    TX 010 088  986171125 01  BNB
    TX 010 088  986171125 02
    TX 060 032  986171125 01  BNB
    TX 060 032  986171125 02 
    TX 060 032  986171125 04
    TX 060 032  986171125 06
    TX 060 032  100000002 01  BNB
    TX 060 032  100000002 22
    TX 060 032  100000002 26
    TX 060 032  100000002 32 
    TX 060 032  986171120 01  BRN
    TX 060 032  986171120 21
    TX 060 032  986171120 22
    TX 060 032  986171120 24
    TX 060 032  986171120 26
    TX 060 032  986171120 28
    AR 070 032  986171121 01  BEN
    AR 070 032  986171121 02
    AR 070 032  986171121 04
    AR 070 032  986171121 06
    NV 070 032  986171125 01  BRN
    NV 070 032  986171125 21
    NV 070 032  986171125 22
    NV 070 032  986171125 24
    NV 070 032  986171125 26
    NV 070 032  986171125 28
    AR 070 032  986171121 01  BEN
    AR 070 032  986171121 02
    AR 070 032  986171121 04
    AR 070 032  986171121 06
    AR 070 032  986171121 06


Code:
//Sysin
*Scode  CCode  Lcode  TTYPE   PCount   /* Note : PCOUNT represents number policies in the respective output file
 TX     010    088    BNB       1      /* out of 2 policies in input only 1 policy has to be copied respective output since pcount 1
 TX     060    032    BNB       2      /* out of 3 policies in input only 2 policy has to be copied respective output since pcount 2
 AR     070    032    BEN       1      /* out of 2 policies in input only 1 policy has to be copied respective output since pcount 1
 NV     070    060    BRN       2      /* out of 1 policies in input only 1 policy has to be copied respective output
--
--
/*

output
"userid".scode+ccode+Lcode.timestamp.TTYPE /* since here there are 4 input entires and hence 4 output files
Back to top
View user's profile Send private message

Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Mar 04, 2010 10:07 pm    Post subject: Re: How to retrieve group of records using icetool
Reply with quote

nkothako wrote:
output
"userid".scode+ccode+Lcode.timestamp.TTYPE /* since here there are 4 input entires and hence 4 output files


I have no clue as to how the split is to be done. Show us a sample output you want from the above input.

Explain the rules for getting from input to output. Explain how PCOUNT is determined.

You mention the RECFM as VB , do you want the output also to be VB?

What is the LRECL of the input and output files? What is the position and format of the fields scode+ccode+Lcode.timestamp.TTYPE ?
Back to top
View user's profile Send private message
nkothako

Active User


Joined: 26 Nov 2008
Posts: 125
Location: chennai

PostPosted: Thu Mar 04, 2010 11:52 pm    Post subject:
Reply with quote

Result should be
case 1: R9XXXX.TX010088.timestamp.BNB
Code:
      
TX 010 088  936173500 01  BNB
TX 010 088  936173500 03 
TX 010 088  936173500 04


Note : out of 2 policies in input file(936173500, 986171125) i need the first matching policy records i.e 936173500)
since pcount is 1

case 2: R9XXXX.TX060032.timestamp.BNB
Code:
      
TX 060 032  100000001 01  BNB
TX 060 032  100000001 12
TX 060 032  100000001 21
TX 060 032  100000001 22
TX 060 032  986171125 01  BNB
TX 060 032  986171125 02 
TX 060 032  986171125 04
TX 060 032  986171125 06


Note : out of 3 policies in input file(100000001, 986171125,100000002) i need the first 2 matching policy records i.e 100000001, 986171125) since
PCount is 2

similarly for all the cases
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Mar 05, 2010 3:39 am    Post subject: Reply to: How to retrieve group of records using icetool
Reply with quote

nkothako,

You still haven't answered all my questions. I think I am just too slow to follow your requirements as I still don't know what you are trying to accomplish.

1. How many Input files do you have? 1 or 2 ?

2. You keep mentioning the PCOUNT, Is that from a different file?

3. Is the number of output files also variable? Like do you want to create 100's of files for each unique key combo?
Back to top
View user's profile Send private message
nkothako

Active User


Joined: 26 Nov 2008
Posts: 125
Location: chennai

PostPosted: Fri Mar 05, 2010 6:06 am    Post subject:
Reply with quote

My input file will contains policy details. Each policy is a set of records and Number of records in each policy is not consitant but starting rec will be identified by RTYPE with 01.
Code:

    SCODE  PNUMBE    RTYPE  TTYPE
    =====  ======    ====   =====
----+----1----+----2----+----3----
**********************************
    TX     936173500    01     BNB   
    TX     936173500    03 
    TX     936173500    04              <<  policy1

    NV     100000001    01     BNB   
    NV     100000001    12
    NV     100000001    21
    NV     100000001    22              <<  Policy2

    TX     100000001    01     BNB     
    TX     100000001    12
    TX     100000001    21
    TX     100000001    22              <<  Policy3

    TX     100000001    01     BNB     
    TX     100000001    12              <<  Policy4

    TX     100000001    01     BRN     
    TX     100000001    12              <<  Policy5

    MN     100000002    01     BEN     
    MN     100000002    22              <<  Policy5

    ---
    ---
    ---
   

Code:
   
    //Sysin
   *Scode    TTYPE   PCount   
     TX        BNB       1       /*Case1
     NV        BNB       2       /*Case2
     TX        BRN       1       /*Case3
    /*

> Since we have 3 cases and hence need to create 3 output file where all the output file should have same attributes of input file.

> PCount is nothing but the number of matched policies need to be there in output file. ex : case1 the o/p 1 should have only 1 policy and o/p file2 should have 2 policies

Case1

The o/p file1 should conatin only 1 policy since PCOUNT is 1 and when scode is "TX" and TTYPE is "BNB" is matched in input file.
so (policy1, policy2 & policy4 matched but output file should contain only one policy since pcount is 1 and so all the corresponding policy records

result in o/p
Code:
   
    TX     936173500    01     BNB   
    TX     936173500    03 
    TX     936173500    04              <<  policy1
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: Fri Mar 05, 2010 6:27 am    Post subject:
Reply with quote

Hello,

And if there are 847 policies in the input? Or 9352? Or More?
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Mar 05, 2010 10:08 pm    Post subject:
Reply with quote

nkothako,

Please read all the questions once again and answer them.

You keep mentioning SYSIN and some instream data . DFSORT can only process DFSORT CONTROL Cards via SYSIN. I have no idea of what you mean by passing the data via SYSIN.
Back to top
View user's profile Send private message
nkothako

Active User


Joined: 26 Nov 2008
Posts: 125
Location: chennai

PostPosted: Sat Mar 06, 2010 2:38 am    Post subject:
Reply with quote

Quote:
You mention the RECFM as VB , do you want the output also to be VB?

Yes

Quote:
What is the LRECL of the input and output files?

The file attributes of input and output should be the same and LRECL=6004

Quote:
How many Input files do you have? 1 or 2 ? Explain the rules for getting from input to output.

Its 2 input file. One is data file and consider its I/p file1 and another one I mentioned in sysin as I/P file2. So please treat sysin as another input dataset. My input data file will contains policy details. Each policy is a set of records and number of records in each policy is not consitant but starting rec will be identified by RTYPE with 01.

Quote:
Explain how PCOUNT is determined.

PCount is nothing but the number of matched policies need to be there in output file.
ex : case1 the o/p 1 should have only 1 policy and o/p file2 should have 2 policies

Quote:
What is the position and format of the fields scode+ccode+Lcode.timestamp.TTYPE ?
This is the naming convention of output dataset "userid."scode""ccode""Lcode".timestamp.TTYPE

Quote:
Is the number of output files also variable? Like do you want to create 100's of files for each unique key combo?

Yes.
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: Sat Mar 06, 2010 2:45 am    Post subject:
Reply with quote

Hello,

Quote:
So please treat sysin as another input dataset.
Sorry, but the sort uses sysin for control statements. You cannot put data there.

Also, what if the input has several thousand policies? Do you intend to create a separate output file for every policy?
Back to top
View user's profile Send private message
nkothako

Active User


Joined: 26 Nov 2008
Posts: 125
Location: chennai

PostPosted: Sat Mar 06, 2010 2:52 am    Post subject:
Reply with quote

Quote:

Also, what if the input has several thousand policies? Do you intend to create a separate output file for every policy?

No. We will create the number of output files based on no of I/P file2 entries... In this case it contains only 3 records. In generally I/P file2 will not contains more than 8 entries per day. But the PCount will be huge
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: Sat Mar 06, 2010 3:44 am    Post subject:
Reply with quote

Hello,

Quote:
In generally I/P file2 will not contains more than 8 entries per day. But the PCount will be huge
Whew icon_smile.gif

The non-selected policies should not be a problem, just the number of files that have to be created. . . icon_wink.gif
Back to top
View user's profile Send private message
nkothako

Active User


Joined: 26 Nov 2008
Posts: 125
Location: chennai

PostPosted: Sat Mar 06, 2010 3:51 am    Post subject:
Reply with quote

yes
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Sat Mar 06, 2010 6:01 am    Post subject: Reply to: How to retrieve group of records using icetool
Reply with quote

nkothako,

The following DFSORT JCL will give you the desired results. Since you did not give me the positions of the fields involved , I had assumed them as follows

Scode at position 5 (inclusive of RDW)
Ccode at position 7 (inclusive of RDW)
Lcode at position 10 (inclusive of RDW)
PNUM at position 13 (inclusive of RDW)
RTYPE at position 22 (inclusive of RDW)
TTYPE at position 24 (inclusive of RDW)

for example

the records looks like this
Code:

RDW|5                     
    TX01008893617350001BNB


A brief explanation of the job.

The STEP0100 step takes in your pull file(sysin as you used) data records and creates include cards for OUTFIL files and create a temp file called C1. Since you said the max number of records in pull file is 8 , I used STOPAFT=8.

The STEP0200 step generates the sequencing of policy numbers using inrec/outrec. We also concatenate the file created from step0100 to split the records into various files.

Code:

//STEP0100 EXEC PGM=SORT                                     
//SYSOUT   DD SYSOUT=*                                       
//SORTIN   DD *                                             
TX     010    088    BNB       1                             
TX     060    032    BNB       2                             
AR     070    032    BEN       1                             
NV     070    060    BRN       2                             
//SORTOUT  DD DSN=&&C1,DISP=(,PASS),SPACE=(TRK,(1,1),RLSE)   
//SYSIN    DD *                                             
  OPTION COPY,STOPAFT=8                                     
  OUTFIL BUILD=(2:C'OUTFIL FNAMES=OUTPUT',SEQNUM,2,ZD,       
                  C',BUILD=(1,4,32),',/,                     
                2:C'INCLUDE=(5,11,CH,EQ,C''',               
                01,02,                   $ SCODE             
                08,03,                   $ CCODE             
                15,03,                   $ LCODE             
                22,03,                   $ TTYPE             
                C'''',                                       
                C',AND,16,8,ZD,LE,',                         
                25,08,UFF,M11,LENGTH=8,  $ TTYPE             
                C')',80:X)                                   
                                                             
//*                                                         
//STEP0200 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTIN   DD DSN=Your VB input file,DISP=SHR
//OUTPUT01 DD SYSOUT=*                                         
//OUTPUT02 DD SYSOUT=*                                         
//OUTPUT03 DD SYSOUT=*                                         
//OUTPUT04 DD SYSOUT=*
//OUTPUT05 DD SYSOUT=*
//OUTPUT06 DD SYSOUT=*
//OUTPUT07 DD SYSOUT=*
//OUTPUT08 DD SYSOUT=*
//SYSIN    DD *                                               
  INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,5,8,19X,5)),             
  IFTHEN=(WHEN=GROUP,BEGIN=(49,2,CH,EQ,C'01'),PUSH=(13:51,3)) 

  SORT FIELDS=(5,11,CH,A,40,9,CH,A),EQUALS                     

  OUTREC IFTHEN=(WHEN=INIT,                                   
  OVERLAY=(16:SEQNUM,8,ZD,RESTART=(5,11))),                   
  IFTHEN=(WHEN=(49,2,CH,EQ,C'01'),                             
  OVERLAY=(16:SEQNUM,8,ZD,RESTART=(5,11))),                   
  IFTHEN=(WHEN=NONE,                                           
  OVERLAY=(24:SEQNUM,8,ZD,RESTART=(5,11),                     
           16:16,8,ZD,SUB,24,8,ZD,M11,LENGTH=8))               

//         DD DSN=&&C1,DISP=SHR                               
//*                                                           
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 Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 1 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm


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