Q

Troubleshooting: Performing an insert with DB2 z/OS

I want to perform a retry on an INSERT under DB2 z/OS when I get a deadlock/timeout. Is there a ZPARM or another method of turning this off?

I want to perform a retry on an INSERT under DB2 Z/OS when I get a deadlock/timeout. -911 causes a rollback automatically. Is there a ZPARM or another method of turning this off? I am inserting millions of rows and do not want a rollback to the last commit point.

Well, first of all, let me recommend that you minimize the size of your unit of work. If you are inserting millions...

of rows without a COMMIT you are likely causing locking issues in your environment. The pages you have locked while you are waiting for your millions of inserts to finish are all unavailable to any other user of the table. That means any data on any page that you have locked cannot be read by anyone else until your unit of work is committed. Any other user running at the same time as you trying to get to any page you have modified would be getting -911 too.

That being said, you can control whether or not the work is rolled back automatically in CICS (on a thread basis) using an RCT or RDO parameter. The parameter is called ROLBE (RCT) or DROLLBACK (RDO). If it is set to YES a CICS SYNCPOINT ROLLBACK is issued and a -911 SQLCODE is returned to the program. If NO is coded, a CICS SYNCPOINT ROLLBACK is not issued and the SQLCODE is set to -913. You will have to programmatically either specify COMMIT or ROLLBACK for the unit of work.

I am not aware of a method of controlling this behavior for a batch program…

Good luck.

This was last published in February 2007

Dig Deeper on IBM DB2 management

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSQLServer

Close