Re: Trouble Droping Temp Table

My users had a problem with dropping temp table. It turned out to be an informix bug that prevented the temp dbspace from getting cleaned out. After a few months the temp dbspace was so chopped up that the users would get a temp table with 100+ extents. It took so long for informix to deal with these tables that informix kept them locked from the user. I was running 7.22.UC3 on HPUX10.10. They had me upgrade to 7.23UC6 Dragi Raos wrote: > > KPonn wrote: > > > > Hi, > > > > We have a Power Builder application to generate reports. We have one user id to > > login to PB (and then to Informix). So, two people (P1 and P2) can login into > > the same application and run the same report. In generating this report we do > > the following: > > 1. genereate temp table A,B,C with no log; > > 2. Insert into A,B, and C. > > 3. Drop table A,B, and C > > Everything is done in that order. > > Problem: If only P1 starts the application (using user_id above) and we have > > no problems. Then P2 logs in (with same user id as P1) and that's it ..Temp > > tables are locked and cannot drop them. We get SQL error of -242. Amazing thing > > is that no matter how many users run this application, all temp tables > > generated are all Unique. No two temp tables have the same name at any given > > name No matter how many users log in (They must all use the same user id to > > start though!). > > > > Thanks in advance for your help. > > > > Keith > > Hi! > > This is quite strange... Error -242 says that informix cannot read a > table, not drop it. What is a C-ISAM error code (they often tell more > than SQL error)? > > You say you create temporary tables, and also that the names are unique. > Why are the names unique? I mean, temp tables are local to particular > connection; so, unless you are using some kind of application server in > stead of connecting to Informix directly, each user has their own temp > tables, regardless of names. > > I was once puzzled when my application (written in JAM) refused to drop > a temp table; it turned out that I still had a cursor reading from it > open. Check out for something like that. > > Regards, > > Dragi "Bonzi" Raos > > +--------------------------------------------------------+ > | 4-MATE Information Engineering Inc. | > +--------------------------+-----------------------------+ > | 4-MATE | Tel: +385 (1) 242-116 | > | Kosa bb | +385 (1) 242-126 | > | 10000 Zagreb | Fax: +385 (1) 242-121 | > | Hrvatska (Croatia) | e-mail: bonzi@4mate.hr | > | | URL: http://www.4mate.hr | > +--------------------------+-----------------------------+