Informix

 View Only
  • 1.  How to see what querry blocked table?

    Posted Tue November 22, 2022 04:11 AM
    Hello.
    I have Informix 14.10.

    I started the session and checked DBINFO('sessionid').
    Then in that session i ran:
    BEGIN;
    INSERT INTO ttt VALUES(1);

    In another session i ran 

    SELECT * FROM syslocks;

    and see 2 locks on my table  and the owner is my sessionid.

    I was expecting that when i run onstat -g sql sessionid i will see my Insert command, but I see 

     SELECT ODB_DBName FROM SysMaster:"informix".SysOpenDB WHERE ODB_SessionID
        = DBINFO('sessionid') AND ODB_IsCurrent = 'Y'


    How i can see the real querry that is locking my table?



    ------------------------------
    Alexander Ivanov
    ------------------------------

    #Informix


  • 2.  RE: How to see what querry blocked table?

    IBM Champion
    Posted Tue November 22, 2022 06:53 AM
    (Not sure where this sysopendb query comes from or why it's being shown ... it probably is irrelevant here.)

    It's not the INSERT statement that holding those locks, but the surrounding transaction.  The INSERT, if it was the last statement in this session might still be visible as 'last parsed SQL statement', but there could as well have been N other statements running since that particular INSERT.

    What you're typically interested in is which session is holding a given lock, and then maybe whether the lock originates from an INSERT, UPDATE or DELETE.  The latter you could see from 'onstat -k' -> DML column.

    ------------------------------
    Andreas Legner
    ------------------------------



  • 3.  RE: How to see what querry blocked table?

    Posted Wed November 23, 2022 09:27 AM
    For some reason DDL column is empty in onstat -k result.

    ------------------------------
    Alexander Ivanov
    ------------------------------



  • 4.  RE: How to see what querry blocked table?

    IBM Champion
    Posted Tue November 22, 2022 10:29 AM
    I have seen this same statement used before in stored procedures - it is simply getting the database name for your connection.

    Assuming that you haven't run any more SQL in the first session where you performed the insert, then I'd see if there are any triggers on the ttt table (unlikely if this is just your own test table).

    What are you using to connect to the database and run the SQL?  If you are using some tool or application to query the database other than dbaccess, then it may execute its own SQL.  Are you connecting through a web server or app server?

    Try using dbaccess on the server itself and see if you get the same results.


    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------



  • 5.  RE: How to see what querry blocked table?

    Posted Wed November 23, 2022 09:24 AM
    Thanks.
    Really whe I use dbaccess I can see expected results.

    ------------------------------
    Alexander Ivanov
    ------------------------------