Re: stack overflow locking sysprocplan

jda wrote: > I'm beginning to believe that the stacksize is a red herring, too. > > > We have 5 tables involved: > id_rec - id and default name and address > aa_rec - alternative addresses (summer, work, home, campus, > solicitation , etc.) > addree_rec - alternative names (formal, greeting, informal, nickname, > etc.) > adre_table - priority of names > aa_table - priority of addresses > > The view that is involved is one that gets the name and address and > formats it for a given id. The procs is where the formatting actually > happens. > > We only get this problem when we use Impromptu which is using an > SDK/ODBC connection to communicate with the database. We have taken > the exact same sql from the Impromptu report and run in dbaccess and > never get the locks. We have been unable at this point to see any > errors on Impromptu so do not know what IDS is returning to Impromptu > when we get the lock problem. > > We run weekly update stats (high, medium, low) every Sunday morning on > all tables and procedures. About 12 months ago per IBM suggestion I > added daily update stats on all procedures. This did not help. All > tables should be row level locking and we use Unbuffered logging. > > Now we do have people using our 3rd party software (Jenzabar's CX) to > add/update names and address on a daily basis, and at times I'm sure > while an Impromptu report is running. Can't say how many, but would > expect a dozen or two, top three dozen names and/or addresses are added > or changed each day. So the tables ending in '_rec' are not static, > but do not have major changes daily. > > I hope this further detail might trigger a eureka moment. :-) > > John > > >>9.40.HC7 is the latest version >> >>Still ... > > > We are waiting for our 3rd party software provider to release a IDS > 10.0 version in 4Q and will be migrating to 10.0 then. Until then the > only version they will support in 9.4 is the one we are on. > Beware of CSDK 2.81.TC1 and TC2 - Search for SQLENSURE. Just for interest ... Check when you do update statistics on your procedures and get the date, when you experience the "locks on sysprocplan" check whether the sysprocplan gets updated. If this is not the same date as when you last ran an explicit update statistics for procedure, then something else is causing the update (i.e. procedure re-optimisation). 2.81.TC1 and 2.81.TC2 apparently forced an update statistics - but proof is in the pudding. (locks with only persist through a begin / commit pair)