SQL TRACE with strange behave...

Posted in 2012

Cesar Inacio Martins — Wed, 18 Jul 2012 14:52:07 — source: IIUG Forums & Mailing Lists

Hi, IFX 11.50 FC9X6 , AIX 6.1 I never was capable make SQL TRACE work properly , it never does what I expected. But, some way or other I always get what I need. Today they appear be totally crazy at our production environment.... never ever do what I configure it to do.... What I always needed is trace a specific session (SID), what appear be impossible... Anyway , at least I trace some user sessions and filter what I need and at end works for me. (but is sad, because I just not able to make the engine works as documented) Now I'm trying trace a user session and they just don't trace! I believed missing something... or doing something totally wrong.... (despite already used this SQLs a lot times before...) Please, someone could check if have any wrong with the SQL bellow? My intention is trace all session from user 'asilva' or the sid = 8414101... select * from table(sysadmin:task('set sql tracing off')) union all select * from table(sysadmin:task('set sql tracing on',40000,'4000b','high','user')) union all select * from table(sysadmin:task('set sql tracing info')) union all select * from table(sysadmin:task('set sql tracing database list')) union all select * from table(sysadmin:task('set sql tracing user list')) union all select * from table(sysadmin:task('set sql tracing session list')) union all select * from table(sysadmin:task('set sql tracing user clear')) union all select * from table(sysadmin:task("set sql user tracing clear")) union all select * from table(sysadmin:task("set sql user tracing off")) union all select * from table(sysadmin:task('set sql tracing databaseclear')) union all select * from table(sysadmin:task('set sql tracing session','clear')) union all select * from table(sysadmin:task('set sql tracing user add', 'asilva')) -- sometimes I include the SQL bellow -- union all select sysadmin:task('set sql tracing session','on', sid) from sysmaster:syssessions where username = 'asilva' and (sid in (8414101) or pid = '7434380') ; And can anyone explain this doubts? The documentation/manuals don't help much... 1) What the difference between : 'set sql tracing user' and 'set sql user tracing' Please, I read the manual, I know the mainly difference : one work with USER mode and other with GLOBAL... but in practice don't works, one you inform the SID and other the USER , but this just appear not work too... I says don't work, because always appear in my traces SQLs of others users or sometimes SQLs executed with informix/dbscheduler (!???) 2) There is someway to make 'set sql tracing session' or 'set sql user tracing' effectively works , filtering only 1 (one) session ? without include new sessions.... I know, a lot of (idiots) doubts ... but for me , the SQL TRACE never work fine.. always it does something what I don't want... is frustrating... and rather help, just give us more work or frustration... Cesar

↪ replying to Cesar Inacio Martins

Fernando Nunes — Wed, 18 Jul 2012 15:07:55 — source: IIUG Forums & Mailing Lists

