Filling Blank feilds with Values

In this Mainframe Forum - You can post your queries on DFSORT, ICETOOL , SyncSort & JCL Utilities

Moderators: Frank Yaeger, Moderator Group

Post Reply
raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Filling Blank feilds with Values

Post by raulravi » Wed Jun 08, 2011 9:43 am

Hi
I have a file 'A' with 100 records of customer details with Key as Customer-ID and date.
And i also have two more file 'B' & 'C' with same key (Customer-ID & Date). File B have dept details. And File C have email address details.

I want to put the Salary details from B and email address details in C into the respective feilds in file 'A'

Code: Select all

Code: 
FileA: 
CustID Date       Name   Salary   Dept               Email 
001    03191984  Raul               REal 
001    04191984  Raul               REal 
001    05191984  Raul               REal 
002    03191984  Messi              FCB 
002    05191984  Messi              FCB 
002    06191984  Messi              FCB 


Code: 
FileB: 
Cust-ID Date       Salary 
001    03191984    5000 
002    03191984    6000 


Code: 
FileC: 
Cust-ID Date        email 
001    03191984    raul@gmail.com 
002    03191984    messi@gmail.com 


Expected Output: 
Code: 
FileA: 
Cust-ID Date      Name    Salary   Dept               Email 
001    03191984  Raul      5000     REal               raul@gmail.com 
001    04191984  Raul      5000     REal               raul@gmail.com 
001    05191984  Raul      5000     REal               raul@gmail.com 
002    03191984  Messi     6000     FCB               messi@gmail.com 
002    05191984  Messi     6000     FCB               messi@gmail.com 
002    06191984  Messi     6000     FCB               messi@gmail.com 
Thanks,

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Wed Jun 08, 2011 7:57 pm

You say the key is the customer id AND date, but it appears to only be the customer id. For example, FileA has this record:

Code: Select all

001    04191984  Raul               REal
but the 04191984 is not present in FileB or FileC, yet you show the output as:

Code: Select all

001    04191984  Raul      5000     REal               raul@gmail.com
So the match here is only on the customer id, not the date. Please explain the rules for getting from input to output more accurately. Does the date really matter?

Give the RECFM and LRECL of each input file, and the expected output file.

Give the starting position, length and format of each field in each input file, and in the output file.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Post by raulravi » Thu Jun 09, 2011 11:18 am

Dear Frank,

Ya you are right , date does not matter here, i am sorry for the confusion.
Cust-Id is the Key.

1) File A contains all the Customer Details, except Salary & email feilds.
2) File B conatains Salary Details for a customer.
3) File c conatains email Details for a customer.

Rules:
1) Suppose if i have three records for a customer in File A. All the three records should be updated with the Salary and Email.

PLease let me know if still I am confusing .

Regards,
Raul

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Thu Jun 09, 2011 9:53 pm

I still need the following from you as asked previously:
Give the RECFM and LRECL of each input file, and the expected output file.

Give the starting position, length and format of each field in each input file, and in the output file.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Post by raulravi » Fri Jun 10, 2011 9:16 am

File A:

Code: Select all

       F1NUM1   F1NAME     F1SALARY   F1CLUB     F1DEPT     F1DATE  
       3/NUM    10/AN      10/AN      10/AN      10/AN      10/AN   
       (1-3)    (4-13)     (14-23)    (24-33)    (34-43)    (44-53) 
       1------- 2--------- 3--------- 4--------- 5--------- 6-------
000001        1 RAVI                  REAL                  19031984
000002        1 RAVI                  REAL                  19031985
000003        1 RAVI                  REAL                  19031986
000004        1 RAVI                  REAL                  19031987
000005        2 ARUN                  REAL                  19031987
000006        2 ARUN                  REAL                  19031988
000007        3 KIRAN                 FCB                   19031984
000008        3 KIRAN                 FCB                   19031985
000009        4 VIJAY                 FCB                   19031985
000010        4 VIJAY                 FCB                   19031985
000011        5 RAJA                  FCB                   19031985

Code: Select all

File B:
       F1NUM1   F1NAME     F1SALARY
       3/NUM    10/AN      10/AN   
       (1-3)    (4-13)     (14-23) 
       1------- 2--------- 3-------
****** ****************************
000001        1 RAVI       5000    
000002        3 KIRAN      6000    

Code: Select all

Output File C:
       F1NUM1   F1NAME     F1SALARY   F1CLUB     F1DEPT     F1DATE  
       3/NUM    10/AN      10/AN      10/AN      10/AN      10/AN   
       (1-3)    (4-13)     (14-23)    (24-33)    (34-43)    (44-53) 
       1------- 2--------- 3--------- 4--------- 5--------- 6-------
