Massive problems with database locks / zombies

HELP --- We have got problems with locks on our SQL database. Additionally we get timeouts on the web (JDBC driver 1.1). We are running tools release 8.96 F1, SQL 2005, Oracle Application server and JDE Application server on a Windows cluster. Performance is excellent but we get a lot of zombies and locked tables in SQL.
__________________________________________________

; JD Edwards initialization file INI(JDE)
; NT Intel specific version - E812
;


[JDE_CG]
;TARGET=DEBUG
TARGET=RELEASE
INCLUDES=c:\Program Files\Microsoft Visual Studio .NET 2003\Vc7\include;c:\Program Files\Microsoft Visual Studio .NET 2003\Vc7\atlmfc\include;c:\Program Files\Microsoft Visual Studio .NET 2003\Vc7\PlatformSDK\Include;$(SYSTEM)\INCLUDE;$(SYSTEM)\INCLUDEV;$(SYSTEM)\CG;$(APP)\INCLUDE;
LIBS=c:\Program Files\Microsoft Visual Studio .NET 2003\Vc7\lib;c:\Program Files\Microsoft Visual Studio .NET 2003\Vc7\atlmfc\lib;c:\Program Files\Microsoft Visual Studio .NET 2003\Vc7\PlatformSDK\lib;$(SYSTEM)\LIB32;$(SYSTEM)\LIBV32;$(APP)\LIB32;
MAKEDIR=c:\Program Files\Microsoft Visual Studio .NET 2003\Common7\Tools;c:\Program Files\Microsoft Visual Studio .NET 2003\Common7\Tools\bin;c:\Program Files\Microsoft Visual Studio .NET 2003\Vc7\bin;c:\Program Files\Microsoft Visual Studio .NET 2003\Common7\IDE
STDLIBDIR=c:\Program Files\Microsoft Visual Studio .NET 2003\Vc7\lib;c:\Program Files\Microsoft Visual Studio .NET 2003\Vc7\atlmfc\lib;c:\Program Files\Microsoft Visual Studio .NET 2003\Vc7\PlatformSDK\lib
CLASSPATH=P:\JDEdwards\E812\DDP\system\Classes\xalan.jar;P:\JDEdwards\E812\DDP\system\Classes\xerces.jar;P:\JDEdwards\E812\DDP\system\Classes\kernel.jar;P:\JDEdwards\E812\DDP\system\Classes\XTS.jar;P:\JDEdwards\E812\DDP\system\Classes\log4j.jar;P:\JDEdwards\E812\DDP\system\Classes

[DEBUG]
Output=NONE
DebugFile=P:\JDEdwards\E812\DDP\log\jdedebug.log
JobFile=P:\JDEdwards\E812\DDP\log\jde.log
JDETSFile=P:\JDEdwards\E812\DDP\log\JDETS.log
LogErrors=1
ClientLog=0
KeepLogs=1
TamTraceLevel=0
QKLog=0

[SVR]
EnvironmentName=DV812
LibraryListName=DV812
SpecPath=spec
SourcePath=source
ObjectPath=obj
HeaderPath=include
HeaderVPath=includev
BinPath=bin32
LibPath=lib32
LibVPath=libv32
MakePath=make
WorkPath=work

[INSTALL]
B9=P:\JDEdwards\E812\DDP
Double_Byte=0
LocalCodeSet=WE_ISO88591
StartServicePrefix=JDE E812

[JDENET]
serviceNameListen=6014
serviceNameConnect=6014
maxNetProcesses=5
maxNetConnections=240
maxKernelProcesses=62
maxKernelRanges=30
netTrace=0
ServiceControlRefresh=5
enablePredefinedPorts=0

[JDENET_KERNEL_DEF1]
krnlName=JDENET RESERVED KERNEL
dispatchDLLName=jdenet.dll
dispatchDLLFunction=_JDENET_DispatchMessage@28
maxNumberOfProcesses=5
numberOfAutoStartProcesses=1

