Siebel Remote: TxnProc Fails due to DELETE Statements on DB2 - 'CLI0111E Numeric value out of range. SQLSTATE=22003' (Doc ID 1964315.1)

Last updated on JULY 11, 2017

Applies to:

Siebel CRM - Version 8.1.1 [21112] and later
Information in this document applies to any platform.

Symptoms

Siebel Remote - on version 8.1.1.4 SIA [21225]:

On Production environment running on IBM DB2 database, all TxnProc comps on all Remote servers are failing when trying to cleanup already processed txns for S_DOCK_TXN_SET table:

GenericLog GenericError 1 00000009530c21ec:0 2014-09-25 12:58:11 Message: Error: An ODBC error occurred,
Additional Message: Function: UTLLDelMASSTxnDel; ODBC operation: SQLExecute

SQLError Statement 0 00000009530c21ec:0 2014-09-25 12:58:11 SQL Statement:
  delete from SIEBEL.S_DOCK_TXN_SET
   where TXN_LOG_ID in
    (select ROW_ID from SIEBEL.S_DOCK_TXN_LOG
       where TXN_ID <= ? - 1
         and OPERATION in ('X', 'Y', 'Z', 'R', 'T', 'Q', 'L'))
SQLError Bind Variables 1 00000009530c21ec:0 2014-09-25 12:58:11 01:2161461782

DBCLog DBCLogError 1 00000009530c21ec:0 2014-09-25 12:58:11 [IBM][CLI Driver] CLI0111E  Numeric value out of range. SQLSTATE=22003

GenericLog GenericError 1 00000009530c21ec:0 2014-09-25 12:58:11 Message: Error: An ODBC error occurred,
Additional Message: pfNativeError: -99999; szSQLState: 22003; szErrorMsg: [IBM][CLI Driver] CLI0111E  Numeric value out of range. SQLSTATE=22003
...

GenericLog GenericError 1 00000009530c21ec:0 2014-09-25 12:58:11 (dlogdbreader.cpp (104) err=3006 sys=2) SBL-GEN-03006: Error calling function: UTLDLogDbReadTxn
...
GenericLog GenericError 1 00000009530c21ec:0 2014-09-25 12:58:11 (lppmain.cpp (741) err=1114259 sys=0) SBL-DCK-00147: Error reading transaction from database.

 

At some later point during troubleshooting, it was found that similar errors could be seen for another DELETE statement:

GenericLog GenericError 1 0000003853142858:0 2014-10-03 13:19:52 Message: Error: An ODBC error occurred,
Additional Message: Function: UTLLDelMASSTxnDel; ODBC operation: SQLExecute

SQLError Statement 0 0000003853142858:0 2014-10-03 13:19:52 SQL Statement:
  delete from SIEBEL.S_DOCK_TXN_LOG
   where TXN_ID <= ? - 1
SQLError Bind Variables 1 0000003853142858:0 2014-10-03 13:19:52 01:2172408713

DBCLog DBCLogError 1 0000003853142858:0 2014-10-03 13:19:52 [IBM][CLI Driver] CLI0111E  Numeric value out of range. SQLSTATE=22003

 

Same SQL statements are running fine if executed directly on DB2 client.


Impact of this issue: TxnProc comps are failing due to this issue, DX files backlog accumulating in TXNPROC folders.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms