DB locks on F4801 as JDE

We are getting database locks on the F4801 table as user JDE. This lock sometimes lasts too long, and blocks several other queries while it lasts. We have to manually kill the spid. Othertimes, the same query blocks a short time and clears on its own. We don't know what is causing the long blocks.

We are running 1-to-1 JDE userIDs to SQL user logins, and most DB locks show under the user login. We can't determine why this lock still shows as JDE (originating from our web app server, not the batch server).

Here's the SQL query:
(@P1 char(2),@P2 char(8),@P3 char(5))SELECT * FROM JDE_PRODUCTION.PRODDTA.F4801 WHERE ( WARCTO = @P1 AND WARORN = @P2 AND WARKCO = @P3 ) ORDER BY WARORN ASC,WARCTO ASC,WARKCO ASC

Anyone have an idea where this query may be coming from, or why it still shows as a JDE user?

Thanks,
KSI

Hi,

JDE is probably showing as the user id blocking F4801 because this is the system id used (proxy id for your database). It is most likely a UBE that is causing the table to lock. Using SQL management console activity monitor try to find the SPID causing and find the corresponding server i.e. name of the batch server or logic server. If its a batch server see what UBE's are currently running. This will help you isolate the UBE causing the deadlock. If the SPID corresponds to a logic server then its a call object kernel that is blocking the table.

[ QUOTE ]
We are getting database locks on the F4801 table as user JDE. This lock sometimes lasts too long, and blocks several other queries while it lasts. We have to manually kill the spid. Othertimes, the same query blocks a short time and clears on its own. We don't know what is causing the long blocks.

We are running 1-to-1 JDE userIDs to SQL user logins, and most DB locks show under the user login. We can't determine why this lock still shows as JDE (originating from our web app server, not the batch server).

Here's the SQL query:
(@P1 char(2),@P2 char(8),@P3 char(5))SELECT * FROM JDE_PRODUCTION.PRODDTA.F4801 WHERE ( WARCTO = @P1 AND WARORN = @P2 AND WARKCO = @P3 ) ORDER BY WARORN ASC,WARCTO ASC,WARKCO ASC

Anyone have an idea where this query may be coming from, or why it still shows as a JDE user?

Thanks,
KSI

[/ QUOTE ]


What type of lock is occurring? Use sp_lock

What process is causing the lock? Use sp_who2 and find the spid identified in sp_lock

What is the wait type associated with the spid? Run

<font class="small">Code:</font><hr /><pre>select * from sys.sysprocesses </pre><hr />

and find spid correlated with lock to give wait type


This script can also be useful:

<font class="small">Code:</font><hr /><pre> select spid, kpid, cast (status as char(15)) as "Status",
cast (hostname as char(15)) as "Host", dbid, cmd
from master..sysprocesses
where kpid != 0
order by kpid</pre><hr />



If you really want to dig into it to determine root cause you can try this:

http://jeffstevenson.karamazovgroup.com/2008/09/identifying-high-cpu-sql-processes.html

You have to determine the root blocker. The query you identified may be blocked by a write process since it is pretty rare that a SELECT statement can cause problems. Slow disk IO may be the cause if it truly is the SELECT statement. The wait type will tell you more about that.

[ QUOTE ]


Anyone have an idea where this query may be coming from, or why it still shows as a JDE user?

Thanks,
KSI

[/ QUOTE ]

Are you sure that no users are setup with JDE as the proxy user?

Try this:

<font class="small">Code:</font><hr /><pre> select * from jde812.sy812.f98owsec
where scsecusr != scuser </pre><hr />

or

<font class="small">Code:</font><hr /><pre> select * from jde812.sy812.f98owsec
where scsecusr = 'JDE' </pre><hr />

It is also possible that you have a user logged in as JDE. Have you limited the JDE user to only JDEPLAN and DEP812?

Closing an old post.
Thanks Joel and Jeff for the responses.

Our issue was a combination of the integration server being accessed with a JDE system user account, and a business function OCM to run on the interactive server.

Solution was to update all integration accounts to the same 1-to-1 mapping as user accounts.