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
 

 

ICETOOL - JOINKEYS - VB input giving FB output

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

New User


Joined: 29 Jan 2014
Posts: 8
Location: USA

PostPosted: Thu Apr 03, 2014 1:14 am    Post subject: ICETOOL - JOINKEYS - VB input giving FB output
Reply with quote

Hi!
All the input files to the ICETOOL process below are VB.
The step executes and gives me the expected records in each of the four output files except the ouput files were created as FB.
I would like to keep the format as VB.
Anyone know what I'm missing or what I need to do differently?
Thanks


Code:
//SELECT   EXEC PGM=ICETOOL                     
//SYSOUT   DD SYSOUT=*                         
//SYSPRINT DD SYSOUT=*                         
//LOANWRK  DD DSN=&OHLQ..LOANWRK.VB,DISP=SHR   
//FOREA    DD DSN=&OHLQ..A,DISP=SHR             
//FORER24  DD DSN=&OHLQ..R24,DISP=SHR           
//FORES05  DD DSN=&OHLQ..S05,DISP=SHR           
//FORES10  DD DSN=&OHLQ..S10,DISP=SHR           
//OUTA     DD SYSOUT=*                         
//OUTR24   DD SYSOUT=*                         
//OUTS05   DD SYSOUT=*                         
//OUTS10   DD SYSOUT=*                         
//TOOLMSG  DD SYSOUT=*                         
//DFSMSG   DD SYSOUT=*                         
//TOOLIN   DD *                                 
  COPY   FROM(FOREA)   TO(OUTA)    USING(OT1A) 
  COPY   FROM(FORER24) TO(OUTR24)  USING(OT2A) 
  COPY   FROM(FORES05) TO(OUTS05)  USING(OT3A) 
  COPY   FROM(FORES10) TO(OUTS10)  USING(OT4A) 
