Solved

Sybase 10 : I am looking for a script that will show the user, table and row and any more relivent data when the database locks

Posted on 2010-11-12
5
681 Views
Last Modified: 2012-05-10
Sybase 10 : I am looking for a script that will show the user, table and row and any more relivent data when the database locks

thanks !!!!
0
Comment
Question by:NAMEWITHELD12
  • 3
5 Comments
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 34127500
Which Sybase product? I hope you mean ASA 10 (only a few years old) and not Sybase SQL Server 10 (15 years old)... :)
0
 
LVL 1

Author Comment

by:NAMEWITHELD12
ID: 34168424
sorry for the delay itis

sybase 10.0.1.3990 ASA
0
 
LVL 1

Author Comment

by:NAMEWITHELD12
ID: 34357144
anybody have an idea , how do you throubleshoot DB locks in sybase?
0
 
LVL 13

Accepted Solution

by:
alpmoon earned 500 total points
ID: 34362428
I have never used that version and I don't have a script. But I think what you need is just query sa_diagnostic_blocking table. I have included definition of that table below. You can find more details at

http://infocenter.sybase.com/help/topic/com.sybase.dbrfen_1001/pdf/dbrfen10.pdf?noframes=true

sa_diagnostic_blocking table

The sa_diagnostic_blocking table is owned by the dbo user, and records blocking events. If logging of
blocking events is enabled, a row is inserted in this table each time a connection is blocked while trying to
access a resource. Typically, this is caused by either a table or a row lock. A large number of blocks may
indicate that you should examine the concurrency in your application to reduce contention for tables and
rows.
There are two versions of this table: sa_diagnostic_blocking, and sa_tmp_diagnostic_blocking.
Columns
Column name Column type Column constraint Table constraints
logging_session_id UNSIGNED INT NOT NULL Foreign key references
sa_diagnostic_cursor.
Foreign key references
sa_diagnostic_request.
lock_id UNSIGNED BIGINT NOT NULL
request_id UNSIGNED BIGINT Foreign key references
sa_diagnostic_request.
cursor_id UNSIGNED BIGINT Foreign key references
sa_diagnostic_cursor.
original_table_object_i
d
UNSIGNED BIGINT
rowid UNSIGNED BIGINT
block_time TIMESTAMP NOT NULL
unblock_time TIMESTAMP
blocked_by UNSIGNED INT NOT NULL
logging_session_id A number uniquely identifying the logging session during which the diagnostic
information was gathered.
lock_id The ID of the lock that caused the blocking if a row or table lock caused the block, otherwise
NULL.
request_id The ID of the request that was blocked if the block did not occur because of a cursor, otherwise
NULL. This value corresponds to the ID assigned to the request in sa_diagnostic_request.
cursor_id The ID of the cursor if the block occurred because of a cursor, otherwise NULL. This value
corresponds to the ID assigned to the cursor in sa_diagnostic_cursor.
original_table_object_id If the block occurred because of a table lock, the ID of the table on which the
block occurred, otherwise NULL.

rowid If the block occurred because of a row lock, the ID of the row on which the block occurred, otherwise
NULL.
block_time The time at which the block occurred.
unblock_time The time at which the block ended.
blocked_by The ID of the connection that held the lock, causing the block.
0
 
LVL 1

Author Closing Comment

by:NAMEWITHELD12
ID: 34414212
awesome
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now