JR47093: REPORT SQL0901N (REASON "BAD OPCODE -9, REF_ARITY 3, SQLNQ_PID ID 941 (SYSIBM.DJ_RPAD)".)

DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• Env:
  Federation server: v10.1fp2 (non-Oracle mode)
  data source: v10.1fp2 DPF (Oracle mode)
  
  Problem summary:
  The following query failed with:
  
  WITH
  OCENA41(CUSTOMERID1, ACCOUNTID1,  AMOUNT41, RATING41)
  AS
  (
  SELECT A.CUSTOMER_ID, A.ACCOUNT_ID, B.COVERAGE_AMOUNT, '100 '
  
  FROM IFRS.HGAA4_MNTH_NBS_ACCOUNT_RATING A
  INNER JOIN IFRS.T_D_CUSTOMER_ACCOUNT_N CA
  ON A.ACCOUNT_ID = CA.ORIGINAL_SOURCE_ID
  LEFT OUTER JOIN IFRS.T_AG_NBS_COLLATERAL_ALGORITHM_SUMMARY B
  ON CA.CUSTOMER_ACCOUNT_SID = B.CUSTOMER_ACCOUNT_SID
  
  INNER JOIN IFRS.T_D_SECURITY_ADEQUACY_SEGMENT C
  ON B.SECURITY_ADEQUACY_SEGMENT_SID =
  C.SECURITY_ADEQUACY_SEGMENT_SID
  AND C.SECURITY_ADEQUACY_SEGMENT_CD = '1'
  
   WHERE A.YEAR = '2013'
    AND  A.MONTH = '04'
    AND  B.COVERAGE_AMOUNT > 0
  
   ),
   OCENA42(CUSTOMERID2, ACCOUNTID2,  AMOUNT42,  RATING42)
  AS
  (
  SELECT A.CUSTOMER_ID, A.ACCOUNT_ID, B.COVERAGE_AMOUNT,
      CASE
        WHEN (RATING3 = '100 ' OR RATING3 = '202 ')  THEN '100 '
        WHEN  RATING3 = '315 '  THEN '202 '
        WHEN  RATING3 = '430 '  THEN '315 '
        WHEN  RATING3 = '5100'  THEN '430 '
     ELSE '    '
     END
  FROM IFRS.HGAA4_MNTH_NBS_ACCOUNT_RATING A
  INNER JOIN IFRS.T_D_CUSTOMER_ACCOUNT_N CA
  ON A.ACCOUNT_ID = CA.ORIGINAL_SOURCE_ID
  LEFT OUTER JOIN IFRS.T_AG_NBS_COLLATERAL_ALGORITHM_SUMMARY B
  ON CA.CUSTOMER_ACCOUNT_SID = B.CUSTOMER_ACCOUNT_SID
  
  INNER JOIN IFRS.T_D_SECURITY_ADEQUACY_SEGMENT C
  ON B.SECURITY_ADEQUACY_SEGMENT_SID =
  C.SECURITY_ADEQUACY_SEGMENT_SID
  AND C.SECURITY_ADEQUACY_SEGMENT_CD = '2'
  
   WHERE YEAR = '2013'
    AND MONTH = '04'
    AND  B.COVERAGE_AMOUNT > 0
     ),
  OCENA43(CUSTOMERID3, ACCOUNTID3,  AMOUNT43, RATING43)
  AS
  (
  SELECT A.CUSTOMER_ID, A.ACCOUNT_ID, B.COVERAGE_AMOUNT,
  A.RATING3
  
  FROM IFRS.HGAA4_MNTH_NBS_ACCOUNT_RATING A
  INNER JOIN IFRS.T_D_CUSTOMER_ACCOUNT_N CA
  ON A.ACCOUNT_ID = CA.ORIGINAL_SOURCE_ID
  LEFT OUTER JOIN IFRS.T_AG_NBS_COLLATERAL_ALGORITHM_SUMMARY B
  ON CA.CUSTOMER_ACCOUNT_SID = B.CUSTOMER_ACCOUNT_SID
  
  INNER JOIN IFRS.T_D_SECURITY_ADEQUACY_SEGMENT C
  ON B.SECURITY_ADEQUACY_SEGMENT_SID =
  C.SECURITY_ADEQUACY_SEGMENT_SID
  AND C.SECURITY_ADEQUACY_SEGMENT_CD = '3'
   WHERE YEAR = '2013'
    AND MONTH = '04'
    AND  B.COVERAGE_AMOUNT > 0
   ),
  SVE(CUSTOMERIDS, ACCOUNTIDS,  AMOUNT41S, RATING41S, AMOUNT42S,
  RATING42S
  )
  AS
  (
  SELECT X.CUSTOMERID1, X.ACCOUNTID1, X.AMOUNT41, X.RATING41,
  Y.AMOUNT42, Y.RATING42
  
  FROM OCENA41 X
  INNER JOIN OCENA42 Y
  ON X.ACCOUNTID1 = Y.ACCOUNTID2
  )
  
  select * from SVE;
  
  DB2 Database Error: ERROR [58004] [IBM][DB2/LINUXX8664] SQL0901N
  The SQL statement failed because of a non-severe system error.
  Subsequent SQL statements can be processed.  (Reason "Bad Opcode
  -9, ref_arity 3, sqlnq_pid ID 941 (SYSIBM.DJ_RPAD)".)
  
  Note: query joins 1 local table an 3 federated tables (over
  nicknames):
  
  - IFRS.HGAA4_MNTH_NBS_ACCOUNT_RATING - local table
  - IFRS.T_D_CUSTOMER_ACCOUNT_N - nickname
  - IFRS.T_AG_NBS_COLLATERAL_ALGORITHM_SUMMARY - nickname
  - IFRS.T_D_SECURITY_ADEQUACY_SEGMENT - nickname
  

Local fix

• Change function mapping from SYSIBM.DJ_RPAD/RPAD to
  SYSIBM.RPAD/RPAD for udb
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * version:v97/v101                                             *
  * datasource:oracle/udb/as400                                  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to v97fp9/v101fp3                                    *
  ****************************************************************
  

Problem conclusion

• v97fp9/v101fp3 will contain this fix, SYSIBM.RPAD will map to
  RPAD for udb/oracle/as400 datasource
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  JR47218 IC94036

Fix information

• Fixed component name

  FEDERATED RUNTI

• Fixed component ID

  5724N9703

Applicable component levels

• RA10 PSN

     UP

• RA10 PSY

     UP