Informix

 View Only

  • 1.  Dropping temp table throws -242/-106

    Posted Wed August 26, 2020 12:29 PM
    Hello everyone.

    We're on 14.10.FC3

    I ran today into a problem that has happened before (on 12.10.Fxx)  but it is sporadic and impossible to reproduce.

    Basically, a running program with one connection to the database creates a bunch of temp tables and later drops them. These tables are created with no log etc. Rarely one of the drop table statements fails with -242 and isam -106. This should not happen AFAIK. Has anyone experienced this? 

    We are running in SDS mode and the this program is connected to the primary.

    Best regs,
    -Snorri

    ------------------------------
    Snorri Bergmann
    ------------------------------

    #Informix


  • 2.  RE: Dropping temp table throws -242/-106

    IBM Champion
    Posted Wed August 26, 2020 12:34 PM
    Snorri:

    Usually this is because the application that created the temp table has a PREPARED statement or CURSOR or both that includes that temp table and which was not FREEd before the DROP TABLE statement was issued.

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.






    Original Message


  • 3.  RE: Dropping temp table throws -242/-106

    Posted Wed August 26, 2020 12:40 PM
    Thanks Art.

    I'll let the programmer responsible look into this.

    Regards,
    -Snorri

    ------------------------------
    Snorri Bergmann
    ------------------------------

    Original Message


  • 4.  RE: Dropping temp table throws -242/-106

    Posted Wed August 26, 2020 02:51 PM
    I believe this error could also be generated within the server due to a timing problem with the drop temp table statement and another session that might be running a sysmaster query looking at partition pages (so like sysptnhdr if I remember correctly).  I don't believe anything can be done with regards to the collision with a sysmaster table due to the shared low level rsam code and the 106 error is non-exclusive access and when looking at the partition structure via sysmaster certain code gets hit which bumps the open count of the structure (so it can't get freed out from underneath it), which could then trigger the 106 error when trying to drop the partition.  I also seem to recall possibly defects along these lines as well, but since you mention 14.10.FC3, I would guess any defect I was remembering would have been fixed.  I guess there might be some room to suggest that the server maybe do some sort of internal retry on a 106 error in the case of the temporary timing problem between a drop and somebody running a sysmaster query that is looking at partition structures in memory.  I don't recall if this has possibly been implemented or suggested already.  Alternatively, you could try and add a retry in your application and see if the drop would go through if resubmitted.

    ------------------------------
    Jacques Renaut
    ------------------------------

    Original Message


  • 5.  RE: Dropping temp table throws -242/-106

    Posted Wed August 26, 2020 03:27 PM
    Hi Snorri!

    I run into the same problem earlier. The problem was we setup the AUTO_READAHEAD onconfig parameter to aggressive. Maybe you run into the same problem.
    We have a APAR for this problem. 
    https://www.ibm.com/support/pages/apar/IT32027

    Original thread: 
    https://community.ibm.com/community/user/hybriddatamanagement/communities/community-home/digestviewer/viewthread?MessageKey=480169c9-f6d4-489f-98b1-0df840f833b8&CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365&tab=digestviewer#bm480169c9-f6d4-489f-98b1-0df840f833b8

    ------------------------------
    Gábor Fekete
    Software Engineer
    Capital Systems
    Budapest
    ------------------------------

    Original Message