How to find indexes working on a temporary table in UDB AIX.(UDB 8.1)

[Sampath Reddy]

Hi Everybody,
We are using UDB v8.1
I will explain about my Stored procedures which we are executing in
UDB AIX box. We have 3 millions(apporox) of data in 22 tables. By
applying the business logic through Stored procedures on 22 tables and
writing into 3 new tables. We have used all temporary tables except
starting 22 tables. The Stored procedures we have used nearly 6
temporary tables to handle the business logic. Finally we are loading
into 3 permanent tables. For processing the above 3 million records it
took 29 hours, which should not happened to take that much time. We
are not able to find whether indexes which we have created on
temporary tables are working or not. We have tuned the same Stored
procedure and run in NT box for 1 lakh records it was executed in 6
min.
Can anybody will give a command to find whether indexes are working or
not? and one more question what is the limit, for which indexes will
work on temporary tables?
I will appreciate, if I get the solution as soon as possible. We are
in deep trouble. Please help me on this.

Thanks and Regards,
Sampath Reddy G.
Chennai - india

[Fan Ruo Xin]

"Sampath Reddy" <gs********@yahoo.com> wrote in message
news:4f**************************@posting.google.c om...

Hi Everybody,
We are using UDB v8.1
I will explain about my Stored procedures which we are executing in
UDB AIX box. We have 3 millions(apporox) of data in 22 tables. By
applying the business logic through Stored procedures on 22 tables and
writing into 3 new tables. We have used all temporary tables except
starting 22 tables. The Stored procedures we have used nearly 6
temporary tables to handle the business logic. Finally we are loading
into 3 permanent tables. For processing the above 3 million records it
took 29 hours, which should not happened to take that much time. We
are not able to find whether indexes which we have created on
temporary tables are working or not. We have tuned the same Stored
procedure and run in NT box for 1 lakh records it was executed in 6
min.
Can anybody will give a command to find whether indexes are working or
not? and one more question what is the limit, for which indexes will
work on temporary tables?
I will appreciate, if I get the solution as soon as possible. We are
in deep trouble. Please help me on this.

Thanks and Regards,
Sampath Reddy G.
Chennai - india

Capture the access plan you could find out if the temporary table use
indexes or not. I never did this. But I remembered I ever asked this
question and the instructor did the test and told me the result.