Extended Dynamic Support for SQL Packages and Lost Tabs

Hi All,

We occassionally have issues relating to corrupt SQL packages that reside on our iSeries. We IPL every week and delete all SQL packages at that time. My understanding of SQL packages are that they are created to improve performance. Additionally, the way you take advantage of these SQL packages is to have Extended Dynamic support enabled in your ODBC configurations of each data source.

Typically, when a user has an issue that I believe is related to a corrupt SQL package, I have the user turn off Extended Dynamic support and their problem usually goes away. Additionally, when I ask the user if they notice anything running slower they indicate that they notice no difference in speed.

We have an on-going issue where *PUBLIC tabs for certain groups of users "disappear". We have a number theories as to why this is happenning but nothing we can prove.

Questions:
Does anyone else experience lost tabs and what do they do about it?
Does Extended Dynamic support really provide a significant (noticeable) performance improvement?
Has anyone ever turned off Extended Dynamic support on all the data sources to see how well things perform?
Does anyone have any idea as to which data source tabs would be accessed through?

Any ideas or suggestions would be appreciated.

Thanks in advance, Jay

We use Extended Dynamic Support. I can't provide any type of benchmark on speed improvement as we've never really run clients without it. I can tell you from reviewing DB Monitor jobs on our AS400 that JDE Clients use the SQL packages rather widely - as do our UBE's. I rarely ever see OneWorld queries that are not using reusable ODP (which is what is placed in the SQL packages).

That being said, I doubt individual users would notice QBE and UBE programs running noticibly faster. The SQL packages primarily decrease CPU cycles in Access Path Query Plan Optimization - which in a cost based system, usually is NOT a great deal of time. Now, if you could setup two identical systems with identical traffic (say, several thousand QBE's and hundreds of UBE's in one hour), and set one system to use SQL packages and the other to not - well, there I would expect an admin to be able to find quantifiable differences in performance metrics.