1. Remote and Replication Manager Administration Guide
  2. Troubleshooting Errors in the S_DOCK_TXN_LOG table

Troubleshooting Errors in the S_DOCK_TXN_LOG table

This troubleshooting guide contains information to help you monitor and manage the transaction backlog for your Siebel Remote implementation. Specifically, it helps you troubleshoot errors in the S_DOCK_TXN_LOG.

Overview

If Transaction Logging is enabled, the Siebel application records transactions to the S_DOCK_TXN_LOG table for later routing to mobile web clients. Before Siebel version 8.0, Transaction Logging was enabled by setting the system preference, Docking: Transaction Logging, to True in Administration - Application > System Preferences. Beginning in version 8.0, Transaction Logging is enabled using the checkbox, Enable Transaction Logging, in Administration - Siebel Remote > Remote System Preferences. Normally, the volume of records in S_DOCK_TXN_LOG is about 2,000. However, this number can increase in the event of large data loads or activity.

"Backlog" represents the outstanding transactions or .dx files that await processing and routing by Siebel Remote. Backlog is the difference between the lowest, last routed transaction and the maximum transaction in the S_DOCK_TXN_LOG table. Backlog is evident by an increase in the volume of records in the S_DOCK_TXN_LOG table or a build-up of .dx files in the \txnproc folder. To check the volume of records in S_DOCK_TXN_LOG, run select count(*) from S_DOCK_TXN_LOG against the server database.

The Transaction Processor (TXNPROC) is responsible for deleting records from S_DOCK_TXN_LOG and .dx files from the \txnproc folder, once the Transaction Router has successfully routed these transactions to all mobile web clients for the Siebel Remote Server.

The Transaction Processor determines the minimum TXN_ID record in the S_DOCK_TXN_LOG table. It compares this value to the minimum LAST_TXN_ID record in the S_DOCK_STATUS table where TYPE = ROUTE and LOCAL_FLG = Y for mobile web clients on the remote server.

If the transaction hasn't yet been routed – and the minimum LAST_TXN_ID is less than the minimum TXN_ID – then there's no deletion. If the transaction has already been routed – and the minimum LAST_TXN_ID is greater than the minimum TXN_ID – then deletion takes place and the transaction is purged from the S_DOCK_TXN_LOG table.

Symptoms, Errors, and Causes

In a healthy Siebel Remote environment, the minimum TXN_ID in the S_DOCK_TXN_LOG table should continue to increase over time. If the minimum TXN_ID doesn't continue to increase and the total volume count continues to grow, review this section for possible causes.

Causes

There are several reasons why your transaction backlog could grow. These reasons might include:

  • The Transaction Process and Transaction Router tasks aren't running
  • The Transaction Processor isn't processing transactions
  • Obsolete Transaction Processors
  • Failed client extracts
  • Incomplete mobile web client setup
  • Orphaned transactions
  • Inactive mobile users
  • Transaction Router performance issues
  • Recent changes to position hierarchy, territories, or large EIM loads

Diagnostic Steps and Solutions

Review this section for diagnostic steps and solutions.

Complete the following steps to diagnose why your transaction backlog might be growing.

  1. Verify that the Transaction Processor and Transaction Router tasks are running.

    The Transaction Processor task, TXNPROC, reads the S_DOCK_TXN_LOG table and creates .dx files in the \txnproc folder.

    The Transaction Router task, TXNROUTE:

    • reads the .dx files from the TXNROUTE folder;
    • determines which mobile web clients on the remote server have visibility to these transactions; and
    • creates .dx files with that data in each mobile web client's DOCKING..\Outbox folder on the server.

    Also note:

    • Both TXNPROC and TXNROUTE need to be in a running state.
    • Only one Transaction Processor task should be running.
    • With Siebel applications version 5 and above, there can be more than one Transaction Router task running at the same time.

    To verify the status of these tasks, access the Server Tasks view, as follows:

    In Siebel applications version 7.7 and higher, navigate to the Administration – Server Management screen and select the Tasks view link from the link bar.

    The status of these tasks can also be determined using the following server manager commands:
    • srvrmgr> list tasks for comp txnproc
    • srvrmgr> list tasks for comp txnroute

    You can find more information on these commands and on using the command-line interface in the Siebel System Administration Guide: Siebel.

  2. Confirm that the Transaction Processor is processing transactions.

    Siebel versions 8.x, 7.x:

    Run the following query on the server database, choosing the appropriate syntax for your RDBMS:

    SELECT ST.TYPE, ST.LAST_FILE_NUM "DX FILE", N.NAME,N.EFF_END_DT,ST.LAST_TXN_NUM "TXN ID",
ST.LAST_UPD, SUBSTR(N.NAME,1,20) "TXNPROC",
SUBSTR(N.APP_SERVER_NAME,1,12) "APPSERVER"
FROM siebel.S_DOCK_STATUS ST, siebel.S_NODE N
WHERE ST.NODE_ID=N.ROW_ID
AND ST.LOCAL_FLG= 'Y'
AND ST.TYPE IN ('ROUTE', 'CLEAN')
AND N.NODE_TYPE_CD= 'TXNPROC'
AND (N.EFF_END_DT IS NULL OR N.EFF_END_DT > sysdate);
MS SQL Server
SELECT ST.TYPE, ST.LAST_FILE_NUM "DX FILE", N.NAME,N.EFF_END_DT,ST.LAST_TXN_NUM "TXN ID",
ST.LAST_UPD, SUBSTRING(N.NAME,1,20) "TXNPROC",
SUBSTRING(N.APP_SERVER_NAME,1,12) "APPSERVER"
FROM S_DOCK_STATUS ST, S_NODE N
WHERE ST.NODE_ID=N.ROW_ID
AND ST.LOCAL_FLG= 'Y'
AND ST.TYPE IN ('ROUTE', 'CLEAN')
AND N.NODE_TYPE_CD= 'TXNPROC'
AND (N.EFF_END_DT IS NULL OR N.EFF_END_DT > getdate());
DB2
SELECT ST.TYPE, ST.LAST_FILE_NUM "DX FILE", N.NAME,N.EFF_END_DT,ST.LAST_TXN_NUM "TXN ID",
ST.LAST_UPD, SUBSTR(N.NAME,1,20) "TXNPROC",
SUBSTR(N.APP_SERVER_NAME,1,12) "APPSERVER"
FROM siebel.S_DOCK_STATUS ST, siebel.S_NODE N
WHERE ST.NODE_ID=N.ROW_ID
AND ST.LOCAL_FLG= 'Y'
AND ST.TYPE IN ('ROUTE', 'CLEAN')
AND N.NODE_TYPE_CD= 'TXNPROC'
AND (N.EFF_END_DT IS NULL OR N.EFF_END_DT > current timestamp);

    This query returns two records for each active Transaction Processor, one of type ROUTE and one of type CLEAN. The ROUTE value represents the last .dx file and transaction copied to the SIEBEL_ROOT\txnproc directory. The CLEAN value represents the last .dx file deleted from the SIEBEL_ROOT\txnproc directory.

    If Transaction Processor and Transaction Router are processing correctly, the ROUTE values for DX FILE and TXN ID continue to increase over time.

    Another way to find information about the last transaction and the last file is to view the Siebel Remote Administration > Processor Status view:
    • In Siebel applications version 7.7 and above, navigate to Administration - Siebel Remote > Processor Status.
    • In Siebel applications versions 7.0.x and 7.5.x, select View > Site Map > Siebel Remote Administration > Processor Status.

    The Processor Status view shows information about the last transaction and the last file created by the Transaction Processor in the \TXNPROC directory on the Siebel server. Normally, if there aren't any problems, the last transaction and last file created keep increasing. If this value keeps increasing, users can see that Transaction Processor is processing transactions.

  3. Verify that there are no obsolete Transaction Processors.

    There might be Transaction Processor entries that are no longer in use. This can happen after an upgrade to a new version of Siebel applications, or if a Siebel server previously in use is no longer active. Transaction Processors without end dates that aren't in use prevent the proper and timely clean up of the transactions in the S_DOCK_TXN_LOG and the .dx files in SIEBEL_ROOT\txnproc directory.

    To identify all active Transaction Processor entries, run the following SQL query:

    select n.NAME,st.TYPE, st.LAST_FILE_NUM "DX FILE", st.LAST_TXN_NUM "TXN ID",
st.LAST_UPD, st.ADDTL_INFO
FROM S_DOCK_STATUS st, S_NODE n
where st.NODE_ID=n.ROW_ID
AND st.LOCAL_FLG='Y'
AND st.TYPE in ('ROUTE', 'CLEAN')
AND n.NODE_TYPE_CD='TXNPROC'
AND n.EFF_END_DT is null;

    Each Siebel remote server should only have one Transaction Processor entry. To inactivate a Transaction Processor entry, navigate to Siebel Remote Administration > Processor Status view and specify an end date.

  4. Confirm mobile web client folders, responsibilities, and positions.

    Confirm that all valid mobile web clients have their own outbox and inbox folders under the SIEBEL_ROOT\Docking directory, and that all have the appropriate responsibility and position associated.

    The Transaction Router can't route to an active mobile web client if:
    • There's no employee associated.
    • There's no position associated.
    • No docking folder exists for the mobile web client.
  5. Check for orphaned transactions.

    With Siebel 99, Siebel Systems introduced the concept of multi-operation transactions. For a multi-operation transaction there's one parent and many children. The child transactions have PAR_TXN_ID set to the parent transaction. Sometimes, if the parent transaction isn't present, the Transaction Processor can't delete the child transactions. These child transactions are then considered orphaned transactions.

    To determine whether there are any orphaned transactions, run the following SQL query:

    SELECT B.TXN_ID, B.ROW_ID, B.PAR_TXN_ID, B.OPERATION, B.ITEM_NAME
FROM S_DOCK_TXN_LOG A, S_DOCK_TXN_LOG B
WHERE A.ROW_ID(+) = B.PAR_TXN_ID
AND A.ROW_ID IS NULL;
DB2 or MS SQL Server
SELECT B.TXN_ID, B.ROW_ID, B.PAR_TXN_ID, B.OPERATION, B.ITEM_NAME FROM S_DOCK_TXN_LOG A
RIGHT OUTER JOIN S_DOCK_TXN_LOG B ON B.PAR_TXN_ID = A.ROW_ID
WHERE A.ROW_ID IS NULL

    You can find more information in the Remote and Replication Manager Administration guide: Using the Endtxnutl Utility to Adopt Orphaned Records.

  6. Verify that all inactive mobile users have an end date set.

    Navigate to the Siebel Remote Administration > Mobile Client view and set the end date for those mobile users who are no longer active.

    You can find inactive users by running the following SQL query against the server database:

    SELECT NAME NODE, LAST_TXN_NUM, LAST_FILE_NUM,
A.MAX_TXN_ID - LR.LAST_TXN_NUM TXNSTOROUTE, APP_SERVER_NAME,
LR.LAST_UPD
FROM S_NODE N, S_DOCK_STATUS LR,
(SELECT MAX(TXN_ID) MAX_TXN_ID FROM S_DOCK_TXN_LOG) A
WHERE N.EFF_END_DT IS NULL
and N.NODE_TYPE_CD = 'REMOTE'
AND LR.NODE_ID = N.ROW_ID
AND LR.TYPE = 'ROUTE'
AND LR.LOCAL_FLG = 'Y'
AND LR.LAST_TXN_NUM != -1
ORDER BY TXNSTOROUTE desc;

    Mobile web clients that haven't synced in a long time should be inactivated or re-extracted and encouraged to sync more often.

  7. Check for recent changes to position hierarchy, territory re-alignments, or large EIM loads.

    Changing position hierarchies, realigning territories, or loading a large amount of data through EIM creates a large number of transactions. It can take a substantial amount of time for the Transaction Processor and Transaction Routers to work through the backlog and to route all the changes to the appropriate mobile users.

    To speed up processing, re-extract all mobile web clients, or use multiple Transaction Routers. Please note that the higher in the hierarchy that changes are made or positions are added or deleted, the more transactions are created.

    Note: Siebel applications version 5 and higher use set-based transaction logging to reduce database contention for S_DOCK_TXN_LOG by only recording one transaction per EIM set in S_DOCK_TXN_LOG. Siebel 7 also introduced a new EIM parameter, LOG TRANSACTIONS TO FILE. When this parameter is set to TRUE, which is the default, EIM will log transactions under the File_System\eim folder with only one marker logged into the S_DOCK_TXN_LOG table. When this parameter is set to FALSE, EIM will use set-based transaction logging.
  8. Review Siebel remote administration reports.

    In Siebel applications version 7.5.2 and later, there are several reports available to help you monitor your Siebel remote environment. These reports are detailed in the Siebel Remote and Replication Manager Administration Guide: Monitoring and Reporting for Siebel Remote

    The following table lists the reports and the views where you can find them:

    Report View Where Available
    Mobile Users Status Report Client Status and Mobile User Summary views
    Transaction Processor Status Report Processor Status view
    Transaction Backlog Status Report Transaction Log view
    Mobile User Synchronization Frequency Report Synchronization Sessions view
    Mobile User Synchronization Session Report Synchronization Sessions view
    Regional Node Status Report Client Status view
    Active Mobile Users Usage Reports (Graph and Table) Synchronization Sessions view
    Transaction Router Backlog Report Transaction Log view
    Outbox Transactions Report Client Status view

If you need more help, log a service request with Oracle Support.