DB2 Stored Procedure Problems

[balachandar]

HI All,
I am using DB2 UDB 7.2. I have the following problems when ever I use stored procedures.

1. Stored procedures take more time for execution than if the query is executed as such. Are stored procedures reliable ?.
2. Very often we are not able to rebuild,drop procedures which were created earlier.Why is this ?.
3. Where can I get a detailed note on stored procedures?
4. Are there any tips regarding stored procedures >

Thanks and Regards
Balachandar Ganesan

 

[DBGod]

What platform are you executing on?

Mick

 

[balachandar]

The server is on UNIX Server. We have clients on NT and WIN2K.
Thanks

 

[DBGod]

Don't know much about the Unix side....I'm more OS/390. But:-

1) Execution times between SPs and queries should be similar if the same access path is being used. Stored Procedures are generally very reliable, and provide a method of standardizing database accesses and also reducing network traffic.

2) Could be an authority problem if different users are doing the DROPs etc.

Can't help you on 3 and 4.

Regards

Mick

 

[balachandar]

How do I ensure that Access path is the same?. Can you please clarify ?. Is there anything that has to be done on the server?