Update SQL

[jpraveen]

Hi

i want to update the same table my sourece & target is same(Sql Server)
and i am using ODBC stage,there is no transformation Logic

my SQl is

UPDATE Table_Name
SET Table_Name.Column1 = 'FL'
WHERE Table_Name.Column1='XX' AND
Table_Name.Column2='CO';

i had only 9000 records,and it is going to Hang or sometimes the job is aborted due to deadlock issue

I had used HASh Partition also, but the result is same,

i had pasted the same query in Target stage(Update SQl in UPSERT MODE)
and the job is finished the records are not updated.

Could you help on this?

[rohithmuthyala]

I'm not sure if that is a correct way of doing it....as you are reading and updating the table in the same job.One can split the task into two jobs:

1st Job: Extract the table with the where condition i.e.
Select * from Table_Name WHERE Table_Name.Column1='XX' AND
Table_Name.Column2='CO';

Pass it through transformer assigning Table_Name.Column1 = 'FL' into a dataset.

2nd Job: Load the above dataset into the table. i.e. update it.

This is what I have thought of,you may get some more options from others.

[jpraveen]

Hi all

as per my project specifications ,we should not use datasets or flat files,
and i am doing this in the same job, we dont have temporary tables also.

Src--->Tfm---> Trg(Update

)

[chulett]

Perhaps your Hash partioning keys were not properly defined then. Does the same problem happen if you run the job on a single node?

[victor236]

Hi All,

I think we can't perform 2 actions (selecting and updating) on a table at the same time, because if the select query is trying to select a record and at the same time if it is trying to update on the same record then the table will be locked. (info known)

Praveen if this problem is solved please share it....

[kris]

i had pasted the same query in Target stage(Update SQl in UPSERT MODE)
and the job is finished the records are not updated.

Perhaps you can try running the script in enterprise stage as a target with sequential mode using delete method. In same situaltion it worked for me.

Use Row Generator stage as input with Number of records = 1 in output properties. Define one column and it doesn't matter what it is. It is just to trigger the stage.

Then link the Rowgenerator directly to the Enterprise stage.

Code: Select all

RowGenerator --> Enterprise Stage

Configure Enterprise stage with below things:

Execution Mode = Sequential
Write Method = Delete rows
Delete Rows Mode = User-Defined Delete
Delete SQL = Your Update Script

Try it. It worked for me!!

[FranklinE]

Before exploring the excellent suggestions so far, check on any contention issues inside your database.

For example, is your select an uncommitted read (UR in DB2) or does it perform a row or table lock? Your update will always fail if there are locks. It will succeed if there are no locks. The equivalent usage in DB2 is fetch with update.

My other question may be because I misunderstand your initial post. If there are no transformations needed, I don't understand why you are doing a select at the beginning of the job.

[kandyshandy]

This may be a simpler solution.

In your SELECT stage, write a SQL like shown below
SELECT primary_key from TABLE1
WHERE Table_Name.Column1='XX' AND
Table_Name.Column2='CO';

In the target stage, have an UPDATE SQL like shown below

UPDATE TABLE1 set Column1 = 'FL'
WHERE primary_key = :primary_key (from SELECT).

This will ensure that there is no lock on the table because primary key is unique.

[kwwilliams]

According to the specification you can't use a flat file or data set? My first recommendation would be to change your specification. What functional reason would anyone have for not allowing you to follow a best practice?

If your specification provider continues to be unreasonable, then follow FranklinE's advice and set your select stage's Isolation Level to Read Uncomitted. This will work, but is not ideal.

[FranklinE]

If your specification provider continues to be unreasonable, then follow FranklinE's advice and set your select stage's Isolation Level to Read Uncomitted. This will work, but is not ideal.

Keith, would you mind explaining why it would not be ideal? Thanks.

[kandyshandy]

Keith and Franklin, sorry to say but would like to let everyone know what i experienced in the past. Even though you use Isolation level to read uncommitted in the SELECT stage, it will not work. The lock happens because of the bulk update.

Update table set table.column1 = 'FL'
where table.column1 = 'XX' and table.column2 = 'CO'

The above DML is not using any column from the SELECT. I am really not sure what Praveen is doing at the SELECT stage.

Praveen, let us know if read uncommitted works for you.

I have implemented my solution explained earlier & that resolved the lock issues.

[kwwilliams]

Restartability. If the job were broken into two parts, extract and load. One could easily create a lookup that would select the data from the database that has already been updated and not perform a subsequent update.

However, if the data is never persisted the dataand only selects of the data from the table and updates of the same table are performed; then my sql statement needs to be smart enough not to select a row that has already been updated. This would be easy enough if

1. The selection criteria for the select statement is the same fields that are being updated.
AND
2. The values being extracted is mutually exclusive of the values being updated.

Without both of these being true it is possible for me to update the source/target table multiple times WHEN the job fails and is restarted. It will fail at some point.

[kwwilliams]

The lock happens because of the bulk update.

This would only be true if the commit point is set to 0. Updates aren't really bulk, and can easily be run at the same time that a select is happening in the database. This happens all the time through non-DataStage means.

Locking is different with every database engine, and so getting the array sizes and commit sizes dialed in to allow for this to work without locking would take coordination between the developer and the dba and quite a bit of testing.

It can be done. However, as I stated I think the specification is incorrect. There is no functional reason to avoid persisting data. So I would encourage push back on the specification and to break the job into an extract and load job.