Resolving Transaction Router Issues After Truncating the Transaction Log

FROM SIEBEL SUPPORT WEB
========================
Service Request #: 38-974910551
Area: Siebel Remote
Product Release: V6 (Siebel 2000-Enterprise)
Version: 6.0.1.38 [2998]
Database: Microsoft SQL Server 2000 SP 2
Application Server OS: Microsoft Windows 2000 Advanced Server SP 2
Database Server OS: Microsoft Windows 2000 Advanced Server SP 2
Status: Resolved
GMT Date Opened: 04/07/2003 07:32:37
Abstract: Transactions not getting routed to mobile clients
Description:
Mobile Clients are not getting any Transactions routed to them.

---

The customer historically had Siebel Remote disabled through setting the Docking Transaction Logging System Preference to FALSE but had recently added a number of mobile users to the Enterprise. They re-enabled transaction logging within the Enterprise by setting the parameter to TRUE but having done this noted that none of the transactions which were being logged within the Enterprise were being sent out to the userbase.

The first step in this scenario was to make a review of the log files being produced by the two components involved in the routing of transactions, the Transaction Processor and Transaction Router. When a review of the Transaction Processor log was made it was identified that the component was repeatedly issuing DELETE statements against the master transaction log table (S_DOCK_TXN_LOG) :

SQL SQL 4 2003-04-08 21:06:10 2003-04-08 21:06:10 Statement:
SQL SQL 4 2003-04-08 21:06:10
delete from dbo.S_DOCK_TXN_LOG
where TXN_ID = {fn convert(?, SQL_NUMERIC)}
SQL SQL 4 2003-04-08 21:06:10 01:1962729
SQL SQL 4 2003-04-08 21:06:10 Parse and Execution Time (only)
[SQL01] Total Rows: 1 Total Time Parse: 0s Execute: 0s Avg Time: 0s
SQL SQL 4 2003-04-08 21:06:10

After examining this statement and the various routing details available through the Siebel Remote Administration screens it was identified that the transactions which were being generated by the Enterprise had a lower TXN_ID than those which had historically been routed. The Transaction Processor maintains a 'LOWSCANMARK' value which indicates the earliest TXN_ID value which is needed by the Enterprise and therefore deletes anything below this value. When new transactions are generated the system sets the TXN_ID to the next value from an object which differs for various databases. In the case of Oracle the TXN_ID is generated from a sequence object S_DOCK_TXN_LOG_S, whilst in the case of Microsoft SQL Server and DB2 the TXN_ID is based on an IDENTITY column.

At some point in time the TXN_ID column's value (created automatically on Insert as this was a Microsoft SQL Server environment and therefore an IDENTITY column) had been reset leading to new transactions having a value below the LOWSCANMARK. These were therefore removed immediately by the Transaction Processor.

In order to resolve this behavior it was necessary to reset the value of the TXN_ID column. This process differs for each database management system. In this case it was necesary to reset the IDENTITY column to a value higher than the LOWSCANMARK.

Please run the following query to determine the LOWSCANMARK values for Transaction Processors in your Siebel Enterprise :

select n.name, ds.NODE_ID "Processor node id", substr(addtl_info, 1, 50) "Low Scan Mark Value" from s_dock_status ds, s_node n
where ds.type='ROUTE'
and ds.local_flg='Y'
and ds.node_id = n.row_id
and n.node_type_cd='TXNPROC';

Having identified the LOWSCANMARKS please ensure that the value is higher than these in the steps below. In order to resolve this behavior in a Microsoft SQL Server environment please follow the steps outlined below :

1) Have all of the mobile clients synchronize with the Server to download any outstanding transactions
2) Wait until all %SIEBEL_SERVER_ROOT%\DOCKING\\Inbox directories are empty, indicating all transactions have been applied to the Server
3) Shutdown the Siebel Enterprise to ensure that new data is not written into the transaction log
4) Run a DBCC CHECKIDENT(S_DOCK_TXN_LOG,NORESEED). This will return the current value of the IDENTITY column and the max value from the table. Make a note of these
5) Run a DBCC CHECKIDENT(S_DOCK_TXN_LOG,RESEED,)
6) Restart the Siebel Enterprise but at the moment restrict login to yourself
7) Re-extract the mobile users
8) Have them initialize and pickup the new extract
9) Check that newly created transactions in S_DOCK_TXN_LOG have a TXN_ID of >=. These should then be written out to .dx files in the \TXNPROC directory
10) Enterprise should then be fully available.

---

Keywords : CHECKIDENT, IDENTITY, RESEED, TxnProc, Processor, routing, clients, LOWSCANMARK