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

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 !!!!
LVL 1
NAMEWITHELD12Asked:
Who is Participating?
 
alpmoonConnect With a Mentor Commented:
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
 
Joe WoodhousePrincipal ConsultantCommented:
Which Sybase product? I hope you mean ASA 10 (only a few years old) and not Sybase SQL Server 10 (15 years old)... :)
0
 
NAMEWITHELD12Author Commented:
sorry for the delay itis

sybase 10.0.1.3990 ASA
0
 
NAMEWITHELD12Author Commented:
anybody have an idea , how do you throubleshoot DB locks in sybase?
0
 
NAMEWITHELD12Author Commented:
awesome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.