Lookup being used on table which gets update in the same job

[sachinnilose]

Hello,

I need to lookup on the same table which is being updated/Inserted in the job. My business requirement can't support me to break the job and do update/Insert in separate job.
What design I should use for achieving the desired result.

1). Is the updated records from source being looked up for next source record.

2). What commit interval I need to set at target.

3). Is there any way I can force Datastage to read uncommitted data at reference link.


I am using Oracle enterprise stage for target and reference lookup with Oracle 10g.

Thanks in advance.

Regards,
Sachin

[chulett]

How can a "business requirement" dictate a job design?

[ray.wurlod]

Resist stupid requirements!

Your professional opinion is that the optimal approach is to separate inserts from updates.

[keshav0307]

why do you need to lookup the same table, which is being insert/update??
why can't directly use UPSERT.

[sachinnilose]

Sorry for not being clear on my requirments.

I need to perform a lookup on the same table which I am Inserting/updating using upsert in the same job.

design is:
source ---> lookup tartget ----->Insert/update target


Say source has 2 records

EMPNO ENAME
7368 DEV
7369 SMITH
7369 SMITH


My requirment is first record should be inserted with an additional column FLAG= 'I'.
If we get another record with the same key it should be updated with the column FLAG = 'U'.

Desired target
EMPNO ENAME FLAG
7368 DEV I
7369 SMITH U


Lookup table desc is:
EMPNO
ENAME
FLAG

The job is not running as expected in the parallel environment as parallel job.
Same job is working fine as a server job.

Sachin

[nishadkapadia]

As suggested earlier by experts it is best advised to split, however
following could achieve the purpose.

You could first identify the Inserts/Updates by doing a look-up,hence
for your example
EMPNO ENAME
7368 DEV (Insert)
7369 SMITH (Insert)
7369 SMITH (insert)

Subsequently, within a transformer you could assign incremental numbers
grouped by employee number which would result as

EMPNO ENAME
7368 DEV (Insert) 1
7369 SMITH (Insert) 1
7369 SMITH (Insert) 2


Identify max for each group and subsequently join with above to isolate
those records and you should have it...

However, would rather go by the suggested advice given earlier

[keshav0307]

My requirment is first record should be inserted with an additional column FLAG= 'I'.
If we get another record with the same key it should be updated with the column FLAG = 'U'.

If the record with EMPNO=7368 is already there in table, it should also be updated??
so it can be :
Desired target
EMPNO ENAME FLAG
7368 DEV U
7369 SMITH U

I believe it can be done without lookup,
identifiy the duplicate key records mark them with 'U' FLAG and keep unique records.
and then user define query in UPSERT Load in the target.

[rameshrr3]

If you have a RAM thats large enough, you can go with looking up and Insert Update to the same target in one job. We have many small diemensions that get loaded this way ( upto 300000 records). You can Lookup from a table and Insert/Update the table in one job, But you cannot read and update/insert in the same job : Got the difference?

[Sainath.Srinivasan]

You can Lookup from a table and Insert/Update the table in one job, But you cannot read and update/insert in the same job

The user wants the currently inserted row available in the lookup whilst referring for next record.

This is different from reading reference into memory.

[ray.wurlod]

Just a thought - there may be a solution using the SCD stage.