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 » Wed May 30, 2012 1:24 pm

Hello ajuatsgp,

did you notice, that you can upload attachments?
Why don't you upload sample data in form of .txt files?

You have to understand that most people here want to test their solutions before posting them into the forum.

Another point (just for me). Could you please explain the abbrevations you use? I think the experts here, who use mainframes for years, know what you mean. As I am not as long working within the mainframe topic I simply do not know what you mean :-)

For example I mean something like this:
each NAN can have multiple BSN but no two NAN can have same BSN



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 BillyBoyo » Wed May 30, 2012 2:40 pm

Oliver, the problem with attachments is that many are prevented from downloading them due to security policy or various other reasons.

The abbreviations, I think, are local for particular categories of data and content therein.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Can we read multiple VSAM files using ICETOOL

Postby NicC » Wed May 30, 2012 6:00 pm

And I ignore posts with attachments just because it is an extra click or 2 of extra work plus usage of my hard drive.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Can we read multiple VSAM files using ICETOOL

Postby ajuatsgp » Wed May 30, 2012 7:22 pm

Hi All,
I think our discussion is heading some other direction.
I would like to share that while going through the solution provide by SKOLUSU which he already mentioned is according to what he interpreted the query,I was also trying to get something out of it I realised few things:
1.We can completely ignore the BS1/2 file as we have the BSN already present in ED1/2 and BX1/2.
2.As mentioned by SKOLUSU for VSAM file there will be concatenate issue,so I am ready perform the ICETOOL step twice once each for BX1,ED1 and BX2,ED2.
Now as requested by many I am providing more details,this might help us in getting the desired output.
1.When I am saying BX1 or BX2 it means same BX file divided in to 2 segments due to their volume.Same is the case for ED1 and ED2.
2.But the driver L1 file will be a single file which will have records from both the segment.
3.BSN,EDN and NAN and nothing but 3 field names.
4.The driver L1 file will have data from BSfile(which we can ignore as the BSN is present in other 2 files),ED file and NA file(this can also be ignored as NAN is present in BX file).Basically BX file will have both BSN and NAN which will allow us to ignore those two(BSN and NAN files).
5.File structure for ED files:
ED1/2
-------
1111222200022221111 1234567890987654321 0
1111333300033331111 1234567890987654321 1
4444888800088884444 9876543210123456789 1
4444555500055554444 1234432100123456789 0
4444676700067674444 9999999990123456789 0
4444999900099994444 9999999990123456789 1

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)
6.File Structure for BX files:
BX1/2
--------
3333444400033334444 1234567890987654321
3333444400033334444 9999999990123456789
5555444400055554444 9876543210123456789
Note: each NAN can have multiple BSN but no two NAN can have same BSN

Explanation of the above layout:
FILLER(1-3position)3333444400033334444(NAN 4-22position)FILLER(23-25position)1234567890987654321(BSN 26-44 position)
7.File Structure for L1 File:
L1
----
2012153121517181BS 1234567890987654321
2012153121517182BS 1234567890987654321
2012153131421191BS 9876543210123456789
2012153121919361ED 1111222200022221111
2012153121919361NA 3333444400033334444
Explanation of the above layout:
L1-variable[working Storage 01 level]
KEY1:(Group Variable length-10 byte)[working Storage 03 level]
05 L1-DATE position 01-04 comp filed S9(7)(Julien date-YYYYDDD) will have Julien date
05 L1-TIME position 05-08 comp filed S9(8)(format-HHMMSSTT) will have time
05 L1-SrNo. position 09-10 comp filed S9(4) will have serial number
KEY2:(Group Variable length-57 byte)[working Storage 03 level]
05 L1-FC-X(2) position 11-12 will have value BS/ED/EN
L1-BS redefines KEY2[working Storage 03 level]
05 L1-BSN size X(19) position 16-34 {this field will have data only when L1-FC='BS'}
L1-ED redefines KEY2[working Storage 03 level]
05 L1-EDN size X(19) position 16-34{this field will have data only when L1-FC='ED'}
L1-NA redefines KEY2[working Storage 03 level]
05 L1-NAN size X(19) position 16-34 {this field will have data only when L1-FC='NA'}
Note:Driver L1 file can have data from any file ED1,ED2,BX1,BX2
8.File Structure for output file:

D,20120531,1215171,3333444400033334444,OPERATOR,1111222200022221111, {for first record of L1}
D,20120531,1215171,5555444400055554444,OPERATOR,4444888800088884444, {for fourth record of L1}
D,20120531,1215171,9999444400099994444,OPERATOR,4444676700067674444, {for fifth record of L1}
Explanation of the above layout:

1st postion "D"[constantvalue]
2nd position ","
3rd postion "DATE" this needs to be populated from L1-DATE in YYYYMMDD format field size 9(8)
4th position ","
5th postion "TIME" this needs to be populated from L1-TIME in HHMMSS format field size 9(8)
6th position ","
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 for L1-EDN we need to get BSN from ED file and then corresponding NAN from BX file for that BSN
if driver file contains L1-FC='NA' then this needs to be populated with L1-NAN
8th position ","
9th position "OPERATOR" [constantvalue]
10th position ","
11th position "EDN" if driver file contains L1-FC='BS' then for L1-BSN this needs to be populated from ED file corresponding EDN when ED-flag=0
if driver file contains L1-FC='ED' then this needs to be populated with L1-EDN
if driver file contains L1-FC='NA' then for L1-NAN we need to get BSN from BX file and then corresponding EDN(when ED-flag=0) from ED file for that BSN
12th position ",
Last but not least all input file characteristics :
File LRECL RECFM
---- ------- --------
ED 4380 FB
BX 45 FB
L1 16100 VB
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 » Thu May 31, 2012 2:18 pm

