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 for joining records from different files

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

New User


Joined: 01 Mar 2007
Posts: 5
Location: Mumbai

PostPosted: Wed May 09, 2007 11:08 pm    Post subject: ICETOOL for joining records from different files
Reply with quote

Hi,

Gone through the few similar posts in this forum, but I was not able to derive solution to my problem.

I am working on a requirement in which I have to join three flat files
File1 : Key1Data1
File2 : Key2Data2
File3 : Key3Data3
Where each file LRECL is of (Key + Data) = (8 + 992) = 1000 bytes.

Now I have to Join these 3 files in LRECL of 3000 Bytes and
1) If a key is common in the three files, O/P will contain
3000 bytes as addition from file1, file2 and file3

2) If there is no matching record found in any of the above files, process should still write an output record with missing file-record being initialized.
For example,
A) If the Key1 is not matching with Key2 or Key3, o/p should
contain only first 1000 bytes and rest 2000 bytes initialized.
(Key1Data1)(Spaces)(Spaces) ----> Total 3k bytes
B) Similarly non matching Key2 will have
(Spaces)(Key2Data2)(Spaces) ----> Total 3k bytes

Can I do this with ICETOOL ? And what should be the key statements icon_question.gif

Thanx,
Amar
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu May 10, 2007 1:28 am    Post subject:
Reply with quote

It's not clear what you want for output in all the possible cases.

Say conceptually your input is as follows with each of the values 1-7 representing a key and Fx representing the data:

File1

1 F1
2 F1
3 F1
4 F1

File2

1 F2
3 F2
6 F2
7 F2

File3
1 F3
4 F3
5 F3
6 F3

What would you expect for the output? I think the first two records would be as follows (where bbbb represents blanks):

Code:

1 F1 1 F2 1 F3
2 F1 bbbb bbbb


What would you expect for the other output records?
Back to top
View user's profile Send private message
amar-tayade

New User


Joined: 01 Mar 2007
Posts: 5
Location: Mumbai

PostPosted: Thu May 10, 2007 1:07 pm    Post subject:
Reply with quote

Sorry, I omitted the example in my first post . Yes the expected file output should be as :

Code:


1 F11 F21 F3
2 F1bbbbbbbb
3 F13 F2bbbb
4 F1bbbb4 F3
bbbbbbbb5 F3
bbbb6 F26 F3
bbbb7 F2bbbb

Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu May 10, 2007 11:24 pm    Post subject:
Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/1000)
//IN2 DD DSN=...  input file2 (FB/1000)
//IN3 DD DSN=...  input file3 (FB/1000)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/300)
//TOOLIN   DD    *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
COPY FROM(IN3) TO(T1) USING(CTL3)
SPLICE FROM(T1) TO(OUT) ON(3002,8,CH) KEEPNODUPS -
  WITHEACH WITH(4002,1001) WITH(5003,1001) USING(CTL4)
