Numbering based on Phils questions below...
1. Im not aware of this being available in any previous versions, unless Ray was referring to what I described below. In my experience, DataStage functionality has always improved significantly at each major version release. 2. I suspect the ability to order reference links was more to be consistent with the ability to order output links. I would have thought that ordering reference links which return multiple rows would have an effect on the order the rows are processed (where there is more than one reference link returning multiple rows.) I am only speculating here, I have not yet had the privilege of driving DS 4.0 first hand. 3. There are potential performance benefits in avoiding unnecessary lookups. However, the functionality can still be achieved by including the condition as part of the reference query. At least has to be worth asking for. From what I have seen of the code that DataStage generates, it wouldnt seem too difficult to incorporate into the product (but then Im not a DataStage
engineer.)
4. From version 4 onwards, a reference link CAN return more than one row. I can only speculate on how it actually works (bit Im itching to try it). I would have thought that each row returned from the reference link is processed individually in conjunction with the primary input fields that triggered this reference lookup. 5. Dont know about version 4. In 3.5 there is no way I am aware of to detect if the database rejected the row. The ability to detect this would allow us to make more reliable jobs though. We do as much as we can to ensure we get no referential integrity failures, but sooner or later, you get unexpected database errors over which we have no control.
Oh well, theres my 2 cents worth.
-----Original Message-----
From: Phil Walker [mailto:
philw@gnosys.co.nz]
Sent: Thursday, 9 November 2000 17:56
To:
informix-datastage@oliver.com
Subject: RE: UniVerse Hashed Lookups
We will try that and see if it works. This discussion has raised some questions though.
1. Should this be possible at 4.0? Because, Ray believes it worked prior to 4.0. I have a vague memory or using it. So is this a regression? Or can we expect the behaviour of DataStage to flip-flop from one release to the next?
2. If it was not, then why have the ability to order the reference links? I cannot think of any other reason, which brings me on to constraints.
3. I think having a constraint on a reference would be a nice feature. As depending on a value of a column in the input (or reference link) we may not want to bother doing a lookup. Rather than having to split the input stream before the lookup it could be done in one transform stage.
4. Ray, could you enlighten me, can a reference link return more than 1 row, and if so how do you access the values?
5. Does @rejected return a different value now, depending on if the row was constrained from not going down an output link, or if the row went down a link but was rejected by for example the database server?
-----Original Message-----
From: David Barham [mailto:
David.Barham@Anglocoal.com.au]
Sent: Thursday, 9 November 2000 20:01
To:
informix-datastage@oliver.com
Subject: RE: UniVerse Hashed Lookups
Something to try.
Use the capability of DS 4 to set the order of the reference links.
In the key expressions of the reference links, make references to fields in prior reference links. You will have to type them manually. The expression builder wont do it for you. (Neither will dragging).
The definition will remain red, but dont worry about that.
Now compile the job. Im guessing that if you put the references in correctly, the job will compile without errors.
Now, here comes the real test. Will the job run? Only you can tell me this.
What do you think?
I dont know if this will work. I do know that everything I have said up to but not including actually running the job does work. I have a job which does something very similar to this. ie apparently has errors, but still compiles and runs correctly/as designed.
If I had more time (or a developer copy of DataStage at home?? ), I would test it for you. But then, maybe it is better if I
hint>share
the fun.
-----Original Message-----
From: Ray Wurlod [SMTP:
ray.wurlod@informix.com]
Sent: Thursday, November 09, 2000 4:31 PM
To:
informix-datastage@oliver.com
Subject: RE: UniVerse Hashed Lookups
Whoa!! It has been pointed out to me that I misread the original note. It is NOT possible to do what was specified (use the results from one reference link in a subsequent reference input link) in a single Transformer stage at release 4.0. (IIRC it was possible prior to release 4.0.) The model I had had in mind used two consecutive Transformer stages referencing the same hashed file (to do a reflexive join, effectively). This is OK.
> ----------
> From: Ray Wurlod[SMTP:
ray.wurlod@informix.com]
> Reply To:
informix-datastage@oliver.com
> Sent: Thursday, 9 November 2000 15:36
> To:
informix-datastage@oliver.com
> Subject: RE: UniVerse Hashed Lookups
>
> It is supposed to work, and it does on Windows NT. Have you sought
> help from Informix support?
>
> Constraints on reference input links will never, IMHO, appear in the
> product, since constraint expressions are a property of output links.
>
> > ----------
> > From: Kuan, Hong (Forest Manukau)[SMTP:
Hong.Kuan@chh.co.nz]
> > Reply To:
informix-datastage@oliver.com
> > Sent: Thursday, 9 November 2000 14:40
> > To:
informix-datastage@oliver.com
> > Subject: UniVerse Hashed Lookups
> >
> > DataStage 4.0. Solaris 2.6.
> >
> > We have a transform stage. This stage has one input link and two
> > (or more reference links). We do a lookup on the 1st reference link
> > using information from the input link. We want to be able to use
> > information from the 1st reference link to then do a lookup of the
> > 2nd reference link (
> and
> > so on). This does not seem to work. Should it? If it does not, I
> > think
> it
> > would be a nice feature, as well as having constraints on the
> > reference links! We are being forces to link transform stages to do
> > this lookup on
> a
> > lookup type scenario, or to stage data more than once to fit the way
> this
> > works.
> >
> > If this is not suppose to work, what is the point of having the
> > ability to order the execution of reference links as I cannot see
> > why
> you
> > would want to do this? I would be open to suggestions.
> >
> > The team at CHHF.
> >
> >
>
*************************************************************************
This e-mail and any files transmitted with it may be confidential and are intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error, please notify the sender by return e-mail, and delete this e-mail from your in-box. Do not copy it to anybody else
*************************************************************************