Well, perhaps we can find a way to start here.

1. First I would sort the driver file into 3 temporary output-files, so that you will have 3 different files for the BS, ED and NA mark:

//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. After that I would concatenate your Input-Files into a new one with all the information you need. A JOINKEYS-Step would produce something like this if you concatenate your Input-Files (only done with two of them to show as an example):

1111222200022221111 1234567890987654321 0 3333444400033334444 1234567890987654321
4444888800088884444 9876543210123456789 1 5555444400055554444 9876543210123456789
4444676700067674444 9999999990123456789 0 3333444400033334444 9999999990123456789


3. Now I think 3 DFSORT-Steps for each BS / ED and NA-Driver-File could do the trick with some simple INREC FIELDS-Commands, like:

INREC FIELDS=(C'D,',1,8,C',',9,4, ..... and so on


Before I write code for this steps, do you think this is what you need?

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 » Thu May 31, 2012 4:57 pm

Dear Oliver,

Really a nice and easy to understand attempt.
But I guess this solution wil have a problem:as I mentioned I will have 2 segments of each input file(BS/ED/BX) but the driver file L1 will be a single file and will ave data from all.
So do you mean we have to write two sets of Join statement one for the combination of each segment?

Secondly I am extremely sorry to mention that there is a small change in the requirement.However I think this won't be a problem as per my guess we can manage it in the join.
We have an NAN filed present in ED File
so the ED File stucture is:

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

Hence the outputfile will now look like:
D,20120531,1215171,3333444400033334444,OPERATOR,1111222200022221111, {for first record of L1}
D,20120531,1215171,3333333333333333333,OPERATOR,4444888800088884444, {for fourth record of L1}
D,20120531,1215171,9999444400099994444,OPERATOR,4444676700067674444, {for fifth record of L1}

Once again appolozige for the above changes.
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 » Thu May 31, 2012 6:11 pm

as I mentioned I will have 2 segments of each input file(BS/ED/BX) but the driver file L1 will be a single file and will ave data from all.
So do you mean we have to write two sets of Join statement one for the combination of each segment?

I still have problems to imagine what all the files would look like after a join, but because you need different contents for BS / ED or BX I think to join all your input files with each other would help a lot to do this. Because you can only join 2 files in one step there will be more than one JOIN-Steps... With DFSORT and ICETOOL you can sort from VSAM-Files into normal fixed blocked files without any problems, that was what I did in my example.

Why don't we begin to join the input-files to look at the result. Because you know your files best you then can decide if with the INREC-Commands you can extract all information you need. To make it simpler I will ignore the filler positions and work with symnames, so you can change them afterwars to match your needs.

When you speak of ED1 and ED2 or BX1 and BX2 can we append these files to each other, or do we need to join them? For example, what is the relationship between ED1 and ED2? Perhaps some code snippets can help, so you can choose:

So what I mean, do we join them:
//JOIN1     EXEC PGM=SORT
//SYMNAMES  DD *                             
 ED1KEY,1,19                                 
 ED2KEY,1,19                                 
//SYSOUT    DD SYSOUT=*                       
//ED1       DD DSN=USERID.VSAM.IN1,DISP=SHR   
//ED2       DD DSN=USERID.VSAM.IN2,DISP=SHR   
//SORTOUT   DD DSN=USERID.VSAM.EDJOIN,DISP=OLD
//SYSIN     DD *                             
 OPTION COPY                                 
 JOINKEYS F1=ED1,FIELDS=(ED1KEY,A)           
 JOINKEYS F2=ED2,FIELDS=(ED2KEY,A)           
/*                                           


Or do we append them to each other and join them with the first other set of appended input 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)           
/*                   
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 » Thu May 31, 2012 7:22 pm

Dear Oliver,
When I said segmented files I mean to say two similar files only data inside them are different due to large volume they have been divided in to two so that when we use the files(as these files are also used by many other jobs in my organization) it will be easy to access them.Saying in terms of data you can assume like the sample data I have given you for ED file divide in two parts half of it will be in ED1 and the other half in ED2,same is case for other files.
But as already mentioned many times my driver file(L1) will contain records that may belongs to any of the 2 segments.
Please let me know if you need any more clarification.
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 » Thu May 31, 2012 8:47 pm

Hello,

It may be time to consider a programming solution . . .

Before you get this running there may be more "scope creep" (change in requirement impacting what runs so far).

If someone posts a complete solution as the requirement currently exists, your people will possibly not understand it well enough to make further changes. . .

The forum will most likely not be able (or willing) to maintain this process for you. If you write code, it will all be under your control with no need for others to make the changes.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Can we read multiple VSAM files using ICETOOL

Postby ajuatsgp » Thu May 31, 2012 8:55 pm

Extremely sorry for changing the requirements but the one I gave last is the final there won't be any change.
I understand a COBOL program can be written for this.But still I would like to have a solution using ICETOOL,so that going forward I can suggest in my organization to use more of ICETOOL than the conventional programming.
Hoping to get a solution using ICETOOL.
ajuatsgp
 
Posts: 82
Joined: Thu May 20, 2010 6:50 pm
Has thanked: 0 time
Been thanked: 0 time

PreviousNext

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post