Locking DB Table w/Stored Proc

Question

Locking DB Table w/Stored Proc

kashush (Programmer)

(OP)

21 Sep 07 11:49

The map runs in 7.5(119). It calls a stored procedure. The system gets an OPEN AUDIT ERROR and the launcher is bounced. The DB process is still locking the database so when the event server comes up the new instance of the map runs untill a DBA kills the lingering DB process.

Is there a way to make sure that the Store Procedure process ends when the launcher is shut down?

eyetry (Programmer) · Answer 1 · 2007-09-21 16:43:59

Not sure this will help but check your ini db settings.  This is from 6.7 but the settings in 7.5 are similar...

IdleDB=0
SLimDB=0
HLimDB=0
KeepDB=0
MinDB=0

IdleXXX is the number of seconds to allow a connection to be idle
before automatically disconnecting.  0 to disable this feature.

SLimXXX is a soft (advisory) limit.  This number can be exceeded at
peak load, but this tries to limit the number of idle connections.
Limits are only checked when opening a connection, so it is possible
to have > number of idle connections.  XXX is adapter type, using the
same notation used on a command line.  Ex: MQS is MQSeries, etc.

HLimXXX is a hard (mandatory) limit.  This number will not be exceeded.
Connections will be placed in a pending state rather than exceed this
number.

MinXXX is a minimum number of connections to keep.  When a limit (SLim
or HLim) is exceeded, idle connections will be removed until no more
than Min remain (if possible).

KeepXXX is how long (in seconds) an idle connection is expected to
remain valid.  Connections idle longer than this will be tested prior
to being used (if the adapter supports it).  If the test fails or is
not supported, the connection will not be reused.  A value of 0 disables
this feature.  A value of -1 will force a test every time, but this is
not recommended.

janhes (TechnicalUser) · Answer 2 · 2007-09-24 04:54:48

Best to eliminate the OPEN AUDIT ERROR. If this is the DSTX audit file, use uniqhe names for the audit log.

kashush (Programmer) · Answer 3 · 2007-09-26 12:07:42

The KeepDB - Is that supported by the Oracle 9i adapter for DSTX 7.5?

eyetry (Programmer) · Answer 4 · 2007-09-27 16:39:16

Hmmm, need to reinstall 9i to check that question. don't recall off hand. I just looked at our test install of version 8 and it's an option in the dstx.ini file so I'd think it would be in 7.5

janhes (TechnicalUser) · Answer 5 · 2007-09-28 04:48:17

It is supported by all versions.
I suspect your stored procedure keeps processing as it's out of DSTX control once it starts. If the Launcher crashes there's nothing DSTX can do to influence it.
Best to cure the underlying fault.

kashush (Programmer) · Answer 6 · 2007-09-28 11:27:36

Acutally that's exactly what an IBM support person said in response to a PMR.

"As for the stored procedure locking issue, i discussed with Level3 and it appears that TX being a client to the database does not have much control over the process in the database after it crashes and it is up to the database server on how it handles these processes. There may be some kind of time out settings on the server side that may be too long and hence it may be waiting before closing these connections. But since we crash there is not much we can do after the crash. We need to concentrate on what may be causing these crashes and narrow down the problem."

Come Join Us!

Posting Guidelines

Jobs

Locking DB Table w/Stored Proc