Clear the rows before inserting depending on non key coloumn

budde43 · Post by **budde43** » Mon Oct 06, 2008 1:58 pm

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

chulett · Post by **chulett** » Mon Oct 06, 2008 2:19 pm

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.

budde43 · Post by **budde43** » Mon Oct 06, 2008 2:35 pm

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

ketfos · Post by **ketfos** » Mon Oct 06, 2008 2:41 pm

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]

chulett · Post by **chulett** » Mon Oct 06, 2008 2:44 pm

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.

dsdevper · Post by **dsdevper** » Mon Oct 06, 2008 3:00 pm

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]

ketfos · Post by **ketfos** » Mon Oct 06, 2008 4:48 pm

Hi,
Can share the datastage job log?

Ketfos

ray.wurlod · Post by **ray.wurlod** » Mon Oct 06, 2008 5:23 pm

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

dsdevper · Post by **dsdevper** » Mon Oct 06, 2008 6:44 pm

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

SURA · Post by **SURA** » Mon Oct 06, 2008 7:01 pm

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

dsdevper · Post by **dsdevper** » Mon Oct 06, 2008 7:50 pm

Hi Sura,

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

Thanks

SURA · Post by **SURA** » Mon Oct 06, 2008 7:56 pm

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

SURA · Post by **SURA** » Mon Oct 06, 2008 8:04 pm

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

dsdevper · Post by **dsdevper** » Mon Oct 06, 2008 8:14 pm

sorry ram ,

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

Thanks

dsdevper · Post by **dsdevper** » Mon Oct 06, 2008 8:27 pm

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

DSXchange

Clear the rows before inserting depending on non key coloumn

Clear the rows before inserting depending on non key coloumn

Re: Clear the rows before inserting depending on non key col

Re: Clear the rows before inserting depending on non key col