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
696 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

828 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