I'm just actually soliciting general comments.

I need to use a temp table in a stored proc in orser to do some manipulation of data (sorting, updating, etc.) The database guys here (who are 99% mainframe) are having a fit because "no application here has EVER used a temp table" blah blah blah....

Please comment on any problems - small or large - you may have encountered. What are the risks/rewards. Are they just overreacting?

We're on AIX DB2 v8 FixPak 4a.

Thanks in advance...

There are three types of temp tables.
1) "permanent" temp tables. These are basically real tables that are used for temporary storage. They can be used to pass data between applications, etc.
2) declared temporary tables (DECLARE GLOBAL TEMPORARY TABLE ...) . These hang around only as long as the session is active (hence the special schema od SESSION.<table>).
3) implicit temporary tables (WITH TEMP1 AS (Select * from department) ...)

All three have their advantages. We are using all three in our SPs. We use (1) for passing large amounts of data to the SP. We us (2) for storing a smaller subset of data than the base tables and then perform operations on the temp table. Very handy. We use (3) whenever this type of query is needed and (2) is overkill for the operation.

An example for (1) is they want to get data for an unspecified number of employees that is picked by an external (to the SP) process. The temp table holds the list of employees where the SP can use an IN predicate.

An example of (2) would be gathering some data and doing some data transposing on a subset of it. I just did this one about a hour ago. It worked fast and great.

We have not had any problems with temp tables. We like them. The only risk I can see is that all the 3 types are written to disk (however short the life of the temp table).

I believe they are overreacting. The main reason is that with a temp table you can work on smaller subsets of data writing the SQL to utilize indexes etc to perform faster. Writing it without temp tables could result in a table scan. I have seen performance improvements changing some queries to use temp tables.

HTH

Andy