Stored Procedure in DB2 ver 8.2

[vin99]

I have one SQL with multi tables Joins and also two ot three subquesries. Group by clause and also Having claue in the same.

I have created a stores procedure for the same.
When in try to call this stores procedure from command editor it will take around 3 min or more to retrieve.

But when i fire the SQL from command editor it will retrieve in 15 sec.

what would be the reason for this ?

 

[sathyarams]

May be the data you want is cached in the buffer pool ..

The other reason could be that when you use dynamic sql (ie from command centre), your distribution statistics are used based on your where condition values to decide on the access plan, whereas in Stored Proc, assuming it is a static SQL, your access path has not been optimized for your where clause input value ... Try binding with an appropriate value for REOPT ...

HTH

Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp