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
 

 

Find for account number in other file which is not a key

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

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Thu Aug 30, 2007 6:41 pm    Post subject: Find for account number in other file which is not a key
Reply with quote

Hi,
I have FileA: VB,Lrecl=474
Code:
REC-ID   AC-NO   EFF_DT   EXPIRE-DT   SC_NO
2/AN   8/AN   10/AN   10/AN   5/AN
(1-2)   (3-10)   (11-20)   (21-30)   (31-35)
UH   DR 20072   39      
FI   LEHEAD   TGC   S   
HD   10108A01   7/16/2007   12/31/9999   007B0
HD   10108A01   8/7/2007   12/31/9999   2337
HD   10108A01   3/16/2007   12/31/9999   21824
HD   20108A01   4/13/2007   12/31/9999   31L55
HD   30108A01   4/5/2006   12/31/9999   312G8
HD   40108A01 7/31/2007   12/31/9999   31607
HD   50108A01   2/7/2006   12/31/9999   31842
HD   10108A01   4/9/2007   12/31/9999   417P7


I need to work on only the detail records - having 'HD' in the first 2 bytes.

FileB: RECFM=FB,LRECL=272 (Shown only the fields of interest)
Code:
ACCT-1          ACCT-2          ACCT-3          ACCT-4   
8/AN            8/AN            8/AN            8/AN     
(113-120)R      (123-130)R      (133-140)R     (143-150)R
10108A01        64A08A08                                 
21J08G02        20108A01        21J08G02                 
43508A00                        30108A01                 
57808K47        7EC08J01                        40108A01


My Requirement:
1) I want to find the AC-NO from fileA - position 3-10 present in any of the ACCT-1 to ACCT-4 in FileB. If present I need the record from FileA to be written to output..

example, the account number 10108A01 is present in FileB. So I need the record in output. Whereas, the account number 50108A01 is not present, so I don't need that in the output file.

So the output file should be:LRECL=474,RECFM=VB (only detail)

Code:
REC-ID   AC-NO   EFF_DT   EXPIRE-DT   SC_NO
2/AN   8/AN   10/AN   10/AN   5/AN
(1-2)   (3-10)   (11-20)   (21-30)   (31-35)
HD   10108A01   7/16/2007   12/31/9999   007B0
HD   10108A01   8/7/2007   12/31/9999   2337
HD   10108A01   3/16/2007   12/31/9999   21824
HD   20108A01   4/13/2007   12/31/9999   31L55
HD   30108A01   4/5/2006   12/31/9999   312G8
HD   40108A01   7/31/2007   12/31/9999   31607
HD   10108A01   4/9/2007   12/31/9999   417P7



Since there is one to any of the 4 matching field, I am not sure how to use the splice operator.
Any pointers will be of more help.
Thanks,
Viji
Back to top
View user's profile Send private message

krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Thu Aug 30, 2007 7:59 pm    Post subject:
Reply with quote

vijikesavan
Use this ICETOOL -SPLICE solution for your case, The only difference between the o/p that you have shown and mine is the record
Code:
HD   10108A01   4/9/2007   12/31/9999   417P7
is at the last in ur o/p sample and its in sorted. Let me know if its needed in the manner you had shown!
Code:
//*******************************************************               
//STEP001  EXEC PGM=ICETOOL                                             
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//IN1      DD *                                                         
UH   DR 20072   39                                                     
FI   LEHEAD   TGC   S                                                   
HD   10108A01   7/16/2007   12/31/9999   007B0                         
HD   10108A01   8/7/2007   12/31/9999   2337                           
HD   10108A01   3/16/2007   12/31/9999   21824                         
HD   20108A01   4/13/2007   12/31/9999   31L55                         
HD   30108A01   4/5/2006   12/31/9999   312G8                           
HD   40108A01 7/31/2007   12/31/9999   31607                           
HD   50108A01   2/7/2006   12/31/9999   31842                           
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
HD   10108A01   4/9/2007   12/31/9999   417P7                           
/*                                                                     
//IN2      DD *                                                         
10108A01        64A08A08                                               
21J08G02        20108A01        21J08G02                               
43508A00                        30108A01                               
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
57808K47        7EC08J01                        40108A01               
/*                                                                     
//TMP1     DD DSN=&&TEMP1,DISP=(MOD,PASS),SPACE=(TRK,(5,5)),UNIT=SYSDA 
//BOTH     DD SYSOUT=*                                                 
//F1ONLY   DD SYSOUT=*                                                 
//TOOLIN   DD *                                                         
 COPY FROM(IN2)  TO(TMP1) USING(CP01)                                   
 COPY FROM(IN1)  TO(TMP1) USING(CP02)                                   
 SPLICE FROM(TMP1) TO(BOTH) ON(6,8,CH) WITH(82,1) WITH(1,80)-           
             WITHALL        USING(CP03) KEEPNODUPS                     
/*                                                                     
//CP01CNTL DD   *                                                       
  OUTFIL FNAMES=(TMP1),BUILD=(6:1,8,81:C'11',83:C'        ',/,         
               6:17,8,81:C'11',/,                                       
               6:33,8,81:C'11',/,                                       
               6:49,8,81:C'11')                                         
/*                                                                     
//CP02CNTL DD   *                                                       
  INCLUDE COND=(1,2,CH,EQ,C'HD')                                       
  OUTREC BUILD=(1,80,81:C'22')                                         
/*                                                                     
//CP03CNTL DD   *                                                       
  OUTFIL FNAMES=BOTH,INCLUDE=(81,2,CH,EQ,C'12'),                       
  BUILD=(1,80)                                                         
/*                                                                     


The o/p file contains
Code:
----+----1----+----2----+----3----+----4----+----5----+----6
HD   10108A01   7/16/2007   12/31/9999   007B0             
HD   10108A01   8/7/2007   12/31/9999   2337               
HD   10108A01   3/16/2007   12/31/9999   21824             
HD   10108A01   4/9/2007   12/31/9999   417P7               
HD   20108A01   4/13/2007   12/31/9999   31L55             
HD   30108A01   4/5/2006   12/31/9999   312G8               
HD   40108A01 7/31/2007   12/31/9999   31607               
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Thu Aug 30, 2007 8:02 pm    Post subject:
Reply with quote

sorry for my incorrect quote
Quote:
is at the last in ur o/p sample and its in sorted. Let me know if its needed in the manner you had shown!

it should be
Quote:
is at the last record in ur o/p sample and its in sorted order in my o/p. Let me know if its needed in the manner you had shown!
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Thu Aug 30, 2007 8:42 pm    Post subject: Reply to: Find for account number in other file which is not
Reply with quote

Hi,
Thanks. My output can be sorted like the way you showed. Its fine.
I didn't get a clear picture of your SPLICE command..But its too early for me to say.I will try it and let you know.

Thanks for helping me.
Appreciate your time.
Thanks,
Viji
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Tue Sep 11, 2007 8:04 pm    Post subject: Reply to: Find for account number in other file which is not
Reply with quote

Hi,
When I try with FB files, am fine. But one of my input file if VB of length 474. I am not able to proceed.

Can anyone pls help.
Thanks,
Viji
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Sep 11, 2007 10:04 pm    Post subject:
Reply with quote

Viji,

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1 (VB/474)
//IN2 DD DSN=...  input file2 (FB/272)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS),
// LRECL=476
//OUT DD DSN=...  output file (VB/474)
//TOOLIN   DD    *
COPY FROM(IN2) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(9,8,CH) VLENOVLY -
  WITHALL WITH(6,470) USING(CTL3)
/*
//CTL1CNTL DD *
  OUTFIL FNAMES=T1,FTOV,
    BUILD=(C'BB',5:113,8,/,C'BB',5:123,8,/,
      C'BB',5:133,8,/,C'BB',5:143,8)
/*
//CTL2CNTL DD *
  INCLUDE COND=(5,2,CH,EQ,C'HD')
  INREC BUILD=(1,4,C'VV',7:5)
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(5,2,CH,EQ,C'BV'),
    BUILD=(1,4,5:7)
/*
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Tue Sep 11, 2007 11:16 pm    Post subject: Reply to: Find for account number in other file which is not
Reply with quote

Hi Frank,
I tried your JCl and I am getting the following error:

Code:
ICE217A 0 476 BYTE VARIABLE RECORD IS LONGER THAN 16 BYTE MAXIMUM FOR T1


My Job ended with RC of 16.
I am posting the entire JCL, pls let me know what did I do wrong.
Code:
//STEP10R EXEC PGM=ICETOOL                                   
//TOOLMSG DD SYSOUT=*                                       
//DFSMSG DD SYSOUT=*                                         
//DFSPARM DD *                                               
  OPTION SOLRF                                               
/*                                                           
//*                                                         
//IN1 DD DISP=SHR,DSN=VK824G7.OTHR.CAS526P0.APDBEX22.FILEHLD1
//* VB 474                                                   
//IN2 DD DISP=SHR,DSN=VK824G7.SORT.PQR710P0.XREFAHBT.FILEB   
//*   FB 272                                                 
//T1  DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE),         
//          DISP=(MOD,PASS)                                 
//*                                                         
//OUT DD DISP=SHR,DSN=VK824G7.OTHR.CAS526P0.APDBEX22.OUTPUT 
//*  VB 474                                                 
//TOOLIN DD *                                             
  COPY FROM(IN2) USING(CTL1)                               
  COPY FROM(IN1) TO(T1) USING(CTL2)                       
  SPLICE  FROM(T1) TO(OUT) ON(9,8,CH) VLENOVLY -           
    WITHALL WITH(6,470) USING(CTL3)                       
/*                                                         
//*                                                       
//CTL1CNTL DD *                                           
  OUTFIL FNAMES=(T1),FTOV,                                 
          BUILD=(C'BB',5:113,8,/,                         
                 C'BB',5:123,8,/,                         
                 C'BB',5:133,8,/,                         
                 C'BB',5:143,8,/,                         
                 C'BB',5:153,8,/,                         
                 C'BB',5:163,8,/,                         
                 C'BB',5:173,8,/,                         
                 C'BB',5:183,8)                           
/*                                                         
//CTL2CNTL DD *                                 
  INCLUDE COND=(5,2,CH,EQ,C'HD')               
  INREC BUILD=(1,4,C'VV',7:5)                   
/*                                             
//CTL3CNTL DD *                                 
  OUTFIL FNAMES=OUT,                           
  INCLUDE COND=(5,2,EQ,C'BV'),BUILD=(1,4,5:7)   
/*                                             
//*   



The error message :
Code:
            COPY FROM(IN1) TO(T1) USING(CTL2)                                 
ICE606I 0 DFSORT CALL 0002 FOR COPY FROM IN1      TO T1       USING CTL2CNTL TERMINATED
ICE602I 0 OPERATION RETURN CODE:  16                                           

Appreciate your time and help.
Thanks,
Viji
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Tue Sep 11, 2007 11:29 pm    Post subject: Reply to: Find for account number in other file which is not
Reply with quote

pls ignore my prev post. I missed to specify the LRECL=496 for T1. I will try it now.
Sorry about it.
Thanks,
Viji
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Tue Sep 11, 2007 11:37 pm    Post subject: Reply to: Find for account number in other file which is not
Reply with quote

Yup.I got the desired results. Thanks Frank.

Thanks Everyone,
Viji
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Sep 11, 2007 11:37 pm    Post subject:
Reply with quote

Quote:
I missed to specify the LRECL=496 for T1.


I was just going to post to tell you that. I'm glad you found it yourself.
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Wed Sep 12, 2007 8:35 pm    Post subject: Reply to: Find for account number in other file which is not
Reply with quote

Hi Frank,
Not sure whether I can post this as a new topic, but I found this while running the above JCL.

Giving the following in the JCL , gave random results each time the JCL is run. I mean the output records differ randomly each time.

Code:

//DFSPARM DD *                                               
  OPTION SOLRF                                               
/*   


If I comment this out, then the results are fine. Does this have significance with VB files? can you please explain?

Thanks,
Viji
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Sep 12, 2007 11:54 pm    Post subject:
Reply with quote

That doesn't make any sense to me. If you specify LRECL=476 for T1 then SOLRF or NOSOLRF has no effect. SOLRF is the shipped default for DFSORT and should be used. I used SOLRF for my runs and I didn't see any random differences. You would have to provide more information (your complete job and examples of input and the output differences you're seeing) for me to make any sense of that. Feel free to send me the info offline (yaeger@us.ibm.com) if you want to pursue it.
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 Copy RECFM =VB TO FB file with RECL =... sahil41352 DFSORT/ICETOOL 3 Wed Dec 28, 2016 11:29 pm
No new posts find particular member name in PDS us... ravi243 CLIST & REXX 10 Mon Dec 19, 2016 6:44 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts How to find the first monday of the w... abdulrafi COBOL Programming 10 Fri Nov 25, 2016 3:24 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm


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