DB2 Connector - Locked out - Convert to Single Tread

[nmacolin]

Hi all,
Getting frustrated and looking for a different approach.
Scenario.

I have a job which has 2 DB2 Connector
DB2 CONN (1) ----> CP Stage -----> DB2 CONN (2)

DB2 CONN (1) Selects from Table X and Generates a new Sequence Number from the database for each missing Code / KEY pair.

Cp Stage -- Uses hash partitioning on the Code column above to ensure no conflicts on the update.

DB2 CONN(2) Performs an Update on the CODE column on Table X with the new generated Sequence number created from DB2 CONN (1).

We are currently running with two nodes, so the partitioning in CPStage should ensure that nodes do not share any data which would be used on the predicate of the where clause for the update. You would think this would be fine if the DB2 tablespace was set up for ROW locking... But unfortunately it's set to ANY which means it would most likely do a PAGE lock on the table.

Just for the record. I have the record Size = 0 and the Isolation Level for the DB2 CONN (1) = Uncommitted Read and isolation Level for DB2 CONN(2) set to Cursor Stability.

My question after all this is I can resolve the issue by making this job run in sequential mode or on a single node.

There doesn't seem to be a way to tell the DB2 connector to run sequentially, it seems to pick up the number of nodes and issue the equivalent number of update statements to the DB2.. In my case 2 update statements which may hold the same rows as a consequence of the PAGE LOCK.

I currently have the DBA working out if they can change the locksize= ROW for me without the DB2 world collapsing....

Can anyone suggest a way I can perform this update in a single node / sequential without mucking about with my configuration file to the determinant of the rest of the project .

Your assistance is appreciated.
Regards
Nick

[behrouz]

Dear,

What type of db2 connector are you using?

If you are using the db2 udp api this one is in sequential mode by default

and the other thing is I propose to you to do a clear in preserve partition ( you have to do it just one stage before your db2insert stage, you find it in stage properties -> advanced)

and you can also define your job to run in sequential mode by defining execution mode sequential
note that all this has to be done at least from previus stage
eg if you have db2---cp---db2
you have to do clear or sequential mode from CP stage

you can also define your job to run in only one node in node definition

Hop this help.

Arash.

[mhester]

Can you post the actual error you are receiving?

Also, there is only one (1) DB2 Connector - the DB2 UDB API is not a connector.

Also, Unless you Entire partition your rows will not be "shared" across partitions. Node 0 will not contain rows from Node 1 unless you used Entire partitioning at some point in the flow.

[behrouz]