I would need more time to read your note and maybe run some tests... But to give you a short answer, have you tried to use OAT for that? I know you don't like it too much... On Jul 18, 2012 3:52 PM, "Cesar Inacio Martins" < cesar_inacio_martins@yahoo.com.br> wrote: > Hi, > > IFX 11.50 FC9X6 , AIX 6.1 > > I never was capable make SQL TRACE work properly , it never does what I > expected. > But, some way or other I always get what I need. > > Today they appear be totally crazy at our production environment.... > never ever do what I configure it to do.... > > What I always needed is trace a specific session (SID), what appear be > impossible... > Anyway , at least I trace some user sessions and filter what I need and > at end works for me. (but is sad, because I just not able to make the > engine works as documented) > > Now I'm trying trace a user session and they just don't trace! > I believed missing something... or doing something totally wrong.... > (despite already used this SQLs a lot times before...) > > Please, someone could check if have any wrong with the SQL bellow? > My intention is trace all session from user 'asilva' or the sid = > 8414101... > > select * from table(sysadmin:task('set sql tracing off')) union all> > select * from table(sysadmin:task('set sql tracing > on',40000,'4000b','high','user')) union all> > select * from table(sysadmin:task('set sql tracing info')) union all> > select * from table(sysadmin:task('set sql tracing database list')) > union all> > select * from table(sysadmin:task('set sql tracing user list')) > union all> > select * from table(sysadmin:task('set sql tracing session list')) > union all> > select * from table(sysadmin:task('set sql tracing user clear')) > union all> > select * from table(sysadmin:task("set sql user tracing clear")) > union all> > select * from table(sysadmin:task("set sql user tracing off")) > union all> > select * from table(sysadmin:task('set sql tracing database> clear')) union all > > select * from table(sysadmin:task('set sql tracing session',> 'clear')) union all > > select * from table(sysadmin:task('set sql tracing user add', > 'asilva'))> > -- sometimes I include the SQL bellow > > -- union all select sysadmin:task('set sql tracing session','on', > sid) from sysmaster:syssessions > > where username = 'asilva' > > and (sid in (8414101) > > or pid = '7434380') ; > > And can anyone explain this doubts? > The documentation/manuals don't help much... > > 1) What the difference between : > > 'set sql tracing user' and 'set sql user tracing' > > Please, I read the manual, I know the mainly difference : one work > with USER mode and other with GLOBAL... > > but in practice don't works, one you inform the SID and other the > USER , but this just appear not work too... > > I says don't work, because always appear in my traces SQLs of > others users or sometimes SQLs executed with informix/dbscheduler (!???) > > 2) There is someway to make 'set sql tracing session' or 'set sql user > tracing' effectively works , filtering only 1 (one) session ? without > include new sessions.... > > I know, a lot of (idiots) doubts ... but for me , the SQL TRACE never > work fine.. always it does something what I don't want... is > frustrating... and rather help, just give us more work or frustration... > > Cesar > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00248c70fdddefa78b04c51c06b7

↪ replying to Fernando Nunes

Cesar Inacio Martins — Wed, 18 Jul 2012 19:28:34 — source: IIUG Forums & Mailing Lists

