db2 9.1 cancel thread ur rollback

Diana Nagel

db2 9.1 cancel thread ur rollback
I started an update transaction on a very large table....it was running, running and not getting done so I cancelled...Well that wasn't a very good idea. Now the transaction is in rollback

KCP0243: WSR WAITING FOR WORKLOAD DEFINITIONS
DSNR048I # DSNRBMON UR BACKOUT PROCESSING LOG RECORD 504

This is a test area and I can recover this tablespace from a backup so I can just stop this activity totally. I don't care.

Anyone know how.

I tried to do -cancel thread mode(force).....still processing.

diana



_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Philip Sevetson

Re: db2 9.1 cancel thread ur rollback
(in response to Diana Nagel)
1) Open an ETR with IBM to get information on what "Waiting for Workload Definitions" means - it sounds like the thread is hanging, doing nothing.
2) Can you or Operations or the DB2 System Programmers tell whether the task is actually rolling back? Has it, for example, allocated archive log files?

--Phil S.

________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Diana Nagel
Sent: Thursday, January 14, 2010 4:44 PM
To: [login to unmask email]
Subject: [DB2-L] db2 9.1 cancel thread ur rollback

I started an update transaction on a very large table....it was running, running and not getting done so I cancelled...Well that wasn't a very good idea. Now the transaction is in rollback

KCP0243: WSR WAITING FOR WORKLOAD DEFINITIONS
DSNR048I # DSNRBMON UR BACKOUT PROCESSING LOG RECORD 504

This is a test area and I can recover this tablespace from a backup so I can just stop this activity totally. I don't care.

Anyone know how.

I tried to do -cancel thread mode(force).....still processing.

diana



________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Raymond Bell

Re: db2 9.1 cancel thread ur rollback
(in response to Philip Sevetson)
If it's a subsystem that can take an outage, and you have certain Zparms set correctly, you could intentionally bring down the DB2 subsystem (cancelling the IRLM is effective) and immediately -STArt DB2. DB2 should start quickly and leave this UR in - if memory serves - postponed abort, and the database objects it was touching unavailable. It's then up to you to do something about those objects, but everything else is able to be accessed.

Found them. The Zparms are LBACKOUT (Limit Backout) and BACKODUR (Backout Duration). The default is Auto and 5 so unless someone changed them it should work. Depends how you feel about crashing a subsystem. Alternatively you wait for the backout to complete. And wait. And wait...

Cheers,


Raymond

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Diana Nagel
Sent: 14 January 2010 21:44
To: [login to unmask email]
Subject: [DB2-L] db2 9.1 cancel thread ur rollback

I started an update transaction on a very large table....it was running, running and not getting done so I cancelled...Well that wasn't a very good idea. Now the transaction is in rollback

KCP0243: WSR WAITING FOR WORKLOAD DEFINITIONS
DSNR048I # DSNRBMON UR BACKOUT PROCESSING LOG RECORD 504

This is a test area and I can recover this tablespace from a backup so I can just stop this activity totally. I don't care.

Anyone know how.

I tried to do -cancel thread mode(force).....still processing.

diana



________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Carol Broyles

Re: db2 9.1 cancel thread ur rollback
(in response to Raymond Bell)
We have successfully gotten rid of these threads using CANCEL
THREAD(token) NOBACKOUT.





Carol L. Broyles

Infrastructure Mgt. Consultant

Commercial Solutions

Office Phone: 937-495-4003

[login to unmask email]



Affiliated Computer Services Inc.

CONFIDENTIALITY NOTICE: This e-mail message, including any attachments,

is for the sole use of the intended recipient(s) and may contain
confidential and

privileged information. Any unauthorized review, use, disclosure, or
distribution is

prohibited. If you are not the intended recipient, please contact the
sender by reply

e-mail and destroy all copies of the original message.



________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: Friday, January 15, 2010 3:48 AM
To: [login to unmask email]
Subject: Re: [DB2-L] db2 9.1 cancel thread ur rollback



If it's a subsystem that can take an outage, and you have certain Zparms
set correctly, you could intentionally bring down the DB2 subsystem
(cancelling the IRLM is effective) and immediately -STArt DB2. DB2
should start quickly and leave this UR in - if memory serves - postponed
abort, and the database objects it was touching unavailable. It's then
up to you to do something about those objects, but everything else is
able to be accessed.



Found them. The Zparms are LBACKOUT (Limit Backout) and BACKODUR
(Backout Duration). The default is Auto and 5 so unless someone changed
them it should work. Depends how you feel about crashing a subsystem.
Alternatively you wait for the backout to complete. And wait. And
wait...



Cheers,





Raymond



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Diana Nagel
Sent: 14 January 2010 21:44
To: [login to unmask email]
Subject: [DB2-L] db2 9.1 cancel thread ur rollback



I started an update transaction on a very large table....it was running,
running and not getting done so I cancelled...Well that wasn't a very
good idea. Now the transaction is in rollback



KCP0243: WSR WAITING FOR WORKLOAD DEFINITIONS

DSNR048I # DSNRBMON UR BACKOUT PROCESSING LOG RECORD 504



This is a test area and I can recover this tablespace from a backup so I
can just stop this activity totally. I don't care.



Anyone know how.



I tried to do -cancel thread mode(force).....still processing.



diana







________________________________

IDUG - The Worldwide DB2 User Community! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >


________________________________

IDUG - The Worldwide DB2 User Community!
< http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L