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

Can we read multiple VSAM files using ICETOOL

Postby ajuatsgp » Sun May 27, 2012 10:22 pm

I have six input files from where data needs to be fetch BS1/2,ED1/2,NA1/2
File layout
BS1
BS2 6-24 BSN(19 digit)

ED1
ED2 6-24 EDN(19 digit) 35-53BSN(19 digit) 60 flag(value 0 or 1)


NA1
NA2 6-24 NAN(19 digit) 35-53BSN(19 digit)

My 7th input driver file has following layout
1-8DATE 9-16TIMESTAMP(HHMMSSms) 45-46BS 70-88BSN(19 digit)(can have record from BS1 or BS2)
1-8DATE 9-16TIMESTAMP(HHMMSSms) 45-46ED 70-88EDN(19 digit)(can have record from ED1 or ED2)
1-8DATE 9-16TIMESTAMP(HHMMSSms) 45-46NA 70-88NAN(19 digit)(can have record from NA1 or NA2)

Required Output file
(if input file has BS) 1-8DATE 9,10-15TIMESTAMP(HHMMSS)16,17-35 corresponding 19 digit NAN 36,37-55 corresponding 19 digit EDN when flag=0
(if input file has ED) 1-8DATE 9,10-15TIMESTAMP(HHMMSS)16,17-35 corresponding19 digit NAN 36,37-5519 digit EDN
(if input file has NA)1-8DATE 9,10-15TIMESTAMP(HHMMSS)16,17-35 19 digit NAN36, 37-55 corresponding 19 digit EDN when flag=0

Digits marked in color represents field position.
Is it possible to get the desired output file using ICETOOL and without writing any COBOL program?
Please let me know if any required detail is missing in my question.
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 Frank Yaeger » Mon May 28, 2012 11:55 pm

It's really not clear from your description what it is you want to do exactly. You need a better example with real values and a better explanation of the rules.

Please show an example of the records in each input file (relevant fields only) and what you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files. If file1 can have duplicates within it, show that in your example. Likewise for the other input files.
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
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Can we read multiple VSAM files using ICETOOL

Postby ajuatsgp » Tue May 29, 2012 8:53 pm