you have db2 udb API which is sequential (I m developing with db2 udb api for 1 year and it is sequential, we use it because we cannot use enterprise
and db2 udb enterprise
plz have a look in your db2 connector

[kwwilliams]

The db2 api is not the same as the db2 connector. These are two separate stages available on the palette.

If the original poster could post the actual error message from the log, more information could be provided. Without the actual error message there is not enough information to provide any further guidance.

[nmacolin]

Hi Kieth

Just to clarify we are on 8.1 and have installed the new DB2 connector which is not the old DB2 API. This connector allows parallel capability without all the setup and limitation of the DB2 Entrpse Stage..

Now, I've had the DBA change the Tablespace locksize=ROW which I would have thought would resolve the problem, but not.

The problem appears at random now, which makes me think we have a system issue with the way in which DB2 on z/OS is escalating the locking. The DBA mentioned the escalation process when the number of locks increase. So you can imagine that they would have set the locking parameters/limits suitable for page locking but by changing the locking to ROW the number of locks obtained in a single update would be huge and probably cause some level of escalation. That is from ROW --> to --> PAGE ---> to --> TABLE ---> to ---> TABLESPACE.

I noticed in the Log that there are two partitions which are trying to update the Database simultaneously partition_0 and parition_1. In a failed run only one partition is successful whilst the other fails with a lockout error as seen below.

Just to make things worse since it divides into two partitions these act as independent transactions, so if one is successful it will commit but the other will fail. So you can see there is the additional problem of having a partially updated table !!! AARRGGHH.

Another good reason to have a single transaction.

I have also tried to set the processing to sequential in the Copy Stage and then in the subsequent Db2 Connection set the Partioning to SAME. Which in effect would mean no partitioning. I would have expected two partitions but only one with all the data... BUT it fails as it expects two partitions regardless....

There doesn't seem to be anywhere in the DB2 Connector to say run sequentially as there was in the Ol' DB2 API...

So still looking for other ways to achieve this.

Will have the DBA's looking into the current system settings on the DB2 database to see if I'm hitting any system limits which maybe causing locking escalation.

Regards
Nick


DB_IMG_UPD_COR_SERVICE,0: SQLExecute reported: SQLSTATE = 57033: Native Error Code = -913: Msg = [IBM][CLI Driver][DB2] SQL0913N Unsuccessful execution caused by deadlock or timeout. Reason code "00C90088". SQLSTATE=57033
SQLExecute reported: SQLSTATE = 57033: Native Error Code = -913: Msg = [IBM][CLI Driver][DB2] SQL0913N Unsuccessful execution caused by deadlock or timeout. Reason code "00C90088". SQLSTATE=57033
SQLExecute reported: SQLSTATE = 57033: Native Error Code = -913: Msg = [IBM][CLI Driver][DB2] SQL0913N Unsuccessful execution caused by deadlock or timeout. Reason code "00C90088". SQLSTATE=57033
SQLExecute reported: SQLSTATE = 57033: Native Error Code = -913: Msg = [IBM][CLI Driver][DB2] SQL0913N Unsuccessful execution caused by deadlock or timeout. Reason code "00C90088". SQLSTATE=57033
SQLExecute reported: SQLSTATE = 57033: Native Error Code = -913: Msg = [IBM][CLI Driver][DB2] SQL0913N Unsuccessful execution caused by deadlock or timeout. Reason code "00C90088". SQLSTATE=57033
SQLExecute reported: SQLSTATE = 57033: Native Error Code = -913: Msg = [IBM][CLI Driver][DB2] SQL0913N Unsuccessful execution caused by deadlock or timeout. Reason code "00C90088". SQLSTATE=57033
SQLExecute reported: SQLSTATE = 57033: Native Error Code = -913: Msg = [IBM][CLI Driver][DB2] SQL0913N Unsuccessful execution caused by deadlock or timeout. Reason code "00C90088". SQLSTATE=57033
SQLExecute reported: SQLSTATE = 57033: Native Error Code = -913: Msg = [IBM][CLI Driver][DB2] SQL0913N Unsuccessful execution caused by deadlock or timeout. Reason code "00C90088". SQLSTATE=57033
SQLExecute reported: SQLSTATE = 57033: Native Error Code = -913: Msg = [IBM][CLI Driver][DB2] SQL0913N Unsuccessful execution caused by deadlock or timeout. Reason code "00C90088". SQLSTATE=57033
SQLExecute reported: SQLSTATE = 57033: Native Error Code = -913: Msg = [IBM][CLI Driver][DB2] SQL0913N Unsuccessful execution caused by deadlock or timeout. Reason code "00C90088". SQLSTATE=57033 (CC_DB2DBStatement::executeUpdate, file CC_DB2DBStatement.cpp, line 805)

[nmacolin]

Hi all.

This has been resolved. A college of mine provided the simple answer. If you look at the actual connector properties window. There is a diagram at the top left corner, which shows the link and the connector.

When you click on properties for the stage it defaults to the link view, BUT if you click on the DB2 connector you will see the Advanced Tab. Then you can change the execution mode from the default of parallel to Sequential.

This will then remove any partitioning and run the update as a single transaction, which for us means we don't have any con currency issues.

So Resolved.

Regards
Nick :D

[DiscGolfer5000]

That solution worked for me as well! I had been getting a fatal error (below) on the DB2 Connector stage when the write mode was set to "Insert then update."

Message Id:IIS-CONN-DB2-00695
Message:
DB2_Connector,0: DB2 reported: SQLSTATE = 40506: Native Error Code = -1,476: Msg = [IBM][CLI Driver][DB2/NT] SQL1476N The current transaction was rolled back because of error "-911". SQLSTATE=40506

The job had defaulted to a multi node config file. I changed the DB2 Connector's Execution mode from Default (Parallel) to Sequential and then the job ran successfully.

Thank you,
Eric Dodson :D :D :D :D

[ivannavi]

Thank you nmacolin and your colleague a thousand times. I would never think of clicking the "upper left corner" image. And this is so poorly documented by the IBM (only for DRS connector this is mentioned once). You saved my life!

[TonyInFrance]

I have encountered the same problem today. The solution was as I read here, changing the execution mode property from the default of parallel to sequential.

However I incorporated this change only in my target DB2 stage. My job is DB2->DB2. No copy stage or transformer stage or any other stage except the 2 DB2 stages in my job with a single link.

What I don't understand is:

For a table with a few hundred thousand lines there is no problem with keeping the execution mode property to the default of parallel. However when I try and copy a table containing 4000 odd lines the job aborts with the message:
DB2_Connector,0: DB2 reported: SQLSTATE = 40506: Native Error Code = -1,476: Msg = [IBM][CLI Driver][DB2/NT] SQL1476N The current transaction was rolled back because of error "-911". SQLSTATE=40506

Bizarre but true.

Can anyone shed any light on why it behaves this way with a few lines but not when copying hundreds of thousands?

[ray.wurlod]

What are DB2 error codes -1476 and -911 ?

[TonyInFrance]

All I have is the message I posted above:
DB2_Connector,0: DB2 reported: SQLSTATE = 40506: Native Error Code = -1,476: Msg = [IBM][CLI Driver][DB2/NT] SQL1476N The current transaction was rolled back because of error "-911". SQLSTATE=40506

I have since changed the DB2 Connector's Execution mode from Default (Parallel) to Sequential but only in the target stage. I specifically wanted to keep enabled the partitioned reads property in my source stage in order to increase the perf since some of my source tables contain a few million lines.

This seems to work fine.

[chulett]

You should either look them (the errors mentioned) up or take them to your DBA.

[Kryt0n]

-911 is deadlock, while my knowledge of DB2 is pretty limited (and I'm no DBA), from what I can figure this is fairly common when you attempt to do an update but there is no index on the keys.

While not 100%, I believe when no index available, the process will lock the rows it scans, if one of those rows happens to be a row required by the other process, that process fails. By having an index on the keys, there is no need to lock rows not being updated and so both live in harmony... at least they have for us and we have updates all over the place

[TonyInFrance]

I will verify if my target tables have indexes defined on their respective key fields and get back.

This doesn't explain however why it works in sequential mode i.e. when the DB2 Connector's Execution mode is changed from from Default (Parallel) to Sequential but only in the target stage everything works great.