Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Simulating the SORT and Deadlock
- Thread starter gbag
- Start date
Sort.
Pick a big table. Select all rows, ordered by a column that isn't in the index. Er, that's it. Note: this won't simulate a sort, it will really do one...
Deadlock.
DB2 has quite sophisticated deadlock detection, so it's quite hard to engineer a deadlock. Are you trying to create a deadlock that DB2 will detect and break, or one that it won't see?
Pick a big table. Select all rows, ordered by a column that isn't in the index. Er, that's it. Note: this won't simulate a sort, it will really do one...
Deadlock.
DB2 has quite sophisticated deadlock detection, so it's quite hard to engineer a deadlock. Are you trying to create a deadlock that DB2 will detect and break, or one that it won't see?
gbag,
I work on a mainframe and to force a deadlock I tend to go into one of the packages available (File Aid for DB2, QMF, Insight etc.) and take an update lock on a table.
Running an app against that table causes the deadlock condition.
It depends what your envirnoment is, but I'm sure you get the idea.
Marc
I work on a mainframe and to force a deadlock I tend to go into one of the packages available (File Aid for DB2, QMF, Insight etc.) and take an update lock on a table.
Running an app against that table causes the deadlock condition.
It depends what your envirnoment is, but I'm sure you get the idea.
Marc
- Thread starter
- #4
Thanks Stevexff & MarcLodge for your suggestions.
I really appreciate your time and suggestions.
I want to simulate the sort and want it to be driven to disk to create heavy io. I am having the following steps:
1. create a huge table without index.
2. Reduce the bufferpool size.
3. select all the rows on a character column.
I believed that this will drive the sort to disk. But this did not. Do I miss any steps.
Regarding deadlock, I am seeing lot of deadlocks from the application we have. But I want to simulate that kind of deadlock. Means, when I run my process/sql, db2 should say "a deadlock has been detected".
Any help.
Thanks in advance.
gBag.
I really appreciate your time and suggestions.
I want to simulate the sort and want it to be driven to disk to create heavy io. I am having the following steps:
1. create a huge table without index.
2. Reduce the bufferpool size.
3. select all the rows on a character column.
I believed that this will drive the sort to disk. But this did not. Do I miss any steps.
Regarding deadlock, I am seeing lot of deadlocks from the application we have. But I want to simulate that kind of deadlock. Means, when I run my process/sql, db2 should say "a deadlock has been detected".
Any help.
Thanks in advance.
gBag.
gbag,
db2 will return an sqlcode of -911 to the application which indicates a deadlock has been detected, whilst also writing certain information to the log. I'ts up to the application to check the sqlcode and return the type of message that you are referring to. DB2 cannot do it, as DB2 does not know whether the app considers this an abend situation, or just a try again situation.
Marc
db2 will return an sqlcode of -911 to the application which indicates a deadlock has been detected, whilst also writing certain information to the log. I'ts up to the application to check the sqlcode and return the type of message that you are referring to. DB2 cannot do it, as DB2 does not know whether the app considers this an abend situation, or just a try again situation.
Marc
gregsimpson
Programmer
- Apr 18, 2002
- 521
- 0
- 0
gbag,
if you wish to force your sort to disk, something you might wish to consider is reducing your SORTHEAP.
Goes with out saying you are generally better off sorting in memory rather than overflowing to disk, if at all possible.
Cheers
Greg
if you wish to force your sort to disk, something you might wish to consider is reducing your SORTHEAP.
Goes with out saying you are generally better off sorting in memory rather than overflowing to disk, if at all possible.
Cheers
Greg
- Thread starter
- #7
Thanks all for your replies.
I appreciate your time and suggestions.
Gregsimpson,
Do you mean to increase SORTHEAP or SORTHEAPTHRESH.
MarcLodge
Yes, the DB2 returns 911 error saying that the Deadlock has been detected. But I want to create that situation. Any ideas...
Thanks.
I appreciate your time and suggestions.
Gregsimpson,
Do you mean to increase SORTHEAP or SORTHEAPTHRESH.
MarcLodge
Yes, the DB2 returns 911 error saying that the Deadlock has been detected. But I want to create that situation. Any ideas...
Thanks.
gregsimpson
Programmer
- Apr 18, 2002
- 521
- 0
- 0
gbag,
I meant for you to decrease not increase, effectively robbing db2 of any memory to do sorts in.
Cheers
Greg
I meant for you to decrease not increase, effectively robbing db2 of any memory to do sorts in.
Cheers
Greg
- Status
Similar threads
- Locked
- Question
- Locked
- Question