[JDENET_KERNEL_DEF2]
krnlName=UBE KERNEL
dispatchDLLName=jdekrnl.dll
dispatchDLLFunction=_JDEK_DispatchUBEMessage@28
maxNumberOfProcesses=3
numberOfAutoStartProcesses=3

[JDENET_KERNEL_DEF3]
krnlName=REPLICATION KERNEL
dispatchDLLName=jdekrnl.dll
dispatchDLLFunction=_DispatchRepMessage@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=1

[JDENET_KERNEL_DEF4]
krnlName=SECURITY KERNEL
dispatchDLLName=jdekrnl.dll
dispatchDLLFunction=_JDEK_DispatchSecurity@28
maxNumberOfProcesses=3
numberOfAutoStartProcesses=1

[JDENET_KERNEL_DEF5]
krnlName=LOCK MANAGER KERNEL
dispatchDLLName=jdekrnl.dll
dispatchDLLFunction=_TM_DispatchTransactionManager@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=0

[JDENET_KERNEL_DEF6]
krnlName=CALL OBJECT KERNEL
dispatchDLLName=XMLCallObj.dll
dispatchDLLFunction=_XMLCallObjectDispatch@28
maxNumberOfProcesses=30
numberOfAutoStartProcesses=10
singleThreadedMode=Y
;ThreadPoolSize=30
;ThreadPoolSizeIncrement=10

[JDENET_KERNEL_DEF7]
krnlName=JDBNET KERNEL
dispatchDLLName=jdekrnl.dll
dispatchDLLFunction=_JDEK_DispatchJDBNETMessage@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=1

[JDENET_KERNEL_DEF9]
krnlName=SAW KERNEL
dispatchDLLName=jdesaw.dll
dispatchDLLFunction=_JDEK_DispatchSAWMessage@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=1

[JDENET_KERNEL_DEF10]
krnlName=SCHEDULER KERNEL
dispatchDLLName=jdekrnl.dll
dispatchDLLFunction=_JDEK_DispatchScheduler@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=1

[JDENET_KERNEL_DEF11]
krnlName=PACKAGE BUILD KERNEL
dispatchDLLName=jdekrnl.dll
dispatchDLLFunction=_JDEK_DispatchPkgBuildMessage@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=1

[JDENET_KERNEL_DEF12]
krnlName=UBE SUBSYSTEM KERNEL
dispatchDLLName=jdekrnl.dll
dispatchDLLFunction=_JDEK_DispatchUBESBSMessage@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=1

[JDENET_KERNEL_DEF13]
krnlName=WORK FLOW KERNEL
dispatchDLLName=workflow.dll
dispatchDLLFunction=_JDEK_DispatchWFServerProcess@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=1

[JDENET_KERNEL_DEF14]
krnlName=QUEUE KERNEL
dispatchDLLName=jdekrnl.dll
dispatchDLLFunction=_DispatchQueueMessage@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=1

[JDENET_KERNEL_DEF15]
krnlName=XML TRANS KERNEL
dispatchDLLName=XMLTransactions.dll
dispatchDLLFunction=_XMLTransactionDispatch@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=1

[JDENET_KERNEL_DEF16]
krnlName=XML List Kernel
dispatchDLLName=xmllist.dll
dispatchDLLFunction=_XMLListDispatch@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=1

[JDENET_KERNEL_DEF19]
krnlName=EVN KERNEL
dispatchDLLName=jdeie.dll
dispatchDLLFunction=_JDEK_DispatchITMessage@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=1

[JDENET_KERNEL_DEF20]
krnlName=IEO KERNEL
dispatchDLLName=jdeieo.dll
dispatchDLLFunction=_JDEK_DispatchIEOMessage@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=0

[JDENET_KERNEL_DEF21]
krnlName=OPE KERNEL
dispatchDLLName=jdeope.dll
dispatchDLLFunction=_BBOPE_DispatchMessage@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=1

[JDENET_KERNEL_DEF22]
krnlName=XML DISPATCH KERNEL
dispatchDLLName=xmldispatch.dll
dispatchDLLFunction=_XMLDispatch@28
maxNumberOfProcesses=4
numberOfAutoStartProcesses=1

