Too many temporary tables in connection error

[former_SQLA_member1694875]

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).
The procedure sp1 calls another procedure sp2 many times (inside a cursor in cursor) and sp2 has 1 temp. table declared itself, but I doubt it can be the reason. From the docs:

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.

Version: 11.0.1.2467.

[former_SQLA_member1694878 · Accepted solution]

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.

[VolkerBarth · Accepted solution]

Time to switch from DEFAULT AUTOINCREMENT to a SEQUENCE ... CYCLE in the SA server itself?

[former_SQLA_member1694875 · Accepted solution]

Thank You for the explanation. The upper limit is too small in this case. 🙂
Well, we'll try to find a workaround. The last pick (if commits won't be applicable) will be to move temp. table declaration from sp2 to sp1 and clear it everytime in sp2.