OAT and mon_syssqltrace
A user wanted SQL trace history in the permanent mon_syssqltrace* tables to extend well beyond the fixed number of traces held in sysmaster:syssqltrace, and found that on 11.70.FC1DE (Mac) the saved data stopped growing once the trace maximum was reached, while 11.50.FC4 on Linux worked fine. Replies explained that syssqltrace is just a fixed, circular shared-memory buffer, so data must be copied to real tables, and that the "Save SQL Trace" scheduler task (sql_showsnap) does this with configurable run frequency and data-delete/retention settings that should be adjusted via OAT's Task Scheduler. The poster reported his task settings looked correct and said he still needed to test 11.70 on Linux; no resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I am trying to get permanent sql traces in mon_syssqltrace using OAT but it seems that is limited to the max numbers of traces set. How can I get a history of saved SQL traces that goes way beyond the max number of traces set for sysamster:syssqltrace? Is it possible to set for example 10000 rows max in the syssqltrace and still keep an sqltrace history in mon_syssqltrace that allows me to save a history for several days? Khaled Bentebal Email: khaled.bentebal@consult-ix.fr >
Hi, I am sorry. I didn't give you the version numbers of IDS. The problem I faced was on IDS 11.70.FC1DE on MAC OS. We tried to perform the same thing on LINUX using 11.50.FC4WE and everything works fine. The mon_syssqltrace keeps on growing without any problems. I am using OAT 2.70 on MAC OS 1.6. Khaled Bentebal Email: khaled.bentebal@consult-ix.fr Le 09/02/11 03:35, Khaled Bentebal a écrit : > Hi, > > I am trying to get permanent sql traces in mon_syssqltrace using OAT but > it seems that is limited to the max numbers of traces set. > > How can I get a history of saved SQL traces that goes way beyond the max > number of traces set for sysamster:syssqltrace? > > Is it possible to set for example 10000 rows max in the syssqltrace and > still keep an sqltrace history in mon_syssqltrace that allows me to save > a history for several days? > > Khaled Bentebal > > Email: khaled.bentebal@consult-ix.fr > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
Hi, The sysmaster tables for the traces are not real tables. They are just a way to view a large buffer in shared memory. That buffer is a fixed size and is used circularly. To get a more permanent trace, you will have to copy the contents of the syssqltrace table (and the related syssqltrace_info, syssqltrace_iter and syssqltrace_hvar tables if you care) to a permanent table or tables. One way to do that is to write and schedule a task to copy the data at some regular interval. You can allocate as much space as you wish in those permanent tables to retain traced SQL. Cheers, Dick Snoke IBM ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "Khaled Bentebal" <khaled.bentebal@consult-ix.fr> To: ids@iiug.org Date: 02/08/2011 09:35 PM Subject: OAT and mon_syssqltrace [22725] Sent by: ids-bounces@iiug.org Hi, I am trying to get permanent sql traces in mon_syssqltrace using OAT but it seems that is limited to the max numbers of traces set. How can I get a history of saved SQL traces that goes way beyond the max number of traces set for sysamster:syssqltrace? Is it possible to set for example 10000 rows max in the syssqltrace and still keep an sqltrace history in mon_syssqltrace that allows me to save a history for several days? Khaled Bentebal Email: khaled.bentebal@consult-ix.fr > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hi, Yes I do know that. I am getting a permament trace into mon_syssqltrace* tables, however, the max number of rows seems to be limited by the max number of traces even in the permanent tables created by the function sql_showsnap (used OAT for this). This problem I am encountering is on IDS 11.70.FC1DE on a MAC. No problem using IDS 11.50.FC4 on LINUX for example. Khaled Bentebal Email: khaled.bentebal@consult-ix.fr Le 09/02/11 15:18, Richard Snoke a écrit : > Hi, > > The sysmaster tables for the traces are not real tables. They are just a > way to view a large buffer in shared memory. That buffer is a fixed size > and is used circularly. To get a more permanent trace, you will have to > copy the contents of the syssqltrace table (and the related > syssqltrace_info, syssqltrace_iter and syssqltrace_hvar tables if you > care) to a permanent table or tables. One way to do that is to write and > schedule a task to copy the data at some regular interval. You can > allocate as much space as you wish in those permanent tables to retain > traced SQL. > > Cheers, > Dick Snoke > IBM ChannelWorks > > dsnoke@us.ibm.com > (404) 487-1595 > > From: "Khaled Bentebal"<khaled.bentebal@consult-ix.fr> > To: ids@iiug.org > Date: 02/08/2011 09:35 PM > Subject: OAT and mon_syssqltrace [22725] > Sent by: ids-bounces@iiug.org > > Hi, > > I am trying to get permanent sql traces in mon_syssqltrace using OAT but > it seems that is limited to the max numbers of traces set. > > How can I get a history of saved SQL traces that goes way beyond the max > number of traces set for sysamster:syssqltrace? > > Is it possible to set for example 10000 rows max in the syssqltrace and > still keep an sqltrace history in mon_syssqltrace that allows me to save > a history for several days? > > Khaled Bentebal > > Email: khaled.bentebal@consult-ix.fr > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
Hi, Is there some other task (or part of moving the data to the permanent tables) that is enforcing that limit. How is the data getting moved? If there is a task, how is that defined. If there is a stored procedure, what is the SQL in that. Note that a task definition may include a limit on how long data is retained (the tk_delete attribute in ph_task). So the devil is in the details here. Cheers, Dick Snoke IBM ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "Khaled Bentebal" <khaled.bentebal@consult-ix.fr> To: ids@iiug.org Date: 02/09/2011 09:32 AM Subject: Re: OAT and mon_syssqltrace [22732] Sent by: ids-bounces@iiug.org Hi, Yes I do know that. I am getting a permament trace into mon_syssqltrace* tables, however, the max number of rows seems to be limited by the max number of traces even in the permanent tables created by the function sql_showsnap (used OAT for this). This problem I am encountering is on IDS 11.70.FC1DE on a MAC. No problem using IDS 11.50.FC4 on LINUX for example. Khaled Bentebal Email: khaled.bentebal@consult-ix.fr Le 09/02/11 15:18, Richard Snoke a écrit : > Hi, > > The sysmaster tables for the traces are not real tables. They are just a > way to view a large buffer in shared memory. That buffer is a fixed size > and is used circularly. To get a more permanent trace, you will have to > copy the contents of the syssqltrace table (and the related > syssqltrace_info, syssqltrace_iter and syssqltrace_hvar tables if you > care) to a permanent table or tables. One way to do that is to write and > schedule a task to copy the data at some regular interval. You can > allocate as much space as you wish in those permanent tables to retain > traced SQL. > > Cheers, > Dick Snoke > IBM ChannelWorks > > dsnoke@us.ibm.com > (404) 487-1595 > > From: "Khaled Bentebal"<khaled.bentebal@consult-ix.fr> > To: ids@iiug.org > Date: 02/08/2011 09:35 PM > Subject: OAT and mon_syssqltrace [22725] > Sent by: ids-bounces@iiug.org > > Hi, > > I am trying to get permanent sql traces in mon_syssqltrace using OAT but > it seems that is limited to the max numbers of traces set. > > How can I get a history of saved SQL traces that goes way beyond the max > number of traces set for sysamster:syssqltrace? > > Is it possible to set for example 10000 rows max in the syssqltrace and > still keep an sqltrace history in mon_syssqltrace that allows me to save > a history for several days? > > Khaled Bentebal > > Email: khaled.bentebal@consult-ix.fr > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
In version 11.70 there is a task which ships by default with the server called "Save SQL Trace". It copies data from the syssqltrace* tables into permanent mon_sqltrace* tables. As part of that task there is a Data Delete interval and a run frequency. If memory servers me correctly these are set to 1 day and 15 minutes by default. It sounds like these values (or the current settings) are not to your liking. I would suggest you select Task Scheduler --> Scheduler and find the task name "Save SQL Trace" and click on the name to bring up the scheduling details and adjust them to suite your needs. Hope it helps, John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 02/09/2011 06:18:36 AM: > From: > > Richard Snoke/Atlanta/IBM@IBMUS > > To: > > ids@iiug.org > > Date: > > 02/09/2011 06:19 AM > > Subject: > > Re: OAT and mon_syssqltrace [22731] > > Sent by: > > ids-bounces@iiug.org > > Hi, > > The sysmaster tables for the traces are not real tables. They are just a > way to view a large buffer in shared memory. That buffer is a fixed size > and is used circularly. To get a more permanent trace, you will have to > copy the contents of the syssqltrace table (and the related > syssqltrace_info, syssqltrace_iter and syssqltrace_hvar tables if you > care) to a permanent table or tables. One way to do that is to write and > schedule a task to copy the data at some regular interval. You can > allocate as much space as you wish in those permanent tables to retain > traced SQL. > > Cheers, > Dick Snoke > IBM ChannelWorks > > dsnoke@us.ibm.com > (404) 487-1595 > > From: "Khaled Bentebal" <khaled.bentebal@consult-ix.fr> > To: ids@iiug.org > Date: 02/08/2011 09:35 PM > Subject: OAT and mon_syssqltrace [22725] > Sent by: ids-bounces@iiug.org > > Hi, > > I am trying to get permanent sql traces in mon_syssqltrace using OAT but > it seems that is limited to the max numbers of traces set. > > How can I get a history of saved SQL traces that goes way beyond the max > number of traces set for sysamster:syssqltrace? > > Is it possible to set for example 10000 rows max in the syssqltrace and > still keep an sqltrace history in mon_syssqltrace that allows me to save > a history for several days? > > Khaled Bentebal > > Email: khaled.bentebal@consult-ix.fr > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
The task "Save SQL Trace" is created by OAT as I see it whether we use 11.50 or 11.70. As soon as we go to the "SQL Explorer" in OAT, the " task is created and launched based on the "sql_showsnap" function. So the data is copied into the permanent tables mon_syssqltrace* from sysmaster:syssqltrace* This part works fine but stops when the max number of traces is reached. On Linux using 11.50.FC4, it works fine. I haven't has the time to test SQLTRACE on 11.70 on Linux. I checked the scheduling details of the Task thru OAT: - start 1:0:0 - stop never - frequency: every minute - data delete : 0 0:0:0 Enabled for everyday. I will have to do the tests using 11.70 on Linux. Khaled Bentebal Email: khaled.bentebal@consult-ix.fr Le 09/02/11 16:11, John Miller iii a écrit : > In version 11.70 there is a task which ships by default with the server > called "Save SQL Trace". It copies data from the syssqltrace* tables > into permanent mon_sqltrace* tables. As part of that task there is a Data > Delete interval and a run frequency. If memory servers me correctly these > are set to 1 day and 15 minutes by default. It sounds like these values > (or the current settings) are not to your liking. I would suggest you > select > Task Scheduler --> Scheduler and find the task name "Save SQL Trace" > and click on the name to bring up the scheduling details and adjust them > to suite your needs. > > Hope it helps, > > John F. Miller III > STSM, Embedability Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 02/09/2011 06:18:36 AM: > >> From: >> >> Richard Snoke/Atlanta/IBM@IBMUS >> >> To: >> >> ids@iiug.org >> >> Date: >> >> 02/09/2011 06:19 AM >> >> Subject: >> >> Re: OAT and mon_syssqltrace [22731] >> >> Sent by: >> >> ids-bounces@iiug.org >> >> Hi, >> >> The sysmaster tables for the traces are not real tables. They are just a >> way to view a large buffer in shared memory. That buffer is a fixed size >> and is used circularly. To get a more permanent trace, you will have to >> copy the contents of the syssqltrace table (and the related >> syssqltrace_info, syssqltrace_iter and syssqltrace_hvar tables if you >> care) to a permanent table or tables. One way to do that is to write and >> schedule a task to copy the data at some regular interval. You can >> allocate as much space as you wish in those permanent tables to retain >> traced SQL. >> >> Cheers, >> Dick Snoke >> IBM ChannelWorks >> >> dsnoke@us.ibm.com >> (404) 487-1595 >> >> From: "Khaled Bentebal"<khaled.bentebal@consult-ix.fr> >> To: ids@iiug.org >> Date: 02/08/2011 09:35 PM >> Subject: OAT and mon_syssqltrace [22725] >> Sent by: ids-bounces@iiug.org >> >> Hi, >> >> I am trying to get permanent sql traces in mon_syssqltrace using OAT but >> it seems that is limited to the max numbers of traces set. >> >> How can I get a history of saved SQL traces that goes way beyond the max >> number of traces set for sysamster:syssqltrace? >> >> Is it possible to set for example 10000 rows max in the syssqltrace and >> still keep an sqltrace history in mon_syssqltrace that allows me to save >> a history for several days? >> >> Khaled Bentebal >> >> Email: khaled.bentebal@consult-ix.fr >> >> >> > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> >> > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >