Shows information about locks in the database, for both the IQ main store and the IQ catalog store.
Requires the MONITOR system privilege. Users without the MONITOR system privilege must be granted EXECUTE permission to run the stored procedure.
Optional sp_iqlocks parameters you can specify to restrict results:
Parameter |
Data Type |
Description |
|---|---|---|
connection |
integer |
Connection ID. With this option, the procedure returns information about locks for the specified connection only. Default is zero, which returns information about all connections. |
owner.table_name |
char(128) |
Table name. With this option, the procedure returns information about locks for the specified table only. Default is NULL, which returns information about all tables in the database. If you do not specify owner, it is assumed that the caller of the procedure owns the table. |
max_locks |
integer |
Maximum number of locks for which to return information. Default is 0, which returns all lock information. |
sort_order |
char(1) |
Order in which to return information:
|
Displays information about current locks in the database. Depending on the options you specify, you can restrict results to show locks for a single connection, a single table, or a specified number of locks.
sp_iqlocks displays the following information, sorted as specified in the sort_order parameter:
Column |
Data type |
Description |
|---|---|---|
conn_name |
VARCHAR(128) |
The name of the current connection. |
conn_id |
INTEGER |
Connection ID that has the lock. |
user_id |
CHAR(128) |
User associated with this connection ID. |
table_type |
CHAR(6) |
The type of table. This type is either BASE for a table, GLBTMP for global temporary table, or MVIEW for a materialized view. Materialized views are only supported for SQL Anywhere tables in the IQ catalog store. |
creator |
VARCHAR(128) |
The owner of the table. |
table_name |
VARCHAR(128) |
Table on which the lock is held. |
index_id |
INTEGER |
The index ID or NULL |
lock_class |
CHAR(8) |
String of characters indicating the type
of lock:
SAP Sybase IQ obtains a share lock before a write lock. If a connection has exclusive lock, share lock does not appear. For write locks, if a connection has all-exclusive, share, and write locks, it is EW. |
lock_duration |
CHAR(11) |
The duration of the lock. One of Transaction, Position, or Connection. |
lock_type |
CHAR(9) |
Value identifying the lock (dependent on the lock class) |
row_identifier |
UNSIGNED BIGINT |
The identifier for the row the lock starts on, or NULL. |
|
row_range |
BIGINT |
The number of contiguous rows that are locked. Row locks in the RLV store can either be a single row, or a range of rows. |
If sp_iqlocks cannot find the connection ID or user name of the user who has a lock on a table, it displays a 0 (zero) for the connection ID and User unavailable for the user name.
The example shows the sp_iqlocks procedure call and its output in the SAP Sybase IQ database. The procedure is called with all default options, so that the output shows all locks, sorted by connection.
call sp_iqlocks()
conn_name conn_id user_id table_type creator table_name ========= ======= ======= ========== ======= ========== SQL_DBC_13cd6038 3 DBA BASE DBA rv_locks2 SQL_DBC_13cd6038 3 DBA BASE DBA rv_locks2 SQL_DBC_13cd6038 3 DBA BASE DBA rv_locks2 RVL_CONN_T775 1000000407 BASE DBA rv_locks2 index_id lock_class lock_duration lock_type row_identifier row_range ======== ========== ============= ========= ============== ========= Schema Transaction Shared Row Transaction Row 1 4 Row Transaction Row 281474976710656 1 Table Transaction Intent