Questions in sysmaster stops new sessions

Are using version 7.31.UD5 on HP-UX. Because of performance problems we having been running some SQL-statements to find sequential scans and questions with high costs. These questions are done in the sysmasters database. At three separate occasions now the possibility to start new sessions is blocked after these kind of questions. It is not possible to do an onmode -z to kill the questions in the sysmaster database, this is the only running question. The instance has been running for more than a year without any kind of problems until we started these questions in the sysmaster database. At two occasions we have been using the SQL Capture function in Server Studio, version 4.1 and 4.2. It does not matter what protocol you are using, there is no way to start a new session. The ones that are running works just fine, but the only way to be able to start new ones is to bounce the engine. The reason is not a resource problem, had the problem today with approximatly 5 % of the normal usage of the engine Has anybody run into similar problems ? Regards Ulf

Hi, are you sure you are doing these queries to sysmaster database in dirty read isolation level ? I guess this should be the preferred isolation level for queries to sysmaster database, especially to avoid creating any locks on resources that are needed by the database itself. Regards, Martin -- Martin Fuerderer IBM Informix Development Munich, Germany Data Management Solutions forum.subscriber@iiug.org wrote on 12.12.2004 16:29:13: > Are using version 7.31.UD5 on HP-UX. Because of performance problems we having been running some SQL-statements to find sequential scans and questions with high costs. These questions are done in the sysmasters database. > > At three separate occasions now the possibility to start new sessions is blocked after these kind of questions. It is not possible to do an onmode -z to kill the questions in the sysmaster database, this is the only running question. > > The instance has been running for more than a year without any kind of problems until we started these questions in the sysmaster database. At two occasions we have been using the SQL Capture function in Server Studio, version 4.1 and 4.2. > > It does not matter what protocol you are using, there is no way to start a new session. The ones that are running works just fine, but the only way to be able to start new ones is to bounce the engine. > > The reason is not a resource problem, had the problem today with approximatly 5 % of the normal usage of the engine > > Has anybody run into similar problems ? > > Regards > > Ulf >

Dear Ulf, What exact SQL query was the session running on which sysmaster table? Did you capture a stack of the main CPU VP (cpu vp no 1) I suspect the query on the sysmaster table may have got into an infinite loop and blocked the main oninit (CPU VP 1) . In that case you will not be able to start new sessions. With kind regards Tilman Model-Bosch -- Tilman Model-Bosch IBM Data Managment Solutions, Informix Advanced Support c\\\\o SAP AG TECHDEV 05 Neurrotstr.16 69190 Walldorf forum.subscriber@iiug.org wrote on 12/12/2004 16:29:13: > Are using version 7.31.UD5 on HP-UX. Because of performance problems > we having been running some SQL-statements to find sequential scans > and questions with high costs. These questions are done in the > sysmasters database. > > At three separate occasions now the possibility to start new > sessions is blocked after these kind of questions. It is not > possible to do an onmode -z to kill the questions in the sysmaster > database, this is the only running question. > > The instance has been running for more than a year without any kind > of problems until we started these questions in the sysmaster > database. At two occasions we have been using the SQL Capture > function in Server Studio, version 4.1 and 4.2. > > It does not matter what protocol you are using, there is no way to > start a new session. The ones that are running works just fine, but > the only way to be able to start new ones is to bounce the engine. > > The reason is not a resource problem, had the problem today with > approximatly 5 % of the normal usage of the engine > > Has anybody run into similar problems ? > > Regards > > Ulf >