|
Hi,sure, each SQL-statement gets its own ODP. That means if you code the same statement over and over again in different programs or service programs you'll end up in a mass of ODPs. But if you only run the statements once, the ODP gets closed after the first call and only stays open after the second one. BTW there is an option in the QAQQINI table to predefine the number of ODPs per job. As far as I remember the default value is 512 per job. If the number of ODPs is reached, the ODP that was the longest not used gets closed and with the next call a full open will be executed.
Modernizing Database means minimizing the full opens. The best way to do this is to encapsulate SQL-Statements into exported procedures that can be called whenever needed. Procedures that only select data, should be grouped in service programs with named activation groups. In this way the same SQL-Statement will only be opened once per job and the number of ODP can be reduced. All procedures with insert, update and delete statements should run in *CALLER activation groups to guarantee that commit and rollback work correctly. ´
Have you had problems leaving the ODPs open? And why not close the activation groups?I rather run into other problems. Neiter our users nor clients will accept to wait 30 seconds for each call, but they will accept the first call taking 30 seconds if all subsequent calls need only one or two seconds.
For more information here is an interesting article written by Dan Cruikshank:
Understanding Access Plans and Open Data Paths http://www-03.ibm.com/servers/eserver/iseries/db2/pdf/UnderstandingSQL.pdf Mit freunlichen Gruessen / Best regards Birgitta Hauser"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)----- Original Message ----- From: <rob@xxxxxxxxx>
To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx> Sent: Friday, October 27, 2006 19:05 Subject: RE: Embedded SQL newby question... We are running into a LOT of issues, especially lately, when leaving these ODP's around is causing us grief. If we were calling the same program multiple times, that might be different. But when you think you're looking at data in another file from something else and find out it's using one of these existing ODP's and find you're looking at the wrong data, it does not make for a pleasant experience. Rob Berendt
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
