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.
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.
>
>
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.
>
>
Is this a new feature in version 4? In version 3.5, no reference key expressions can reference other reference links. I realise that controlling the order of the links is now explicit in 4, so I can see that referencing "prior" reference links could now make sense.
*sigh* yet another reason to upgrade to 4.
-----Original Message-----
From: Ray Wurlod [SMTP:ray.wurlod@informix.com]
Sent: Thursday, November 09, 2000 2:36 PM
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
*************************************************************************
*sigh* yet another reason to upgrade to 4.
-----Original Message-----
From: Ray Wurlod [SMTP:ray.wurlod@informix.com]
Sent: Thursday, November 09, 2000 2:36 PM
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
*************************************************************************
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.
> >
> >
>
> ----------
> 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.
> >
> >
>
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
*************************************************************************
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
*************************************************************************
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
*************************************************************************
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
*************************************************************************
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
*************************************************************************
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
*************************************************************************
David,
What you describe below does work. I have been using this logic since DataStage 1.0 the key is making sure the order of the references occur correctly. with prior to 4.0 of you could manipulate the order that the references occured by renaming the ref links knowing that when you renamed a link it got pushed to the last reference to perform. Now 4.0 has made it easier.
Actually in 1.0 the reference link columns were available in the drop down for the keys. Still would not draw a line though.
Rick Schirm
-----Original Message-----
From: David Barham [mailto:David.Barham@Anglocoal.com.au]
Sent: Thursday, November 09, 2000 1:01 AM
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
*************************************************************************
What you describe below does work. I have been using this logic since DataStage 1.0 the key is making sure the order of the references occur correctly. with prior to 4.0 of you could manipulate the order that the references occured by renaming the ref links knowing that when you renamed a link it got pushed to the last reference to perform. Now 4.0 has made it easier.
Actually in 1.0 the reference link columns were available in the drop down for the keys. Still would not draw a line though.
Rick Schirm
-----Original Message-----
From: David Barham [mailto:David.Barham@Anglocoal.com.au]
Sent: Thursday, November 09, 2000 1:01 AM
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
*************************************************************************
I will answer the questions that I do know.
1. This still works in 4.0 of DataStage
2. The reason for the ordering of the reference links was an easy add in when doing the output row ordering. And all of the original Ardent DataStage SEs and former (myself) are happy that this is there because of the reference to reference lookups. 3. I believe that a closer term to use would be a reference condition for a reference link. If a calculated variable(column) has this condition then perform the reference. This is something that has been brought up to engineering before. 4. The reference link returning more than a 1 row result set is only available in the ODBC and the UVTable Stages. The way that this works is one row in the primary performs a reference and there are X(12) rows in the reference result set then X(12) rows of data are processed. I have not tested to see what happens if there is more than one reference returning multi row result sets. By logic it may return an outer join result set but do not know. 5. Do not Know.
Rick Schirm
-----Original Message-----
From: Phil Walker [mailto:philw@gnosys.co.nz]
Sent: Thursday, November 09, 2000 1:56 AM
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
*************************************************************************
1. This still works in 4.0 of DataStage
2. The reason for the ordering of the reference links was an easy add in when doing the output row ordering. And all of the original Ardent DataStage SEs and former (myself) are happy that this is there because of the reference to reference lookups. 3. I believe that a closer term to use would be a reference condition for a reference link. If a calculated variable(column) has this condition then perform the reference. This is something that has been brought up to engineering before. 4. The reference link returning more than a 1 row result set is only available in the ODBC and the UVTable Stages. The way that this works is one row in the primary performs a reference and there are X(12) rows in the reference result set then X(12) rows of data are processed. I have not tested to see what happens if there is more than one reference returning multi row result sets. By logic it may return an outer join result set but do not know. 5. Do not Know.
Rick Schirm
-----Original Message-----
From: Phil Walker [mailto:philw@gnosys.co.nz]
Sent: Thursday, November 09, 2000 1:56 AM
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
*************************************************************************
Actually I had to go back and think there actually was not any lines to be had until release 3 oh well I guess I have tried to push the early look and feels of the product away.
-----Original Message-----
From: Rick Schirm [mailto:rick@schirms.net]
Sent: Thursday, November 09, 2000 7:49 AM
To: informix-datastage@oliver.com
Subject: RE: UniVerse Hashed Lookups
David,
What you describe below does work. I have been using this logic since DataStage 1.0 the key is making sure the order of the references occur correctly. with prior to 4.0 of you could manipulate the order that the references occured by renaming the ref links knowing that when you renamed a link it got pushed to the last reference to perform. Now 4.0 has made it easier.
Actually in 1.0 the reference link columns were available in the drop down for the keys. Still would not draw a line though.
Rick Schirm
-----Original Message-----
From: David Barham [mailto:David.Barham@Anglocoal.com.au]
Sent: Thursday, November 09, 2000 1:01 AM
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
*************************************************************************
-----Original Message-----
From: Rick Schirm [mailto:rick@schirms.net]
Sent: Thursday, November 09, 2000 7:49 AM
To: informix-datastage@oliver.com
Subject: RE: UniVerse Hashed Lookups
David,
What you describe below does work. I have been using this logic since DataStage 1.0 the key is making sure the order of the references occur correctly. with prior to 4.0 of you could manipulate the order that the references occured by renaming the ref links knowing that when you renamed a link it got pushed to the last reference to perform. Now 4.0 has made it easier.
Actually in 1.0 the reference link columns were available in the drop down for the keys. Still would not draw a line though.
Rick Schirm
-----Original Message-----
From: David Barham [mailto:David.Barham@Anglocoal.com.au]
Sent: Thursday, November 09, 2000 1:01 AM
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
*************************************************************************