Here is my scenario.
ODBC1 --> recordset having 2 columns (reg_id, reg_number)
ODBC2 --> recordset having 1 column (reg_number)
I want to delete all the records in ODBC1 whose reg_number is not found in ODBC2.
ODBC1 and ODBC2 are both Sql Server but different databases.
I wrote a delete Stored procedure which takes reg_id as input.
Now all I have to do is pass the "reg_id" whose reg_number is not found to the other side of transform.
ODBC1 ---- IN ---- Transform ---OUT --- ODBC1(Delete Stored procedure)
ODBC2 -------IN1-------(same transform)
Now if I use constraint IN1.NOTFOUND its not working.
Please help!!!!!!!!!!
Deleting Records - When Not found - Datastage Job - help pls
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
Hi Ramesh,
I'm not able to understand clearly how you designed your job. But I understand your problem.
It is a good way to achieve this by using the ODBC2 as a lookup table.
You can load this ODBC2 data into a hash file for faster retrieval of data.
For Ex:
HashFile(ODBC2 data)
|
|
|
ODBC1 -------> Transformer -------> Output table or file
In the transformer, make sure you use the InLink.NOTFOUND.
Hope that helps,
Let me know if you have any questions,
Naveen.
I'm not able to understand clearly how you designed your job. But I understand your problem.
It is a good way to achieve this by using the ODBC2 as a lookup table.
You can load this ODBC2 data into a hash file for faster retrieval of data.
For Ex:
HashFile(ODBC2 data)
|
|
|
ODBC1 -------> Transformer -------> Output table or file
In the transformer, make sure you use the InLink.NOTFOUND.
Hope that helps,
Let me know if you have any questions,
Naveen.
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
Sorry about the typo, the job will look like this,
Thanks,
Naveen.
Code: Select all
HashFile(ODBC2 data)
|
|
|
ODBC1 -------> Transformer -------> Output table or file
Naveen.
Thanks Naveen
Hi Naveen,
I just figured out myself and came here to saw the same logic you mentioned here. Anyways thanks a bunch pal.
Both constraints IN1.NOTFOUND and ISNULL(IN1.reg_number) are working.
But both of them are not working directly on ODBC2. I had to change the ODBC2 to HASHED_ODBC2.
Just curious is there a way to directly do it? I know its advantageous to do HASHED way because of performance boost, But curious to know whether its possible or not.
Thanks Guys.
I just figured out myself and came here to saw the same logic you mentioned here. Anyways thanks a bunch pal.
Both constraints IN1.NOTFOUND and ISNULL(IN1.reg_number) are working.
But both of them are not working directly on ODBC2. I had to change the ODBC2 to HASHED_ODBC2.
Just curious is there a way to directly do it? I know its advantageous to do HASHED way because of performance boost, But curious to know whether its possible or not.
Thanks Guys.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The output "table or file" is the source table ODBC1. User-defined SQL is of the form
You might like to use an intermediate text file so that all rows are selected from ODBC1 before you start deleting rows, and therefore don't get locking issues. The file needs only the key column(s) in it.
Code: Select all
DELETE FROM tablename WHERE keycol = ?
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.