Helpful SQL Queries Used by Siebel Developers

Siebel

Jun 6

Working on Siebel often means jumping back and forth between Tools, the Siebel Client, and the underlying database. At some point, every Siebel developer ends up needing a small set of SQL queries to troubleshoot problems, validate data, or verify a configuration change. This post walks through some of the most common queries used day‑to‑day and how they fit into typical Siebel development tasks.

Looking up users and responsibilities

When access or visibility is wrong, you often need to check user and responsibility mappings.

To see basic user details:

  
    SELECT
    ROW_ID,
    LOGIN,
    FST_NAME,
    LAST_NAME,
    EMP_FLG,
    ACTIVE_FLG
FROM S_USER
WHERE LOGIN = 'SADMIN';
  

To list responsibilities for a given user:

  
    SELECT
    ur.ROW_ID,
    u.LOGIN,
    r.NAME AS RESPONSIBILITY_NAME
FROM S_USER_RESP ur
JOIN S_USER u
  ON u.ROW_ID = ur.USER_ID
JOIN S_RESP r
  ON r.ROW_ID = ur.RESP_ID
WHERE u.LOGIN = 'SADMIN';
  

To list all views associated with a responsibility:

  
    SELECT
    r.NAME        AS RESPONSIBILITY,
    v.NAME        AS VIEW_NAME,
    v.VIEWBAR_TEXT,
    v.PROJECT_NAME
FROM S_RESP r
JOIN S_APP_VIEW_RESP rv
  ON r.ROW_ID = rv.RESP_ID
JOIN S_APP_VIEW v
  ON v.ROW_ID = rv.VIEW_ID
WHERE r.NAME = 'Sales Representative'
ORDER BY v.NAME;
  

Exporting Runtime Events and Action Sets

At times, you may need to export all runtime events and their related action sets. This may be helpful when troubleshooting a problem or when documenting your configuration. Use the query below to display or export this information.

  
    SELECT
    -- Runtime Event (Events view)
    evt.ROW_ID              AS EVENT_ID,
    evt.NAME                AS EVENT_NAME,
    evt.OBJECT_TYPE_CD      AS OBJECT_TYPE,
    evt.OBJECT_NAME         AS OBJECT_NAME,
    evt.EVENT_CD            AS EVENT,
    evt.CONDITION           AS CONDITION_EXPR,
    evt.ACTION_SET_NAME     AS ACTION_SET_NAME,
    evt.ACTIVE_FLG          AS EVENT_ACTIVE_FLG,
    evt.COMMENTS            AS EVENT_COMMENTS,
    evt.CREATED             AS EVENT_CREATED,
    evt.CREATED_BY          AS EVENT_CREATED_BY,
    evt.LAST_UPD            AS EVENT_LAST_UPD,
    evt.LAST_UPD_BY         AS EVENT_LAST_UPD_BY,

    -- Action Set (Action Sets view)
    aset.ROW_ID             AS ACTION_SET_ID,
    aset.NAME               AS ACTION_SET_UI_NAME,
    aset.ACTIVE_FLG         AS ACTION_SET_ACTIVE_FLG,
    aset.EFF_START_DT       AS ACTION_SET_START_DT,
    aset.EFF_END_DT         AS ACTION_SET_END_DT,
    aset.COMMENTS           AS ACTION_SET_COMMENTS,
    aset.CREATED            AS ACTION_SET_CREATED,
    aset.CREATED_BY         AS ACTION_SET_CREATED_BY,
    aset.LAST_UPD           AS ACTION_SET_LAST_UPD,
    aset.LAST_UPD_BY        AS ACTION_SET_LAST_UPD_BY,

    -- Action (Actions view)
    act.ROW_ID              AS ACTION_ID,
    act.SEQ_NUM             AS ACTION_SEQUENCE,
    act.ACTION_TYPE_CD      AS ACTION_TYPE,
    act.BUS_SVC_NAME        AS BUSINESS_SERVICE_NAME,
    act.BUS_SVC_METHOD_NAME AS BUSINESS_SERVICE_METHOD,
    act.BUS_SVC_CONTEXT     AS BUSINESS_SERVICE_CONTEXT,
    act.WF_PROC_NAME        AS WORKFLOW_PROCESS_NAME,
    act.ACTIVE_FLG          AS ACTION_ACTIVE_FLG,
    act.COMMENTS            AS ACTION_COMMENTS,
    act.CREATED             AS ACTION_CREATED,
    act.CREATED_BY          AS ACTION_CREATED_BY,
    act.LAST_UPD            AS ACTION_LAST_UPD,
    act.LAST_UPD_BY         AS ACTION_LAST_UPD_BY

FROM S_RTE_EVT        evt      -- Runtime Events
JOIN S_RTE_SET        aset
  ON aset.NAME = evt.ACTION_SET_NAME   -- link event to its action set
JOIN S_RTE_ACT        act
  ON act.RTE_SET_ID = aset.ROW_ID      -- actions in the action set

-- Optional: filter only active events / sets / actions
-- WHERE evt.ACTIVE_FLG = 'Y'
--   AND aset.ACTIVE_FLG = 'Y'
--   AND act.ACTIVE_FLG = 'Y'

ORDER BY
    evt.OBJECT_TYPE_CD,
    evt.OBJECT_NAME,
    evt.EVENT_CD,
    aset.NAME,
    act.SEQ_NUM;

  

Identify failed outbound emails

If your Siebel system sends outbound emails, there may be times when for one reason or another these emails fail to send. When this occurs, use the query below to find the problem records so they can be re-processed. We recommend running this query in a script that executes on a scheduled interval and sending administrators the results.

  
    SELECT
    req.ROW_ID              AS REQUEST_ID,
    req.REQ_ID              AS COMM_REQUEST_NUM,
    req.NAME                AS REQUEST_NAME,
    req.PROFILE_NAME        AS COMM_PROFILE,
    req.STATUS_CD           AS REQUEST_STATUS,
    req.DESC_TEXT           AS ERROR_TEXT,
    req.CREATED             AS CREATED_DATE,
    req.CREATED_BY          AS CREATED_BY,
    req.LAST_UPD            AS LAST_UPD_DATE,
    req.LAST_UPD_BY         AS LAST_UPD_BY,
    itm.ROW_ID              AS ITEM_ID,
    itm.RECIP_ADDR          AS RECIPIENT_EMAIL,
    itm.SUBJECT_TEXT        AS EMAIL_SUBJECT,
    itm.MSG_BODY            AS EMAIL_BODY
FROM S_COMM_REQ     req
JOIN S_COMM_REQ_IT  itm
  ON itm.COMM_REQ_ID = req.ROW_ID
WHERE req.CHANNEL_CD = 'Email'
  AND req.STATUS_CD IN ('Error', 'Failed', 'Aborted')   -- adjust to match your codes
ORDER BY req.CREATED DESC;

  

Identifying failed Siebel components

The following query will help administrators identify Siebel components that are not currently online or running. As with the query mentioned above, we recommend calling this query from a script/job that executes on a regularly scheduled interval and emailing the results to an admin distribution list.

  
    SELECT
    s.ENTERPRISE_NAME,
    s.SERVER_NAME,
    c.COMP_NAME,
    c.DESC_TEXT          AS COMPONENT_DESC,
    st.TASK_STATE        AS RUN_STATE,
    st.ENABLE_STATE,
    st.STATUS_MSG,
    st.LAST_UPD          AS LAST_UPDATE
FROM S_SRM_CONFG       c          -- component definition
JOIN S_SRM_STATE       st
  ON st.COMP_ID = c.ROW_ID        -- runtime state
JOIN S_SRM_SERVER      s
  ON s.ROW_ID = st.SRVR_ID        -- server info
WHERE st.TASK_STATE NOT IN ('Online', 'Running')
ORDER BY
    s.ENTERPRISE_NAME,
    s.SERVER_NAME,
    c.COMP_NAME;

  

That’s it for now. We’ll continue to update this list as we come across new queries that we think might be helpful.

siebel