[JDENET_KERNEL_DEF23]
krnlName=XTS KERNEL
dispatchDLLName=xtskrnl.dll
dispatchDLLFunction=_JDEK_DispatchXTSMessage@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=1

[JDENET_KERNEL_DEF24]
krnlName=XML SERVICE KERNEL
dispatchDLLName=xmlservice.dll
dispatchDLLFunction=_XMLServiceDispatch@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=1

[JDENET_KERNEL_DEF30]
krnlName=METADATA KERNEL
dispatchDLLName=mdserializer.dll
dispatchDLLFunction=_MetadataDispatch@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=1

[NETWORK QUEUE SETTINGS]
QKActive=1
QKOnIdle=300
QEnv=PD812
QUser=JDE
QPassword=idontcare
QueueDelay=5
UBEPriority=5
JDENETTimeout=60
UBEQueues=23
UBEQueue1=QB7333
UBEQueue2=QB7333
UBEQueue3=QB7333
UBEQueue4=QB812
UBEQueue5=QB812
UBEQueue6=PNPQUE
UBEQueue7=SNDQUE
UBEQueue8=FINQUE
UBEQueue9=PLNQUE
UBEQueue10=ASHQUE
UBEQueue11=WGTQUE
UBEQueue12=DISQUE
UBEQueue13=SNDAS
UBEQueue14=SNDAR
UBEQueue15=SNDEM
UBEQueue16=SNDOB
UBEQueue17=PNWAS
UBEQueue18=PNWAR
UBEQueue19=PNWEM
UBEQueue20=PNWOB
UBEQueue21=SFIN
UBEQueue22=SSND
UBEQueue23=SPNP
PackageQueues=1
PKGQueue1=PCKQUE
SpecInstallQueues=1
SpcQueue1=PCKQUE
KillImmediate=1

[BSFN BUILD]
BuildArea=P:\JDEdwards\E812\DDP\packages
OptimizationFlags=/FD /Gz /O2 /Zi /MD /W4 /EHs /Gy
DebugFlags=/FD /GZ /Gz /Od /Zi /MDd /W4 /EHs /Gy /D "_DEBUG"
InliningFlags=
DefineFlags=/D "WIN32" /D "_WINDOWS" /D "IAMASERVER" /D "KERNEL" /D "UNICODE" /D "_UNICODE"
CompilerFlags=/nologo /c
OSReleaseLevel=5.0
LinkFlags=/DLL /DEBUG /SUBSYSTEM:windows /FORCE:MULTIPLE /FORCE:UNRESOLVED /INCREMENTAL:YES /VERBOSE /WARN:3
LinkLibraries=jdekrnl.lib jdel.lib jdenet.lib jdeipc.lib owver.lib jdeunicode.lib v_verify.lib xerceswrapper.lib
SimultaneousBuilds=5

[JDEIPC]
ipcTrace=0
;maxNumberOfResources=2000
;maxNumberOfSemaphores=1000
;maxNumberOfResources=1000
;maxNumberOfSemaphores=100
;CLSID=
startIPCKeyValue=6000

[JDEMAIL]
;Rule1=90|OPT|MAILSERVER=gmsmtp01.oraclecorp.com
;Rule2=100|DEFAULT|[email protected]
;Rule3=110|DEFAULT|[email protected]
;Rule4=120|DEFAULT|[email protected]
;Rule5=130|OPT|MERGELOCAL=1
;Rule6=140|OPT|UPDATELOCAL=0

Rule1=90|OPT|MAILSERVER=GBKLWS103.colbond.intra
Rule2=100|DEFAULT|[email protected]
Rule3=110|DEFAULT|[email protected]
Rule4=120|DEFAULT|[email protected]
Rule5=130|OPT|MERGELOCAL=1
Rule6=140|OPT|UPDATELOCAL=0
ClientType=Windows