/*
//CTL1CNTL DD *
  INREC BUILD=(3001:C'1',3002:1,1000,
               4002:C'1',4003:1,1000,
               5003:C'1',5004:1,1000)
/*
//CTL2CNTL DD *
  INREC BUILD=(3001:C'2',3002:1,1000,
               4002:C'2',4003:1,1000,
               5003:C'2',5004:1,1000)
/*
//CTL3CNTL DD *
  INREC BUILD=(3001:C'3',3002:1,1000,
               4002:C'3',4003:1,1000,
               5003:C'3',5004:1,1000)
/*
//CTL4CNTL DD *
  OUTFIL FNAMES=OUT,
    IFOUTLEN=3000,
    IFTHEN=(WHEN=(3001,1,CH,EQ,C'1'),
       OVERLAY=(1:3002,1000),HIT=NEXT),
    IFTHEN=(WHEN=(3001,1,CH,EQ,C'2'),
       OVERLAY=(1001:3002,1000),HIT=NEXT),
    IFTHEN=(WHEN=(3001,1,CH,EQ,C'3'),
       OVERLAY=(2001:3002,1000),HIT=NEXT),
    IFTHEN=(WHEN=(4002,1,CH,EQ,C'2'),
       OVERLAY=(1001:4003,1000),HIT=NEXT),
    IFTHEN=(WHEN=(4002,1,CH,EQ,C'3'),
       OVERLAY=(2001:4003,1000),HIT=NEXT),
    IFTHEN=(WHEN=(5003,1,CH,EQ,C'3'),
       OVERLAY=(2001:5004,1000))
/*
Back to top
View user's profile Send private message
amar-tayade

New User


Joined: 01 Mar 2007
Posts: 5
Location: Mumbai

PostPosted: Tue May 15, 2007 8:24 pm    Post subject:
Reply with quote

Hi Frank,

Appreciate your quick reply.
I tried with the above code and the job failed with following error:

ICE146I 0 END OF STATEMENTS FROM CTL4CNTL - PARAMETER LIST STATEMENTS FOLLOW
DEBUG NOABEND,ESTAE
OPTION,MSGDDN=DFSMSG,LIST,MSGPRT=ALL,RESINV=0,SORTDD=CTL4,SORTIN=T7,
SORTOUT=OUT123,DYNALLOC,SZERO,EQUALS,NOVLSHRT,LOCALE=NONE
OCHECK
SORT FIELDS=(3002,8,CH,A)
MODS E35=(ICE35DU,12288)
ICE201I E RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE027A 1 END OF FIELD BEYOND MAXIMUM RECORD LENGTH

Here is the code I used
Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=Myid.DSN1,DISP=SHR
//IN2 DD DSN=Myid.DSN2,DISP=SHR
//IN3 DD DSN=Myid.DSN3,DISP=SHR
//OUT123 DD DISP=(NEW,CATLG,DELETE),
//         DSN=Myid.OUTPUT,
//         UNIT=DISK,
//         SPACE=(CYL,(50,25),RLSE),
//         DCB=(RECFM=FB,LRECL=3000,BLKSIZE=27000)
//T7 DD DSN=&&T7,DISP=(,PASS),UNIT=SYSDA,SPACE=(CYL,(5,5))
//TOOLIN DD *
  COPY FROM(IN1) TO(T7) USING(CTL1)
  COPY FROM(IN2) TO(T7) USING(CTL2)
  COPY FROM(IN3) TO(T7) USING(CTL3)
  SPLICE FROM(T7) TO(OUT123) ON(3002,8,CH) KEEPNODUPS
   WITHEACH WITH(4002,1001) WITH(5003,1001) USING(CTL4)
/*
//CTL1CNTL DD *
  INREC BUILD=(3001:C'1',3002:1,1000,
               4002:C'1',4003:1,1000,
               5003:C'1',5004:1,1000)
/*
//CTL2CNTL DD *
 INREC BUILD=(3001:C'2',3002:1,1000,
               4002:C'2',4003:1,1000,
               5003:C'2',5004:1,1000)
/*
//CTL3CNTL DD *
  INREC BUILD=(3001:C'3',3002:1,1000,
               4002:C'3',4003:1,1000,
               5003:C'3',5004:1,1000)
/*
//CTL4CNTL DD *
  OUTFIL FNAMES=OUT123,
    IFOUTLEN=3000,
    IFTHEN=(WHEN=(3001,1,CH,EQ,C'1'),
       OVERLAY=(1:3002,1000),HIT=NEXT),
    IFTHEN=(WHEN=(3001,1,CH,EQ,C'2'),
       OVERLAY=(1001:3002,1000),HIT=NEXT),
    IFTHEN=(WHEN=(3001,1,CH,EQ,C'3'),
       OVERLAY=(2001:3002,1000),HIT=NEXT),
    IFTHEN=(WHEN=(4002,1,CH,EQ,C'2'),
       OVERLAY=(1001:4003,1000),HIT=NEXT),
    IFTHEN=(WHEN=(4002,1,CH,EQ,C'3'),
       OVERLAY=(2001:4003,1000),HIT=NEXT),
    IFTHEN=(WHEN=(5003,1,CH,EQ,C'3'),
       OVERLAY=(2001:5004,1000))
/*


I have also tried
1) writting to separate temp datasets and concatenating and
2) with (MOD,PASS) disposition for temp datasets (See below code)
but still got ICE027A.

Earlier code :
Code:

//T1 DD DSN=&&T1,DISP=(MOD,PASS),UNIT=SYSDA,SPACE=(CYL,(3,3))
//T2 DD DSN=&&T2,DISP=(MOD,PASS),UNIT=SYSDA,SPACE=(CYL,(3,3))
//T3 DD DSN=&&T3,DISP=(MOD,PASS),UNIT=SYSDA,SPACE=(CYL,(3,3))
//T4 DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,PASS)
//   DD DSN=*.T2,VOL=REF=*.T2,DISP=(OLD,PASS)
//   DD DSN=*.T3,VOL=REF=*.T3,DISP=(OLD,PASS)


Not sure why DFSORT is failing when I haven't explicitly specified LRECL for TEMP dataset.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue May 15, 2007 9:31 pm    Post subject:
Reply with quote

You need MOD for //T7 as I had for //T1:

//T7 DD DSN=&&T7,DISP=(MOD,PASS),UNIT=SYSDA,SPACE=(CYL,(5,5))

Make sure you make that change to //T7. You don't need that "earlier code" with the concatenation.

Not having MOD would give you incorrect output, but it wouldn't give you the error you're getting as that indicates the T7 data set has less than 3001 bytes.

In fact, the only reason I can think of for you getting that message is that your site has changed DFSORT's shipped installation default of SOLRF=YES to SOLRF=NO. That's NOT a good idea. But if it's true, then you can add the following to your job to take care of it:

Code:

//DFSPARM DD *
   OPTION SOLRF
/*


If that doesn't fix the problem, then post ALL of your DFSMSG messages.
Back to top
View user's profile Send private message
amar-tayade

New User


Joined: 01 Mar 2007
Posts: 5
Location: Mumbai

PostPosted: Wed Aug 01, 2007 10:58 pm    Post subject: Re: ICETOOL for joining records from different files
Reply with quote

Thanks Frank for the solution. OPTION SOLRF did work for me.
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 Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am


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