SQL Query to List All Active RCR jobs in Siebel

As with any Siebel implementation, one of the day-to-day monitoring tasks for an Administrator are the Siebel components. And the RCR jobs running on the servers tend to be an integral part of the Workflow Process Manager and Workflow Process Batch Manager component requests. So, tracking them becomes useful in situations where there are component issues or some critical job failure.

This SQL can be a reference to list all the RCR jobs by their Execution Server name which is very helpful if you are not assigning any Server Name to the job upon submitting.

SELECT
    PAR_REQ_ID "Job Id",
    rpt_interval||' '||rpt_uom "Frequency",
    STATUS,
    ACTL_START_DT,
    EXEC_SRVR_NAME
FROM
    SIEBEL.S_SRM_REQUEST
WHERE
    REQ_TYPE_CD = 'RPT_INSTANCE'
    AND STATUS = 'ACTIVE'
    AND PAR_REQ_ID in
(
select
    par.row_id
from
    siebel.S_SRM_REQUEST par,
    siebel.S_SRM_ACT_PARAM aparam ,
    siebel.S_SRM_REQUEST child ,
    siebel.S_SRM_REQ_PARAM param
where
    par.row_id = child.par_req_id
    and par.req_type_cd = 'RPT_PARENT'
    and par.STATUS = 'ACTIVE'
    and child.status in ('QUEUED','ACTIVE')
    and par.row_id = param.req_id
    and child.req_type_cd = 'RPT_INSTANCE'
    and param.ACTPARAM_ID = aparam.row_id
    and aparam.NAME = 'Workflow Process Name'
group by par.row_id,param.value, par.rpt_interval||' '||par.rpt_uom
)
order by ACTL_START_DT desc;

As usual, you can feel free to play around and come up with complex outputs with this reference SQL.

Cheers,
Shyam

SQL Query to List all users by Responsibility Name in Siebel

I had a random request come in which wanted the list of users by a responsibility name, some quick Google searches later realized that its not available readily on the Internet. So here it is for an easy reference guys.

SELECT U.LOGIN
FROM SIEBEL.S_USER U, SIEBEL.S_PER_RESP PER, SIEBEL.S_RESP R
WHERE U.ROW_ID = PER.PER_ID
AND R.ROW_ID = PER.RESP_ID
AND R.NAME LIKE 'Siebel Administrator';

You can play around and tweak it as you like for more complex results.

Alter sessions commands for Siebel SQL

In order to have the Siebel SQL statements that we obtain from the logs run with the same execution time with SQL clients like SQL Developer/Toad, these alter statements need to be run in the DB client window where you plan to execute the SQL from Siebel logs:

alter session set optimizer_mode = first_rows_10
alter session set “_optimizer_sortmerge_join_enabled” = false
alter session set “_optimizer_join_sel_sanity_check” = true