DB2 stored procedure performance issue:
Posted: Wed Feb 16, 2011 7:26 pmIn my project, we use VB application which pulls data from mainframe DB2 tables using inline queries.
Recently we implemented a project for decommissioning the VB application and migrated to .NET platform as per standards and policies of the organization.
The .NET application calls DB2 stored procedures to pull data from mainframe tables as opposed to inline queries used by VB application.
There are some stored procedures executing for a very long time and therefore we are not able to generate reports from .NET application.
I wanted to know the techniques to improve stored procedure performance so that the execution time is reduced.
Note: We have used dynamic SQL in one stored procedure where it was not possible to use static SQL. Is dynamic SQL causing queries to perform poorly? If yes, is there any way we can improve performance of dynamic SQL?
Are there any specific parameters that increase execution time of stored procedures?
Appreciate your help on this. If you require more details, kindly let me know.
Recently we implemented a project for decommissioning the VB application and migrated to .NET platform as per standards and policies of the organization.
The .NET application calls DB2 stored procedures to pull data from mainframe tables as opposed to inline queries used by VB application.
There are some stored procedures executing for a very long time and therefore we are not able to generate reports from .NET application.
I wanted to know the techniques to improve stored procedure performance so that the execution time is reduced.
Note: We have used dynamic SQL in one stored procedure where it was not possible to use static SQL. Is dynamic SQL causing queries to perform poorly? If yes, is there any way we can improve performance of dynamic SQL?
Are there any specific parameters that increase execution time of stored procedures?
Appreciate your help on this. If you require more details, kindly let me know.