IBM Mainframe Forum

by **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:

Select all

//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:

Select all

//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

Select all

//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:

Select all

//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)
/*

by **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

by **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:

Select all

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

by **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

by **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:

Select all

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).

Select all

//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

by **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

by **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.

by **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

by **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?

by **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:

Select all

//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:

Select all

12345 XXXXXYYYYY BBBBB 12345

Regards,

Oliver

IBM Mainframe Forum

Can we read multiple VSAM files using ICETOOL

Re: Can we read multiple VSAM files using ICETOOL

Re: Can we read multiple VSAM files using ICETOOL

Re: Can we read multiple VSAM files using ICETOOL

Re: Can we read multiple VSAM files using ICETOOL

Re: Can we read multiple VSAM files using ICETOOL

Re: Can we read multiple VSAM files using ICETOOL

Re: Can we read multiple VSAM files using ICETOOL

Re: Can we read multiple VSAM files using ICETOOL

Re: Can we read multiple VSAM files using ICETOOL

Re: Can we read multiple VSAM files using ICETOOL