Deleting Records - When Not found - Datastage Job - help pls

[mkramesh]

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

[Luk]

you can set stream from ODBC2 as lookup to ODBC1 and in constraint you can pass only those records which do not have references in ODBC2

[I_Server_Whale]

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_Server_Whale]

Sorry about the typo, the job will look like this,

Code: Select all

                      HashFile(ODBC2 data) 
                                | 
                                | 
                                | 
             ODBC1 -------> Transformer -------> Output table or file 

Thanks,
Naveen.

[mkramesh]

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.

[ray.wurlod]

The output "table or file" is the source table ODBC1. User-defined SQL is of the form

Code: Select all

DELETE FROM tablename WHERE keycol = ?

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.