Hi Fernando, Yes... I'm not much close to OAT yet... I thought of using it today , just to make sure I doing something very wrong and OAT will do right... At this moment , I waiting again for the scheduled user process to monitor... I hope capture something... Although I reactive a user trace a few hours and inform just one user name, and this user not opened any session so far, but this not prevent the TRACE capture 7480 statements so far... 100% informix user, 100% sysadmin database... SQLs like bellow.... I will try filter with OAT too.... not today, maybe tomorrow. Database: sysadmin Statement text: SELECT FIRST 1 {+first_rows} tk_id, tk_sequence, tk_result_table, tk_execute, tk_delete, tk_start_time, tk_stop_time, tk_next_execution, tk_frequency, tk_attributes, tk_type, tk_name, tk_dbs, CASE WHEN tk_next_execution - CURRENT < INTERVAL (-365) DAY(3) TO DAY THEN -31536000 ELSE (tk_next_execution - CURRENT)::INTERVAL SECOND(9) TO SECOND::char(20)::integer END as tm_rem, (decode(tk_sunday, 't',1,0) + decode(tk_monday, 't',2,0) + decode(tk_tuesday, 't',4,0) + decode(tk_wednesday, 't',8,0) + decode(tk_thursday, 't',16,0) + decode(tk_friday, 't',32,0) + decode(tk_saturday, 't',64,0))::integer as tm_days, tk_total_time / decode(tk_total_executions,0,1,tk_total_executions) FROM ph_task WHERE BITAND(tk_attributes, 512) = 0 AND tk_next_execution IS NOT NULL AND tk_enable ORDER BY tk_next_execution, tk_priority SELECT using table [ ph_task ] On 18/7/2012 12:07, Fernando Nunes wrote: > I would need more time to read your note and maybe run some tests... But to > give you a short answer, have you tried to use OAT for that? I know you > don't like it too much... > On Jul 18, 2012 3:52 PM, "Cesar Inacio Martins" < > cesar_inacio_martins@yahoo.com.br> wrote: > >> Hi, >> >> IFX 11.50 FC9X6 , AIX 6.1 >> >> I never was capable make SQL TRACE work properly , it never does what I >> expected. >> But, some way or other I always get what I need. >> >> Today they appear be totally crazy at our production environment.... >> never ever do what I configure it to do.... >> >> What I always needed is trace a specific session (SID), what appear be >> impossible... >> Anyway , at least I trace some user sessions and filter what I need and >> at end works for me. (but is sad, because I just not able to make the >> engine works as documented) >> >> Now I'm trying trace a user session and they just don't trace! >> I believed missing something... or doing something totally wrong.... >> (despite already used this SQLs a lot times before...) >> >> Please, someone could check if have any wrong with the SQL bellow? >> My intention is trace all session from user 'asilva' or the sid = >> 8414101... >> >> select * from table(sysadmin:task('set sql tracing off')) union all>> >> select * from table(sysadmin:task('set sql tracing >> on',40000,'4000b','high','user')) union all>> >> select * from table(sysadmin:task('set sql tracing info')) union all>> >> select * from table(sysadmin:task('set sql tracing database list')) >> union all>> >> select * from table(sysadmin:task('set sql tracing user list')) >> union all>> >> select * from table(sysadmin:task('set sql tracing session list')) >> union all>> >> select * from table(sysadmin:task('set sql tracing user clear')) >> union all>> >> select * from table(sysadmin:task("set sql user tracing clear")) >> union all>> >> select * from table(sysadmin:task("set sql user tracing off")) >> union all>> >> select * from table(sysadmin:task('set sql tracing database>> clear')) union all >> >> select * from table(sysadmin:task('set sql tracing session',>> 'clear')) union all >> >> select * from table(sysadmin:task('set sql tracing user add', >> 'asilva'))>> >> -- sometimes I include the SQL bellow >> >> -- union all select sysadmin:task('set sql tracing session','on', >> sid) from sysmaster:syssessions >> >> where username = 'asilva' >> >> and (sid in (8414101) >> >> or pid = '7434380') ; >> >> And can anyone explain this doubts? >> The documentation/manuals don't help much... >> >> 1) What the difference between : >> >> 'set sql tracing user' and 'set sql user tracing' >> >> Please, I read the manual, I know the mainly difference : one work >> with USER mode and other with GLOBAL... >> >> but in practice don't works, one you inform the SID and other the >> USER , but this just appear not work too... >> >> I says don't work, because always appear in my traces SQLs of >> others users or sometimes SQLs executed with informix/dbscheduler (!???) >> >> 2) There is someway to make 'set sql tracing session' or 'set sql user >> tracing' effectively works , filtering only 1 (one) session ? without >> include new sessions.... >> >> I know, a lot of (idiots) doubts ... but for me , the SQL TRACE never >> work fine.. always it does something what I don't want... is >> frustrating... and rather help, just give us more work or frustration... >> >> Cesar >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --00248c70fdddefa78b04c51c06b7 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to Cesar Inacio Martins

John Miller iii — Wed, 18 Jul 2012 20:20:38 — source: IIUG Forums & Mailing Lists