[DB SYSTEM SETTINGS]
Version=43
Default User=JDE
Default Pwd=xxxxx
Default Role=*ALL
Default Env=DV812
Default PathCode=DV812
Base Datasource=JDECLNET - 812 Server Map
Object Owner=SVM812
Server=KLWJDESQL1
Database=KLWJDESQL1
Load Library=
Decimal Shift=Y
Julian Dates=Y
Use Owner=Y
Secured=Y
Type=S
Library=
Library List=
DatabaseName2=JDE812
DatabaseInstance=KLWJDESQL1
ServerPort=1433
JDBNETUse=N
UnicodeFlag=Y
LOBFlag=N

[UBE]
UBEDebugLevel=0
UBESaveLogFile=1
UBEPrintDataItems=1
UBEIntelNTInternalPrint=0
prtCSVExtention=.csv
prtCSVSeparator=;


[LOCK MANAGER]
;Server=JDECLNET
Server=
AvailableService=NONE
RequestedService=NONE

[SERVER ENVIRONMENT MAP]
; Pristine environment
JDEPLAN=PS812

[SECURITY]
;SecurityServer=
SecurityServer=JDECLNET
User=JDE
Password=idontcare
Default Role=*ALL
DefaultEnvironment=DV812
DataSource=System - 812
History=0

[INTEROPERABILITY]
FilteredEvents=*NONE
RegisteredEvents=

[SAMPLE_EVENT]
DS1=DXXXXXXXX
DS2=DYYYYYYYY
DS3=DZZZZZZZZ

[JDEITDRV]
DrvCount=4
Drv1=RT:rtdrv.dll
Drv2=WF:wfdrv.dll
Drv3=Z:zdrv.dll
Drv4=JDENET:jdetrdrv.dll

[LREngine]
System=P:\JDEdwards\E812\DDP\output
Repository_Size=20
Disk_Monitor=YES

[WORKFLOW]
; Workflow Runtime Interop, inbound (server only)
WRIUser=xxx
WRIPassword=xxx
WRIEnvironment=xxx
WRIRole=*ALL
; Workflow Runtime Interop, outbound
PrimaryEvnServer=xxx
SecondaryEvnServer=xxx

[JAS PREFERENCE]
JasServer=jdeweb1.colbond.local
Port=81
Servlet=/jde/servlet/html.login

;[TRANSPORTS]
;TransportName1=xxxTransportExeNamexxx
;TransportParams1=xxxTransportParametersxxx

[XAPI]
XMLDirectory=P:\JDEdwards\E812\DDP\log

[XMLLookupInfo]
XMLRequestType1=list
XMLKernelMessageRange1=5257
XMLKernelHostName1=local
XMLKernelPort1=0

XMLRequestType2=callmethod
XMLKernelMessageRange2=920
XMLKernelHostName2=local
XMLKernelPort2=0

XMLRequestType3=trans
XMLKernelMessageRange3=5001
XMLKernelHostName3=local
XMLKernelPort3=0

XMLRequestType4=JDEMSGWFINTEROP
XMLKernelMessageRange4=4003
XMLKernelHostName4=local
XMLKernelPort4=0
XMLKernelReply4=0

XMLRequestType5=xapicallmethod
XMLKernelMessageRange5=14251
XMLKernelHostName5=local
XMLKernelPort5=0

XMLRequestType6=realTimeEvent
XMLKernelMessageRange6=14251
XMLKernelHostName6=local
XMLKernelPort6=0
XMLKernelReply6=0

[XTSRepository]
XTSRepositoryPath=P:\JDEdwards\E812\DDP\system\XtsRepository
XSL-JDE-BIRDS-REQUEST=birds.xsl
XSL-JDE-V2_2_NATIVE-REQUEST=v2toNative.xsl
XSL-JDE-V1_NATIVE-REQUEST=ml.xsl
XSL-JDE-V1_NATIVE-RESPONSE=lm.xsl

[XTS]
XTSTemplateSelector1=com.jdedwards.xts.xtsm.XTSMJDETemplateSelector
XTSTraceLevel=2
XTSDelay=0

[MTR VALIDATION]
EnterpriseDiskInMB=3000

[TCENGINE]
TraceLevel=1

##
## Additions to JDE.INI for the Performace Monitor components
## configuration for consumptioin by Enterprise Server.
##

