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
697 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

726 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