Q - sql trace for the scheduler user

[navzjoshi00]

All

Might sound silly (forgot ... :( ) .. how to set sql trace for the scheduler user ?

Regards
NJ

[Anon Anon]

If you turn it on (CMN_SEC_USERS.SQLTRACE_ACTIVE) for the user that submitted (scheduled) the job then you gets trace files for the job. (At least for some jobs you do, don't know about all of them).

[navzjoshi00]

Thanks Dave - just a quick question though - if the user is "Locked" in Clarity - e.g. the "admin" user right now locked in our environment. As I see, the job has been scheduled using the "admin" user - would this affect the SQL trace in any way ?

Regards
NJ

[Anon Anon]

No idea.... why not try it and see!

(and its pretty simple to unlock the admin user too)

[Deactivated User]

So you are not talking about
TEC468125 Clarity: How do I enable Scheduler logging for Open Workbench troubleshooting???

Martti K.

[Anon Anon]

^ Right, I may have misunderstood I was in "job scheduler" user and not "plan scheduler" mode!?!?! :wacko:

[navzjoshi00]

Infact, the question is about "job scheduler" user ... :)

I will let u knw abt my finding.

Regards
NJ

[Anon Anon]

:bashful:

--

(but Martti's note about the OWB scheduler tracing was much more interesting to me!)

[Anon Anon]

I have always used this script to trace a user that is using Openworkbench

update cmn_sec_users
set sqltrace_active = '10'
where user_name = 'user ID goes here';
commit;

[Deactivated User]

The problem I have had with the standard trace is that it saves database actions, but you can do all kinds of thing in OWB between saves. Only the final state is traced.

The reference to the locked user hints which scheduler is in question.

Just ran a test:
Logged in as a user (id= administrator).
Created a scheduled job.
Logged out,
Logged in as a different user.
Locked user administrator.
Set the trace on (level 2)
Waited until the scheduled time had passed
Results:
Job had run
There was a trace for the user.

Martti K.

[Anon Anon]

Am I understanding this correctly?

You log in as the administrator and schedule a job. You then log out and log in as another user to lock the administrator. The job runs after the admin ID is locked but your trace log doesn't show it? Did you start the trace after you logged off of the administrator ID? Isn't the trace log created when a user logs in. If the ID is locked and it's only a job running, I wouldn't think it would log anything.

Try setting your trace to 10 instead of 2. My question would be, how is the job running on an ID that is locked? Makes me wonder if the job is running under another ID, hence no log file for on the job under the administrator.

[Deactivated User]

No,
The trace shows that the job is run and is set to complete.
That is the result is that the job ran fine and it was traced even if the user was locked.
Note that the ID=administrator is different from the admin.

Martti K.

[Deactivated User]

Here is the trace

Martti K.

Attachment(s)

Administrator_5000575_trace_log.txt   106 KB 1 version