[PERFMON]

#
# To turn on PM APIs set to 'true'.
#
agentActive=false

#
# URL of Performance Monitor. Used by instrumented code to register with
# Performance Monitor.
#
monitorUrl=

#
# Identifier for source of performance data.
#
hostPort=

#
# Time to wait for response from E1 Java Adapter (Java Kernel)
#
jdenetTimeout=60

#
# Transaction will be timed out by Monitor server if end transaction is not
# received this number of milliseconds after the start transaction.
#
transactionTimeout=60000

#
# Not currently being used.
#
sampler=60

#
# Maximum number of Java Adapters (Java Kernels)
#
maxJavaAdapter=1

#
# Default filter level used by the agent for all transactions.
#
filterMask=1


[PERFMON_JAVA_ADAPTER_1]

#
# Name of machine where Java Adapter is running.
#
javaAdapterHost=

#
# Port on which Java Adapter is communicating; should be the same as
# the [JDENET] serviceNameListen value used by the E1 Transaction Server.
#
javaAdapterPort=

[JDE JVM]
# Settings governing the usage of the in-process JVM inside of E1 Kernels.
# The JVM is used primarily to support XML specs but would be used for any
# Java code run inside of an E1 process.
#
# Full path to jvm.dll (include 'jvm.dll' in path) that will be loaded for java
# processing within Enterprise One C/C++ processes. MUST be version J2SE 1.4 or later!
# Default is to use the JRE located in the JRE directory inside of the E1 'system'
# directory.

# InProcessJVMHome=<JVM Location>

# Class path for the JVM will be constructed using all the .jar files contained
# in the <install location>/system/classes directory. It is possible to prepend
# items from other locations to the class path by setting the 'CLASSPATH' attribute
# in the [JDE_CG] section.

[METADATA]
# There are some parts of metadata objects that are stored in the Object Librarian data source and are
# retrieved after the metadata object is deserialized from XML. This data is not needed during normal
# runtime processing and to improve performance should be turned off.
# '0' - Do not retrieve data from Object Librarian data source when deserializing metadata.
# '1' - Retrieve data from Object Librarian data source and fully populate metadata objects when deserializing
# metadata.
AllCPopulationEnabled=0

[TRUSTED NODE]
numTrustedNodes=1
RegularLifeTime=5760
ExtendedLifeTime=5760
NodeName=_LOCALNODE
MachineName=_LOCALNODE
NodePassword=_LOCALPWD

[XML Dispatch]
PollIntervalMillis=1000

Just a FYI

You just posted your JDE password. I'm sure you don't care, but others might....

[ QUOTE ]
Just a FYI

You just posted your JDE password. I'm sure you don't care, but others might....

[/ QUOTE ]


I don't think he cares.


User=JDE
Password=idontcare

Please post the results of sp_lock and sp_who2 when this occurs.

You might want to post them as attachment files.

I just attached the file to my first post. All of our consultants do not know how to fix but the locks are sometimes blocking the whole system - sometimes they are gone without killing them. And: I do not really care

[ QUOTE ]
I just attached the file to my first post. All of our consultants do not know how to fix but the locks are sometimes blocking the whole system - sometimes they are gone without killing them. And: I do not really care

[/ QUOTE ]

I am not seeing an attachment.

What are your timeout settings in WAS/OAS and E1? If your HTML cleints timesout in E1 before they timeout in WAS/OAS, this can cause record locks in the DB.

We had the same issue at a customer of ours on virtually the same setup (our TR = 8.96.H1).

We solved the majority of the locks by adding the following to the end of the [DB SYSTEM SETTINGS] section of our enterprise server jde.ini:-

ConnectionPooling=0
SSQueryTimeout=8
SSQueryTimeoutRetries=3

; Solution ID 201037988

See the solution id above on customer connection for more info.

We now only have one UBE that causes the lock to occur (R03B50). This UBE seems to create a lock on its own process id on the database (but not everytime and when it does it is not always at the same place in the process). Sometimes it is straight away, sometimes half way throughand sometimes almost right at the end.

