IDS Forum

Re: SQL TRACE with strange behave...

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

In Response To: Re: SQL TRACE with strange behave... (Fernando Nunes)

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.
>

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.