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

Find for account number in other file which is not a key
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
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
 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
 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
 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
 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
 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 Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Tue Sep 11, 2007 10:04 pm
 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
 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
 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
 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 Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Tue Sep 11, 2007 11:37 pm
 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
 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 Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Wed Sep 12, 2007 11:54 pm
 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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts FTP VB File from Mainframe retaining ... JCL & VSAM 1
No new posts Extract the file name from another fi... DFSORT/ICETOOL 6
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us