IDS Forum

Re: SQL TRACE with strange behave...

Posted By: Cesar Inacio Martins
Date: Wednesday, 18 July 2012, at 9:01 p.m.

In Response To: Re: SQL TRACE with strange behave... (John Miller iii)

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.
>>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>

Messages In This Thread

• SQL TRACE with strange behave...
  Cesar Inacio Martins -- Wednesday, 18 July 2012, at 10:52 a.m.
  • Re: SQL TRACE with strange behave...
    Fernando Nunes -- Wednesday, 18 July 2012, at 11:07 a.m.
    • Re: SQL TRACE with strange behave...
      Cesar Inacio Martins -- Wednesday, 18 July 2012, at 3:28 p.m.
      • Re: SQL TRACE with strange behave...
        John Miller iii -- Wednesday, 18 July 2012, at 4:20 p.m.
        • Re: SQL TRACE with strange behave...
          Cesar Inacio Martins -- Wednesday, 18 July 2012, at 9:01 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.