IBM DB2 IP2016 IRM Error Message (SQL0440N No authorized routine named "SUBSTRING") (Doc ID 2210233.1)

Last updated on DECEMBER 19, 2016

Applies to:

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

Symptoms

On : 8.1.1.9 [23016] version, Upgrade

When running IRM (upgrep) upgrade from 8.1.1.9 to 16.0
the following error occurs.

ERROR
-----------------------
DBCLog DBCLogError 1 00000002582d017c:0 2016-11-17 13:45:57 SQL Warning, SQL State 42884, -440, [IBM][CLI Driver][DB2/AIX64] SQL0440N No authorized routine named "SUBSTRING" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

SQLError Statement 0 00000002582d017c:0 2016-11-17 13:45:57 SQL Statement:
  insert into S_ISS_VALDN_MSG ( ROW_ID , CREATED , CREATED_BY , LAST_UPD , LAST_UPD_BY , MODIFICATION_NUM , CONFLICT_ID , DB_LAST_UPD , DB_LAST_UPD_SRC , MSG_SRC_TYPE_CD , MSG_TYPE_CD , MSG_TEXT , MSG_LVL_CD ) SELECT VALDN_RULE.ROW_ID , VALDN_RULE.CREATED , VALDN_RULE.CREATED_BY , VALDN_RULE.LAST_UPD , VALDN_RULE.LAST_UPD_BY , VALDN_RULE.MODIFICATION_NUM , VALDN_RULE.CONFLICT_ID , {fn now ()} , 'Data Migration' , {fn substring(RULE_SET.NAME, 1, 30)} , {fn concat (VALDN_RULE.RETURN_CD, substring({fn CONVERT(VALDN_RULE.SEQ_NUM,SQL_CHAR)},1,30))} , VALDN_RULE.ERR_MSG_TXT , CASE WHEN VALDN_RULE.BUSCOMP_NAME IN ('Quote','Quote Item') THEN 'Quote' WHEN VALDN_RULE.BUSCOMP_NAME IN ('Order Entry – Orders','Order Line Items') THEN 'Order' WHEN VALDN_RULE.BUSCOMP_NAME IN ('Service Agreement','FS Agreement Item') THEN 'Agreement' ELSE 'Other' END FROM S_VALDN_RULE VALDN_RULE, S_VALDN_RL_SET RULE_SET WHERE VALDN_RULE.RULE_SET_ID = RULE_SET.ROW_ID AND VALDN_RULE.VALDN_MSG_ID IS NULL AND VALDN_RULE.ERR_MSG_TXT IS NOT NULL AND NOT EXISTS ( SELECT 'x' FROM S_ISS_VALDN_MSG VALDN_MSG WHERE (VALDN_MSG.MSG_TYPE_CD = VALDN_RULE.RETURN_CD OR VALDN_MSG.MSG_TYPE_CD = {fn concat(VALDN_RULE.RETURN_CD, substring({fn CONVERT(VALDN_RULE.SEQ_NUM,SQL_CHAR)},1,30))}) AND VALDN_MSG.MSG_TEXT = VALDN_RULE.ERR_MSG_TXT AND VALDN_MSG.MSG_SRC_TYPE_CD = {fn substring(RULE_SET.NAME, 1, 30)} ) AND RULE_SET.ROW_ID = (SELECT MAX (VRS.ROW_ID) FROM S_VALDN_RL_SET VRS, S_VALDN_RULE VR1 WHERE VR1.RULE_SET_ID=VRS.ROW_ID AND VR1.VALDN_MSG_ID IS NULL AND VR1.ERR_MSG_TXT IS NOT NULL AND {fn substring(VRS.NAME,1,30)} = {fn substring(RULE_SET.NAME,1,30)} AND VR1.RETURN_CD=VALDN_RULE.RETURN_CD AND VR1.SEQ_NUM=VALDN_RULE.SEQ_NUM )
DBCLog DBCLogError 1 00000002582d017c:0 2016-11-17 13:45:57 [IBM][CLI Driver][DB2/AIX64] SQL0440N No authorized routine named "SUBSTRING" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

UpgradeLog UpgradeError 1 00000002582d017c:0 2016-11-17 13:45:57 [IBM][CLI Driver][DB2/AIX64] SQL0440N No authorized routine named "SUBSTRING" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

UpgradeLog UpgradeError 1 00000002582d017c:0 2016-11-17 13:45:57 Error in function UTLOdbcExecDirect
UpgradeLog UpgradeError 1 00000002582d017c:0 2016-11-17 13:45:57 Error executing sql statement (




STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Open a command window (Shell)
2. Stop all Siebel Servers.
3. Stop the Siebel Gateway Name Server.
4. Change directory to $SIEBEL_ROOT
5. Source the environment variables from the siebsrvr root directory: install_location/
siebsrvr:
Korn shell: . siebenv.sh
C shell: source siebenv.csh

6. Start the Database Configuration Wizard:
install_location/config/config -mode dbsrvr
7. Select upgrep

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, the Siebel 16.x upgrade cannot continue

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