Killing the lock (from the db) lets the UBE finish processing.

It really looks like a timeout problem but we cannot fix it.
Can somebody explain me if a SQL feature called "read committed snapshot" maybe can be helpful?
For the timeout: Do we have to compare just the jas.ini with the jde.ini?
For the connectionpooling=0 --- I get the information of Oracle that this can also cause performance problems.
It stays unstable - it is a little bit better because of "changing" timoeouts and adding statements for SQl timeouts to the jde.ini but it is not what it should be - still zombies (not so much) and callobject errors and additonal locks on the database.
I was also told to change the page-level locking on SQL level to row-level locking but that can be a dangerous change for the consistency of database or?

[ QUOTE ]
We had the same issue at a customer of ours on virtually the same setup (our TR = 8.96.H1).

We solved the majority of the locks by adding the following to the end of the [DB SYSTEM SETTINGS] section of our enterprise server jde.ini:-

ConnectionPooling=0
SSQueryTimeout=8
SSQueryTimeoutRetries=3

; Solution ID 201037988

See the solution id above on customer connection for more info.

We now only have one UBE that causes the lock to occur (R03B50). This UBE seems to create a lock on its own process id on the database (but not everytime and when it does it is not always at the same place in the process). Sometimes it is straight away, sometimes half way throughand sometimes almost right at the end.

Killing the lock (from the db) lets the UBE finish processing.

[/ QUOTE ]

Terry,
I'm on H1 and I'm having similar problems. I only have the connectionpooling=0 in my [DB System Settings] Did you have to add the other two settings?

Hi,

I added them myself as a result of seeing the solution id I mentioned earlier.

The other thing I did which also helped was rerun the generate index UBE R9698713.

[ QUOTE ]
Hi,

I added them myself as a result of seeing the solution id I mentioned earlier.

The other thing I did which also helped was rerun the generate index UBE R9698713.

[/ QUOTE ]

Interesting. Thanks for the information bud. I'm going to have to try this on my DEV Enterprise server.

[ QUOTE ]
Hi,

I added them myself as a result of seeing the solution id I mentioned earlier.

The other thing I did which also helped was rerun the generate index UBE R9698713.

[/ QUOTE ]

Index regeneration (Native SQL or E1 method) should be done weekly. I also defrag indexes nightly using SQL.

Not sure what your ES is, but why would I (or anyone, for that matter) need to regenerate indexes weekly?

Hi,
some interesting news about those zombies and locks. We have had two mistakes in our environment - first misconfigured ODBC settings on the cluster nodes and another and really ugly issue writing non unicode into a unicode database (PD812). The last issue was resolved by changing the SQL Native Client settings to "Perform translation for character data".
It looks like the system is now stable - no zombies any more. We still have got SQL locks but those are gone after a while (6 minutes). It looks like there is a porblem now with 5 prohramms: P31114,P31113,P4205,P4113 and most horrible P4210. Those programs are not working and causing locks on the SQL server (2005 - 64bit - really fantastic performance). Has anyone ever heard about problems for our version around those programs?

I think Jeff is talking about Optimizing indexes.

On SQL Server, you need to run the optimization plan about once a week. On Oracle, you need to run an analyze at least once a week - and on AS/400 you need to reclaim storage (and probably some other stuff).

You certainly don't need to regenerate indexes - that would be a bit of a nightmare !!

I reorg once a week on SQL. Still doesn't matter if your indexes are jacked on f4210 Here is some advice to everyone in the JDE world, don't customise P42101 or P4210.

[ QUOTE ]
Not sure what your ES is, but why would I (or anyone, for that matter) need to regenerate indexes weekly?

[/ QUOTE ]

For table read performance. It is easy to automate with SQL Server, I set up the job and it does the rest. As a matter of fact, I defrag indexes nightly. Same concept- easy to setup, automated, increases performance.

Ah, that is something we don't have to bother with on the iSeries.

[ QUOTE ]
Ah, that is something we don't have to bother with on the iSeries.

[/ QUOTE ]

What's that? Performance?