JDBJ899 SQLPKG

[sashton]

Anyone know anything about this SQLPKG? All of a sudden in the last couple weeks we are seeing users receive the "Unknown JAS error has occurred. Please contact your system administrator." message when trying to login. The JAS e1root log shows the following message over and over again in it - "Row or object JDBJ899 in SY812 type *PGM in use" I have attached the actual E1 log if you'd like to peruse through it. What we usually end up doing is looking at object locks on JDBJ899 *SQLPKG and ending the QZDASOINIT connections. This sometimes fixes it, but other times we have to completely stop and restart E1 services and delete all SQL packages. That of course is unacceptable during the day when production is running. Anyone have any idea what would cause this? The only thing that I can think of that recently changed was refreshing PY data from PD.

 

[KBrigiotta]

A little over a month ago we started to see the same errors. We had just installed some PTF on our AS400 and we thought it may have had something to do with that. IBM instructed us to delete the JCBJ899 Proddta SQLPKG - that was 3 weeks we have not had any errors since.

Karen
Truck-Lite Co.

8.10 8.98 AS400 V6R1

 

[klwong]

According to this (Oracle Doc ID 1271107.1), some other process must be locking the SQL package.

This suggestion for more information (IBM document) doesn't really help though. I think Karen's suggestion is better.

 

[markdcci]

Steven,

We also recently started seeing similar error messages. We just started this past weekend scheduling what will be a weekly delete of the JDBJ899 SQLPKG's. We could not delete them manually even after taking services down due to QZDASOINIT locks. We now do it directly after our weekly IPL at 4 AM via a scheduled AS400 job just prior to our E1 services start before the QZDASOINIT jobs get a chance to get a lock. Have not seen similar errors, but the week is not over.

I noticed from your sig that you are on 8.98.3.4 - did you see this issue directly after applying the tools release? We upgraded to 8.98.3.4 in December and I think it may be related for us.

We have had numerous issues with tools release 8.98.3.4, which seemed to break most workflow processes, has been causing UBE crashes and has seemed to make us more vulnerable to SQLPKG vagaries among other things. We haven't gotten too far with Oracle Support. I am actually considering moving back to 8.98.2.3 after almost 2 months of this insanity.

E1 9.0
AS400 Enterprise Server V6R1
Webpshere 6.1.0.21 on Windows 2003
Tools Release 8.98.3.4 (Unfortunately)

 

[sashton]

Mark,

I have been under suspicion all along that this could be a tools release issue. This error never appeared before we were on this tools release, but has only happened since. The only consistent event that we have been able to track to when it occurs is after we do data refreshes. I really hope I don't have to go back a tools release level because we like a lot of the functionality .3.x provides. I don't think .4 is stable enough to move forward from what I am reading either.

 

[Tom_Davidson]

Steve,

I just got done figuring this out on our system (an hour ago). In our case, we had just done a data refresh.

We clear the xxxDTA library, restore the library from *SAVF and copy a few in the xxxCTL library

It ended up being some QSQSRVR jobs that had somehow gotten 'disconnected' from their host jobs. After I killed them, all was well. I tried deleting the regular *SQLPKG's in the data libs and it didn't help. Finally tried deleting the th QRECOVERY/R* packages and that's how I found the QSQSRVR jobs.

HTH

Tom

 

[sashton]

Right, so data refreshes appear to be the common link with the problem. I had to restore the F0004 and F0005 tables from backup tape and as soon as I did, the error occurred. So as far as addressing the issue, what is the best practice after data refreshes? Stop and restart services? Delete all SQLPKGs? Terminate all the QZDASOINIT connections? I still find it odd that this is the first I am experiencing this lockup on that JDBJ899 SQLPKG, which makes me think it is still a TR issue. Oracle says that users have been reporting this since 8.93.

 

[Tom_Davidson]

There are a couple of options:

1) Stop services, both JAS and ES (assuming their startup is for the environment being refreshed), this should disconnect the QSQSRVR and QZDASOINIT jobs. Manually end any other QZDASOINIT jobs (from fat clients). Then do the refresh, and restart everything. This is probably the safest way.

2) do an ENDPJ for QSQSRVR and QZDASOINIT jobs, do the refresh, STRPJ for the jobs. This may or may not work, depending on if JAS/ES can cope with the failure and reconnect. the JAS server seems OK with this in 8.98.3.4. But I have not done extensive testing. No idea if the ES Kernels can cope.

