Look Up Without Keys
Moderators: chulett, rschirm, roy
Look Up Without Keys
Hi,
Is it possible to have a job design (running) in DataStage 5.x version where we have a source database stage and a look up database stage connected to a transformer without any keys defined for look up?
Here some columns are being fetched from the source table and some others are being fetched from the look up table but they have no common key columns.
This design is not functional on DatStage 7.x version.
But I have this code in 5.x which I am supposed to migrate to 7.x.
I have no access to a 5.x server to check this.
Can anyone help me on this and let me know if this design is functional, then how are the records fetched? ( Any cross product or sth similar takes place?)
Thanks in advance for your help and time
Is it possible to have a job design (running) in DataStage 5.x version where we have a source database stage and a look up database stage connected to a transformer without any keys defined for look up?
Here some columns are being fetched from the source table and some others are being fetched from the look up table but they have no common key columns.
This design is not functional on DatStage 7.x version.
But I have this code in 5.x which I am supposed to migrate to 7.x.
I have no access to a 5.x server to check this.
Can anyone help me on this and let me know if this design is functional, then how are the records fetched? ( Any cross product or sth similar takes place?)
Thanks in advance for your help and time
Are you sure you're looking at the job correctly? It's impossible to not have a WHERE clause on a lookup. With multi-row enabled, your result is that every primary input stream row will produce as many output rows as returned in the lookup. Without a where clause, you get a cartesian product. Without multi-row enabled, only the first row in the lookup set is used.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
There really isn't anything like a 'lookup without keys'. If you are planning on joining tables then columns not in your where clause - i.e. used to join the tables - could be keys. The only time I've done something without keys was when the 'lookup' returned something regardless of the input row - specifically a Oracle sequence fetch from nextval() and dual.
Can you give an example of the join you are planning?
ps. If you can do it in 5.x you can do it in 7.x - and you can import the 5.x job into your 7.x server to examine it.
Can you give an example of the join you are planning?
ps. If you can do it in 5.x you can do it in 7.x - and you can import the 5.x job into your 7.x server to examine it.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
It is practically impossible to create a lookup in a server job without at least one key.
The reason is that the key expression associated with the key has to be evaluated as part of triggering the lookup operation (which, for example, does not occur if the expression evaluates to NULL).
The reason is that the key expression associated with the key has to be evaluated as part of triggering the lookup operation (which, for example, does not occur if the expression evaluates to NULL).
Last edited by ray.wurlod on Tue Dec 26, 2006 6:05 pm, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Not impossible at all, just not generally practical. As I noted, I've created a 'keyless lookup' only for doing a 'select xxxxx.nextval() from dual' statement. Each row gets one with no key expression needed or desired. Other than something like that, however, I agree with you...
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thanks all for your reply.....
But this job design I have got is functional on DataStage 5.1.
But when I am trying to run the same job on DataStage 7.5.1 server,it is aborting with the error:
"NPRGMPCK5_UPGRADE_COPY..SIXJOLOF: No primary key column(s) defined for reference input. If the lookup table has no key column(s), use the 'alter table' SQL command to add a primary key or unique constraint, and specify the key column(s) in the DataStage column definitions. "
The job design is as:
SOURCE TABLE (1)___________>TRANSFORMER__________->TARGET TABLE
^
|
|
|
|
LOOK UP TABLE(2)(No Keys Specified)
(The Look Up table is connected to the transformer)
Here the look up table query is: "SELECT SQXJOPRG.NEXTVAL FROM DUAL" like what Craig has replied.
Do I have some way of maintaining this design and making the code runnable on DataStage 7.5.
Note: I can not include this query in the Source Table SQL because of some design and transformation reasons.
But this job design I have got is functional on DataStage 5.1.
But when I am trying to run the same job on DataStage 7.5.1 server,it is aborting with the error:
"NPRGMPCK5_UPGRADE_COPY..SIXJOLOF: No primary key column(s) defined for reference input. If the lookup table has no key column(s), use the 'alter table' SQL command to add a primary key or unique constraint, and specify the key column(s) in the DataStage column definitions. "
The job design is as:
SOURCE TABLE (1)___________>TRANSFORMER__________->TARGET TABLE
^
|
|
|
|
LOOK UP TABLE(2)(No Keys Specified)
(The Look Up table is connected to the transformer)
Here the look up table query is: "SELECT SQXJOPRG.NEXTVAL FROM DUAL" like what Craig has replied.
Do I have some way of maintaining this design and making the code runnable on DataStage 7.5.
Note: I can not include this query in the Source Table SQL because of some design and transformation reasons.
What stage are you using for the reference lookup? I'll be the first to admit I haven't done the keyless lookup thing recently, and I don't ever recall dealing with a message like that before. Perhaps that's something new with 7.5.x...
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
You could always give it a fake key, a hard-coded value that you ignore. Add an extra column to the stage, mark it as a key and then ignore it (except to select back into it) in your user-defined sql. Something like...
Stick anything in the Key Expression of the lookup. Make sure you've got a lovely little annotation on the canvas explaining all this.
Edited to add: If you are using an OCI stage, it will want the key field bound. Pass an 'X' in as the Key Expression and then try this as the sql:
The where clause will resolve to " where 'X' = 'X' " which doesn't change the outcome (as long as you pass in the right value!) and satisfies the parser.
Code: Select all
SELECT 'X', SQXJOPRG.NEXTVAL FROM DUAL
Edited to add: If you are using an OCI stage, it will want the key field bound. Pass an 'X' in as the Key Expression and then try this as the sql:
Code: Select all
SELECT 'X', SQXJOPRG.NEXTVAL FROM DUAL WHERE 'X' = :1
Last edited by chulett on Thu Dec 28, 2006 9:07 am, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
The transformer stage has been used for implementing the keyless look up logic.
P.S. This logic has been implemented in 5.x version, not in 7.5.
I have got this code for migration to 7.5.1 and it is failing there.
I was just wondering how 5.x was handling it and how the job can be modified to work fine in 7.5.1.
P.S. This logic has been implemented in 5.x version, not in 7.5.
I have got this code for migration to 7.5.1 and it is failing there.
I was just wondering how 5.x was handling it and how the job can be modified to work fine in 7.5.1.
No, I meant which database stage are you using for the lookup. And I wouldn't burn alot of brain cells wringing my hands and asking why, let's just get it working now, k?
Read my previous post again as it has undergone a little editing.
Read my previous post again as it has undergone a little editing.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
1. To correctly answer your question, I am using the DB2 API Stage.
2. I would like to share some of our project details:
a. Its a project done on 5.1 with around 500 jobs. Each of these jobs has a keyless look up and the information we have got is it is a working code on 5.1.
b. We have to migrate it to 7.5.1 now in minimum possible time.
c. It is not going well with the team that we need to redesign it for 7.5.1.
d. We were thinking if we could get a patch or some fast solution for making these jobs work.
In case we don't get anything, we will have to invest quite a deal of man hours on this.
But still hoping to find some easier solution as DS 5.1 supported keyless look up...
2. I would like to share some of our project details:
a. Its a project done on 5.1 with around 500 jobs. Each of these jobs has a keyless look up and the information we have got is it is a working code on 5.1.
b. We have to migrate it to 7.5.1 now in minimum possible time.
c. It is not going well with the team that we need to redesign it for 7.5.1.
d. We were thinking if we could get a patch or some fast solution for making these jobs work.
In case we don't get anything, we will have to invest quite a deal of man hours on this.
But still hoping to find some easier solution as DS 5.1 supported keyless look up...
Ok, can't help you with the DB2 stage but perhaps someone else can. Keep in mind that if the 'minimum possible time' involves tweaking those 500 jobs then include that effort in your estimation of the 'minimum'. Sometimes you gotta do what you gotta do, even if it doesn't 'go well' with the team.
As to the patch question, this isn't the place. Contact your Support provider, explain your situation and see what they can do to help. That help may even be the patch you seek, who knows?
As to the patch question, this isn't the place. Contact your Support provider, explain your situation and see what they can do to help. That help may even be the patch you seek, who knows?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers