Querying for Orphan Records

To examine orphan records in the Siebel database, you can use an SQL query. This query locates rows in the S_DOC_TXN_LOG table that meet any of the following conditions:

• Row ID is not equal to the parent transaction ID

• The parent transaction ID does not equal the row ID of any other row in the table

To query for orphan records

1. Query for the orphaned records:

   • To query in Oracle Database, use the following command:

     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
     

   • To query in IBM DB2 or Microsoft SQL Server, use the following command:

     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
     

2. If you find a gap in transaction ID numbers, then use the Txnskip utility.

   An orphan record does not necessarily cause a gap in transaction ID numbers. For situations where you find a gap, you can use the Txnskip utility instead of the Endtxnutl utility. For more information, see Using the Txnskip Utility to Examine Skipped Transactions.