Complex join with DFSORT/ICETOOL ?

Searchman · New User Joined: 28 Dec 2006 Posts: 80 Location: France

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 !

Frank Yaeger · Posted: Sat Feb 17, 2007 10:08 pm

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.

Searchman · New User Joined: 28 Dec 2006 Posts: 80 Location: France

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)

dick scherrer · Posted: Sun Feb 18, 2007 5:03 am

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.

Searchman · New User Joined: 28 Dec 2006 Posts: 80 Location: France

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

dick scherrer · Posted: Sun Feb 18, 2007 10:05 pm

Hello,

I'm certain that it was completely clear - to you

Searchman · New User Joined: 28 Dec 2006 Posts: 80 Location: France

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 ?

Frank Yaeger · Posted: Mon Feb 19, 2007 10:37 pm

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.