Lookup being used on table which gets update in the same job
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 12
- Joined: Wed Jul 29, 2009 2:47 am
- Location: Germany
Lookup being used on table which gets update in the same job
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Premium Member
- Posts: 12
- Joined: Wed Jul 29, 2009 2:47 am
- Location: Germany
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
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
-
- Charter Member
- Posts: 47
- Joined: Fri Mar 18, 2005 5:59 am
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
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
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
If the record with EMPNO=7368 is already there in table, it should also be updated??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'.
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.
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?
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: