Hi,

I've a table and a Stored procedure which selects the data on the table.
When I drop the table and re-create the table, the performance of stored procedure become decreased. SP is taking more time.

When I drop and re-create the Stored procedure also it's executing fast.

Why the dropping and re-creating the stored procedure improves the performance though the Stored procedure pointing out the table name only?
Is it necessary to drop and recreate the stored procedure also whenever the table is dropped and re-created?

When you drop the table on which the stored procedure (SP) depends, then DB2 notices that. The next time the SP is called, DB2 must verify that all dependencies can be resolved. Due to the DROP TABLE, this is not the case, of course. So DB2 has to recompile the stored procedure on the fly. This takes some time.

Do you see the performance degradation also when calling the SP repeatedly - without dropping anything in between?

Thanks for the reply.
I ran 6 times and then dropped SP and re-created.

Let me phrase my question differently:
- you drop + recreate the table
- you call the procedure
- you call the procedure again
- you call the procedure yet again

What's the performance of the procedure in the second and third call?