000001        1 RAVI       REAL       5000                  19031984
000002        1 RAVI       REAL       5000                  19031985
000003        1 RAVI       REAL       5000                  19031986
000004        1 RAVI       REAL       5000                  19031987
000005        2 ARUN       REAL                             19031987
000006        2 ARUN       REAL                             19031988
000007        3 KIRAN      FCB        6000                  19031984
000008        3 KIRAN      FCB        6000                  19031985
000009        4 VIJAY      FCB                              19031985
000010        4 VIJAY      FCB                              19031985
000011        5 RAJA       FCB                              19031985
File Details:
File A--- Record format . . . : FB,Record length . . . : 80
File B--- Record format . . . : FB,Record length . . . : 80
File C--- Record format . . . : FB,Record length . . . : 80

But these are my test files... In my original requirement, there ar a total of 4 files to be merged. and a toatl of nearly 25-30 feilds will be available. Then the Record format : FB,Record length . . . : more than 80.

Please let me know if iam missing any info.

I am trying to do this with Splice.. but getting Rc 12.

Code: Select all

//TTYASAS3 JOB ,CLASS=3,MSGCLASS=0,NOTIFY=&SYSUID        
//* RUN THROUGH JCLPREP ON 09/06/2011 AT 07:03:21 BY GONU
//*                                                      
//DFSORT   EXEC PGM=ICETOOL                              
//TOOLMSG  DD SYSOUT=*                                   
//DFSMSG   DD SYSOUT=*                                   
//IN1      DD DSN=TTYA.GONUGRK.JOINKEY3,DISP=SHR         
//IN2      DD DSN=TTYA.GONUGRK.JOINKEY4,DISP=SHR         
//T1       DD DSN=&&T1,DISP=(MOD,PASS),                  
//             SPACE=(CYL,(5,5)),UNIT=SYSDA              
//OUT      DD DSN=TTYA.GONUGRK.JOIN4,DISP=MOD            
//TOOLIN   DD *                                          
    COPY FROM(IN1) TO(T1) USING(CPY1)                    
    COPY FROM(IN2) TO(T1) USING(CPY2)                    
    SPLICE FROM(T1) TO(OUT) ON(1,13,CH,8,10,CH,18,10,CH)-
         WITHEACH WITH(14,10) WITH(24,10) WITH(34,10) -  
         WITH(44,10) USING (CPY3)                        
/*                                                       
//CPY1CNTL DD *                                          
    OUTREC FIELDS=(1:1,7,24:24,10,34:44,10,54:X)         
/*                                                       
//CPY2CNTL DD *                                          
    OUTREC FIELDS=(11:14,10,24:X)                        
/*                                                       
//CPY3CNTL DD *                                          
   OUTFIL FNAMES=OUT,OUTREC=(1,54)                       
/*                                                       
Regards,
Raul

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Fri Jun 10, 2011 9:35 pm

Your approach with SPLICE won't work and has syntax errors. But a DFSORT JOINKEYS job like the following would be a better choice anyway:

Code: Select all

//S1    EXEC  PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD *
1  RAVI                REAL                19031984
1  RAVI                REAL                19031985
1  RAVI                REAL                19031986
1  RAVI                REAL                19031987
2  ARUN                REAL                19031987
2  ARUN                REAL                19031988
3  KIRAN               FCB                 19031984
3  KIRAN               FCB                 19031985
4  VIJAY               FCB                 19031985
4  VIJAY               FCB                 19031985
5  RAJA                FCB                 19031985
//IN2 DD *
1  RAVI      5000
3  KIRAN     6000
//SORTOUT DD SYSOUT=*
//SYSIN   DD *
  JOINKEYS F1=IN1,FIELDS=(1,13,A)
  JOINKEYS F2=IN2,FIELDS=(1,13,A)
  JOIN UNPAIRED,F1
  REFORMAT FIELDS=(F1:1,53,F2:14,10)
  OPTION COPY
  OUTREC BUILD=(1,13,14:24,10,24:54,10,44:44,10)
/*
SORTOUT would have:

Code: Select all

1  RAVI      REAL      5000                19031984            
1  RAVI      REAL      5000                19031985            
1  RAVI      REAL      5000                19031986            
1  RAVI      REAL      5000                19031987            
2  ARUN      REAL                          19031987            
2  ARUN      REAL                          19031988            
3  KIRAN     FCB       6000                19031984            
3  KIRAN     FCB       6000                19031985            
4  VIJAY     FCB                           19031985            
4  VIJAY     FCB                           19031985            
5  RAJA      FCB                           19031985            
For complete details on DFSORT's JOINKEYS, see:

https://www.ibm.com/support/docview.wss ... g3T7000174
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Post by raulravi » Wed Jun 15, 2011 9:34 am

Code: Select all

//SYSIN   DD * 
  JOINKEYS F1=IN1,FIELDS=(1,13,A) 
  JOINKEYS F2=IN2,FIELDS=(1,13,A) 
  JOIN UNPAIRED,F1 
  REFORMAT FIELDS=(F1:1,53,F2:14,10) 
  OPTION COPY 
  OUTREC BUILD=(1,13,14:24,10,24:54,10,44:44,10) 
/* 
Hi Frank can you plz explain me the above JOin keys actions ?

Regards,
Raul

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Wed Jun 15, 2011 9:54 pm

For complete details on JOINKEYS, see:

http://www.ibm.com/support/docview.wss? ... g3T7000174
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others