Just an FYI, 1) By default in 11.70 the database scheduler threads have tracing disabled. 2) In version 11.50 you can disable tracing these threads manually. 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 07/18/2012 12:28:34 PM: > From: "Cesar Inacio Martins" <cesar_inacio_martins@yahoo.com.br> > To: ids@iiug.org, > Date: 07/18/2012 12:31 PM > Subject: Re: SQL TRACE with strange behave... [27665] > Sent by: ids-bounces@iiug.org > > Hi Fernando, > > Yes... I'm not much close to OAT yet... > I thought of using it today , just to make sure I doing something very > wrong and OAT will do right... > > At this moment , I waiting again for the scheduled user process to > monitor... I hope capture something... > Although I reactive a user trace a few hours and inform just one user > name, and this user not opened any session so far, but this not prevent > the TRACE capture 7480 statements so far... 100% informix user, 100% > sysadmin database... > > SQLs like bellow.... > I will try filter with OAT too.... not today, maybe tomorrow. > > Database: sysadmin > Statement text: > > SELECT FIRST 1 {+first_rows} tk_id, tk_sequence, tk_result_table,> > tk_execute, tk_delete, tk_start_time, tk_stop_time, tk_next_execution, > > tk_frequency, tk_attributes, tk_type, tk_name, tk_dbs, CASE WHEN > > tk_next_execution - CURRENT < INTERVAL (-365) DAY(3) TO DAY THEN > -31536000 > > ELSE (tk_next_execution - CURRENT)::INTERVAL SECOND(9) TO > > SECOND::char(20)::integer END as tm_rem, (decode(tk_sunday, > 't',1,0) > > + decode(tk_monday, 't',2,0) + decode(tk_tuesday, 't',4,0) + > > decode(tk_wednesday, 't',8,0) + decode(tk_thursday, 't',16,0) + > > decode(tk_friday, 't',32,0) + decode(tk_saturday, > 't',64,0))::integer > > as tm_days, tk_total_time / > > decode(tk_total_executions,0,1,tk_total_executions) FROM ph_task > WHERE > > BITAND(tk_attributes, 512) = 0 AND tk_next_execution IS NOT NULL AND > > tk_enable ORDER BY tk_next_execution, tk_priority > > SELECT using table [ ph_task ] > > On 18/7/2012 12:07, Fernando Nunes wrote: > > I would need more time to read your note and maybe run some tests... But to > > give you a short answer, have you tried to use OAT for that? I know you > > don't like it too much... > > On Jul 18, 2012 3:52 PM, "Cesar Inacio Martins" < > > cesar_inacio_martins@yahoo.com.br> wrote: > > > >> Hi, > >> > >> IFX 11.50 FC9X6 , AIX 6.1 > >> > >> I never was capable make SQL TRACE work properly , it never does what I > >> expected. > >> But, some way or other I always get what I need. > >> > >> Today they appear be totally crazy at our production environment.... > >> never ever do what I configure it to do.... > >> > >> What I always needed is trace a specific session (SID), what appear be > >> impossible... > >> Anyway , at least I trace some user sessions and filter what I need and > >> at end works for me. (but is sad, because I just not able to make the > >> engine works as documented) > >> > >> Now I'm trying trace a user session and they just don't trace! > >> I believed missing something... or doing something totally wrong.... > >> (despite already used this SQLs a lot times before...) > >> > >> Please, someone could check if have any wrong with the SQL bellow? > >> My intention is trace all session from user 'asilva' or the sid = > >> 8414101... > >> > >> select * from table(sysadmin:task('set sql tracing off')) union all> >> > >> select * from table(sysadmin:task('set sql tracing > >> on',40000,'4000b','high','user')) union all> >> > >> select * from table(sysadmin:task('set sql tracing info')) union all> >> > >> select * from table(sysadmin:task('set sql tracing database list')) > >> union all> >> > >> select * from table(sysadmin:task('set sql tracing user list')) > >> union all> >> > >> select * from table(sysadmin:task('set sql tracing session list')) > >> union all> >> > >> select * from table(sysadmin:task('set sql tracing user clear')) > >> union all> >> > >> select * from table(sysadmin:task("set sql user tracing clear")) > >> union all> >> > >> select * from table(sysadmin:task("set sql user tracing off")) > >> union all> >> > >> select * from table(sysadmin:task('set sql tracing database> >> clear')) union all > >> > >> select * from table(sysadmin:task('set sql tracing session',> >> 'clear')) union all > >> > >> select * from table(sysadmin:task('set sql tracing user add', > >> 'asilva'))> >> > >> -- sometimes I include the SQL bellow > >> > >> -- union all select sysadmin:task('set sql tracing session','on', > >> sid) from sysmaster:syssessions > >> > >> where username = 'asilva' > >> > >> and (sid in (8414101) > >> > >> or pid = '7434380') ; > >> > >> And can anyone explain this doubts? > >> The documentation/manuals don't help much... > >> > >> 1) What the difference between : > >> > >> 'set sql tracing user' and 'set sql user tracing' > >> > >> Please, I read the manual, I know the mainly difference : one work > >> with USER mode and other with GLOBAL... > >> > >> but in practice don't works, one you inform the SID and other the > >> USER , but this just appear not work too... > >> > >> I says don't work, because always appear in my traces SQLs of > >> others users or sometimes SQLs executed with informix/dbscheduler (!???) > >> > >> 2) There is someway to make 'set sql tracing session' or 'set sql user > >> tracing' effectively works , filtering only 1 (one) session ? without > >> include new sessions.... > >> > >> I know, a lot of (idiots) doubts ... but for me , the SQL TRACE never > >> work fine.. always it does something what I don't want... is > >> frustrating... and rather help, just give us more work or frustration... > >> > >> Cesar > >> > >> > >> > >> > > > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > >> > > --00248c70fdddefa78b04c51c06b7 > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to John Miller iii

Cesar Inacio Martins — Thu, 19 Jul 2012 01:01:51 — source: IIUG Forums & Mailing Lists

Hi John, Hmmm... this is very very useful and probally will solve all my problems with the trace. Looking at manuals I do not found anything... Can you tell us how disable the tracing over db scheduler?? Obs.: I get this SQL trace with : onstat -g his Regards Cesar On 18/7/2012 17:20, John Miller iii wrote: > Just an FYI, > > 1) By default in 11.70 the database scheduler threads have tracing > disabled. > > 2) In version 11.50 you can disable tracing these threads manually. > > 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 07/18/2012 12:28:34 PM: > >> From: "Cesar Inacio Martins" <cesar_inacio_martins@yahoo.com.br> >> To: ids@iiug.org, >> Date: 07/18/2012 12:31 PM >> Subject: Re: SQL TRACE with strange behave... [27665] >> Sent by: ids-bounces@iiug.org >> >> Hi Fernando, >> >> Yes... I'm not much close to OAT yet... >> I thought of using it today , just to make sure I doing something very >> wrong and OAT will do right... >> >> At this moment , I waiting again for the scheduled user process to >> monitor... I hope capture something... >> Although I reactive a user trace a few hours and inform just one user >> name, and this user not opened any session so far, but this not prevent >> the TRACE capture 7480 statements so far... 100% informix user, 100% >> sysadmin database... >> >> SQLs like bellow.... >> I will try filter with OAT too.... not today, maybe tomorrow. >> >> Database: sysadmin >> Statement text: >> >> SELECT FIRST 1 {+first_rows} tk_id, tk_sequence, tk_result_table,>> >> tk_execute, tk_delete, tk_start_time, tk_stop_time, tk_next_execution, >> >> tk_frequency, tk_attributes, tk_type, tk_name, tk_dbs, CASE WHEN >> >> tk_next_execution - CURRENT < INTERVAL (-365) DAY(3) TO DAY THEN >> -31536000 >> >> ELSE (tk_next_execution - CURRENT)::INTERVAL SECOND(9) TO >> >> SECOND::char(20)::integer END as tm_rem, (decode(tk_sunday, >> 't',1,0) >> >> + decode(tk_monday, 't',2,0) + decode(tk_tuesday, 't',4,0) + >> >> decode(tk_wednesday, 't',8,0) + decode(tk_thursday, 't',16,0) + >> >> decode(tk_friday, 't',32,0) + decode(tk_saturday, >> 't',64,0))::integer >> >> as tm_days, tk_total_time / >> >> decode(tk_total_executions,0,1,tk_total_executions) FROM ph_task >> WHERE >> >> BITAND(tk_attributes, 512) = 0 AND tk_next_execution IS NOT NULL AND >> >> tk_enable ORDER BY tk_next_execution, tk_priority >> >> SELECT using table [ ph_task ] >> >> On 18/7/2012 12:07, Fernando Nunes wrote: >>> I would need more time to read your note and maybe run some tests... > But to >>> give you a short answer, have you tried to use OAT for that? I know you >>> don't like it too much... >>> On Jul 18, 2012 3:52 PM, "Cesar Inacio Martins" < >>> cesar_inacio_martins@yahoo.com.br> wrote: >>> >>>> Hi, >>>> >>>> IFX 11.50 FC9X6 , AIX 6.1 >>>> >>>> I never was capable make SQL TRACE work properly , it never does what > I >>>> expected. >>>> But, some way or other I always get what I need. >>>> >>>> Today they appear be totally crazy at our production environment.... >>>> never ever do what I configure it to do.... >>>> >>>> What I always needed is trace a specific session (SID), what appear be >>>> impossible... >>>> Anyway , at least I trace some user sessions and filter what I need > and >>>> at end works for me. (but is sad, because I just not able to make the >>>> engine works as documented) >>>> >>>> Now I'm trying trace a user session and they just don't trace! >>>> I believed missing something... or doing something totally wrong.... >>>> (despite already used this SQLs a lot times before...) >>>> >>>> Please, someone could check if have any wrong with the SQL bellow? >>>> My intention is trace all session from user 'asilva' or the sid = >>>> 8414101... >>>> >>>> select * from table(sysadmin:task('set sql tracing off')) union all>>>> >>>> select * from table(sysadmin:task('set sql tracing >>>> on',40000,'4000b','high','user')) union all>>>> >>>> select * from table(sysadmin:task('set sql tracing info')) union all>>>> >>>> select * from table(sysadmin:task('set sql tracing database list')) >>>> union all>>>> >>>> select * from table(sysadmin:task('set sql tracing user list')) >>>> union all>>>> >>>> select * from table(sysadmin:task('set sql tracing session list')) >>>> union all>>>> >>>> select * from table(sysadmin:task('set sql tracing user clear')) >>>> union all>>>> >>>> select * from table(sysadmin:task("set sql user tracing clear")) >>>> union all>>>> >>>> select * from table(sysadmin:task("set sql user tracing off")) >>>> union all>>>> >>>> select * from table(sysadmin:task('set sql tracing database>>>> clear')) union all >>>> >>>> select * from table(sysadmin:task('set sql tracing session',>>>> 'clear')) union all >>>> >>>> select * from table(sysadmin:task('set sql tracing user add', >>>> 'asilva'))>>>> >>>> -- sometimes I include the SQL bellow >>>> >>>> -- union all select sysadmin:task('set sql tracing session','on', >>>> sid) from sysmaster:syssessions >>>> >>>> where username = 'asilva' >>>> >>>> and (sid in (8414101) >>>> >>>> or pid = '7434380') ; >>>> >>>> And can anyone explain this doubts? >>>> The documentation/manuals don't help much... >>>> >>>> 1) What the difference between : >>>> >>>> 'set sql tracing user' and 'set sql user tracing' >>>> >>>> Please, I read the manual, I know the mainly difference : one work >>>> with USER mode and other with GLOBAL... >>>> >>>> but in practice don't works, one you inform the SID and other the >>>> USER , but this just appear not work too... >>>> >>>> I says don't work, because always appear in my traces SQLs of >>>> others users or sometimes SQLs executed with informix/dbscheduler > (!???) >>>> 2) There is someway to make 'set sql tracing session' or 'set sql user >>>> tracing' effectively works , filtering only 1 (one) session ? without >>>> include new sessions.... >>>> >>>> I know, a lot of (idiots) doubts ... but for me , the SQL TRACE never >>>> work fine.. always it does something what I don't want... is >>>> frustrating... and rather help, just give us more work or > frustration... >>>> Cesar >>>> >>>> >>>> >>>> > ******************************************************************************* > >>>> Forum Note: Use "Reply" to post a response in the discussion forum. >>>> >>>> >>> --00248c70fdddefa78b04c51c06b7 >>> >>> >>> > ******************************************************************************* > >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >> >> > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > >@

Related threads

date issue (one day more)