Env: DB2 8.2/AIX 5.3

Is it possible to estimate tempspace requirement for a complex query before execution using explain plan or any other method ?

Tempspace tablespaces should be defined as SMS (to speed up creating tables). You should have at least enough space for the temp tablespace to grow to as large as your largest table.

For a particular query, you would need to look places where data is spooled to a temp tablespace in the explain, so you should be able to estimate it. They are usually associated with sorts. Are you having trouble understanding the explain? If so, try the visual explain from the Control Center.