Attempting to run ssolite stored procedures on a 64-bit Linux DB2 server fails with a SQLSTATE=42724 error code with a reason code of 5

Attempting to run ssolite stored procedures on a 64-bit Linux DB2 server fails with a SQLSTATE=42724 error code with a reason code of 5

book

Article ID: KB0094531

calendar_today

Updated On:

Products Versions
TIBCO iProcess Engine (DB2) -
Not Applicable -

Description

Resolution:
The following is an example of the full error received:

SQL0444N  Routine "*EQ_TRANS" (specific name "SW_GET_SEQ_TRANS") is
implemented with code in library or path ".../function/ssolite", function
"ssolite_get_sequence" which cannot be accessed.  Reason code: "5".  
SQLSTATE=42724

The reason for this error is the ssolite64.so file included with the iProcess engine binaries is actually compiled as a 32-bit application.  If you look at the db2diag log, you will see errors of:

2009-06-12-21.24.23.851144-300 E7189967E511        LEVEL: Error (OS)
PID     : 6712                 TID  : 1079040320   PROC : db2fmp (
INSTANCE: db2inst1             NODE : 000
FUNCTION: DB2 UDB, oper system services, sqloLoadModule, probe:190
CALLED  : OS, -, dlopen
DATA #1 : Library name or path, 38 bytes
/home/db2inst1/sqllib/function/ssolite
DATA #2 : shared library load flags, PD_TYPE_LOAD_FLAGS, 4 bytes
0
DATA #3 : String, 67 bytes
/home/db2inst1/sqllib/function/ssolite: wrong ELF class: ELFCLASS32

To resolve the issue, drop the existing SW_GET_SEQ_TRANS stored procedure and replace it with the following (remember to change TABLE_OWNER with your iPE database background user):

CREATE PROCEDURE TABLE_OWNER.SW_GET_SEQ_TRANS
(
         IN     @szTableOwner       VARCHAR(48), -- table owner
         IN     @nSeqType      INTEGER,         -- Type of sequence to retrieve reqid is 1 and casenum is 2
         IN     @nSeqSize       INTEGER,         -- Size of cache for sequences
         OUT @nSeqValue    BIGINT,              -- return for sequence number
         OUT     @szSQLState     VARCHAR(33),    -- on error holds the SQL state
         OUT     @szSQLErr       VARCHAR(257)  -- on error holds the SQL error message
)
SPECIFIC SW_GET_SEQ_TRANS
DYNAMIC RESULT SETS 0
NOT DETERMINISTIC
LANGUAGE C
PARAMETER STYLE SQL
NO DBINFO
FENCED
NOT THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'ssolite!ssolite_get_sequence'

Once modified, you should be able to successfully use ssolite stored procedures that call SW_GET_SEQ_TRANS.

Issue/Introduction

Attempting to run ssolite stored procedures on a 64-bit Linux DB2 server fails with a SQLSTATE=42724 error code with a reason code of 5