SQL Packages and AS/400

We are running OneWorld at V5R2 and are having problems with the SQL Packages. The QZADOSINIT jobs show SQL1917 messages (Access plan not updated) many times and the performance of the system become very slow.

Did anyone already see this problem ?

In IBM the PMR number is 20125.

This would be a good question to cross post on the midrange-l list at
midrange.com. It's a free listserver. checkout www.midrange.com for
lists and signup info.



On Thu, 30 Oct 2003, dfaryniuk wrote:

> We are running OneWorld at V5R2 and are having problems with the SQL
Packages. The QZADOSINIT jobs show SQL1917 messages (Access plan not
updated) many times and the performance of the system become very
slow.Did anyone already see this problem ?In IBM the PMR number is 20125.
> Demetryo Faryniuk
> OneWorld XE SP 20 M1 - Update 7 / World A7.3 CUM 10 - Coexistent
> AS400 - Enterprise - V5R2
> W2K - Deployment
> W2K - Citrix Environment
> --------------------------
> To view this thread, go to:
http://www.jdelist.com/ubb/showthreaded.php?Cat=&Board=OW&Number=63381
>
> This is the JDELIST One World / XE Mailing List. To stop receiving
these messages, login to http://www.jdelist.com/forums, click Control
Panel, then click Edit by "Subscribe / Unsubscribe from receiving board
posts by email, change message notifications, etc." and adjust your
subscription preferences. JDEList is not affiliated with JDEdwards®

We don't show these messages. Have you reviewed the APAR II13384? We put on all the PTFs and CUMs recommended and we don't have any issues. Also, when you upgraded did you run the object converstion?

How often do you clear out the SQL packages?

I called IBM on the same issue and received this response:

There are many legitimate reasons for the SQL7917. One of the reasons is the size of the program. If the program is not ILE, and the program isnear 16 meg. then you may want to recommend users convert to ILE.If the program is ILE, then you'd want to investigate whether or not theSQL7917 is legitimate.In some cases the SQL7917's should not be generated. I suspect we'll becoming out with a PTF or two that will correct the situation where thereSQL7917 messages being incorrectly generated. That is still underinvestigation.For now, I'd recommend item listed above and start tracking the name of theprogram that is generating the message.

There is not alot we can do about it. But this message being generated should not be causing slow performance. Work with IBM on the items that are slow.

The client we are currently working with kept having this issue. They are 24/7 and have not more than 20 min. of planned downtime per month......could not delete sqlpkgs many times because there are, of course, locks on them all the time. Because the limit is right around 500MB for an sqlpkg and because there is also a finite number of statements, we kept finding that once the sqlpkg filled up (which is why you are seeing the message you are seeing), performance took a nose dive. This is because the sqlpkg is first searched for an appropriate statement. If none is found, the sqlpkg is not used. Then after the statement is executed, an attempt is made to save to the sqlpkg. Can't do because it is full. Because of all the extra steps, we tested NOT using sqlpkgs on the DV environment and had them pound it pretty hard. We stopped using SQLPKGs altogether about 5-6 months ago and life has been much, much happier.
BTW, this client is one of the top 10 largest AS400 OneWorld clients and have data going back about 5 years. Environment is OneWorld Xe, SP21L1

Jennifer Sharma, Sr. Tech Specialist
AELLIUS Professional Research & Consulting, LLC

We stopped using them 2.5 years ago and have had NO issues (because we
HAD performance issues with them).

Has anyone actually quantified a performance improvement with them?

Dan

OW 7321 on V5R2 AS/400.


______________________________

Dan Doenges
Dir of Technical Services
Department 56, Inc.
6436 City West Parkway
Eden Prairie, MN 55344
Office: (952) 943-4467
Mobile: (952) 240-0967
Fax: (952) 826-1203




OW 7321, SP 12.5, coexistant, ES 400 V5R2, DS Win2000

Re: RE: SQL Packages and AS/400

Dan,

I have benchmarked the performance of SQL Packages, and the performance improvement (if any) of course is 'It Depends'. <VBG>

