How to fire an update query using datastage job?

[arunpramanik]

Still you can't do ? then my last suggestion is (considering that the table in which you are working is a Fact/detail table) -

Delete the existing rows with old keys and then Insert the new rows with new keys

Please update the Subject to something like "updating key column"

[qutesanju]

I'm getting an error for this as
SQLSTATE=23000, DBMS.CODE=2601
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert duplicate key row in object 'dbo.CREFEE' with unique index 'ix_location_1'.
SQLSTATE=01000, DBMS.CODE=3621
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

[arunpramanik]

Please describe your job design.

[qutesanju]

job design is
input query -->transformer-->database table

[arunpramanik]

Considering that we are working with KEY FIELDS.
Here is an alternate design -

Code: Select all

                               pk1,pk2,pk3 = oldval
input -----------> xfm------------ODBC (delete)
                   | pk1,pk2,pk3 = newval
                   |
                ODBC (insert)

Use user define statement in each statement for example
DELETE FROM "xyz" WHERE ("pk1" = ? AND "pk2" = ? AND "pk3" = ?);

[chulett]

I'm getting an error for this as
SQLSTATE=23000, DBMS.CODE=2601
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert duplicate key row in object 'dbo.CREFEE' with unique index 'ix_location_1'.

Not really sure what "this" is anymore or how we got to insert errors trying to do an update but the answer here is quite simple - stop trying to insert duplicate records. If for some reason we're now talking about the need to delete and then insert rather than update, there is usually an Update Action called 'Replace existing row completely', use that. Or simply follow the advice you've been given (more than once) on how to do updates on key fields.

If you still have problems, make sure you let us know what you are actually trying to do and post the SQL that you are using.

[HariK]

What is the array/transaction size you have set for delete operation?

[arunpramanik]

I think the Delete operation is not working; and most probable reason can be that you are deleting Master table and FK constraint is not allowing.

But this should not create the INSERT problem, unless the record do exists in Master table with same PKs

[arunpramanik]

From the very first post -

using a query I can get below recods to update
System Plan Id
P1 102 202BILGN16
P1 102 202BILGN16
P1 102 202BILGN16
P2 103 202BILGN15
P2 103 202BILGN15
P2 103 202BILGN15

Based on this above data at least there is atleast another composite key. Hope you have identified all your keys to work in this type of scenario