Look Up Without Keys

Deepa · Post by **Deepa** » Tue Dec 26, 2006 8:52 am

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

kcbland · Post by **kcbland** » Tue Dec 26, 2006 8:59 am

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.

chulett · Post by **chulett** » Tue Dec 26, 2006 9:02 am

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.

ray.wurlod · Post by **ray.wurlod** » Tue Dec 26, 2006 3:27 pm

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

chulett · Post by **chulett** » Tue Dec 26, 2006 5:30 pm

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

kumar_s · Post by **kumar_s** » Tue Dec 26, 2006 5:55 pm

Hi,
You may not have 5.x server, but if you can check the key and mapping of the transformer in 7x if imported correctly and not changed after that.

Deepa · Post by **Deepa** » Thu Dec 28, 2006 1:32 am

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.

chulett · Post by **chulett** » Thu Dec 28, 2006 8:59 am

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

chulett · Post by **chulett** » Thu Dec 28, 2006 9:03 am

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

Code: Select all

SELECT 'X', SQXJOPRG.NEXTVAL FROM DUAL

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:

Code: Select all

SELECT 'X', SQXJOPRG.NEXTVAL FROM DUAL WHERE 'X' = :1

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.

Deepa · Post by **Deepa** » Thu Dec 28, 2006 9:07 am

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.

chulett · Post by **chulett** » Thu Dec 28, 2006 9:10 am

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.

Deepa · Post by **Deepa** » Thu Dec 28, 2006 9:36 am

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

chulett · Post by **chulett** » Thu Dec 28, 2006 9:45 am

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?