ODBC stage -Multiple update statements - Lock

premupdate · Post by **premupdate** » Fri Jun 13, 2014 8:21 pm

Gurus,

Job flow

Row generator -----> transformer ------>ODBC stage

I have been trying to update a sybase table.There are multiple update statements.(I had used Row generator and transformer for a dummy purpose)Due to restrictions, we are allowed to use ODBC connector stage where we created DSN for sybase database. I had used all update statements inside a single ODBC stage and the job ran success with few(10) rows.

The actual update records will be in million.If I use full strength of records, is there any chance of getting the table lock error.If so ,how it can be avoided.

Your help most appreciated.

Note: The update statements are written for a same column in same table but with different where clauses.

chulett · Post by **chulett** » Fri Jun 13, 2014 9:59 pm

Can you be a wee bit more specific about what "there are multiple update statements" means exactly?

premupdate · Post by **premupdate** » Sat Jun 14, 2014 9:04 pm

Sorry for the delayed reply.

There are some ten update statements for a single table with different where clause and different joins with other tables.

First we thought of using multiple ODBC stages as lookups and process these updates.Keeping performance in mind , all update statements are used in a single stage.

ArndW · Post by **ArndW** » Sun Jun 15, 2014 4:47 am

Since your SQL is within one stage all of the updates in it will be treated as one transaction. For each row that is processed into your ODBC stage how many records are being updated? Also, if you run the job in parallel, could records from one process be updating records from another? This means if you partition on column A with a 4-node job, but the update is done using column B, then you might get lock contention. Your database settings will determine how much rollback space you have for your very large transactions.

If one input record only does 10 updates, each of which only update a single row, then you can control the rollback space usage by setting your job's transaction size accordingly.

chulett · Post by **chulett** » Sun Jun 15, 2014 8:11 am

Where exactly are these in the stage - in the body of the stage or before/after? I'd also be curious how many update statements will be generating these million updates. However, rather than sitting here and trying to puzzle out exactly what it is you are doing, we can probably just cut straight to the chase - your actual question:

premupdate wrote:If I use full strength of records, is there any chance of getting the table lock error. If so, how it can be avoided.

I can't answer that for Sybase but your DBA can. Take DataStage out of the picture and discuss with them what you are doing and the concerns you have, let them answer your question. You'd also, as Arnd notes, need to bring whatever partitioning you are doing into the conversation as well.