How to fire an update query using datastage job?

[qutesanju]

I have few records in a table as shown below ,but due to business rules
I want to update below id 202BILGN16 to 202BILGN12
rest all other rows will be as it is

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

but where should I write an update constraint in order to update 202BILGN16 to 202BILGN12

[Sreenivasulu]

Use the property 'user-defined sql'. Here you can write any DML statement

[Sainath.Srinivasan]

If it is a one-off activity, why not run a stand-alone query ?

[ray.wurlod]

What database? Which stage type?

[qutesanju]

yep,
using ODBC i can erite a SQL -update query but is there an alternative way to write it using datastage jobs?

[qutesanju]

Database is SQL SERVER
but i m thinking what logic can be implemented for this?
Also this is not one time activity otherwise i would have fired SQL statement for this(using an update query)

What database? Which stage type? ...

[chulett]

Since generated SQL can't bind the same column twice, include it twice in your link (old and new values) then use user-defined SQL to do the actual update in the target stage.

[arunpramanik]

What are the keys for the update?


I will prefer to use Store procedure, as I get quite confuse with positional parameters and the way columns should be place in input stream for this type of update while using ODBC

[ray.wurlod]

Any column marked as Key in the Columns grid can (should, must) be used in the WHERE clause.

Does not the ODBC stage allow you to generate UPDATE statement?

[chulett]

It does but not one they could actually use, seeing as how that one column essentially needs to be both a key and a non-key.

[qutesanju]

I think using below fuction i can achieve this
change
ereplace OR
convert OR
Left OR
Right

[arunpramanik]

I think using below fuction i can achieve this
change
ereplace OR
convert OR
Left OR
Right

based on your requirement the User Define query should look like

Code: Select all

update  tablexyz set key3=? where key1=? and key2=? and key3=?

and this statement should be such that it can be parse by SQL engine, so any use of DS function will not work.

now if you have in input column
key1, key2,key3,newkey3

I think, and if I am not wrong, the key3 in the set statement will always take the third field from input stream, not the 4th field which you want to have. and If I am wrong, You can try this, and let us know if you have succeeded then post here

[qutesanju]

I achieved it using

Change(columnName, "16", "12")
because I want to change 202BILGN16 to 202BILGN12

here is flow for my job
input query -->transformer-->seq file
after applying Change(columnName, "16", "12")
I checked seq file i found expected changes was there

I'm trying same now on database by changing flow as
input query -->transformer-->database table

[arunpramanik]

input query -->transformer-->database table

Can you share the stage used and the Update statement used in stage

[chulett]

Your solution has already been posted here - twice now - first by myself and then again by arunpramanik. Read and apply that advice. The only thing we've had to guess on are your actual key fields but I think it's a pretty good guess.