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 |
