Our customer has an event and a stored procedure sp1 which has only 2 temporary tables declared (declare local temorary table...). After running for some hours it throws an error: Too many temporary tables in connection (SQLCODE -817).
Declared local temporary tables within compound statements exist within the compound statement.
There are no triggers in the table which is updated in the procedure.
What reasons may cause this error? What should we check/consider? Thanks.
There is a upper limit of 1,048,576 temporary tables that can be created per transaction. Dropping a temporary table will not allow you to create more than this number. Temporary tables in a transaction are assigned auto-incrementing IDs and currently the server cannot go back and 're-use' IDs of dropped temporary tables.
Is there a place where you could execute a COMMIT or reuse the same temporary table? For example, after each iteration of the cursor you could clear the temporary table and reuse it for the next iteration.
Edit: This is enhanced in version 17. The server can now go back and re-use IDs of dropped temporary tables.