Can we read multiple VSAM files using ICETOOL



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Re: Can we read multiple VSAM files using ICETOOL

Postby olivermf » Thu May 31, 2012 10:25 pm

Hello ajuatsgp,

so my second example is the right one.
The DD-Statement "SORTOUT" will contain all records of the combined ED- and BX-files.
After that you could make three SORT-Steps or a ICETOOL-Step with three SORTs within it like I already posted before.

With the code I wrote here I think you already can begin to try out, if the results do match your needs.
I will try to sum up what I posted before, but I am currently at home (in my time zone it's nearly 7:00 pm), so I could not test anything of it and cannot guarantee that I did no mistakes :-)

1. Build three temporary output files in dividing your driver-file:
//STEP1   EXEC PGM=ICETOOL                   
//TOOLMSG   DD SYSOUT=*                     
//DFSMSG    DD SYSOUT=*                     
//L1        DD DISP=SHR,DSN=USERID.VSAM.IN5   
//BS        DD DISP=OLD,DSN=USERID.VSAM.OUT1 
//ED        DD DISP=OLD,DSN=USERID.VSAM.OUT2 
//NA        DD DISP=OLD,DSN=USERID.VSAM.OUT3 
//TOOLIN    DD *                             
 COPY FROM(L1) TO(BS) USING(CTL1)           
 COPY FROM(L1) TO(ED) USING(CTL2)           
 COPY FROM(L1) TO(NA) USING(CTL3)           
/*                                           
//CTL1CNTL  DD *                             
 SORT FIELDS=COPY                           
 INCLUDE COND=(17,2,CH,EQ,C'BS')             
/*
//CTL2CNTL  DD *                             
 SORT FIELDS=COPY                           
 INCLUDE COND=(17,2,CH,EQ,C'ED')             
/*
//CTL3CNTL  DD *                             
 SORT FIELDS=COPY               
 INCLUDE COND=(17,2,CH,EQ,C'NA')
/*


2. Join your BX and ED files:

//JOIN1     EXEC PGM=SORT                   
//SYMNAMES  DD *                             
 ED12KEY,1,19                                 
 BX12KEY,21,19                                 
//SYSOUT    DD SYSOUT=*                       
//ED12      DD DSN=USERID.VSAM.IN1,DISP=SHR   
            DD DSN=USERID.VSAM.IN2,DISP=SHR   
//BX12      DD DSN=USERID.VSAM.IN3,DISP=SHR   
            DD DSN=USERID.VSAM.IN4,DISP=SHR   
//SORTOUT   DD DSN=USERID.VSAM.EDBXJOIN,DISP=OLD
//SYSIN     DD *                             
 OPTION COPY                                 
 JOINKEYS F1=ED1,FIELDS=(ED12KEY,A)           
 JOINKEYS F2=ED2,FIELDS=(BX12KEY,A)           
/*                   


3. With your three driver files and some INREC FIELDS-Commands build whatever you want out of it :-)
a) DFSORT
//BSDRIVER     EXEC PGM=SORT   
//SYSOUT    DD SYSOUT=*
//SORTIN    DD DSN=USERID.BS.DRIVER.FILE,DISP=SHR
//SORTOUT DD DSN=USERID.FINAL.OUTPUT,DISP=(NEW,CATLG,DELETE)
INREC FIELDS=(C'D,',1,8,C',',9,4, ..... and so on)
/*
//* Do the same for 'ED' and 'NA'
//EDDRIVER     EXEC PGM=SORT
//SYSOUT    DD SYSOUT=*
//SORTIN    DD DSN=USERID.ED.DRIVER.FILE,DISP=SHR
//SORTOUT DD DSN=USERID.FINAL.OUTPUT,DISP=MOD
INREC FIELDS=(C'D,',1,8,C',',9,4, ..... and so on)
/*
//NADRIVER     EXEC PGM=SORT
//SYSOUT    DD SYSOUT=*
//SORTIN    DD DSN=USERID.NA.DRIVER.FILE,DISP=SHR
//SORTOUT DD DSN=USERID.FINAL.OUTPUT,DISP=MOD
INREC FIELDS=(C'D,',1,8,C',',9,4, ..... and so on)
/*

b) ICETOOL
With ICETOOL you can make this in one step:
//BSDRIVER     EXEC PGM=ICETOOL   
//SYSOUT    DD SYSOUT=*
//BSIN DD DSN=USERID.BS.DRIVER.FILE,DISP=SHR
//EDIN DD DSN=USERID.BS.DRIVER.FILE,DISP=SHR
//NAIN DD DSN=USERID.BS.DRIVER.FILE,DISP=SHR
//OUT1 DD DSN=USERID.FINAL.OUTPUT,DISP=(NEW,CATLG,DELETE)
//OUT2 DD DSN=USERID.FINAL.OUTPUT,DISP=MOD
//SYSIN DD *
COPY FROM(BSIN) TO(OUT1) USING(CTL1)
COPY FROM(EDIN) TO(OUT2) USING(CTL2)
COPY FROM(NAIN) TO(OUT2) USING(CTL3)
/*
//CTL1CNTL DD *
SORT FIELDS=COPY
INREC FIELDS=(C'D,',1,8,C',',9,4, ..... and so on)
/*
//CTL2CNTL DD *
SORT FIELDS=COPY
INREC FIELDS=(C'D,',1,8,C',',9,4, ..... and so on)
/*
//CTL3CNTL DD *
SORT FIELDS=COPY
INREC FIELDS=(C'D,',1,8,C',',9,4, ..... and so on)
/*
olivermf
 
Posts: 53
Joined: Tue Feb 08, 2011 4:28 pm
Has thanked: 3 times
Been thanked: 0 time

Re: Re: Can we read multiple VSAM files using ICETOOL

 

Re: Can we read multiple VSAM files using ICETOOL

Postby ajuatsgp » Fri Jun 01, 2012 3:00 pm

Dear Olivermf,
I was going through the solution proposed by you ,I could not inderstand the STEP 2 can you explain please.Did you get any chance to test what ever you have mentioned in your solution.

Thanks
ajuatsgp
 
Posts: 82
Joined: Thu May 20, 2010 6:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Can we read multiple VSAM files using ICETOOL

Postby olivermf » Fri Jun 01, 2012 3:25 pm

Hello ajuatsgp,

to understand my second step (JOINKEYS-Step) have a look at the IBM documentation material.
http://publibz.boulder.ibm.com/epubs/pdf/ice1ca60.pdf (Application & Programming Guide)

For examples I recommend the DFSORT Smart Tricks:
ftp://public.dhe.ibm.com/storage/dfsort ... rttrck.pdf

The only thing I forgot in my example is the REFORMAT FIELDS-Command.
The JOINKEY-Step joins data in files like you would join data in a database join.
In my example the JOINKEY-Command joins your key in the BX-files on position 1 in length 19 with the key on position 21 length 19 in your ED files. I used the position information in your example data so you can customize it to match your needs, because in the "real files" the positions will differ. I used symnames only to make it more readable. If you did not know this you can use the position information directly:
 
 JOINKEYS F1=ED12,FIELDS=(1,19,A)           
 JOINKEYS F2=BX12,FIELDS=(21,19,A) 
 REFORMAT FIELDS=(F1:1,50,F2:1,50)


The REFORMAT FIELDS-command in my example will join the information in your ED12 dataset and concatenates it with the information in your BX12 dataset.
(F1:1,50,F2:1,50) means in words: Take data from F1 (that's ED12) from position 1 in length 50 and then take data from F2 (that's BX12) from position 1 in length 50.

Regards,

Oliver
olivermf
 
Posts: 53
Joined: Tue Feb 08, 2011 4:28 pm
Has thanked: 3 times
Been thanked: 0 time

Re: Can we read multiple VSAM files using ICETOOL

Postby ajuatsgp » Fri Jun 01, 2012 3:39 pm

Hello Oliver,
I am still bit confued when the driver file will have L1-FC=ED,how will the required fields will be populated?

Thanks
ajuatsgp
 
Posts: 82
Joined: Thu May 20, 2010 6:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Can we read multiple VSAM files using ICETOOL

Postby olivermf » Fri Jun 01, 2012 5:27 pm

Hello ajuatsgp,

I got confused with all the data and made a mistake.
Here I have an complete example how to join the files.
The complete Join results in the following, so I am not sure if I am doing something wrong, or if one of the keys does not fit to another.
Every two lines has to be read as one line with a record length of 118:
1111222200022221111 1234567890987654321 03333444400033334444 12345678909876543212012153121517181BS 1234567890987654321                                                                                 
1111222200022221111 1234567890987654321 03333444400033334444 12345678909876543212012153121517182BS 1234567890987654321                                                                                 
1111333300033331111 1234567890987654321 13333444400033334444 12345678909876543212012153121517181BS 1234567890987654321                                                                                 
1111333300033331111 1234567890987654321 13333444400033334444 12345678909876543212012153121517182BS 1234567890987654321                                                                                 
4444888800088884444 9876543210123456789 15555444400055554444 98765432101234567892012153131421191BS 9876543210123456789                                                                                 


After you have a join with all information you can build what you need with the IFTHEN-Operator.
I have to go now, so we can look it through tomorrow without mainframe-access or monday.

Where I wrote "BUILD(C'OUTPUT FOR BS')), " you have to fill in what you want to build out of the joined file.
Perhaps you can now understand the principles of what I try to do, because you can run this code actually (I tested it).

//JOIN1     EXEC PGM=SORT                   
//SYMNAMES  DD *                             
 ED12KEY,21,19                               
 BX12KEY,21,19                               
//SYSOUT    DD SYSOUT=*                     
//ED12      DD *                             
1111222200022221111 1234567890987654321 0   
1111333300033331111 1234567890987654321 1   
4444888800088884444 9876543210123456789 1   
4444555500055554444 1234432100123456789 0   
4444676700067674444 9999999990123456789 0   
4444999900099994444 9999999990123456789 1   
/*                                           
//BX12      DD *                             
3333444400033334444 1234567890987654321     
3333444400033334444 9999999990123456789   
5555444400055554444 9876543210123456789   
/*                                         
//SORTOUT   DD DSN=USERID.TEST80,DISP=OLD   
//SYSIN     DD *                           
 OPTION COPY                               
 JOINKEYS F1=ED12,FIELDS=(ED12KEY,A)       
 JOINKEYS F2=BX12,FIELDS=(BX12KEY,A)       
 REFORMAT FIELDS=(F1:1,41,F2:1,39)         
/*                                         
//JOIN2     EXEC PGM=SORT                 
//SYMNAMES  DD *                           
 EDBXKEY,21,19                             
 L1KEY,20,19                               
//SYSOUT    DD SYSOUT=*                   
//EDBXJOIN  DD DSN=USERID.TEST80,DISP=SHR   
//L1DRIVER  DD *                           
2012153121517181BS 1234567890987654321     
2012153121517182BS 1234567890987654321         
2012153131421191BS 9876543210123456789         
2012153121919361ED 1111222200022221111         
2012153121919361NA 3333444400033334444         
/*                                             
//SORTOUT   DD DSN=USERID.VSAM.EDBXL1,DISP=OLD 
//SYSIN     DD *                               
 OPTION COPY                                   
 JOINKEYS F1=EDBXJOIN,FIELDS=(EDBXKEY,A)       
 JOINKEYS F2=L1DRIVER,FIELDS=(L1KEY,A)         
 REFORMAT FIELDS=(F1:1,80,F2:1,38)             
/*                                             
//SORT1     EXEC PGM=SORT                     
//SYSOUT    DD SYSOUT=*                       
//SORTIN    DD DSN=USERID.VSAM.EDBXL1,DISP=SHR 
//SORTOUT   DD DSN=USERID.TEST200,DISP=OLD     
//SYSIN     DD *                               
 SORT FIELDS=COPY                             
 INREC IFTHEN=(WHEN=(97,2,CH,EQ,C'BS'),BUILD(C'OUTPUT FOR BS')),
       IFTHEN=(WHEN=(97,2,CH,EQ,C'ED'),BUILD(C'OUTPUT FOR ED')),
       IFTHEN=(WHEN=(97,2,CH,EQ,C'NA'),BUILD(C'OUTPUT FOR NA')) 
/*                                                               


Regards,

Oliver
olivermf
 
Posts: 53
Joined: Tue Feb 08, 2011 4:28 pm
Has thanked: 3 times
Been thanked: 0 time

Re: Can we read multiple VSAM files using ICETOOL

Postby ajuatsgp » Tue Jun 05, 2012 8:27 am

Dear Oliver,
I guess you have missed one of my post:
by ajuatsgp » Thu May 31, 2012 7:27 pm
ED1/2
-------
1111222200022221111 1234567890987654321 0 1111111111111111111
1111333300033331111 1234567890987654321 1 2222222222222222222
4444888800088884444 9876543210123456789 1 3333333333333333333
4444555500055554444 1234432100123456789 0 4444444444444444444
4444676700067674444 9999999990123456789 0 5555555555555555555
4444999900099994444 9999999990123456789 1 6666666666666666666
Note:There can be single or multiple EDN for each BSN but if multiple EDN then flag must be different for each
Explanation of the above layout:
FILLER(1-3position)1111222200022221111(EDN 4-22position)FILLER(23-84position)1234567890987654321(BSN85-103position)FILLER(104-443position)0(ED-FLAG444position)FILLER(445-489position)1111111111111111111(NAN490-508position)

Hence we will have only a small change in the one field(NAN-7th position) of output file's field population:
7th postion "NAN"
if driver file contains L1-FC='BS' then for L1-BSN this needs to be populated from BX file corresponding NAN
if driver file contains L1-FC='ED' then to be populated with NAN from ED file for the corresponding L1-EDN
if driver file contains L1-FC='NA' then this needs to be populated with L1-NAN

As while trying the solution proposed by you I am confused doing the join between ED and BX file.
Please consider the data mentioned here.

Thanks
ajuatsgp
 
Posts: 82
Joined: Thu May 20, 2010 6:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Can we read multiple VSAM files using ICETOOL

Postby dick scherrer » Tue Jun 05, 2012 9:12 am

Hello,

As i mentioned previously, suggest you seriously consider writing code. . . .

So far you do not understand what is being discussed and coded. Your management is going to be most unhappy if you deliver something you don't understand and cannot maintain.

Some of my clients have disciplined people for tryng to implement a solution they did not write and/or understand.

Getting help is almost always all right, but getting entire solutions is not.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Can we read multiple VSAM files using ICETOOL

Postby ajuatsgp » Tue Jun 05, 2012 9:40 am

Hi Dick,
I understand the proposed solution by Oliver.
Secondly if we can extract fields now then later we can add more if the requirement changes.


Hi Oliver,
I am also doubtful about the JOIN2 step,as EDBXKEY starts 21position,I dont think it will match properly with L1KEY starting at 20 position.
As USERID.TEST80 will always have BSN @ 21 postion where as L1 file can have BSN,ED Nor NAN @ 20th position.

Please let me know if I am not clear.
Thanks
ajuatsgp
 
Posts: 82
Joined: Thu May 20, 2010 6:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Can we read multiple VSAM files using ICETOOL

Postby NicC » Tue Jun 05, 2012 11:50 am

I dont think it will match properly

Why not try it out and see if it does not work? Suppose it does work: how much time has been wasting thinking it does not work instead of proving it?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Can we read multiple VSAM files using ICETOOL

Postby olivermf » Tue Jun 05, 2012 6:52 pm

Hello ajuatsgp,

I think NicC is right. What I tried to do here is showing dfsort techniques and not working out final solutions. I showed you how to match files on a key. What you want is to extract information out of 3 files. If there is BS, ED or NA on a defined position you want to build output in different ways. I think I showed you everything that you need in my last post. Please have a look at the documentation material and try yourself.

If you have problems show us what you did and where you had problems, I'm sure someone here can help.

I am also doubtful about the JOIN2 step,as EDBXKEY starts 21position,I dont think it will match properly with L1KEY starting at 20 position.

What do you mean by that? The JOINKEY Step joins files on a key somewhere in a file. So if you have one key at position 1 in one file and the other at position 13 in the other file you still can join them. For example:
//FILE1 DD *
12345 XXXXX
FILE2 DD *
YYYYY BBBBB 12345
//SYSIN DD *
 OPTION COPY
 JOINKEYS F1=FILE1,FIELDS=(1,5,A)       
 JOINKEYS F2=FILE2,FIELDS=(13,5,A)
 REFORMAT FIELDS=(F1:1,11,F2:1,17)


This will produce the output:
12345 XXXXXYYYYY BBBBB 12345



Regards,

Oliver
olivermf
 
Posts: 53
Joined: Tue Feb 08, 2011 4:28 pm
Has thanked: 3 times
Been thanked: 0 time

PreviousNext

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post