Hi,

I have successfully used the attached SQL against imadb to find
blocking sessions. In most up-to-date Ingres releases, for example
9.2.1 on Win-32, it does not show the expected results.

So the question: has something changed in the imadb area?

Regards
Gerhard Hofmann

SELECT s.server,

s.effective_user,

s.db_name,

s.session_terminal,

s.session_query,

s.client_host

FROM ima_locks lg,

ima_locks lb,

ima_resources r,

ima_locklists ll,

ima_server_sessions s

WHERE lg.lock_state = 'GRANTED' AND

lg.resource_id = r.resource_id AND

lb.resource_id = r.resource_id AND

lb.lock_state = 'WAITING' AND

lg.locklist_id = ll.locklist_id AND

ll.locklist_session_id = s.session_id AND

ll.locklist_server_pid = s.server_pid

Try 'GR' rather than 'GRANTED'

Robert

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Gerhard Hofmann
Sent: Thursday, 13 May 2010 9:15 a.m.
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] imadb SQL to identify locking sessions does not work anymore

Hi,

I have successfully used the attached SQL against imadb to find blocking sessions. In most up-to-date Ingres releases, for example
9.2.1 on Win-32, it does not show the expected results.

So the question: has something changed in the imadb area?

Regards
Gerhard Hofmann

SELECT s.server,

s.effective_user,

s.db_name,

s.session_terminal,

s.session_query,

s.client_host

FROM ima_locks lg,

ima_locks lb,

ima_resources r,

ima_locklists ll,

ima_server_sessions s

WHERE lg.lock_state = 'GRANTED' AND

lg.resource_id = r.resource_id AND

lb.resource_id = r.resource_id AND

lb.lock_state = 'WAITING' AND

lg.locklist_id = ll.locklist_id AND

ll.locklist_session_id = s.session_id AND

ll.locklist_server_pid = s.server_pid _______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres
Confidentiality/Privilege Notice:
This communication is confidential and may be legally privileged. If you are not the intended recipient please delete the message and notify the sender at Ports of Auckland Limited. Any use, disclosure, copying, distribution or retention of this communication is strictly prohibited.

On May 12, 2010, at 5:14 PM, Gerhard Hofmann wrote:

I think the lock_state has changed to 'GR' and 'WT'.
Try a select from ima_locks to see for sure.

Karl