/*                                                       
//OT1ACNTL DD *                                         
   JOINKEYS F1=LOANWRK,FIELDS=(5,7,A)                   
   JOINKEYS F2=FOREA,FIELDS=(13,7,A)                     
   REFORMAT FIELDS=(F2:1,4,13,7,5,4000)                 
/*                                                       
//OT2ACNTL DD *                                         
   JOINKEYS F1=LOANWRK,FIELDS=(5,7,A)                   
   JOINKEYS F2=FORER24,FIELDS=(15,7,A)                   
   OUTFIL FNAMES=OUTR24,INCLUDE=(29,2,BI,EQ,X'0001',OR, 
                              29,2,BI,EQ,X'0006',OR,     
                              29,2,BI,EQ,X'0014',OR,     
                              29,2,BI,EQ,X'0015',OR,     
                              29,2,BI,EQ,X'0016',OR,     
                              29,2,BI,EQ,X'0019')       
   REFORMAT FIELDS=(F2:1,4,15,7,5,4000)                 
/*                                                       
//OT3ACNTL DD *                           
   JOINKEYS F1=LOANWRK,FIELDS=(5,7,A)     
   JOINKEYS F2=FORES05,FIELDS=(15,7,A)     
   REFORMAT FIELDS=(F2:1,4,15,7,5,4000)   
/*                                         
//*                                       
//OT4ACNTL DD *                           
   JOINKEYS F1=LOANWRK,FIELDS=(5,7,A)     
   JOINKEYS F2=FORES10,FIELDS=(15,7,A)     
   REFORMAT FIELDS=(F2:1,4,15,7,5,4000)   
/*                                         
//*     


FROM DFSMSG:

Code:
ICE084I 0 BSAM ACCESS METHOD USED FOR OUTS10                     
ICE751I 1 EF-BASE   F0-BASE   E8-K96411                           
ICE421I 0 JOINED RECORDS: COUNT=3                                 
ICE090I 0 OUTPUT LRECL = 4011, BLKSIZE = 4011, TYPE = FB         
ICE055I 0 INSERT 3, DELETE 0                                     
ICE054I 0 RECORDS - IN: 0, OUT: 3                                 
ICE052I 0 END OF DFSORT


Code'd
Back to top
View user's profile Send private message

Skolusu

Senior Member


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

PostPosted: Thu Apr 03, 2014 1:38 am    Post subject:
Reply with quote

John W Perry,

You need to have a tail to retain the VB format ie. If the REFORMAT statement defines a position without a length (p without m) field, each joined record will be variable-length (TYPE=V).

That takes care of your original question, but your JCL is a mess and can be optimized. Please answer these questions

1. What is the LRECL of LOANWRK? In the output file you just need to move the value at position 15 next to RDW?

2. What is the LRECL and RECFM of FOREA, FORER24,FORES05 and FORES10?

3. Except for FOREA file all the other files have the key at position 15 which can be used to concatenate all of them into a single file (assuming they are VB files)
Back to top
View user's profile Send private message
John W Perry

New User


Joined: 29 Jan 2014
Posts: 8
Location: USA

PostPosted: Thu Apr 03, 2014 6:43 am    Post subject:
Reply with quote

Thanks Kolusu,

Updated JCL below.
All output files are now OK with VB 4011.

Answer to your questions:

1. LOANWRK is VB LRECL 4004
2. FOREA, FORER24,FORES05 and FORES10 are also VB 4004
3. I concatenated FORS05 & FORS10 under the same DD. FORER24 has the same key (15,7) but has some selection criteria so I kept this in a separate copy.

Question:
LOANWRK could contain 3 million records, will LOANWRK be processed three times in the step - once for each copy?

My goal is to end up with one file containing LOANWRK, OUTA, OUTR24 & OUTSXX merged/sorted by 5,7. I intended to add another SORT step to do this and expect to have 7 million records in the final file.

If you see any oportunity to optimize the process further please let me know.

Thanks again


Code:
//SELECT   EXEC PGM=ICETOOL                     
//SYSOUT   DD SYSOUT=*                         
//SYSPRINT DD SYSOUT=*                         
//LOANWRK  DD DSN=&OHLQ..LOANWRK.VB,DISP=SHR   
//FOREA    DD DSN=&OHLQ..A,DISP=SHR             
//FORER24  DD DSN=&OHLQ..R24,DISP=SHR           
//FORESXX  DD DSN=&OHLQ..S05,DISP=SHR           
//         DD DSN=&OHLQ..S10,DISP=SHR           
//OUTA     DD DSN=&OHLQ..OUTA,                 
//            DISP=(NEW,CATLG),                 
//            SPACE=(CYL,(1,1),RLSE)           
//OUTR24   DD DSN=&OHLQ..OUTR24,               
//            DISP=(NEW,CATLG),                 
//            SPACE=(CYL,(1,1),RLSE)           
//OUTSXX   DD DSN=&OHLQ..OUTSXX,               
//            DISP=(NEW,CATLG),                 
//            SPACE=(CYL,(1,1),RLSE)           
//TOOLMSG  DD SYSOUT=*                                       
//DFSMSG   DD SYSOUT=*                                       
//TOOLIN   DD *                                             
  COPY   FROM(FOREA)   TO(OUTA)    USING(OT1A)               
  COPY   FROM(FORER24) TO(OUTR24)  USING(OT2A)               
  COPY   FROM(FORESXX) TO(OUTSXX)  USING(OT3A)               
/*                                                           
//OT1ACNTL DD *                                             
   JOINKEYS F1=LOANWRK,FIELDS=(5,7,A)                       
   JOINKEYS F2=FOREA,FIELDS=(13,7,A)                         
   REFORMAT FIELDS=(F2:1,4,13,7,5)                           
/*                                                           
//OT2ACNTL DD *                                             
   JOINKEYS F1=LOANWRK,FIELDS=(5,7,A)                       
   JOINKEYS F2=FORER24,FIELDS=(15,7,A)                       
   OUTFIL FNAMES=OUTR24,INCLUDE=(29,2,BI,EQ,X'0001',OR,     
                              29,2,BI,EQ,X'0006',OR,         
                              29,2,BI,EQ,X'0014',OR,         
                              29,2,BI,EQ,X'0015',OR,       
                              29,2,BI,EQ,X'0016',OR,       
                              29,2,BI,EQ,X'0019')           
   REFORMAT FIELDS=(F2:1,4,15,7,5)                         
/*                                                         
//*                                                         
//OT3ACNTL DD *                                             
   JOINKEYS F1=LOANWRK,FIELDS=(5,7,A)                       
   JOINKEYS F2=FORESXX,FIELDS=(15,7,A)                     
   REFORMAT FIELDS=(F2:1,4,15,7,5)                         
/*


Code'd
Back to top
View user's profile Send private message
John W Perry

New User


Joined: 29 Jan 2014
Posts: 8
Location: USA

PostPosted: Thu Apr 03, 2014 10:30 pm    Post subject:
Reply with quote

The process is working OK:

OUTA contains a subset FOREA
OUTR24 contains a subset of FORER24
OUTSXX contains a subset of FORES05 & FORES10.

LOANWRK records are not in any output files. This file was just used as a "finder" file to create the subsets of the other files.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7240

PostPosted: Fri Apr 04, 2014 1:06 am    Post subject: Reply to: ICETOOL - JOINKEYS/REFORMAT - VB input giving FB o
Reply with quote

You are sorting LOANWRK three times. If not originally in order, you have data in order after the first sort.

If you're not using all the LOANWRK records, just the key, you could extract the keys from LOANWRK to an FB. Even if only using LOANWRK once, you can cut down the records to just the key, even if not sorting.

If nothing to identify source file when concatenating, it is possible to make something, a dummy "header" for each different file.

Are you other input files in key order, or do they require sorting for the JOINKEYS?

You wouldn't need to SORT your outputs, a simple MERGE would do, as they will certainly by that time be in key order.

Your INCLUDE as it stands is wasteful, because you are doing it on OUTFIL, rather than on input.

So, as a starting point, are the other files in key order already?
Back to top
View user's profile Send private message
John W Perry

New User


Joined: 29 Jan 2014
Posts: 8
Location: USA

PostPosted: Fri Apr 04, 2014 1:18 am    Post subject:
Reply with quote

Thanks for the reply Bill,

Yes, all the input files are in loan number order (7 bytes) prior to the ICETOOL step.

Thanks for the advice on the INCLUDE - I'll move it to INFILE processing.

And on the LOANWRK, I'll strip just the loan number from the input record to create a 7 byte file for input to ICEMAN.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7240

PostPosted: Fri Apr 04, 2014 1:44 am    Post subject: Reply to: ICETOOL - JOINKEYS/REFORMAT - VB input giving FB o
Reply with quote

If all the files are in order for the match already, specify SORTED,NOSEQCK on the JOINKEYS statements.

Step 1

Use JOIN UNPAIRED,F1

REFORMAT FIELDS=(F1:1,4,?,F1:5,7,F2,5)

One OUTFIL takes all the Bs, a second OUTFIL takes everything, just BUILD the key and use VTOF. On your first OUTFIL, BUILD=(1,4,6) should get the key pre-pending your reocrds.

Use the second OUTFIL as input to the following steps.

Use INCLUDE on JNF2CNTL where you need it.

Seperate step for each process.

Final step to MERGE your files.

Unless there is a particular need to use ICETOOL (for functionality it provides) there is not much point in jamming everything together. If your third COPY bombs, you need to change the control cards before re-submitting, or waste all the time by doing it again.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri Apr 04, 2014 2:36 am    Post subject: Reply to: ICETOOL - JOINKEYS/REFORMAT - VB input giving FB o
Reply with quote

John w perry,

It is quite unnecessary to read the Loanwrk file to get the matched records. Here is a JCL which will give you the desired results reading the loanwrk file just once and the output is also sorted on your key, so you don't need another step.

I assumed that you don't have keys with spaces in loanwrk file. But if you do have spaces as keys in the loanwrk and want to retain them then it is an easy fix.
Code:

//*********************************************************************
//* CREATE A HEADER WITH $ SYMBOL TO USE AS FILE IDENTIFIER          **
//*********************************************************************
//STEP0100 EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD *                                                       
//SORTOUT  DD DSN=&&HDR,DISP=(,PASS),SPACE=(TRK,(1,0))                 
//SYSIN    DD *                                                       
  OPTION COPY                                                         
  OUTFIL REMOVECC,FTOV,HEADER1=(7'$')                                 
//*                                                                   
//*********************************************************************
//* CONCATENATE FILE HEADER WITH FOREA, FORE24, FORES05 AND FORES10  **
//* FILE AND MATCH WITH LOANWRK FILE TO EXTRACT THE KEYS             **
//*********************************************************************
//STEP0200 EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//FORALLF  DD DISP=SHR,DSN=&&HDR,VOL=REF=*.STEP0100.SORTOUT           
//         DD DISP=SHR,DSN=&OHLQ..A
//         DD DISP=SHR,DSN=&&HDR,VOL=REF=*.STEP0100.SORTOUT           
//         DD DISP=SHR,DSN=&OHLQ..R24
//         DD DISP=SHR,DSN=&&HDR,VOL=REF=*.STEP0100.SORTOUT           
//         DD DISP=SHR,DSN=&OHLQ..S05
//         DD DISP=SHR,DSN=&&HDR,VOL=REF=*.STEP0100.SORTOUT           
//         DD DISP=SHR,DSN=&OHLQ..S10
//*                                       
//LOANWRK  DD DISP=SHR,DSN=&OHLQ..LOANWRK.VB
//SORTOUT  DD SYSOUT=*                                                 
//SYSIN    DD *                                                       
  OPTION COPY                                                         
  JOINKEYS F1=FORALLF,FIELDS=(6,7,A)                                   
  JOINKEYS F2=LOANWRK,FIELDS=(5,7,A)                                   
  REFORMAT FIELDS=(F1:1,4,6)                                           
//*                                                                   
//JNF1CNTL DD *                                                   
  OPTION DYNALLOC=(,16)                                           
  INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,8X,5)),                     
  IFTHEN=(WHEN=GROUP,BEGIN=(13,7,CH,EQ,C'$$$$$$$'),PUSH=(5:ID=1)),
  IFTHEN=(WHEN=(5,1,ZD,EQ,1),OVERLAY=(6:21,7)),                   
  IFTHEN=(WHEN=(5,1,ZD,EQ,2,AND,                                 
               (37,2,BI,EQ,X'0001',OR,                           
                37,2,BI,EQ,X'0006',OR,                           
                37,2,BI,EQ,X'0014',OR,                           
                37,2,BI,EQ,X'0015',OR,                           
                37,2,BI,EQ,X'0016',OR,                           
                37,2,BI,EQ,X'0019')),OVERLAY=(6:23,7)),           
  IFTHEN=(WHEN=(5,1,ZD,GT,2),OVERLAY=(6:23,7))                   
//*       
//JNF2CNTL DD *       
  OPTION DYNALLOC=(,08)
  OMIT COND=(5,7,CH,EQ,C' ')
  INREC BUILD=(1,11)                                               
//*
Back to top
View user's profile Send private message
John W Perry

New User


Joined: 29 Jan 2014
Posts: 8
Location: USA

PostPosted: Fri Apr 04, 2014 8:06 am    Post subject:
Reply with quote

Awesome,

I made one change - 37,2,BI,EQ to 30,2,BI,EQ - and got the same results as my process which used multiple COPY steps. Your solution reducued CPU time & other resources by 50%.

In reality, I will be selecting records from 13 different files with a total of six different key positions and not just the four files I am currently testing with, so your solution will result in even greater resource savings.

FYI:
The seven byte key is PIC 9(13) comp-3 - so it will not contain spaces.
I will also have to write all the records in loanwrk to the output file.

Many Thanks
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7240

PostPosted: Fri Apr 04, 2014 1:58 pm    Post subject: Reply to: ICETOOL - JOINKEYS/REFORMAT - VB input giving FB o
Reply with quote

Did you remember SORTED,NOSEQCK for LOANWRK?

I've also shown you how to include the LOANWRK in the output, same can be applied here.

It is alway a good idea to inform us of the full requirement. Solutons can change.

I'd suggest a slight change since you have 13 files. In the step to generate the dummy headers, I'd sequence then 01-13. This can be done with REPEAT on OUTFIL to get multiple records, then BUILD with the sequence number in the final two of the seven.

Then instead of using the ID, use the value on the record in the PUSH. It will make the code more stable if files are removed later, you won't have to "resequence" the code instead.

Saves a byte in the records as well :-) Two bytes, as you'd need a two-digit ID.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri Apr 04, 2014 11:11 pm    Post subject:
Reply with quote

John W Perry wrote:
Awesome,

I made one change - 37,2,BI,EQ to 30,2,BI,EQ - and got the same results as my process which used multiple COPY steps.


I have to ask as to why you changed the position. My solution is shifting the data contents by 8 bytes and that is the reason as to why I changed the check from position 29 to position 37. But now if you changed it back to 30 then, it would mean that you are now Validating data at position 22 instead of 29. Is that true?



John W Perry wrote:

In reality, I will be selecting records from 13 different files with a total of six different key positions and not just the four files I am currently testing with, so your solution will result in even greater resource savings.


I am guessing that you are fully aware of the changes that needs to be done. The file identifier is now increased 2 bytes and everything gets shifted by another byte. Make sure you change all the places where file identifier is being used.

John W Perry wrote:

The seven byte key is PIC 9(13) comp-3 - so it will not contain spaces.
I will also have to write all the records in loanwrk to the output file.


Remember that a PD field can have C or F as positive sign and D or B as negative sign. So if you have a mixed bag of keys with C and F then you need to normalize the keys before you match as Joinkeys does the matching in binary format
Back to top
View user's profile Send private message
John W Perry

New User


Joined: 29 Jan 2014
Posts: 8
Location: USA

PostPosted: Sat Apr 05, 2014 12:02 am    Post subject:
Reply with quote

Thanks Bill & Kolusu,

Bill,
I added SORTED,NOSEQCK which shaved another 10% of the CPU time.

JOINKEYS F2=LOANWRK,FIELDS=(5,7,A),SORTED,NOSEQCK

Kolusu,
Position 22 is correct. My JCL referenced the field in outfile processing after I had already added seven bytes.

I've not used DFSORT Group operations before, but I'm familiar with the other operations in the job. After a little trial & error, I should be able to make the updates.

Thanks again.
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 Performing arithmetic on input field zh_lad DFSORT/ICETOOL 24 Tue Dec 06, 2016 8:04 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Syncsort - Hard coded Value in output PORYES SYNCSORT 1 Thu Nov 03, 2016 9:23 am
No new posts Joinkeys operation when key1 and key2... Susanta DFSORT/ICETOOL 5 Wed Nov 02, 2016 5:35 pm


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