In general when using SQL statments that execute over files with a small number of Logicals (access paths) such as F0002, there is a small improvement if the statement is found, if the statement is not found there is a versmall (< .5 sec) negative impact.

When files with a large number of logicals, such as F0911(we have about 70 logicals), if the statement is found we see a significant improvement in the statement of 2-3 seconds. If the statement is not found we see a very slight negative impact, unless the statement has been executed recently, then it may be in the system temp package and we see the 2-3 second improvement.

Just thought you would be interested.

Tom Davidson

How did you 'stop using sql packages'? Is there an option to turn them off? Is it in the QAQQINI file?

Use the windows ODBC Data Source Administrator tool. For XP, you will
see a "Packages Tab." Within that tab you will see a checkbox for
"Enable extended dynamic (package) support." Unchecked is off. Checked
is on.

Your ODBC admin tool may be different, but you will see something
similar.

What this means is that you will have to administer this for every
client. Until you have every client turned off, you will have SQL
packages being used. We use Microsoft policies to centrally manage ODBC
settings (and many other registry resident values).

Dan

______________________________

Dan Doenges
Dir of Technical Services
Department 56, Inc.
6436 City West Parkway
Eden Prairie, MN 55344
Office: (952) 943-4467
Mobile: (952) 240-0967
Fax: (952) 826-1203




OW 7321, SP 12.5, coexistant, ES 400 V5R2, DS Win2000

I'm sorry I took so long to respond.....very busy month! We basically did
this through updating the odbc.inf (not exact name, but you should be able to
find) on the deployment server that is used when packages are deployed. We
weren't really concerned with the system and server map data sources, but
more with the environment-specific data sources. We first did it only for
DV and PY environments, and allowed testing. Once this was done, we rolled
it out to all environments. Just be sure to make copies of the file before
altering, as this is not something that is "supported" by JDE. We have been
package-free for over 6 months.....otherwise it took a "near IPL" to get
those blasted things deleted when they reached 500MB, and performance is WAY
better now.

Also note: You cannot get rid of the UBE-specific SQL packages as that seems
to be hard-coded somewhere. We added a scheduled job entry to delete those
(dltsqlpkg B7334sys/R*) once a week on the weekends.....

Hope this helps, and hope you have a happy holiday season!!

Jennife

Hello,
We had the same problem on our AS400 and we had written a CL to deletethe SQLPKG who takes a lot of place on our AS400 .
Christophe KONIK
OneWorld Administrator
OW-Xe Update 2 SP17
AS400 V450 DB2
WTS W2k

-----Message d'origine-----
De : aprc [mailto:[email protected]]
Envoyé : jeudi 4 décembre 2003 20:12
À : [email protected]
Objet : Re: SQL Packages and AS/400


I'm sorry I took so long to respond.....very busy month! We basically did
this through updating the odbc.inf (not exact name, but you should be able to
find) on the deployment server that is used when packages are deployed. We
weren't really concerned with the system and server map data sources, but
more with the environment-specific data sources. We first did it only for
DV and PY environments, and allowed testing. Once this was done, we rolled
it out to all environments. Just be sure to make copies of the file before
altering, as this is not something that is "supported" by JDE. We have been
package-free for over 6 months.....otherwise it took a "near IPL" to get
those blasted things deleted when they reached 500MB, and performance is WAY
better now.
Also note: You cannot get rid of the UBE-specific SQL packages as that seems
to be hard-coded somewhere. We added a scheduled job entry to delete those
(dltsqlpkg B7334sys/R*) once a week on the weekends.....
Hope this helps, and hope you have a happy holiday season!!
Jennife
--------------------------
To view this thread, visit the JDEList forum at: http://www.jdelist.com/ubb/showflat.php?Cat=&Board=OW&Number=64891

This is the JDELIST One World / XE Mailing List. To stop receiving these messages, login to http://www.jdelist.com/forums, click Control Panel, then click Edit by "Subscribe / Unsubscribe from receiving board posts by email, change message notifications, etc." and adjust your subscription preferences. JDEList is not affiliated with JDEdwards®