An IMADB question - which table is locked? 
Author Message
 An IMADB question - which table is locked?

Hello all,

Having spent my free time the last few days reading the Ingres manuals
and looking at the definitions of the imadb in the vdba directory I thought
perhaps it was time to ask the group.

The examples given in the System's Administrator's Guide for determining
who is holding locks (Ingres 2.5 pg 10-21) is interesting....
but incomplete in a practical sense.

Is there a relatively easy path between the resource ID and the table name?
If so, please post the info.

Our intention is to run a script every 5 minutes on each machine.  
Compare the results with that of the previous run and report on locked resources
which appear in both runs.  Both the users (easy) and the resource (my question)
would be reported in an email to the DBA group.

Thanks

Fred

Frederick Myers
Ingres DBA  
Western Australian Department of Training & Employment
Perth Australia



Sun, 18 Jul 2004 19:07:53 GMT
 An IMADB question - which table is locked?

ima_resources.resource_table_id=iirelation.reltid
ima_resources.resource_index_id=iirelation.reltidx

The problem is the 'iirelation' table is not in imadb.
You'll need to store copies of iireation from each database
in imadb (I havent' tried that) or use DBAnalyzer (www.dmt.com)

Piotr

-----Oryginalna wiadomo??-----

Wys3ano: Wednesday, January 30, 2002 11:20 AM
Do: <
Temat: An IMADB question - which table is locked?

Hello all,

Having spent my free time the last few days reading the Ingres manuals
and looking at the definitions of the imadb in the vdba directory I thought
perhaps it was time to ask the group.

The examples given in the System's Administrator's Guide for determining
who is holding locks (Ingres 2.5 pg 10-21) is interesting....
but incomplete in a practical sense.

Is there a relatively easy path between the resource ID and the table name?
If so, please post the info.

Our intention is to run a script every 5 minutes on each machine.  
Compare the results with that of the previous run and report on locked
resources
which appear in both runs.  Both the users (easy) and the resource (my
question)
would be reported in an email to the DBA group.

Thanks

Fred

Frederick Myers
Ingres DBA  
Western Australian Department of Training & Employment
Perth Australia



Sun, 18 Jul 2004 20:14:42 GMT
 An IMADB question - which table is locked?

Quote:

> Hello all,

> Having spent my free time the last few days reading the Ingres manuals
> and looking at the definitions of the imadb in the vdba directory I thought
> perhaps it was time to ask the group.

> The examples given in the System's Administrator's Guide for determining
> who is holding locks (Ingres 2.5 pg 10-21) is interesting....
> but incomplete in a practical sense.

> Is there a relatively easy path between the resource ID and the table name?
> If so, please post the info.

> Our intention is to run a script every 5 minutes on each machine.  
> Compare the results with that of the previous run and report on locked resources
> which appear in both runs.  Both the users (easy) and the resource (my question)
> would be reported in an email to the DBA group.

> Thanks

> Fred

> Frederick Myers
> Ingres DBA  
> Western Australian Department of Training & Employment
> Perth Australia

Hi Fred

I have written a little 4gl program that checks for blocking locks and
emails details of the blocker/blockee and the query that is being
blocked.  the main sql of the program is below (this progam runs in a
loop and requires a dbevent to stop it):

WHILE ( :exit_loop = 0 ) DO

    sleep :loop_time;

    /*  Check for locks - and display the blocker and blocked details
*/

    SELECT block_count = count(*)
      FROM ima_server_sessions_extra
     WHERE session_wait_reason     = 'LOCK';

    CALLPROC fap_errhan (en = byref(:en), rc = byref(:rc), em =
byref(:em));
    IF en != 0 THEN
      CALLPROC printmsg ('IMACTRL: Error reading session extra
details');
      RETURN;
    ENDIF;
    COMMIT;

    IF block_count > 0 THEN

      CALLPROC printmsg ('IMACTRL: Blocked sessions have been found
!');

      SELECT session_id  = ss.session_id
           , real_user   = ss.real_user
           , client_info = ss.client_info
           , session_query = ss.session_query
        FROM ima_server_sessions ss
           , ima_server_sessions_extra se
       WHERE se.session_wait_reason = 'LOCK'
         AND se.session_id          = ss.session_id
       {

         /* Try to find the blocker and display both blocked and
blocker */

         SELECT blocker_session_id  = s.session_id
              , blocker_client_info = s.client_info
           FROM ima_server_sessions s
              , ima_locklists ll
          WHERE s.session_id = ll.locklist_session_id
            AND ll.locklist_id =
              ( SELECT z.locklist_id  
                  FROM ima_locks z
                 WHERE z.lock_state = 'GRANTED'
                   AND z.resource_id =
                     ( SELECT distinct i.resource_id
                  FROM ima_locklists l
                     , ima_locks i
                 WHERE l.locklist_session_id = :session_id
                   AND l.locklist_status = 'WAITING'
                   AND l.locklist_wait_id = i.lock_id));

         CALLPROC fap_errhan (en = byref(:en), rc = byref(:rc), em =
byref(:em))
;
         IF en != 0 THEN
           CALLPROC printmsg ('IMACTRL: Error reading session extra
details');
           RETURN;
         ENDIF;
         COMMIT;

         CALLPROC printmsg ('IMACTRL: Session ' + trim(:session_id)  +
'(' + hex
(int4(:session_id)) + ')' + ' for ' +  trim(:real_user) + ' is
blocked');

         sys_cmd := '/bin/printf ' + '"' + 'SESSION ID: ' +
hex(int4(:session_id
)) + '\n' + 'CLIENT DETAILS: ' + :client_info + '\n' + 'QUERY: ' +
:session_quer
y + '\n' + 'BLOCKER SESSION ID: ' + hex(int4(:blocker_session_id)) +
'\n' + 'BLO
CKER CLIENT DETAILS: ' + :blocker_client_info  + '"' + ' | /bin/mailx
-s "' + 'I

         CALL SYSTEM :sys_cmd;

       };

    ENDIF;

    /* Check to see if the imablockedlocks_shutdown dbevent has been
raised */

    GET DBEVENT;

    INQUIRE_SQL( event    = dbeventname
               , owner    = dbeventowner
               , db       = dbeventdatabase
               , evdate   = dbeventtime
               , eventtxt = dbeventtext);

    COMMIT;

    IF event = 'imablockedlocks_shutdown' THEN

      CALLPROC printmsg ('IMABLOCKEDLOCKS: Shutdown dbevent found -
exiting loop
');
      exit_loop := 1;

    ENDIF;

  ENDWHILE;

Hope this helps!
Cheers
Glenn



Mon, 19 Jul 2004 05:27:54 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Enterprize Manager locks up when i am trying to make changes to a table

2. Table is locked, workstation coughs, table stays locked.

3. i am facing a problem with row level locking

4. Locking error, DataControl Error, or am I stupid ?

5. Why am I locking here?

6. creating imadb for Visual DBA

7. update with no effect in imadb

8. IMADB

9. Getting info from imadb

10. Access to imadb

11. imadb

12. VDBA problems accessing imadb under Digital UNIX


 
Powered by phpBB® Forum Software