Процедура заблокирована чем-то. Как узнать, чем и разлочить её?
Для таблиц существует reorg, а здесь что?

:)

DB2 9.5

Нет, reorg я вывожу из pending state. Это всегда у меня решало проблему. Про lock я тут был неправ, просто таблицы никогда не лочились пока.

А вот что делать с процедурами - не знаю. ЧТо-то произошло, процедура, видимо, кем-то используется - т.е. заблокирована, так же? - не могу ее удалить. А мне нужно ее изменить...

Сделал, как Вы советуете.
В первом случае - да, возвращается определенный ID

Но в таблице sysibmadm.snaplockwait не то, что нету записей с данным agent_id - она вообще пустая!

То есть, если процедура ничем не залочена - почему она может не удаляться?

Извиняюсь, может не стоило так прям, но... :)
Lock wait - там, где, собственно, производится попытка DROP.


Handle Agents partition number pid/thread
------------------------------ -------------------- ---------- -------------------------------------------------------------- ----- ---------- ---------------- --------------- ------------------------------ -------------------------- -------- -------- --------------------
DB2DEV Toad.exe 38927 127.0.0.1.48843.081028125852 00033 1 0 38201 UOW Waiting Not Collected B4RDKLT1 WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV Toad.exe 38348 192.168.21.155.40909.0810281109 00001 1 0 13815 UOW Waiting Not Collected NAV WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV Toad.exe 39269 127.0.0.1.50450.081028141551 00014 1 0 35639 UOW Waiting Not Collected PC-VMO WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV Toad.exe 39045 127.0.0.1.64716.081028132958 00001 1 0 57936 Connect Completed Not Collected B4RDKLT1 WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV Toad.exe 39512 192.168.21.158.7508.08102815103 00015 1 0 28196 Lock-wait Not Collected PC-RTE WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV Toad.exe 39183 127.0.0.1.22477.081028135705 00001 1 0 23831 Connect Completed Not Collected B4RDKLT1 WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV WebDev.WebServer.EXE 39617 192.168.21.151.65295.0810281535 00004 1 0 33331 UOW Waiting Not Collected PC-MYU WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV Toad.exe 39064 127.0.0.1.2253.081028133523 00211 1 0 22289 UOW Waiting Not Collected B4RDKLT1 WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV Toad.exe 39511 192.168.21.158.6996.08102815102 00024 1 0 39228 UOW Waiting Not Collected PC-RTE WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV db2wlmd 37899 *LOCAL.DB2.081028073455 00001 1 0 9201 Connect Completed Not Collected PC-VMO WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV WebDev.WebServer.EXE 39616 192.168.21.151.2832.08102815352 00002 1 0 11523 UOW Waiting Not Collected PC-MYU WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV Toad.exe 39267 127.0.0.1.46098.081028141530 00007 1 0 44853 UOW Waiting Not Collected PC-VMO WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV Toad.exe 38056 192.168.21.155.55232.0810280836 00016 1 0 46650 UOW Waiting Not Collected NAV WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV Toad.exe 37852 127.0.0.1.15051.081028122204 00008 1 0 20244 UOW Waiting Not Collected B4RDKLT1 WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV db2taskd 37898 *LOCAL.DB2.081028073454 00001 1 0 33588 Connect Completed Not Collected PC-VMO WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV Toad.exe 39003 127.0.0.1.65484.081028133155 00015 1 0 47932 UOW Waiting Not Collected B4RDKLT1 WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV Toad.exe 39595 127.0.0.1.50132.081028153039 00036 1 0 13062 UOW Waiting Not Collected B4RDKLT1 WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV Toad.exe 39292 127.0.0.1.5907.081028142224 00003 1 0 57385 UOW Waiting Not Collected PC-VMO WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV WebDev.WebServer.EXE 39588 192.168.21.151.47887.0810281527 00012 1 0 18287 UOW Waiting Not Collected PC-MYU WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV Toad.exe 38384 127.0.0.1.28873.081028111015 00206 1 0 26400 UOW Waiting Not Collected B4RDKLT1 WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV db2stmm 37897 *LOCAL.DB2.081028073453 00001 1 0 17164 Connect Completed Not Collected PC-VMO WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV Toad.exe 39002 127.0.0.1.1485.081028133306 00012 1 0 11010 UOW Waiting Not Collected B4RDKLT1 WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV Toad.exe 39620 192.168.21.155.15842.0810281535 00001 1 0 15615 UOW Waiting Not Collected NAV WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV WebDev.WebServer.EXE 39587 192.168.21.151.44559.0810281526 00016 1 0 25886 UOW Waiting Not Collected PC-MYU WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
DB2DEV Toad.exe 39613 192.168.21.158.48724.0810281534 00001 1 0 36666 UOW Waiting Not Collected PC-RTE WIZARDS /db2data/dev/wizards/db2dev/NODE0000/SQL00001/

