All , I have a question regarding loading and deleting from a same table.
Initial job design,
File-Tranformer-lookup-Join-DB2Connector.
Data looks like,
ID, NAME,START,END,STATUS
1234,John Smith,01/01/2015,12/31/2016,Pending
1234,John Smith,04/17/2015,12/31/2016,Pending
Now, as first record is the old record, I want to delete this record after loading in DB2 Connector stage. So, I can use a logic that of START of second row is >than previous then delete the record.
Any thoughts how to achieve this, Can we achieve this in a single job.
Thanks.
DELETE MULTIPLE RECORD AFTER DB2 STAGE
Moderators: chulett, rschirm, roy
How about some details...
This "old" record, it is already there when you load the new one? If so, will there only ever be one old record in the table when you insert a new version? I'm asking because it sounds like you may be able to use the Delete then Insert write mode property:
Delete then insert: Replaces data in an existing table; runs the DELETE statement first, and then runs the INSERT statement.
If that ID is unique, you could key off that.
This "old" record, it is already there when you load the new one? If so, will there only ever be one old record in the table when you insert a new version? I'm asking because it sounds like you may be able to use the Delete then Insert write mode property:
Delete then insert: Replaces data in an existing table; runs the DELETE statement first, and then runs the INSERT statement.
If that ID is unique, you could key off that.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: DELETE MULTIPLE RECORD AFTER DB2 STAGE
You mean logical delete or that record will be deleted from the table?sam334 wrote:I want to delete this record after loading in DB2 Connector stage. So, I can use a logic that of START of second row is >than previous then delete the record.
Thanks.
Thanks
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
There are things you can do before even sending the record to DB2 Connector. You can restrict that record in your job itself. I think if you sort on key and start and take the last record. Pass it through the lookup to get start of the record already there in the data base and if new record has start greater that previous, send it to DB2 in upsert mode else just drop it.
I prefer to keep history in Data warehouse and perform logical delete in there. But all depends on requirement.
I prefer to keep history in Data warehouse and perform logical delete in there. But all depends on requirement.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
OK. Rather than load both / everything and then go back and delete the ones you didn't really want, why not just load the ones you do want and ignore the rest? Seem like that would be a simple matter of making sure the records are sorted appropriately and then using a Remove Duplicates stage... unless I'm missing something.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers