Implementing 'system' user ID - one to one per each JDE ID

Implementing \'system\' user ID - one to one per each JDE ID

we are E9.0 tools 8.98.4.10 in non-production, going to production on that + weblogic in the next 4 to 6 weeks. Back end database is Microsoft SQL 2008R2. Current tools release in production is 8.98.3.1 + OAS


Not sure if this topic has been previously discussed, nonetheless, here's the question.

With current configuration, we are using just one "system" user (JDEE1USR) for all JDE userID's. When troubleshooting issues on SQL server, every user thread shows up as the same (JDEE1USR), making it impossible to pinpoint.

We are considering creating a one to one configuration where each JDE user ID will have a corresponding system ID and a SQL server ID. It will easy to identify the offending process on SQL server.

I realize there is an additional admin effort if going this route. The contractor dba has offered to help automate some of this to minimize that extra admin effort by using scripts.

If anyone on SQL server backend is using this type of configuration, I'm very interested in knowing additional details. Even if you are not and have feedback for enabling this, please share your thoughts. Our servers are probably beefy enough to handle the additional threads.

Thank you

Re: Implementing \'system\' user ID - one to one per each JDE ID

I have implemented this for a number of JDE sites on various DB platforms. I also used scripts to keep it easy to maintain.

The only real issue with this setup is scalability. Using dedicate system proxy users per JDE application user breaks the effectiveness of the JDBC connect pool. Connection pooling will allow database connections for the same underlying database user to be shared across JDE sessions. With dedicated system users you will have at least 3-5 database connections per application user.

If you have a specific group of users who are likely to be the culprits for long running queries you might consider just rolling out this dedicated system user to them. I often keep a couple of tracking system users in reserve that I can assign to groups of users or individual users when there are performance issues in specific modules or coming from one department.

Re: Implementing \'system\' user ID - one to one per each JDE ID

How many users are you implementing this for ?

Its probably fine for a few dozen to a couple hundred users. Not practical for thousands of users.

Re: Implementing \'system\' user ID - one to one per each JDE ID

What kind of issues are you troubleshooting directly on the SQL database ? Is it high resource (disk , cpu or memory) utilization of some sort ?

With new tools like Server Manager and UBE Introspection it is not that difficult to trace back a database session to the front end. If you can find the process id and host from where the DB session originated you can find the kernel process and attached users in server manager or the UBE by correlating the process id in the job master. I agree if the session was a direct JDBC from the web server it does it make it difficult , but you could still examine the active users on that web server and from their open applications narrow down the possible culprits.

I agree that a direct user to proxy id mapping would be much easier to track down that going through all of the above , but as others have pointed out it can cumbersome to maintain in the long run and it sounds like you want to do this more to troubleshoot issues as opposed to it being a regulatory requirement (which is why some clients set it up) to be able to trace every database change back to a user.

If you do decide to go with the one to one proxy mapping route in production do look into your JDBC connection pool settings as mentioned by Justin.

Using connection pooling in a one to one proxy mapping setup has known to cause JAS performance issues.

Recommended values from an Oracle document I came across recently

minConnection=5 (default) (for unique proxy users set to 0)
maxConnection=num of max Concurrent Users (for unique proxy users set to 5)
initialConnection=5 (default) (for unique proxy users set to 1)
poolGrowth=10 (default is 5) (for unique proxy users set to 1)

Re: Implementing \'system\' user ID - one to one per each JDE ID

Ice_cube210, altquark JEMILLER -thank you for the valuable feedback.

Using select * from sysprocesses where spid = xxxx gets me some of the details but trying to get specifics from a web session user is challenging.

I have wrestled over the additional admin workload versus the gain of easier visibility of the offending user/thread. At this point, going the one-to-one route seems the only route.

We have close to 250 concurrent users. Originally, we thought break it down by divisions, such has finance, mfg, distribution etc but a lot of our users are cross functional so that wouldn't work for us.

I will certainly keep the recommendations and parameters in mind. The hardware in use is powerful enough to expand the connections, hopefully that shouldn't pose any concerns.

regards,
Jay