Next Number (F0002) Locking

Hi All,

Since migrating from db2 to oracle 9i 18 months ago, we've been experiencing an annoying problem - users locking the next number F0002 table.

We have logged this problem with Oracle and have spoken with many people without a successful resolution. I'm hoping that you guys might give a different insight into the problem...

When AP are entering data and someone goes into supplier details, this locks the next number table, causing everyones screen to lock. We have written a couple of quick sql scripts that show who is causing the lock and we get that person to exit the screen which releases the lock.

These are standard JDE screens and no one other than us seem to experience this issue. We have provided Oracle with a jde.log showing the dead lock, but cannot produce this with debug turned on.

Does anyone have any ideas or suggestions? Other than this issue, our system is working quite well...

Regards,
Filiz

JDE CNC Administrator

EnterpriseOne 8.9 SP2
Database: Oracle 9i on AIX 5.3
Ent Server: windows 2003

Known issue on Xe/8.0 and supposed to be default for 8.9 and above. See OTI-02-0051

Here's the INI change for the Enterprise Server:

[DB System Settings]
OracleServerHandleReuse=0

Thanks for the response. We made that change to our JDE.INI files some 18 months ago. Did not fix the problem.

Just a thought, but are you really running SP2 with 8.9?

Hi Charles,

I don't know if I should be offended or laugh at your comment. After 18 months here I think I'd know what version we're on.

As per the ptf.log on the servers and clients:

ERP9.0
SP2_F1

I really need to resolve this. Anyone have any other suggestions?

Regards,
Filiz

You should really consider updating your Tools Release. There is no reason to be offended or to laugh at my comment - there is in fact a database driver in the tools, for instance libora90.xx, which can be and very well may be at the root of your problem.

Your Tools Release is no longer supported, buggy, and should you contact Oracle about the issue, they will likely request that you upgrade the Tools in order to resolve the issue.

If there are legitimate business reasons not to upgrade, you may be relagated to living with the problem, if indeed it can be tracked back to the Tools Release.

I speak of this issue with some bit of knowledge; my company is also running EnterpriseOne 8.9 (you referred to it by its former name "ERP9.0" and we have run almost every Tools Release ever released...SP1, SP2, 8.93-94 & 8.96.) The earlier releases were definitely lacking quality.

Does anyone else have any suggestions? I need suggestions, not a lecture.

Trying a new Tools Release looks like a suggestion to me.

Dude,

your tools release is old and buggy - like Charles said.

Run from SP2 like the plague.

You'll be much happier on 8.96.

There are reasons why we haven't upgraded yet.

If SP2 was the cause of the locking, why hasn't other customers had this problem? This is not a known problem with SP2, otherwise Oracle would have advised us accordingly.

It's easy to say "upgrade" and when we're ready, we will. I was after intelligent and creative troubleshooting ideas based on experience. First response is always "upgrade TR", but I was hoping that there were sufficient expertise in this group to help troubleshoot it further. Looks like I was wrong.

I am very dissappointed in some of the responses I received. No wonder I don't use this site often.

Awwwwwww, a grumpy JDE user after a long time.
Suggestions or help can never be demanded mate !

[ QUOTE ]
This is not a known problem with SP2, otherwise Oracle would have advised us accordingly.

[/ QUOTE ]

So you're assuming that the Oracle support person you're working with is an expert on this? That's a big assumption. All they really do is search the support site just like you and I to find out if an issue was reported before. There's been times where I've had to explain to them how their product works so I wouldn't hold my breath on having them remember what the issues were from a tools release that's a few years old.

For what it's worth, we used to have problems like this on B7331 and Xe back in 2000 and 2001. Between application, tools release and database upgrades, the problem went away. I don't recall which did it, being so long ago.

I'm sorry that the JDE List posters that responded weren't able to answer to your satisfaction. So what if these respondents have over 2,800 posts and many have been JDE List members since 2000 -- experience doesn't count for that much, does it? I wish you the best in finding an answer to your problem.

You asked for help, and people generously gave you suggestions and advice based on their experience. That is, after all, what you asked for. If you can't accept the advice given, then don't ask for it. Go back to Oracle with your tail between your legs, and beg them to help you. Ingrate. You won't be missed.

All this support brings a tear to my eye. Say hello to my new avatar Iron Eyes Cody.

Please understand, other customers have had this problem. We went live with 8.9 in July 2004, on SP2, and had horrendous issues in production with blocking locks on the F0002 next numbers table. Guess what we did?

We applied TR 8.93 and the problems magically disappeared. Not until 8.94_P1 did we see issues with blocking locks on the F0002. Oracle suggested the OracleServerHandleReuse fix, which of course is supposed to be included in the tools. I brought the issue up, never received a response. Adding it back to the JDE.INI did not resolve the issue. Guess how we fixed it?

We applied 8.94_T1. All was well. Ladi-da.

I suppose this answer still isn't good enough for you. I could hypothesize you are looking for a response like "comment out line 38 of BSFN X0010" which would move the onus away from your CNC responsibilities to a developer or analyst. Tough luck.

Charles,

I appreciate you giving me insight into the issues you've seen. This helps me understand the issues others have faced. We have logged it with Oracle, it has been escalated and they have not come back with a solution. Due to our security design, we cannot upgrade just yet. We use multiple roles per user and need to find a way for users to still log on with *ALL roles before upgrading. Switching between roles is not an option for us.

I would have also apppreciated your last post first instead of the "are you really on 8.9 SP2" post. This came accross as incredibly condescending and although I know it wasn't meant that way, that's the way it came accross.

This also isn't about shifting the burden and onus onto another member of our small team. When there is a problem, it affects all of us and we work as a team to try and resolve it.

To those of you who chose to jump on the band wagon and critise me for my posts, especially the one below:

[ QUOTE ]
Go back to Oracle with your tail between your legs, and beg them to help you. Ingrate. You won't be missed.

[/ QUOTE ]

I thank you for your wonderful contribution to this thread. I'm signing off now, so no need to continue with the mud-slinging.

Regards,
Filiz

Filizrae

I just saw this post - and really want to weigh in here.

First of all, you'll get better support here than with Oracle - you're talking with users, customers and often people who have substantially more experience with the product than those people at Oracle.

Secondly, the advice you get here is not because we're benefiting in any one way except to expand our own knowledge and to get a warm fuzzy feeling. You came in here asking a question, and one of the users gave a "flat" answer (which, by the way, was the correct answer - but it wasn't delivered right - I'll explain in a second). You then felt offended and this thread ended quite bluntly. Remember, the guy giving you advice is just like anyone else here - either a customer or a user. We're not here to try and grab any of your money (unlike Oracle).

Now, to the question that you asked. The next number table is indeed a "bit bucket" - and is very much single-user based on the type of transactions that are occurring. It sounds like your company is using the next number table pretty substantially - which is very strange, since there are many ways around using the NN table.

Now - a service pack - or "tools" update is actually very easy to implement. It is NOT an upgrade - it only changes the foundation code (ie, the code that is compiled prior to delivery to the customer). It doesn't necessarily affect the application code (although you really need to test functionality after the service pack has been applied). SP2 is the very first generally available 8.9 service pack - and is plagued by bugs, so the previous users shock that you would be using such an early service pack is probably warranted in some way. Again, it takes less than a day to move the service pack to 8.96 - and approximately a weekend to have your functional team test everything out before you open up production.

As such, updating your service pack is wise, and you should really consider planning for this.

HOWEVER, that does not necessarily mean that the service pack absolutely will affect your next numbering issue (although it sounds like other customers have seen improvement). A better way to fully understand your functional requirements would be to trace what is actually affecting next numbers. If you're running a bunch of orders and its affecting NN, then you're not using Advanced Next Numbering by Company - and you should absolutely consider splitting your use of the next numbering system up by company and/or document type.

I'm not a functional specialist, but as a CNC guy who worked with JDE on the original Next Numbering issues eons ago, I know that anyone queuing up behind the "select for update" on the F0002 is a bad situation. If it is possible to change the application to prevent that from happening, then you should absolutely take every conceivable step.

Lastly - please make sure that you are using the libora90 libraries in the datasource definitions. Using the libora80 libraries on Oracle 9i is probably not a good idea...!

Hi Jon,

Thanks for your response. You summed it up perfectly.

Upgrading the tools release isn't the problem. It's our multi-role security design. We're reviewing a menu merge tool by All Out Security that will still provide *ALL menu view to the users who have multiple roles (almost all of them). We cannot upgrade the tools release until we implement this tool. Hopefully this will be soon.

I will look into Advanced Next Numbering by Company.

The libora90 libraries - I want to ensure we're set up correctly but don't know how to find this. I have gone through the database setups but cannot see a reference to it. I know definitely that we've loaded the correct oracle client on the servers and clients and would like to rule this out as a cause.

Over the next six months, we planning to implement the menu merge security tool, upgrade the tools release to 8.96, the upgrade the database to 10g. I'm hoping that this will correct the NN locking issue, but in the back of my mind am not sure.

Again, thanks for your help.

Filiz