DSXchange

Hi

I have job requirement in which i have to delete some rows in the target before inserting into the target table depending on combination of key and nonkey coloumns.

in target i have 22 coloumns in which 3 are key coloumns.i have to delete the rows in the target depending on 4 coloumns of which 2 are key coloumns and 2 are non key coloumns.

My input and output are DB2 tables.any solutions please..

Don't confuse 'key columns' with indexes or indexed columns. Mark as a 'keys' the columns needed to drive the deletes, i.e. be included in the where clause.

Hi Craig,

I did what you said, i have marked them as keys in the output DB2 stage and wrote a custom sql statement

Delete from <tablename> where col =? Colb = 2008 and colc = 'c' and cold='d'.

but it still not deleting those rows ..

thanks

hi,
is your custom sql able to delete rows outside datastage.
i.e using sqlplus or TOAD.

Ketfos

I have job requirement in which i have to delete some rows in the target before inserting into the target table depending on combination of key and nonkey coloumns.

in target i have 22 coloumns in which 3 are key coloumns.i have to delete the rows in the target depending on 4 coloumns of which 2 are key coloumns and 2 are non key coloumns.

My input and output are DB2 tables.any solutions please..[/quote]

So, only one column actually supplies values to the where clause and the other three values are hard-coded? Then you only need one key column. The number and order of the key columns must match the "?" bind parameters used in the sql.

Hi Ketfos i can delete them using my sql tool outside datastage.

Thanks

ketfos wrote:hi,
is your custom sql able to delete rows outside datastage.
i.e using sqlplus or TOAD.

Ketfos

I have job requirement in which i have to delete some rows in the target before inserting into the target table depending on combination of key and nonkey coloumns.

in target i have 22 coloumns in which 3 are key coloumns.i have to delete the rows in the target depending on 4 coloumns of which 2 are key coloumns and 2 are non key coloumns.

My input and output are DB2 tables.any solutions please..

[/quote]

Hi,
Can share the datastage job log?

Ketfos

"Share" in what sense? It's a database table in the DataStage Repository database.

can any one say how can i do it.I did as craig said by marking them as key coloumns but when i hard code them to the values the job aborting,

Please can any one say how to delete the rows in the output table before inserting new rows.these rows sholud be deleted regardless of what incoming data is.

Rows should be deleted an coloumn values where all of them are not keys.

Thanks

Hi

In your input to the DB-->SQL-->Before

You can write the custom SQL which will trigger first before the records start to insert.

Regards
Ram

dsdevper wrote:can any one say how can i do it.I did as craig said by marking them as key coloumns but when i hard code them to the values the job aborting,

Please can any one say how to delete the rows in the output table before inserting new rows.these rows sholud be deleted regardless of what incoming data is.

Rows should be deleted an coloumn values where all of them are not keys.

Thanks

Hi Sura,

I have implimented what you told its not working,its not deleting rows in the target table before inserting.

Thanks

Hi

Check the log, for any error message / info?

And also check the privilage for that specific user which is beeing used via DS.

Regards
Ram

dsdevper wrote:Hi Sura,

I have implimented what you told its not working,its not deleting rows in the target table before inserting.

Thanks

And also give commit after the DDL stagement.

Ram

SURA wrote:Hi

Check the log, for any error message / info?

And also check the privilage for that specific user which is beeing used via DS.

Regards
Ram

dsdevper wrote:Hi Sura,

I have implimented what you told its not working,its not deleting rows in the target table before inserting.

Thanks

sorry ram ,

i didnt get you.please can you tell me how to do it...

Thanks

Sorry may be i am confusing you..
i am giving an example..
please help me.

input data:

c0l1|col2|col3|col4
----------------------
111|2008|C|D
112|2008|C|D
113|2008|C|D


output table(existing) (col1,col2,col3 are keys)

col1|col2|col3|col4
----------------------
222|1985|A|B
223|2008|C|D ( IT SHOULD BE DELETED)
224|2008|C|D (IT SHOULD BE DELETED)
225|2008|C|B

Here i have to delete coloumns where col2 = 2008 and col3 = C and col4 = D from output table before inserting the input table data

So my desired output should be

col1|col2|col3|col4
---------------------
111|2008|C|D
112|2008|C|D
113|2008|C|D
222|1985|A|B
225|2008|C|B