3) Cope after the fact if you have to. We have done several refreshes where we didn't have an issue. I suspect the real issue in my case was UBE's running when the refresh happened.

#3 is how I'm going to cope this time, if it reoccurs regularly then I'l probably do #1

Tom

 

[sashton]

Tom,

Thanks for the insight. I suppose "cope" is what we will have to do. Appreciate all the input from everyone.

 

[Tom_Davidson]

JDBJ899 SQLPKG (Update)

Just an update. We went to new hardware this weekend, when we came back up we had this issue in spades. I tracked back most of it, but not all, to an issue with authority. The issue seems to be if the RUNUBE failed, on an authority check, the QSQSRVR job did not detatch properly and held a lock on the *SQLPKG. Once I got the authority straightened out, most of the locks on the *SQLPKG's went away.

I still seem to have a problem when two RUNUBEXML's kick off at the same time, one of the jobs sometimes fails with the package lock.

Hope this helps someone else.

Tom

 

[Tom_Davidson]

Re: JDBJ899 SQLPKG (Update)

Yet another update. The new hardware keeps getting locks on the SQL packages. These are the changes I've made until it gets figured out:

CHGPJE QSYSWRK QSQSRVR MAXUSE(1)
Changed JDBD.INI to add: as400PackageLibrary=QTEMP for each instance

Changed JDE.INI on ES to have this:
[DB SYSTEM SETTINGS]
SQL Package Library=2

So far so good.

Tom

 

[cmteague]

Re: JDBJ899 SQLPKG (Update)

We were having the same issue after applying some PTFs. We deleted the SQL packages and the issue still happened. We ended the JDE services and ended the QUSRWRK subsystem. Then we deleted all the EnterpriseOne related sql packages. Started the services up again and we have had no issues since.

 

[xiseesxis]

Re: JDBJ899 SQLPKG (Update)

Hi Tom,
I'ver been battling this issue for the past month. Can you tell me what your changes to the ES JDE.INI do for sqlpkgs. Does it force the jobs to delete the sqlpkg as soon as the job is complete? Have your system been stable since these changes. What OS level are you on V6R1? Thanks.[ QUOTE ]
Yet another update. The new hardware keeps getting locks on the SQL packages. These are the changes I've made until it gets figured out:

CHGPJE QSYSWRK QSQSRVR MAXUSE(1)
Changed JDBD.INI to add: as400PackageLibrary=QTEMP for each instance

Changed JDE.INI on ES to have this:
[DB SYSTEM SETTINGS]
SQL Package Library=2

So far so good.

Tom

[/ QUOTE ]

 

[WinPrice]

Sorry to revive this long-dormant thread, but I've recently come across this issue recently for a E1 9.0 TR 8.98.4.12 on V7R2 customer. We were able to stop the web servers, clear these SQLPKGs (there were 5 JDBJ9899 SQLPKGs), then bring up web services again and have the users resume work. UBE/Subsystem jobs/DSI/EDI was unaffected. What we have been unable to find, however, is the cause of the corruption of the SQLPKGs.

In fact, SQLPKG issues in one form or another have been the biggest issue with stability for E1 for this customer. We delete them every 2 weeks and this last occurrence with the JDBJ9899 SQLPKGs happened one day after we cleared them.

Who has found a good way to troubleshoot causes for them?

 

[TFZ]

I don't know if you can do it on that tools release, but I just had an issue last week where Oracle asked us to turn them off completely via JDBJ.ini. The thought is it doesn't truly affect performance anymore, and to be fair - I haven't noticed degradation. I realize this doesn't give you troubleshooting steps, that could be tricky. I've seen everything from multi language corruption too bad data cause it. You may want to dig in to the sqlpkg itself and see if anything sticks out.

 

[WinPrice]

I don't know if you can do it on that tools release, but I just had an issue last week where Oracle asked us to turn them off completely via JDBJ.ini. The thought is it doesn't truly affect performance anymore, and to be fair - I haven't noticed degradation. I realize this doesn't give you troubleshooting steps, that could be tricky. I've seen everything from multi language corruption too bad data cause it. You may want to dig in to the sqlpkg itself and see if anything sticks out.

Thanks. This is the first time I have heard that. The jde.ini setting may let us turn them off at this release level. I also now have the command to view the SQLPKG information as a SPLF, which is PRTSQLINF, which I can use next time to dig into it more, as you suggested. Otherwise, these are still a bit of a black box.