Т.е. это две команды?

После первой же команды получаю ошибку

SQL1096N The command is not valid for this node type.

Ага, т.е. я теперь подконнектился к серверному инстансу? Спасибо, это мне тоже пригодится :)
Однако если по сути, то вот snaphshot, в котором holding lock отсутствует...
Но процедура по-прежнему не удаляется:

Application Snapshot

Application handle = 39512
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1208
Application country/region code = 380
DUOW correlation token = 192.168.21.158.7508.08102815103
Application name = Toad.exe
Application ID = 192.168.21.158.7508.08102815103
Sequence number = 00079
TP Monitor client user ID =
TP Monitor client workstation name =
TP Monitor client application name =
TP Monitor client accounting string =

Connection request start timestamp = Not Collected
Connect request completion timestamp = Not Collected
Application idle time = Not Collected
CONNECT Authorization ID = DB2DEV
Client login ID = RTE
Configuration NNAME of client = PC-RTE
Client database manager product ID = SQL09013
Process ID of client application = 62136
Platform of client application = NT
Communication protocol of client = TCP/IP

Inbound communication address = 10.225.1.250 26493

Database name = WIZARDS
Database path = /db2data/dev/wizards/db2dev/NODE0000/SQL00001/
Client database alias = WIZARDS
Input database alias =
Last reset timestamp = Not Collected
Snapshot timestamp = 2008-10-29 11:06:56.283583
The highest authority level granted =
Direct DBADM authority
Direct CREATETAB authority
Direct BINDADD authority
Direct CONNECT authority
Direct CREATE_NOT_FENC authority
Direct LOAD authority
Direct IMPLICIT_SCHEMA authority
Direct CREATE_EXT_RT authority
Direct QUIESCE_CONN authority
Indirect SYSADM authority
Indirect CREATETAB authority
Indirect BINDADD authority
Indirect CONNECT authority
Indirect IMPLICIT_SCHEMA authority
Coordinating database partition number = 0
Current database partition number = 0
Coordinator agent process or thread ID = 28196
Agents stolen = 0
Agents waiting on locks = 0
Maximum associated agents = 1
Priority at which application agents work = 0
Priority type = Dynamic

Lock timeout (seconds) = -1
Locks held by application = 0
Lock waits since connect = 8
Time application waited on locks (ms) = 0
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Number of Lock Timeouts since connected = 0
Total time UOW waited on locks (ms) = Not Collected

Total sorts = 0
Total sort time (ms) = Not Collected
Total sort overflows = 0

Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool temporary data logical reads = Not Collected
Buffer pool temporary data physical reads = Not Collected
Buffer pool data writes = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool index physical reads = Not Collected
Buffer pool temporary index logical reads = Not Collected
Buffer pool temporary index physical reads = Not Collected
Buffer pool index writes = Not Collected
Buffer pool xda logical reads = Not Collected
Buffer pool xda physical reads = Not Collected
Buffer pool temporary xda logical reads = Not Collected
Buffer pool temporary xda physical reads = Not Collected
Buffer pool xda writes = Not Collected
Total buffer pool read time (milliseconds) = Not Collected
Total buffer pool write time (milliseconds)= Not Collected
Time waited for prefetch (ms) = Not Collected
Unread prefetch pages = Not Collected
Direct reads = Not Collected
Direct writes = Not Collected
Direct read requests = Not Collected
Direct write requests = Not Collected
Direct reads elapsed time (ms) = Not Collected
Direct write elapsed time (ms) = Not Collected

