DB2 Temporary Tables

ajay_dheepak · New User Joined: 12 May 2005 Posts: 32 Location: Chennai

Hi

I need to know

1. what is the purpose of using temp. tables inside stored procs?
2. When the temp. table will get destroyed if it is not dropped in a stored pro?

Also tell me whether (a) or (b) is more efficient.
(a) I have a single query with two Full outer joins declared in a cursor. I'll open it at the end of the proc for the fron end to retrieve data.

(b) I have three cursors... I'll declare a temp table and populate each row with the value fetched from the three cursor and insert it to the temp table. Finally I'll declare a cursor for that temp table to fetch all rows and open it at the end.

I need to know these details. Pls write to me.

- ajay

parikshit123 · Active User Joined: 01 Jul 2005 Posts: 269 Location: India

Obviously,

(a) is more efficient it has lesser overheads.
All though, being it an outer join, (a cartisian product), you should think of optimization with putting restriction as opposed to join before restriction before join if possible.