V5R2 and SQLPKGs

I was going to post this on the recent SQLPKG thread, but decided it's different enough to warrant a new thread.

We upgraded to V5R2 in the spring, and since then we've had repeated problems with corrupt SQLPKGs. The symptom in OW is usually the same - a user goes into an application and clicks "find", but no records are returned. The application can vary, but it seems to affect only one application at a time. JDE.log contains:

440/988 Fri Aug 13 09:55:37 2004 JDBODBC.C5319
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0516 - Prepared statement QZ88435337A32CEDA2 not found. - SQLSTATE: S1000

440/988 Fri Aug 13 09:55:37 2004 jdb_drvm.c920
JDB9900401 - Failed to execute db request

The solution is to take services down, delete all SQLPKGS (I believe the problem SQLPKG is the OEXPLORFJA in PRODDTA) and bring it back up again - at least once a week.

I'm working with both IBM and PS on this, but no solutions seem to be forthcoming. Now I seem to recall someone else mentioning problems with SQLPKGS and V5R2. I'd like to get a couple more IBM clients with problems in order to get a bit more visibility on this problem. Has anyone else had this problem?

We too are experiencing this issue. The applications vary but the symptoms are the same....a user clicks Find^ and no records appear. Our configuration is quite like yours...AS/400 V5R2M0, Citrix/Metaframe, OneWorld Xe, XU7, SP21_G1, currently using Database Fix Pack 12.

The issue occurs sporadically.

I thought there had (once) been a recommendation from 'JDE' to weekly delete the SQL Packages, anyway...

I don't know if the recommendation had ever been updated -

regards

hi Jamie92688 thanks for replying to my post.

I want to PM you to perhaps get IBM or JDE call numbers, but you're set not to allow PMs. Can you contact me at dpeplinski (at) brantford(dot)ca

I thought there had (once) been a recommendation from 'JDE' to weekly delete the SQL Packages, anyway...

I don't know if the recommendation had ever been updated -

regards

Click to expand...

a question on this - how is this done automatically? As it stands, I have to take down edwards, and delete many of the QZDASOINIT jobs

Current PSFT recommendation IS to delete every week.

However, automatically, that's a good question. Certainly can't simply delete all SQLPKG's...well actually, you can, but boy will you be in for some fun...and since there are SQLPKG's that have unique names and would only appear if you've run certain processes, I'd like to see if anyone has any nifty CL script that will delete, automatically, all non-essential SQLPKG's.

Regards,

Thank you dpeplinski,

I have changed my settings on JDELIST to allow PMs.

While unsure of the cause, we have been investigating the issue in-house, enlisting users to help capture more details to substantiate this as a 'real issue'. The 'JDB9900401 - Failed to execute db request' message has been the most of what has been generated by the system in this case, and invariably..when shadowing user sessions....another click of the Find^ button lists the records as expected.

We were suspecting a red-herring, but your post mirrors these symptoms. We build and deploy update packages almost every week... weekly we delete all JDE *SQLPKGS and bounce services. This seems to correct the problem for the first couple of days in the week, but the issue is (now) virtually expected to reoccur. An IBM and/or (JDE) PeopleSoft call number will happen after the next occurrence.

There is a know issue with ODBC's for R520 (V5R2) which is fixed in R530 (V5R3). You'll likely see this issue in both V5R2 and V5R3. Here's what I did on a V5R3 box to resolve the issues: (perform in the indicated order)

1. Delete all EnterpriseOne SQL Packages
-Stop EnterpriseOne (ENDNET & CLRIPC)
-Stop Host Services (ENDHOSTSRV *ALL)
-Delete all SQL packages
-use the following commands to speed up this process:
DLTSQLPKG (*ALL/R*)
DLTSQLPKG (*ALL/A*)
DLTSQLPKG (*ALL/O*)

2. Delete an IBM SQL Package
-end all subsystems ENDSBS *ALL *IMMED
-delete the IBM SQL Package QGPL/QZDAPKG
-start all subsystems (this will recreate QGPL/QZDAPKG

3. Check the IBM APAR to ensure that you have the latest required PTF's
http://www-1.ibm.com/servers/enable/site/events/jde_links.html

4. Order & Apply IBM PTF's for V5R3 (check if they are installed first)
-Latest PTF for SQLPKG is SI14789
-PTF SF99503-1


5. On each ODBC connection to the iSeries, under the performance tab uncheck "Enable Lazy Close Support"
-"Enable Laxy Close Support" when checked is suposed to increase performance. Only uncheck it after the previous items have failed.


If none of the above works I can direct you to the proper channels at IBM


Colin
:grin:

You are the man, Colin !

I am chasing my tail, wondering why a customer cannot suddenly insert into their production F4101C table - even after recreating the table - and your succint post seems to be nailing the issue precisely !

Thankyou Colin - I believe the PTF's are supposed to directly address this issue...I'll post more information when I get the PTF's installed by IBM.

We have been deleting SQL packages for quite some time, but once we upgraded to V5R2, we experienced similar issues as you. We recently took the following PTF's, and it seems to have fixed the problem...so far (About 2 weeks since they've been laid down) SI14567, SI14561, & SI14573

XE SP20 AS400 V5R2

From our 400 developer:

"I suspect records/files were updated out of regular sequence.

One way this could happen is if they have SMP feature (symmetric multiprocessing) installed on their iSeries. This is a useful feature if you have multiple processors running on the iSeries as a query can use multiple processors to do its work in parallel.

However, this also means that it can do things “out of sequence”. This can be controlled using commitment control, but SQL developer needs to worry about that, not the end user.


If they want to make sure it’s not SMP, he can change this job via CHGQRYA command and set optimize to *NONE. Query will then use a single processor."

From our 400 developer:

"I suspect records/files were updated out of regular sequence.

One way this could happen is if they have SMP feature (symmetric multiprocessing) installed on their iSeries. This is a useful feature if you have multiple processors running on the iSeries as a query can use multiple processors to do its work in parallel.

However, this also means that it can do things “out of sequence”. This can be controlled using commitment control, but SQL developer needs to worry about that, not the end user.



If they want to make sure it’s not SMP, he can change this job via CHGQRYA command and set optimize to *NONE. Query will then use a single processor."

Geoff,
After you applied these PTF's did you see any substantial difference in response time. We have our R30835 running 8-12 hours and during that time the AS400 status in WRKACTJOB is LCKW. I was wondering if these PTF's might have had an impact on this process?

What object is R30835 waiting on? Check the job locks and see what it's trying to access and make that object accessible to the program.

It was the only UBE running in the system and all online was not active. It could not be locked by anything other than itself.

It would not have been in status LCKW unless it was locked on something. Next time check the job locks and see what it was waiting for.

I wanted to post a final follow up for this.

I forward some of this information to IBM, and based on the V5R3 PTFs in this thread, they recommended we install

SI14561
SI14573
SI14567

Also, We had a program that deleted SQLPKGs automatically on the weekend - I didn't realize that it ran BEFORE the IPL, and most SQLPKGS weren't deleted. We moved it after the IPL and now everything gets deleted.

So, with these two changes, we haven't had any SQLPKG problems since. Knock on wood.