First of all I would say sorry for providing the information in such a compacted way.Actually I know these files very well so assumed every one will understand what I am saying.Anyways please find a more detail requirement as below:
1.As I mentioned in the subject VSAM files, hence all my input files are VSAM and don't have Duplicate.
2.I have one driver file L1 and 6 other files BS1/2,ED1/2,BX1/2
File LRECL RECFM
---- ------- --------
BS1/2 8490 FB
ED1/2 4380 FB
BX1/2 45 FB
L1 16100 VB
3.Input files layout with records which are present in driver L1 file and which needs to be extracted
(i)From BS1/2 file: (a)BSN size X(19) position 4-22 will have values as 1234567890987654321
(ii)From ED1/2 file: (a)EDN size X(19) position 4-22 will have values as 1111222200022221111
(b)BSN size X(19) position 85-103 will have values as 1234567890987654321
(c)ED-flag size X(1) position 444 will have value 0 or 1
(iii)From BX1/2 file: (a)NAN size X(19) position 4-22 will have value as 3333444400033334444
(b)BSN size X(19) position 26-44 will have values as 1234567890987654321
4.Driver file 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 will have values as 1234567890987654321 {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 will have values as 1111222200022221111 {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 will have values as 3333444400033334444 {this field will have data only when L1-FC='NA'}
Note:Driver L1 file can have data from any file BS1,BS2,ED1,ED2,BX1,BX2
the Driver file L1 will look like:(only relevant fields shown)

2012201121517181BS 1234567890987654321(this is equals to BSN from BS file)
2012201121517182BS 1234567890987654321(this is equals to BSN from BS file)
2012201131421191BS 9876543210123456789(this is equals to BSN from BS file)
2012201121919361ED 1111222200022221111(this is equals to EDN from ED file)
2012201121919361NA 3333444400033334444(this is equals to NAN from NA file)
5.Output File layout:
D,DATE,TIME,NAN,OPERATOR,EDN,
below is the explanation:
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 ","

Please let me know if I am still missing any important info.I understand COBOL program can be written to achieve this but curious to know if ICETOOL can read VSAM file and fetch data
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 skolusu » Tue May 29, 2012 11:11 pm

ajuatsgp ,

It is still confusing.

From what I understood you just need to pull/extract records from 6 vsam files. Is that it or do I have to extract a record from BS1 file and then search NAN in NA vsam file? ( i am not sure where this NA file is)
Since your inputs are vsam files, you cannot concatenate them , so you need to extract from each file individually. I am just showing the matching records. You can use INREC/OUTREC to modify the matched records.

Here is a sample to extract all BS records from BS1 vsam file matching with the driver file.

//STEP0100 EXEC PGM=ICETOOL                     
//TOOLMSG  DD SYSOUT=*                           
//DFSMSG   DD SYSOUT=*                           
//BS1      DD DSN=Your Input BS1 VSAM FIle,DISP=SHR                 
//DRV      DD DSN=Your Input VB driver file,DISP=SHR                 
//OUT      DD SYSOUT=*                           
//TOOLIN   DD *                                 
  COPY JKFROM TO(OUT) USING(CTL1)               
//CTL1CNTL DD *                                 
  JOINKEYS F1=DRV,FIELDS=(17,19,A),TASKID=T1     
  JOINKEYS F2=BS1,FIELDS=(04,19,A),TASKID=T1     
  REFORMAT FIELDS=(F1:5,12,F2:4,19)             
//*                                             
//T1F1CNTL DD *                                 
  INCLUDE COND=(15,2,CH,EQ,C'BS')               
  INREC BUILD=(1,4,5,12,20,19)                   
  SUM FIELDS=NONE                               
//*                                             
//T1F2CNTL DD *                                 
  RECORD TYPE=F
  INCLUDE COND=(444,1,CH,EQ,C'0')               
  INREC BUILD=(4:4,19)                           
//* 
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Can we read multiple VSAM files using ICETOOL

Postby ajuatsgp » Wed May 30, 2012 12:06 am

Dear skolusu,

May I know where I am not clear.
Yes I nead to extract records from 6 VSAM file.
If you notice my reply to Frank,then you can see I have mentioned a BX file which contains both BSN and NAN.
I have also mentioned that after I get the matching record in the BS file,I need to get the EDN from ED file for the corresponding BSN
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 » Wed May 30, 2012 12:17 am

Hello,

Suggest you post a bit of input data from each file and the output you want when this input is processed.

Keep in mind that the best way to get help is to completely and clearly explain what you want to do along with data that demonstrates this.
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 dick scherrer » Wed May 30, 2012 12:55 am

Hello,

Dear Sir,
As already mentioned the file length are very large hence I have only given sample data of relevant fields from each file.
Understood - however i repeat:

You need to post some sample input from Each input file and the output data you want when this sample data is processed. And this would serve better if it is shown as "records" not just text.

Also, it is best to continue topic dialog in the topic rather than via PM.
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 » Wed May 30, 2012 1:01 am

Dear Sir,
As already mentioned Dear Sir,
As already mentioned the length of each input file is large,I have given sample data of relevant fields from each of the input file.
Any ways as you asked please find below sample data for the above mentioned files:
BS1/2
------
FILLER(1-3byte)1234567890987654321(BSN 4-22position)
FILLER(1-3byte)9876543210123456789(BSN 4-22position)
FILLER(1-3byte)1234432100123456789(BSN 4-22position)
FILLER(1-3byte)9999999990123456789(BSN 4-22position)


ED1/2
-------
FILLER(1-3position)1111222200022221111(EDN 4-22position)FILLER(23-84position)1234567890987654321(BSN85-103position)FILLER(104-443position)0(ED-FLAG444position)
FILLER(1-3position)1111333300033331111(EDN 4-22position)FILLER(23-84position)1234567890987654321(BSN85-103position)FILLER(104-443position)1(ED-FLAG444position)
FILLER(1-3position)4444888800088884444(EDN 4-22position)FILLER(23-84position)9876543210123456789(BSN85-103position)FILLER(104-443position)1(ED-FLAG444position)
FILLER(1-3position)4444555500055554444(EDN 4-22position)FILLER(23-84position)1234432100123456789(BSN85-103position)FILLER(104-443position)0(ED-FLAG444position)
FILLER(1-3position)4444676700067674444(EDN 4-22position)FILLER(23-84position)9999999990123456789(BSN85-103position)FILLER(104-443position)0(ED-FLAG444position)
FILLER(1-3position)4444999900099994444(EDN 4-22position)FILLER(23-84position)9999999990123456789(BSN85-103position)FILLER(104-443position)1(ED-FLAG444position)
Note:There can be single or multiple EDN for each BSN but if multiple EDN then flag must be different for each
BX1/2
-------
FILLER(1-3position)3333444400033334444(NAN 4-22position)FILLER(23-25position)1234567890987654321(BSN 26-44 position)
Note: each NAN can have multiple BSN but no two NAN can have same BSN
L1
----
2012201121517181BS 1234567890987654321(this is equals to BSN from BS file)
2012201121517182BS 1234567890987654321(this is equals to BSN from BS file)
2012201131421191BS 9876543210123456789(this is equals to BSN from BS file)
2012201121919361ED 1111222200022221111(this is equals to EDN from ED file)
2012201121919361NA 3333444400033334444(this is equals to NAN from NA file)
Note:each fields are already explained in my previous post.

I have also mentioned the extraction criteria for my output file.Mentioning again:
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 ","the length of each input file is large,I have given sample data of relevant fields from each of the input file.
Any ways as you asked please find below sample data for the above mentioned files:
BS1/2
------
1234567890987654321 which represents BSN
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 » Wed May 30, 2012 1:25 am

Hello,

As very often happens here, there is a communications breakdown. . .

And this would serve better if it is shown as "records" not just text.


This best accomplished by showing relevant parts of all of the input files and the output data wanted from all of the sample input(s). There should be several sample "records" in each input file and the appropiate output records from that input.

All of the "data" should be shown as you presented this:
1234567890987654321
except there would be multiple records for each file.
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 » Wed May 30, 2012 9:46 am

Sorry I didn't understand what you mean by communication break down.
In my last post I have given sample data of relevant fields from each of the input gile.
Please let me know if anything is still not clear.
ajuatsgp
 
Posts: 82
Joined: Thu May 20, 2010 6:50 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post