SP with temp tables getting error "code: -313 Mess

Hi all, I am trying to troubleshoot an issue and would really appreciate advice from some experienced Informix users. I have a front end reporting application which is using stored procedures on an IDS 11 database (version 11.50.UC9X7) on the backend. Intermittently (typically after a few days of using the report) the report will fail with "SQLState: IX000 Vendor code: -313 Message: Not owner of table" error. It fails persistently after that until the session is reset. Some further information, the stored procedure essentially takes the following steps: 1. On exception -206 drop temp tables 2. Create temp tables 3. Perform the actions to generate the results 4. Drop temp tables (clean-up) - The stored procedure is created by user "hruser" (i.e. "hruser" is the owner of the stored procedure). - The reporting application executes the stored procedure as user "hrclient". Under normal circumstances, the stored procedure should clean up it's temp tables at the end of the procedure, so it should be clean for the next time it runs. For some reason after some time it seems the SP (perhaps it is failing somewhere) and leaving the temp tables existing, the next time it runs it fails to clean up the temp tables. Initially our DE's suspected we were running into this issue: http://www-01.ibm.com/support/docview.wss?uid=swg1IC69822 But I don't think that is the case as when we could see the temp tables we could see the owner was "hruser" Some questions to help me troubleshoot this: 1. If user "hrclient" executes a stored procedure which is owned by "hruser" which creates temp tables, then who should the owner of the temp tables be? From research I think it is supposed to be "hruser" (i.e.: the owner of the stored procedure) but I'm not 100% sure, can someone confirm? 2. Assuming the above, when running the stored procedure with user "hrclient" (always only run by this user), should this have permission to drop the temp tables? 3. Any other information or suggestions to help with this issue, to try to understand why it works for some time then fails? Please let me know if any further details are required, I will very much appreciate any assistance. /Michael

I always drop the tables at the beginning of the procedure for that very reason. Thank you, Jim Goldrick System Admin (573) 979-9079 jgoldrick@judsonu.edu Reminder: Judson University IT will never ask for your password or other personal information over email. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MICHAEL GREEN Sent: Tuesday, July 09, 2013 6:53 AM To: ids@iiug.org Subject: SP with temp tables getting error "code: -313 Mess [30796] Hi all, I am trying to troubleshoot an issue and would really appreciate advice from some experienced Informix users. I have a front end reporting application which is using stored procedures on an IDS 11 database (version 11.50.UC9X7) on the backend. Intermittently (typically after a few days of using the report) the report will fail with "SQLState: IX000 Vendor code: -313 Message: Not owner of table" error. It fails persistently after that until the session is reset. Some further information, the stored procedure essentially takes the following steps: 1. On exception -206 drop temp tables 2. Create temp tables 3. Perform the actions to generate the results 4. Drop temp tables (clean-up) - The stored procedure is created by user "hruser" (i.e. "hruser" is the owner of the stored procedure). - The reporting application executes the stored procedure as user "hrclient". Under normal circumstances, the stored procedure should clean up it's temp tables at the end of the procedure, so it should be clean for the next time it runs. For some reason after some time it seems the SP (perhaps it is failing somewhere) and leaving the temp tables existing, the next time it runs it fails to clean up the temp tables. Initially our DE's suspected we were running into this issue: http://www-01.ibm.com/support/docview.wss?uid=swg1IC69822 But I don't think that is the case as when we could see the temp tables we could see the owner was "hruser" Some questions to help me troubleshoot this: 1. If user "hrclient" executes a stored procedure which is owned by "hruser" which creates temp tables, then who should the owner of the temp tables be? >From research I think it is supposed to be "hruser" (i.e.: the owner of the stored procedure) but I'm not 100% sure, can someone confirm? 2. Assuming the above, when running the stored procedure with user "hrclient" (always only run by this user), should this have permission to drop the temp tables? 3. Any other information or suggestions to help with this issue, to try to understand why it works for some time then fails? Please let me know if any further details are required, I will very much appreciate any assistance. /Michael ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Yes, but that is already being done here if you look at the steps in the SP. Basically the developer of the SP has followed the recommendation which is shown in the most upvoted answer on this question: http://stackoverflow.com/questions/1834699/life-span-of-temp-table But this is not helping here as it seems to not be able to drop them at the start (see original post with error 313) unless they are dropped successfully at the end of the SP.