Fwd: SQL Capture - DB version 11.7.FC4

Tom: Try instead to turn the SQLTRACE feature on and query sysmaster:syssqltrace instead. That accesses a different area of memory in the server and may avoid the bug that is causing the crash after repeated runs. There is a lot more information about the queries and how they performed in syssqltrace than in syssqlstat, syssqexplain, or sysconblock. Set up SQLTRACE to capture a bit more than 5 minutes worth of queries in memory and you can poll that every 5 minutes. FYI, I would write my history records to a database in a separate server (as Server Studio normally does) rather than in the engine itself (as the sysadmin "Save SQL Trace" sensor does). If you are going to be writing to the production server anyway, just configure SQLTRACE and enable that sensor and it will gather the data for you in the sysadmin:mon_syssqltrace table. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Mar 23, 2012 at 10:43 AM, <tomcaml@gmail.com> wrote: > Hello All, > We recently upgraded to version 11.7.FC4 (on AIX 6.1) and have run into an > issue that appears to be related to the specific version. While trying to > capture the SQL hitting one table on our primary server (we have an HDR > pair) using Sentinel (Server Studio), the instance crashed. The folks at > AGS believe it to be a bug in this Informix version and we have an > outstanding IBM PMR connected to it. > We also experienced a crash on Primary when running our own SQL Capture > through crontab that executed every 5 minutes. This home grown script has > been used on many prior Informix versions with no adverse impact. > I have run the sql used in both processes manually through an editor and > so far, it has not resulted in anything negative, so I am not sure if it > might be load related or some other such thing. > > Is anyone else experiencing a similar issue? > > Below are the SQL's taken out of the assert failure files: > > #1) > >From cron script (inserts into a stand alone DB on the instance): > insert into sql_stats> select > DBINFO('dbhostname') , current year to > second , c.username, c.hostname, c.sid, a.sqs_dbname, > b.sqx_estcost, b.sqx_estrows, > b.sqx_sqlstatement from sysmaster:syssqexplain b, > sysmaster:syssessions c, sysmaster:syssqlstat a where > b.sqx_sessionid = c.sid and a.sqs_sessionid = c.sid and > b.sqx_sqlstatement != 'select count(*) from dummy' and > b.sqx_sqlstatement is not null and b.sqx_sqlstatement != '' and > a.sqs_dbname in('wlms','edidb','edidb_staging','contract_management') > group by 1,2,3,4,5,6,7,8,9> > #2)From Sentinel : > SELECT t2.cbl_sessionid session_id, cbl_stmt[1,454] prpstm, > t2.cbl_estcost sqs_estcst, t2.cbl_estrows sqs_estrws, t2.cbl_seqscan > sqs_seqscn, t2.cbl_autoindex sqs_autoidx, t2.cbl_tempfile sqs_tempfls, > cbl_sdbno seqnum from sysmaster:'informix'.sysconblock t2 where > cbl_ismainblock='Y' AND length(cbl_stmt) between 1 AND 454 and > t2.cbl_sessionid not in (1106114,1106290,1106291) > > thanks, > tom > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >