0

I have a problem with the temporary table in DB2.

Inside a script, I declared a temporary table, inserted data into it (1 row inserted successfully - shown in the message after execution) but there is no row when I selected just after the INSERT statement, this results to another issue that I cannot join this temp table with another table cause no data for joining.

With the same code, I brought it into a stored procedure, it works. The temp table has data when selecting and it can be used for joining.

Anyone knows why? Can you advise me please? I need to work this in a script.

Thanks, Trong

Trong Lee
  • 1
  • 1
  • 1
    A temporary table is cleared on commit by default, unless you declare it with the "on commit preserve rows" clause. If you run your script with autocommit switched on, then each statement is executed inside its own transaction. So, thee table is cleared immediate after INSERT and implicit COMMIT afterwards. But all the same statements are executed in the same transaction in SP. – Mark Barinstein Nov 22 '20 at 07:01
  • Thanks Mark.Let me try with the "ON COMMIT PRESERVE ROWS" to see how does it work. :) – Trong Lee Nov 22 '20 at 07:41

0 Answers0