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
 

 

Complex join with DFSORT/ICETOOL ?

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

New User


Joined: 28 Dec 2006
Posts: 80
Location: France

PostPosted: Sat Feb 17, 2007 5:21 pm    Post subject: Complex join with DFSORT/ICETOOL ?
Reply with quote

Good Afternoon,

This is my problem,

One file F1 in input :
Job1 R1 S
Job2 R1 S
Job3 R1 S
Job4 R1 E
Job5 R1 E
Job6 R2 S
Job4 R2 E
...

I want one file F2 in output like this :
Job4 Job1 R1
Job4 Job2 R1
Job4 Job3 R1
Job5 Job1 R1
Job5 Job2 R1
Job5 Job3 R1
Job4 Job6 R2
...

Explanation : Rn is a file in a job, in output if "S", in input if "E"
The file F2 gives the dependances between jobs ; ex : Job4 (as JOB5) has predecessors Job1, 2, 3 for R1 and predecessor Job6 for R2.

Is it too harsh to do to DFSORT/ICETOOL ?

Thank you for your help !
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: Sat Feb 17, 2007 10:08 pm    Post subject:
Reply with quote

I can't figure out what "rules" you want to use to go from input to output. It looks like 'S' and 'E' and the order of the records has something to do with it, but it's not clear exactly how it should work. Please explain the "rules" more clearly and in more detail.
Back to top
View user's profile Send private message
Searchman

New User


Joined: 28 Dec 2006
Posts: 80
Location: France

PostPosted: Sun Feb 18, 2007 12:05 am    Post subject: Re: Complex join with DFSORT/ICETOOL ?
Reply with quote

OK, 'E' and 'S' correspond respectively, in french, to I(nput) and O(utput) in english.

When Jobx has Rn with associated 'S' (=output) it means Rn is a file in ouput of jobx and if this same Rn is present in Joby but with 'E' (=input) then Joby depends of Jobx by Rn, so I will get in the result file :
Jobx Joby Rn
(Jobx depends of Joby by Rn or Joby predecessor of Jobx by Rn)

Thus, in my previous post,
Job4 has three dependences on R1 with Job1, Job2 and Job3
idem for Job5.

But Job4 has also one dependence on R2 with Job6, therefore I will write
Job4 Job6 R2


The goal of the procedure is to deduce dependences from CORTEX and compare them with an extract of OPC which is as follow :
Jobx Joby (Joby predecessor of Jobx).

An other example in different order :

F1 (input)
Job1 R1 E
Job1 R2 E
Job1 R3 E
Job1 R4 E
Job2 R1 E
Job3 R1 S
Job4 R3 S
Job4 R4 S
Job5 R5 S

F2 (output)
Job1 Job3 R1
Job2 Job3 R1
Job1 Job4 R3
Job1 Job4 R4

Rmks :
1- Job1 has one dependence with Job3 (R1) and two dependences with Job4 (R3, R4)
2- Job3 is predecessor of two jobs : Job1 and Job2
3- R2 is associated with 'E' (Job1) but not with 'S', that's why R2 doesn't appear in F2
4- R5 is associated with 'S' (Job5) but not with 'E', that's why R5 doesn't appear in F2 (and then Job5)
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Feb 18, 2007 5:03 am    Post subject:
Reply with quote

Hello,

Is the newest example accurate? How is "Job3 R1 S" be identified as having a dependancy on "Job1 R1 E" and "Job2 R1 E"?

If the "E" entries are input and the "S" entries are output, is Job3 really a predecessor to Job1 and Job2 or is it a successor?

Please post some pseudo-code that demonstrates the logic needed to accomplish what you want? At this point the requirement needs to be better understood.
Back to top
View user's profile Send private message
Searchman

New User


Joined: 28 Dec 2006
Posts: 80
Location: France

PostPosted: Sun Feb 18, 2007 2:00 pm    Post subject: Re: Complex join with DFSORT/ICETOOL ?
Reply with quote

I thought it was clear now, but let's go on

Suppose you load the flat file F1 in a DB2 table called Job_res
with 3 columns : Job, Res and Es

One SQL request can produce the result file F2

Select a.Job, b.Job, b.Res /* job, job_predecessor, resource
from Job_res a, Job_res b /* self join
where
a.Res=b.Res
and
a.Es = 'E'
and
b.Es = 'S'

To take the last example, Job3 is obviously a predecessor of Job1 and Job2 on R1 :
Job3, Job2, and Job1 have the same resource R1
R1 for Job3 = 'S' (b.Es = 'S')
R1 for Job1 and Job2 = 'E' (a.Es = 'E')

Then, part of the SQL result is:
a.Job, b.Job, b.Res = Job1, Job3, R1
a.Job, b.Job, b.Res = Job2, Job3, R1
(...)
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Feb 18, 2007 10:05 pm    Post subject:
Reply with quote

Hello,

I'm certain that it was completely clear - to you icon_smile.gif
Quote:
I thought it was clear now,

and
Quote:
obviously a predecessor

but not to everyone else.

Now that you've posted an sql solution it is more clear.

Why not just load F1 into a (temporary?) table and run the code you posted? You could actually write an F2 file or include the code that would have been used to process the F2 data.
Back to top
View user's profile Send private message
Searchman

New User


Joined: 28 Dec 2006
Posts: 80
Location: France

PostPosted: Sun Feb 18, 2007 11:44 pm    Post subject: Re: Complex join with DFSORT/ICETOOL ?
Reply with quote

Hello,

Yes, what you propose can be done but :
1- I don't master DB2 with JCL (I just know SQL language), so I'd rather do it with an Icetool
2- I've recently found out the powerful of SPLICE and I wonder where is the limit of DFSORT/ICETOOL

To come back to the problem, I think the difficulty occurs when there are at less two predecessors for a job. I explain for resource R1

in the previous example:
F1 (input)
Job1 R1 E
Job1 R2 E
Job1 R3 E
Job1 R4 E
Job2 R1 E
Job3 R1 S
Job4 R3 S
Job4 R4 S
Job5 R5 S

I think F1 sorted like this (S first)
Job3 R1 S
Job1 R1 E
Job2 R1 E
a splice + overlay + outrec may produce the waited output
Job1 Job3 R1
Job2 Job3 R1
(splice on R1 + splice and overlay to move Job3 in 2nd and 3rd rec + omit to eliminate record 'S')

But, it becomes more complex if in F1 for R1 I have another job (Job6) with R1/S, like this :
Job6 R1 S
Job3 R1 S
Job1 R1 E
Job2 R1 E
in F2 I would like :
Job1 Job6 R1
Job2 Job6 R1
Job1 Job3 R1
Job2 Job3 R1

In fact, how the SPLICE can generate twice record Job1 and twice record Job2 ?
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: Mon Feb 19, 2007 10:37 pm    Post subject:
Reply with quote

I'm going to pass on this one. I still don't understand the "rules" and the SQL pseudocode doesn't help me since I don't know SQL. I just don't have the time to try to understand what you want and figure out how to do it, if in fact it can be done. Sorry.
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 Using ICETOOL, how we can seperate th... bshkris SYNCSORT 5 Tue May 09, 2017 8:33 pm
No new posts Group elements using ICETOOL Learncoholic DFSORT/ICETOOL 2 Fri Apr 14, 2017 11:01 am
No new posts DFSORT - Overriding default option TU... Steve Ironmonger DFSORT/ICETOOL 3 Tue Apr 04, 2017 3:54 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts DFSORT MUL FIELDS tspr52 DFSORT/ICETOOL 16 Fri Mar 03, 2017 11:53 pm


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