Dropping temp table throws -242/-106

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

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 www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ 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.

Thanks Art. I'll let the programmer responsible look into this. Regards, -Snorri ------------------------------ Snorri Bergmann ------------------------------

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 ------------------------------

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 ------------------------------