I have had a process that running on ids 7.31 , where it use the dbspace temp to create some temporary tables. This process always ran in around 1 hour.
I migrated the ids to 9.40 and now my process run all night long and doesn´t finish. When i rewrited the process to doesn´t use temporary tables it ran faster.

Do someone know if there is any difference about the use of dbspace temp on IDS 9.40??

stupid question I know, but you have updated stats since migrating?

Another stupid question: have you verified that:
- the temp table is created 'with no log', or the select that creates it has that phrase as the last statement.
- The temporary dbspace(s) was/were created as 'temporary' (check onstat -d) (don't laugh, it was the case in one of my customers)
- the temporary dbspace(s) is/are listed in the onconfig file (DBSPACETEMP), or in the application environment (DBSPACETEMP)

Just my 2 cents...

When migrating from IDS 7.x to IDS 9.x, you should issue the following:

update statistics low drop distributions;
update statistics medium distributions only;

then update statistics the way you normally do.

thanks for all by the help.

This situation is a knew defect on IDS 9.30 and greater.

the problem in the fact is this :

when you are creating a temp table and populate data on it, and after you create a index on the temp table. the optmizer don´t caucule the rowcount for this table, because of this it can´t use the index. You have to run a update statistcs low on table to solve the problem.

on the IDS 7.31 the optimizer caucule this information