Number of SQL requests since last commit = 0
Commit statements = 78
Rollback statements = 0
Dynamic SQL statements attempted = 233
Static SQL statements attempted = 78
Failed statement operations = 52
Select SQL statements executed = 67
Xquery statements executed = 0
Update/Insert/Delete statements executed = 44
DDL statements executed = 8
Inactive stmt history memory usage (bytes) = 0
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 1
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Binds/precompiles attempted = 0
Rows deleted = 0
Rows inserted = 0
Rows updated = 0
Rows selected = 63
Rows read = 58
Rows written = 8

UOW log space used (Bytes) = Not Collected
Previous UOW completion timestamp = Not Collected
Elapsed time of last completed uow (sec.ms)= Not Collected
UOW start timestamp = Not Collected
UOW stop timestamp = Not Collected
UOW completion status = Not Collected

Open remote cursors = 0
Open remote cursors with blocking = 0
Rejected Block Remote Cursor requests = 0
Accepted Block Remote Cursor requests = 67
Open local cursors = 0
Open local cursors with blocking = 0
Total User CPU Time used by agent (s) = Not Collected
Total System CPU Time used by agent (s) = Not Collected
Host execution elapsed time = Not Collected

Package cache lookups = 109
Package cache inserts = 4
Application section lookups = 233
Application section inserts = 13
Catalog cache lookups = 16
Catalog cache inserts = 2
Catalog cache overflows = 0
Catalog cache high water mark = 0

Workspace Information

Shared high water mark = 0
Total shared overflows = 0
Total shared section inserts = 0
Total shared section lookups = 0
Private high water mark = 0
Total private overflows = 0
Total private section inserts = 0
Total private section lookups = 0

Most recent operation = Static Commit
Most recent operation start timestamp = Not Collected
Most recent operation stop timestamp = Not Collected
Agents associated with the application = 1
Number of hash joins = 0
Number of hash loops = 0
Number of hash join overflows = 0
Number of small hash join overflows = 0

Agent process/thread ID = 28196
Agent Lock timeout (seconds) = -1
Memory usage for agent:

Memory Pool Type = Other Memory
Current size (bytes) = 393216
High water mark (bytes) = 720896
Configured size (bytes) = 8589934592

В чем же тогда причина может быть? :( ПРосто рестартовать сервер, что ли?

Понятно, спасибо, продвинулся дальше :) Теперь имеем такое:

Agent process/thread ID = 15615
Agent Lock timeout (seconds) = -1
Memory usage for agent:

Memory Pool Type = Other Memory
Current size (bytes) = 524288
High water mark (bytes) = 524288
Configured size (bytes) = 8589934592

ID of agent holding lock = 38348
Application ID holding lock = 192.168.21.155.40909.0810281109
Lock name = 0x5039353030393333709A7C6A41
Lock attributes = 0x00000000
Release flags = 0x40000000
Lock object type = Internal Plan Lock
Lock mode = Share Lock (S)
Lock mode requested = Exclusive Lock (X)
Lock wait start timestamp = Not Collected

Однако таблица sysibmadm.snaplockwait по-прежнему не содержит записей...
Что-то я запутался.

Я вывел
select * from sysibmadm.snapappl_info where appl_id= '192.168.21.155.40909.0810281109'

Получил запись. Но это мне ничего внятного не сообщило. По-прежнему непонятно, кто же заблокировал процедуру... :(

И вообще немного непонятна общая картина:

у меня открыт toad, который использует каталогизированный нод для доступа к удаленной базе на
AIX-е.

В одном окне я попытался выполнить

select agent_id from sysibmadm.snapappl_info where appl_id=application_id();
set lock timeout -1;
drop procedure TSMALERTCONFIG.ACTIVITYLOGMESSAGEINCLUSIONLIKE_LOADBYCONFIGID;

затем, подконнектившись к серверному инстансу, я "поймал" id агента, который якобы вызвал lock, т.е. 38348

В другом окне toad-а я вывожу записи из sysibmadm.snaplockwait, в надежде узнать виновника. Но там вообще нет записей.

Бр-р-р-р!

Наконец-то... И приложение узнал через list applications.

А также узнал много нового и полезного. Очень благодарен Вам за помощь!